Role-based SQL Server Security

Mike Wolfe -

One of the big advantages of using SQL Server to store your data is the improved security versus Access files.  You can set up very granular permissions.  You can grant users INSERT-only access to a change history table, SELECT-only access to a reporting table, and DELETE-only access to an ... um ... OK, I can't think of an example of a table where you'd want to grant users DELETE-only access, but just know that if you wanted to do that--for whatever reason--you could do it.

The possibilities are truly limitless if you incorporate stored procedures into your design.  You could grant users SELECT-only permission to view customer orders placed in the first quarter of the previous year by customers without the letter "E" in their names.  You know, if you need to cater to a particularly eccentric accountant who's really into pangrammatic lipography.

The risks of granular control

All of this power comes with great responsibility.  Namely, how does one manage it all?  

One of the earliest and largest applications I worked on had dozens of tables.  We granted read and write privileges at the table-level.  We used Windows Authentication.  We granted the rights to Active Directory security groups instead of to individual users.  So far, so good.

The problem?  

We granted these table-level permissions--dozens of permissions--directly to the security groups themselves.  This seemed safe, since there were only a couple of departments that needed access to the database in the beginning.  But then other departments needed access.  We had to decide which of those table-level permissions to grant to the new department.  This was annoying, but still relatively manageable.

And then we sold the application to a second client.

Obviously, the permissions we had set up did not transfer to the new client.  The new client had their own Windows domain.  To make things worse, we realized that, moving forward, any change in permissions for one client would have to be manually applied to security groups at the other client.  There was no way to automate permissions changes.

Such an approach was unsustainable.  We needed a better approach.

Role-based Access Control

Role-based access control (RBAC) is an important concept in computer security.  The idea is to introduce a layer of separation between a system's permissions and the users of that system.

RBAC is beneficial even if the role only comprises a single user.  

In Active Directory, you implement RBAC with security groups. For example, consider a small bank with a single Compliance Officer.  This individual has a network folder full of files related to regulatory compliance.  Your first instinct may be to grant the user access to this folder directly.  After all, she is the only employee who will need such access.

A better approach is to create a "Compliance" security group, add the individual as the sole member of the group, and then grant permissions to the "Compliance" security group and not to the individual serving as compliance officer.

This may seem like needless administrative overhead; the sort of thing for which Dilbert would offer a pithy burn.  But there are many benefits to this approach:

  • Personnel turnover: when the bank replaces the compliance officer, it requires no changes in the permission structure; simply remove the previous individual from the Compliance security group and add the new officer in her place
  • Temporary absence: compliance officer calls in sick one day?  assigning someone to cover the role is as simple as adding them to the security group and then removing them when the full-time compliance officer returns to work
  • Handle vacancies: you could write a script to copy permissions from the old compliance officer to the new one; but what if there is a gap between officers and the new employee is not in the system when the old employee is removed?

SQL Server Roles

In SQL Server, you implement RBAC with roles.  For application data security, you specifically use database-level roles.  In addition to the usual benefits you gain from RBAC generally, you also gain some benefits that help specifically with the Microsoft Access software development process.

Why not Windows security groups?

Active Directory security groups provide all the benefits of RBAC.  So why not assign database permissions directly to security groups?  I can think of several reasons SQL Server roles are worth implementing even if you use Windows Authentication and security groups to provide SQL Server data access.

Development-environment friendly

When developing Access applications professionally, you should always have at least two environments: development and production.  With SQL Server roles, you can configure all of your permissions--down to the very last detail--in your development environment.  You can then thoroughly test the permissions before deploying them to production.

Version-control friendly

The T-SQL required to configure SQL Server roles is straightforward.  Here's a quick sample:

CREATE ROLE [TaxClaimChief];
CREATE ROLE [TaxClaimClerk];

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
  ON [dbo].[CertMailing] TO [TaxClaimChief];

GRANT SELECT 
  ON [dbo].[CertMailing] TO [TaxClaimClerk];

This means you can easily integrate database permissions configuration with the rest of your database schema changes.  And, since permissions are a common source of bugs ("It works on my computer!"), any change to the permission structure of your database belongs in version control:

2. Anything that can lead to a bug in our software belongs in version control.

Pre-staged permissions

Assigning permissions directly to users can feel a little like a chicken and egg problem.  How do you assign permissions during development, when you're not sure which users will ultimately be using the system?  On the other hand, you don't want to roll out your software before you've had a chance to set up the permission structure.

By using SQL Server roles, you can set up the permission structure long before you know who the users will be.  To prepare the production database for first use, then, you just need to assign users to the pre-configured database roles.

Security requirements differences

There may be differences between the members of an Active Directory security group and a SQL Server database role.  It's not uncommon for all members of a department to belong to a single security group, but require different permissions within the database.  Relying on windows security groups to always match up perfectly with your database roles is a recipe for future pain.  In fact, I should probably add it to my list of sources of technical debt.

Besides, if the roles do overlap perfectly, it's easy enough to simply assign the security group to the SQL Server role.  This is a small price to pay for the peace of mind that future changes to the Windows security group won't have a direct effect on your database's permissions scheme.

Multi-tenant friendly

How do you assign database permissions to Active Directory security groups in a different domain than the one to which you are currently joined?  This was the original challenge we faced earlier in the article after we sold our application to a second client.  The short answer? You don't.  The longer answer--as you might have guessed--is to use SQL Server database roles.

Simply create the database roles and then let each client decide how they should assign users to those roles.  If you are clear about the purpose of each role, you will be able to continue refining the security around these roles even as you deploy new updates to your clients.

Image by Michel van der Vegt from Pixabay