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

[Note from Pinal]: This is a 22nd episode of Notes from the Field series. Security is very important and we all realize that. However, when it is about implementing the security, we all are not sure what is the right path to take. If we do not have enough knowledge, we can damage ourself only. DB Data Roles are very similar concept, when implemented poorly it can compromise your server security.

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

In this episode of the Notes from the Field series database expert Brian Kelley explains a very crucial issue DBAs and Developer faces on their production server. Read the experience of Brian in his own words.

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;
EXEC sp_addrolemember @membername = 'TestDBRoleUser', @rolename = 'db_datareader';
CREATE ROLE ExplicitPermissions;
GRANT SELECT ON HumanResources.Employee TO ExplicitPermissions;

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

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

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.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
Developer – How To Market Yourself as a Software Developer
Next Post
SQL SERVER – Unable to ALTER Computed Column in SQL Server – How to ALTER Computed Column

Related Posts

3 Comments. Leave new

  • I have to beg difference and would say this post should NOT be considered as a suggestion applicable to most environments.

    Let’s first look at the first reason “A Violation of the Principle of Least Privilege”, which in reality, is not necessarily true. The most common case is there are a few key tables (or may e just a few columns in a few tables) that probably need to be kept “secret”. In this case, the best way is to use db_datareader plus a specific set of denial rules applied to database principals (who belong to db_datareader role) to achieve the security requirement as denial always overrides permission.
    That’s why for most SSRS reports, we define the data source access account as db_datareader role and for some absolute “sensitive” tables, we deny it to all except for a specific database role.

    Let’s now look at the 2nd reason, “Failing the 3 AM Test”, I have to say I am surprised that a senior DBA will rely on SSMS GUI to do any serious trouble-shooting work. For me, to do the trouble-shooting, I rely on scripts which embed a predefined set of logics specific to my environments. My scripts (either t-sql or PowerShell) may dump data to a table, which is consumed by SSRS to present the GUI, but I never use SSMS as GUI to “collect” data first.

    The bottom line is: I’d see db_datareader not as a genius invention but more as a common sense design result, and I agree “common sense” does not apply to all environments but “common sense” does apply to majority of environments, and that is “common sense” for.

    Can we conceive the sql server world without db_datareader role?

  • jxhayo –

    You raise some good questions for sure – but a couple counter points:

    1.) It sounds like you are making an assumption that most environments are like your environment where you want all users to have access to all tables. I suppose it really does depend – but I would say it has been my experience (clients in government, banking, finance, health insurance, investment firms, customer containing data, local government, etc.) that there are often specific requirements for security to be granted. In fact in any audited environment met with regulators and auditors you’ll often find the db_datareader rule a clear violation of the policy of least privilege and receive an audit ding.

    2.) About the 3AM situation – I suppose it depends on the situation. I, too, use scripts and tools and predefined steps for monitoring, alerting, troubleshooting. But if those processes have just woken me up at 3AM and I am trying to understand permissions or make quick changes? You can bet that the GUI is used. I’ve been a DBA and consultant for 15 years. I am not afraid of the GUI – but use it as just another tool int the toolbox.

    I can certainly conceive of SQL Server world without db_datareader because you could easily just roll your own explicitly with a simple granting of select to a role you create yourself. This would be an intentional decision to violate least privilege but would require a few more steps – which if you use your own scripts and logic for shouldn’t be that much work – and it would be an affirmation that “yes.. I know I am sort of violating least privilege here”.

    Good thoughts and I totally see both sides, but after going through many audits with regulators I have the principle of least privilege drilled into me fairly well so I see where Brian is coming from and tend to have my customers be very explicit and not just grant full on db_datareader to anyone because there will invariably be some table that they didn’t want those folks to read and they took the easy way out an paid by having too much access later.

  • Personally, I think having the db_datareader and db_datawriter, and a sysadmin, should be all the roles you even need in SQL Server, period – except on the rare instance where you only want to give read privileges on a certain table, in which case you only grant db_datareader to the account on a particular table, but SQL Server is not that granular – I do not believe you can limit access like that – either the account has been added to the table or it hasn’t, and it gets whatever privileges are in that account’s role.

    Skip ahead to how most applications gain access to the database. It’s through one single service account! That service account needs over-arching privileges in order to do everything the application can do – create, retrieve, update, delete. All of it. It is up to the application to enforce security – not the database, its roles, accounts, none of that. SQL security becomes silly and too encumbering when you’re talking about application development, because you do not have the luxury of doing a double-hop, from the web application to the database, to pass on the identity of the user, unless you enforce impersonation. Maybe that’s the intent, but it makes things a pain on the development side and adds needless complexity when you can just have the app handle the checks against AD, a database table, or some other mechanism for determining the user’s actual permission based on a grouping you can customize without that, by just making the service account able to do all database operations. Sure, it’s not the most secure, but no matter whether it’s the user’s account or the service account that’s hacked, either one would have the permissions to do what the other can, so I don’t really see the point of securing the service account greater. The service account is usually not “in the open”, i.e. used in any other system to expose who it is. The user’s account, however, most definitely will be, and therefore will always be more vulnerable.


Leave a Reply