SQL SERVER – Say No to DB Data Roles – SQL Security – Notes from the Field #022

I am prejudiced against two fixed database roles: db_datareader and db_datawriter. When I give presentations or talk to customers, some are surprised by my stance. I have two good reasons to recommend against these two roles (and their counterparts, db_denydatareader and db_denydatawriter).

A Violation of the Principle of Least Privilege

The first reason is they violate the Principle of Least Privilege. If you’re not familiar with this security principle, it’s really simple: give permissions to do the job – no more and no less. The db_datareader and db_datawriter roles give access to all tables and views in a given database. Most of the time, this is more access than what is needed. This is a violation of the Principle of Least Privilege.

There are some cases where a user needs such access, but there is always the possibility that a new table or view will be added which the user should not have access to. This creates a dilemma: do I create new roles and remove the user from db_datareader or db_datawriter or do I start using DENY permissions? The first involves additional work.The second means the security model is more complex. Neither is a good solution.

Failing the 3 AM Test

The second reason is the use of these roles violates what I call the “3 AM test.” The 3 AM test comes from being on call. When I am awakened at 3 AM because of a production problem, is this going to cause me unnecessary problems? If the answer is yes, the solution fails the test. I classify db_datareader and db_datawriter role usage as failing this test. Here’s why: the permissions granted are implicit. As a result, when I’m still trying to wake up I may miss that a particular account has permissions and is able to perform an operation that caused the problem. I’ve been burned by it in production before. That’s why it fails my test.

An Example

To see why this is an issue, create a user without a login in a sample database. Make it a member of the db_datareader role. Then create a role and give it explicit rights to a table in the database. This script does so in the AdventureWorks2012 database:

USE AdventureWorks2012;
GO
CREATE USER TestDBRoleUser WITHOUT LOGIN;
GO
EXEC sp_addrolemember @membername = 'TestDBRoleUser', @rolename = 'db_datareader';
GO
CREATE ROLE ExplicitPermissions;
GO
GRANT SELECT ON HumanResources.Employee TO ExplicitPermissions;
GO



Pick any table or view at random and check the permissions on it. I’m using HumanResources.Employee:

We see the permissions for the role with explicit permissions. We don’t, however, see the user who is a member of db_datareader. When first troubleshooting it’s easy to make the assumption that the user doesn’t have permissions. This assumption means time is wasted trying to figure out how the user was able to cause the production problem. Only later, when someone things to check db_datareader, will the root cause be spotted. This is why I say these roles fail the 3 AM test.