Building a Security Philosophy

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.

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.

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