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
- Understand the permissions of the various fixed database roles.
- Learn how to audit for users who belong to roles which give implicit permissions.
- Build disaster recovery scripts for each of your database roles.
