[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. 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.
Reference: Pinal Dave (https://blog.sqlauthority.com)
4 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.
Tom –
I have to disagree with you on a lot of that. I know I am reviving an old post, but I disagree on both of your points.
For point number 1 – granting permissions to the user is fine, until the user changes roles in the company. For example, you have a production assembler who needs read/write to some tables due to running tests on hardware as it is built. They do that for a few years and then their manager gets promoted and they get promoted into that manager position. Now they need more permissions. If you had a role set up for this, you just swap roles and they have all of the permissions they need on the tables,stored procedures, views, etc to do the manager position without you having to replicate permissions for the user. Now imagine you have a system with thousands of tables, views and stored procedures. I have one system (out of 70-ish that I administer) with over 5000 objects that need permissions applied to them. Not 5000 different sets of permissions, probably only about 50, but having 50 different roles is MUCH easier to mange than managing permissions on 5000 objects for 1000+ employees. That would be a nightmare.
Jumping over to point number 2, why would you have your applications access the database by anything other than windows authentication? Let the database handle permissions! Your application should as well though. Why have a single point of failure? I DO application development at my workplace and security is applied to both the SQL Server side and the application side. A good auditor would flag you on that for sure! If you have 1 service account per application, it becomes a pain doing password management. Every time you do password maintenance on service accounts (you ARE doing password maintenance on your service accounts, right?), you need to recompile each application so your end users can run it? That’s crazy talk. I can’t imagine the maintenance nightmare you deal with with that.
Now with web development, you really really want to have as much protection on the database side as you can. Your users should be doing some form of authentication to the system and your pre-authentication stage should be using a VERY limited account. Once they authenticate, you can use that username/password pair to connect to the database and just keep the connection open during the session, closing it on timeout or logout. If your non-authenticated account has full access to the database, you run the risk of SQL Injection and URL spoofing and cooking spoofing.
The service account should be doing the bare minimum on the database side to make auditing a LOT easier. Plus the user should only be able to access their data, not all data. With your setup, if I am understanding it right, you have a single point of failure in terms of security. One account gets compromised (the service account), you have access to the entire database… all usernames, all passwords, and all data that you have stored in there. If the service account is LEAST permissive, it gets compromised, you lost very little. And if end users can only access their data, a single account getting compromised is not as big of an issue.
By running your database with users in a “datareader” role, you run a big security risk as users can access all data unless you put some explicit deny’s on there. If you are fine with users potentially accessing ALL data in the database, then your approach works. I know I like to keep things as protected as possible though… least priv model and all that…