solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

Blocking SQL Server db_datareader, db_datawriter, and db_owner Permissions

MSSQLTips author K. Brian Kelley By:   |   Read Comments (6)   |   Related Tips: More > Security
Problem

I'm in a dilemma. In a database I currently support, the application makes use of db_datareader and db_datawriter to give permissions to the tables. In a different database, it's even worse as the application uses db_owner. We're adding new tables to both databases and we don't want the application to have access to these tables. How can I deal with the use of these three roles? I do not have time to re-architect the security and redo the permissions.  Check out this tip to learn more.

Solution

You've hit upon one of the reasons many DBAs dislike the use of all inclusive roles like db_datareader, db_datawriter, and db_owner: if you have to make a security change, it because very problematic to do so. The best option, from an architecture perspective, is to redo the security permissions so that they are the minimum necessary for each user. However, because we're often under time constraints to come up with a solution, it's not possible to make major changes. There is something you can do, however. It involves the use of DENY.

First, let's create a sample database and several users with the appropriate roles so that we can see how the DENY works:

-- Test Database to Show Permissions
CREATE DATABASE [TestDB];
GO 
-- Use the new DB
USE [TestDB];
GO 
-- Create the three users we can work with
CREATE USER [Reader] WITHOUT LOGIN;
GO 
CREATE USER [Writer] WITHOUT LOGIN;
GO 
CREATE USER [DBOwn] WITHOUT LOGIN;
GO 
-- Add them to the three roles we're concerned about
EXEC sp_addrolemember @rolename = 'db_datareader', @membername = 'Reader';
EXEC sp_addrolemember @rolename = 'db_datawriter', @membername = 'Writer';
EXEC sp_addrolemember @rolename = 'db_owner', @membername = 'DBOwn';
GO 
-- Create a simple table to show permissions
CREATE TABLE dbo.SampleTable (SampleColumn INT);
GO 

Let's use the EXECUTE AS USER in order to specify test harnesses so we can see how the roles give implicit access:

-- Note, no permissions have been assigned. However, implicit SELECT 
-- permissions are in effect because of the roles
EXECUTE AS USER = 'Reader';
GO 
SELECT SampleColumn FROM dbo.SampleTable;
GO
REVERT;
GO 
EXECUTE AS USER = 'DBOwn';
GO
SELECT SampleColumn FROM dbo.SampleTable;
GO
REVERT;
GO 
-- Note again, no permissions have been assigned. INSERT, UPDATE,
-- and DELETE are allowed because of implicit permissions due to the roles
EXECUTE AS USER = 'Writer';
GO 
INSERT INTO dbo.SampleTable 
(SampleColumn) VALUES (0);
GO
REVERT;
GO 
EXECUTE AS USER = 'DBOwn';
GO
INSERT INTO dbo.SampleTable 
(SampleColumn) VALUES (1);
GO
REVERT;
GO 

As we expected, these roles give the users permissions to the table, even though we've not explicitly defined such permissions. This is the way these roles work. Simply being a member of these roles gives the access and that's the problem. Here's where we can see the DENY block access. Let's create a role and assign DENY permissions to the four basic query commands. Then we'll make each user a member of the role.

-- Now let's use DENY to block explicitly
CREATE ROLE [DenyAccess];
GO
DENY SELECT, INSERT, UPDATE, DELETE ON dbo.SampleTable TO DenyAccess;
GO
EXEC sp_addrolemember @rolename = 'DenyAccess', @membername = 'Reader';
EXEC sp_addrolemember @rolename = 'DenyAccess', @membername = 'Writer';
EXEC sp_addrolemember @rolename = 'DenyAccess', @membername = 'DBOwn';
GO 
-- Now go and try the SELECT and INSERT statements again

With the DENY in place, try the test harnesses and you'll see that the users can no longer access the table. The DENY is a trump: it doesn't matter if the user has either implicit or explicit permission, the DENY will block the access. In the case of db_datareader and db_datawriter, there's nothing more that they can do about it. The issue with db_owner is that a user with db_owner can always remove the DENY. However, if you're talking about an application, this type of command is likely not built into the application.

Note: there is one exception and that's if the user is seen as dbo. You can't assign a DENY to dbo and you can't make dbo a member of a role, meaning there is no way to block dbo. You can assign DENY explicitly against the public role, which should apply to dbo. However, it does not and dbo effectively ignores the DENY. Members of the sysadmin server role come into a database as dbo. So does the true database owner (as seen in sys.databases). If an application actually owns the database or the user the application comes in is a member of the sysadmin role, you won't be able to block access in this manner. In the first case you'll have to change out the database owner if you can and then make the user a member of the db_owner role. In the second case, there's nothing you can do short of re-architecting the security from the ground up.

Next Steps


Last Update: 3/6/2013


About the author
MSSQLTips author K. Brian Kelley
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Wednesday, March 06, 2013 - 9:12:37 AM - Bob St. Aubyn Read The Tip

Brian - you touched on a topic that I have been dealing with for most of my 12+ year career in SQL Server.  In every SQL environment I've worked in, permissions have been assigned with a "db_owner/sysadmin" mentality, mostly because nobody knew how to grant permissions at more granular levels and/or didn't want to take the time.  The, "if I grant dbo (or sysadmin) to the user, I know it will work" approach to granting database permissions, when a company doesn't have the benefit of a DBA on staff, has unfortunately been the norm.  Consequently, in every place I've worked, I've had to pontificate about why this is bad and launch a tedious and frustrating adventure to find out what the users and applications really need and back permission down gradually over time.  And it's so hard to do this after the fact.  The first answer to the question, "what does this application need to be able to do in the database?", will almost certainly be "everything" or "I have no idea".

Then there are the permissions requirements for purchased or consultant-created software packages.  I've seen two different applications that "required" db_owner membership because they were designed to create tables in the database on-the-fly.  Then there was the app that actually had a check to make sure it was a member of the sysadmin role before it would initialize.  I sure hope the hackers never find that one because they'd have have a ball.

Security in SQL Server has been one of the hardest battles I've fought for thus far.  It seems that the "least possible prevelidge" best practice paradigm is known only to DBA's and, unfortunately, either unknown or ignored among other disciplines.

Sorry about the long post, but as you can tell, this has been a major frustration for me for a long time!  Anyway, thanks for the very relevant post!

PS: I wonder how many of your readers realize what they're really granting when they throw a user in the db_owner role!


Wednesday, March 06, 2013 - 11:34:48 AM - Rob Volk Read The Tip

If you're really desperate and have no other alternative, you can try the techniques I've listed here:

http://weblogs.sqlteam.com/robv/archive/2011/12/13/t-sql-tuesday-025-ndash-check-constraint-tricks.aspx

It's easy to modify the constraints to use IS_ROLEMEMBER() for database roles.  Unfortunately these won't help for limiting SELECT or DELETE operations.


Tuesday, April 02, 2013 - 7:44:29 AM - Kev Read The Tip

I think the point is that most vendors or developers take a lazy approach and instead of going out of their way to find out what their usres actually need, they put their users into a God group.

But to actually hardcode the necessity for a user to be db_owner or sysadmin is one reason for me at least, why a vendor will fail the selection process.


Tuesday, April 02, 2013 - 1:02:04 PM - AZJim Read The Tip

Personally, I dislike DENY as much as global GRANT permissions.  If you have network acconts, troubleshooting why a person doesn't have permissions can be more difficult.  This happens because SQL Server will take the most restrictive/pessimistic approach to permissions (as it has to).  As an example, if you have permissions from the following network accounts of db_datareader1, db_datareader2, db_datawriter, and if a business user is in all three, should one of the datareaders have a DENY, then the business user will lack permissions in accordance with the DENY.

SQL Server DBAs will eventually have to realize that as SQL Server becomes more strategic in the enterprise (i.e., replacing Unix or mainframe DBMS apps), permissions will have to grow stricter.  Business users will have to identify what tables/views they require access to and the corresponding GRANT permissions will have to be granted at a more granular level.


Monday, September 16, 2013 - 9:30:43 AM - Seth Delconte Read The Tip

Thanks Brian.


Wednesday, September 18, 2013 - 10:08:25 AM - John Langston Read The Tip

Thank you Brian.

Underscores the need for everyone (end user management and IT) to understand the access requirements of the user community. 

I personally prefer the use of AD groups as a means to apply granualar permissions to objects but even this has a downside if one needs to cross a linked server to get data from another database on another server.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.