I think we all should have some type of base philosophy about security that
keeps on us track during busy weeks. Some of us are lazy about it, some haven't
thought about it, some would protect the data so well no one could use it! A
full discussion of security could easily fill a book, so let's narrow it down a
bit for this discussion by looking at what rights you give to non sysdamin
users. These comments apply to production and usually QA servers, not
development servers.
I start with a pretty simple approach; you're either a sysadmin or you're
nobody! I like the concept of junior/assistant DBA's to handle the workload, but
in practice they don't get the job unless I trust them. So even though we have a
number of fixed server roles (listed below) I don't use them. I'm not suggesting
they be removed from the product as they may be useful in some scenarios, but in
my career I've never had a reason to use them. I don't see any point in giving
someone just processadmin for example, how often is there a need for a person
you only trust to kill spids that run wild? The one temporary exception at the
server level is to let someone temporarily have permissions to run a trace
(ALTER TRACE) which was added in SQL 2005, prior to that they had to be a
sysadmin.
| bulkadmin | Granted: ADMINISTER BULK OPERATIONS |
| dbcreator | Granted: CREATE DATABASE |
| diskadmin | Granted: ALTER RESOURCES |
| processadmin | Granted: ALTER ANY CONNECTION, ALTER SERVER STATE |
| securityadmin | Granted: ALTER ANY LOGIN |
| serveradmin | Granted: ALTER ANY ENDPOINT, ALTER RESOURCES, ALTER SERVER STATE, ALTER SETTINGS, SHUTDOWN, VIEW SERVER STATE |
| setupadmin | Granted: ALTER ANY LINKED SERVER |
| sysadmin | Granted with GRANT option: CONTROL SERVER |
Equally, I don't assign anyone to any of the fixed roles on a permanent
basis. There may be times when I temporarily grant someone deeper access to a
single database and in most cases I'll put them in db_owner, supplementing that
with a warning to them to not screw things up! In particular I don't use
db_datareader/db_datawriter, preferring to explicitly grant permissions in all
cases and almost always preferring to gate access using stored procedures.
| Fixed Database Role | Database-level Permission | Server-level Permission |
|---|---|---|
| db_accessadmin | Granted: ALTER ANY USER, CREATE SCHEMA | Granted: VIEW ANY DATABASE |
| db_accessadmin | Granted with GRANT option: CONNECT | |
| db_backupoperator | Granted: BACKUP DATABASE, BACKUP LOG, CHECKPOINT | Granted: VIEW ANY DATABASE |
| db_datareader | Granted: SELECT | Granted: VIEW ANY DATABASE |
| db_datawriter | Granted: DELETE, INSERT, UPDATE | Granted: VIEW ANY DATABASE |
| db_ddladmin | Granted: ALTER ANY ASSEMBLY, ALTER ANY ASYMMETRIC KEY, ALTER ANY CERTIFICATE, ALTER ANY CONTRACT, ALTER ANY DATABASE DDL TRIGGER, ALTER ANY DATABASE EVENT, NOTIFICATION, ALTER ANY DATASPACE, ALTER ANY FULLTEXT CATALOG, ALTER ANY MESSAGE TYPE, ALTER ANY REMOTE SERVICE BINDING, ALTER ANY ROUTE, ALTER ANY SCHEMA, ALTER ANY SERVICE, ALTER ANY SYMMETRIC KEY, CHECKPOINT, CREATE AGGREGATE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE QUEUE, CREATE RULE, CREATE SYNONYM, CREATE TABLE, CREATE TYPE, CREATE VIEW, CREATE XML SCHEMA COLLECTION, REFERENCES | Granted: VIEW ANY DATABASE |
| db_denydatareader | Denied: SELECT | Granted: VIEW ANY DATABASE |
| db_denydatawriter | Denied: DELETE, INSERT, UPDATE | |
| db_owner | Granted with GRANT option: CONTROL | Granted: VIEW ANY DATABASE |
| db_securityadmin | Granted: ALTER ANY APPLICATION ROLE, ALTER ANY ROLE, CREATE SCHEMA, VIEW DEFINITION | Granted: VIEW ANY DATABASE |
Within the database I create one or two roles per application depending on
how it's being built. For example, for most web applications everything will run
under a single account, so I'll create a role called MyWebApp, add the
login/user to that role, and then grant the appropriate access. Having the role
allows me to add - if needed - a developer so that they can debug using the
exact same permissions. In some cases I'll set up a secondary role called
MyWebAppRO, the RO indicating it's a read only role and I use it for any type of
dynamic search that might have a sql injection vulnerability.
As far as logins, I prefer to use Windows groups whenever possible, starting
with a base DBA group that I assign to the SQL sysadmin role. Using groups
reduces the time I spend administering and of course using NT authentication
lets me leverage the strengths of AD; requiring strong passwords, account
lockout, etc. For applications I prefer that the connection be done using a
single service account (unless we need a separate one that will be for read
only) and I load these individually, not as a group. Ideally that service
account is used only on an application server/web server. If a sql login is used
- and this is common for desktop client/server apps - I also set up a separate
login per application, and try to make sure that the developers have been very
careful about how they store/retrieve the password for the account.
Is that overkill? Am I missing out on the possibilities that the built in
roles offer? Or do you agree with my philosophy?
Visit my blog at http://blogs.sqlservercentral.com/andy_warren/default.aspx
