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.

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. Linchpin People are database coaches and wellness experts for a data driven world. 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;
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.

If you want to get started with performance tuning and database security with the help of experts, read more over at Fix Your SQL Server.

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

About these ads

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

  1. 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?

    Like

  2. 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.

    Like

  3. Pingback: SQL SERVER – Difference Between Login and User – SQL in Sixty Seconds #070 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s