Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Building a Security Philosophy

By Andy Warren,

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

Total article views: 4525 | Views in the last 30 days: 2
 
Related Articles
ARTICLE

Stairway to SQL Server Indexes: Level 12, Create Alter Drop

Options and impacts when creating, altering and dropping an index

FORUM

Alter Database with variable

Alter Database with variable

ARTICLE

Moving Database Files Detach/Attach or ALTER DATABASE?

Learn why ALTER DATABASE should be preferred over Detach/Attach for moving database files on the sam...

BLOG

ALTER SCHEMA

Use this select statement to create a list of ALTER SCHEMA statements for all stored procedures in a...

FORUM

Altering SP's in Different Servers

Altering SP's in Different Servers

Tags
security    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones