Steve Jones (Blog) has taken off the co-ordinator hat and put on the hosting hat for this months TSQL Tuesday, and he’s come up with something that’s quite topical for me – Database Permissions. In the last week I’ve been in calls discussing internal permissions on our own databases, calls around managing application permissions, active directory groups and a permissions security cousins Encryption(Hint: If you are looking at a SaaS solution ask if they encrypt their databases, and then ask for proof).

The common thread is that this is not usually initiated from an application owners side, it is initiated from the DBA side. Application owners like their application to work, and it seems common that security is just an assumed thing that happens. It’s not that they don’t care about security – it’s just that they hope someone else is taking care of it. And that can easily be where it ends unless someone asks some very pointed questions about who all these people with access are and whether they really need it. So let’s start with happy land, or how I like to see things set up, and we can talk about reality later:

• There is a single connection used by the application and actual user permissions to data are handled by roles within the database.
  • Preferably this is a Windows login, but it’s no big deal if it’s a SQL login, so long as the only thing using that SQL login is the application, and the password is sensible.
  • That login should have the minimum permissions that the application needs in the database. Usually this is going to be read and write permissions, maybe it also needs to update objects depending on how your releases hang together. When the documentation says ‘sa’ account is required…..it’s usually not. Certainly past the initial install.
• Any direct access to the database is handled by windows logins, preferably with groups. Groups are something I love and hate. They are great for managing the reasons for accessing a database and assigning an equivalent to role-based access. They are less great for visibility as to who can actually see what data. When I get access to a client system it is usually at the SQL level and auditing nested AD groups to provide a list of who can actually see what is not always easy. Regardless, for any direct access least privilege is a must.
• Changes are completed under change control with a recorded reason and authorization.
• BI Developers don’t exist.

Now, in the real world you don’t often get that. At a minimum that last one is going to be tricky because Business Intelligence provides massive business benefit when done right and most applications don’t have quality reporting built in, or the reporting isn’t specific enough to your business, or it doesn’t provide the insights you are looking for. It’s not that I don’t like BI developers, it’s just that they enter a new variable in controlling my data. Specifically they want to query it in different ways than the application developers intended and usually they want to move it somewhere I no longer have control of it.

So out in the real world we don’t often see my happy land simple implementation, and instead we see a huge mish-mash of systems that talk to each other, replicate to secondary servers and have access from people all over the place. That access may or may not still be needed, or may or may not have been appropriate on the first place. My personal favourite was a server we reviewed just before Christmas that had “Domain Users” set up as a system admin. I’m sure Joe the cleaning temp enjoys knowing what his bosses make and will resist the temptation of bumping up his own salary.

If we can’t necessarily control all of the various ways that the databases are accessed, then we should at least ensure that we have visibility. This comes in two forms:

Visibility of what the current state is: Who has server level roles, who has database owner level access, and who can read or write to each database. Even if you don’t go beyond the built in server and database level roles if you have this information then you are well ahead of a good 90% of organizations out there. There are some specialized products that can help with this, or some organizations have custom solutions. Others simply export the environment permissions and keep a record in source control. Most don’t have it at all.

I want to include quite a few things in this visibility. System level role membership is a given. Database level role membership should also be relatively easy to collect. Individual permissions including allow and deny rules gets complicated, and often overlooked is comparing permissions between servers, particularly where high availability is involved. Following that, one of the key pieces of information I look for in this is ‘who has the ability to change permissions’ with the preferred answer being as few people as possible.

Visibility of changes: This is trickier, but having an alert when permissions change is very nice to have. The reality is that your SQL Server permissions just shouldn’t change that often. Ideally you set up the application and the permissions are static beyond that. New permissions should be tied to a new or changed business requirement. We have this implemented in a few places so when permissions changes are made there is an immediate alert that goes out to an administrator that includes what changed and who changed it. That means there is some nice visibility and just the fact that someone is being notified actually seems to reduce the number of incidents of random inappropriate escalations.

If you have the above two levels of visibility, then you are actually armed to start tidying up permissions or (we can hope) verifying that your current permissions structures and processes are appropriate and working well. If you do not, then getting that visibility should be a priority.