SQL Server Security – Database Roles
Wednesday, December 31st, 2008Database roles (also referred to as database-level roles) are security roles that exist on a database level, as opposed to the server level. If you are familiar with any aspect of system administration, database roles are similar to groups in the world of Windows system administration. Just like a Windows group, when a user is added to a role they inherit all the rights and permissions of the role.
There are two kinds of database roles in SQL Server: fixed roles and flexible roles.
Fixed Roles
Before getting into the fixed roles that are available, it’s important to know what fixed roles actually are. Fixed roles are roles that automatically exist in each database. Adding a user to one of these roles will not change that user’s permissions in any other database.
Any user or role can be added to a database role. Once a user has been added to a role, they can add other users or roles to that role.
A Word of Warning: Be exceptionally carefully when adding flexible roles to a fixed role. You could very easily elevate privileges for a large number of users in one simple step.
With that warning out of the way, let’s take a look at the fixed roles available in SQL Server.
- db_owner
- db_securityadmin
- db_accessadmin
- db_backupoperator
- db_ddladmin
- db_datawriter
- db_datareader
- db_denydatawriter
- db_denydatareader
- Public
db_owner
Users in the db_owner role have it all, within a single database. They can grant and revoke access, create tables, stored procedures, views, run backups, schedule jobs. Heck, a user who is db_owner can even drop the database.
However, just because you have the keys to the kingdom doesn’t mean that you can do everything. Users who have been granted db_owner will still need specific permissions to run traces and view many of the dynamic management views. Why is that? Those are managed at the server level and will require that server level permissions or roles are granted to your login.
Why use db_owner? You would want to add a user to the db_owner role if you have a user who needs to make extensive modifications to all aspects of a database - creating users, adding them to roles, creating tables/views, creating stored procedures, and adding security settings for the tables, views, and stored procedures. One example of this would be a developer who is creating a database for a new application (or creating extensive modifications to an existing application) but you don’t want to add that user to the sysadmin group.
-- Create our sample database and switch to it
CREATE DATABASE TestFixedRoles;
GO
USE TestFixedRoles;
GO
-- Set up our test users
CREATE USER user_dbo WITHOUT LOGIN;
CREATE USER user_security WITHOUT LOGIN;
CREATE USER user_reader WITHOUT LOGIN;
CREATE USER user_writer WITHOUT LOGIN;
CREATE USER user_backup WITHOUT LOGIN;
GO
-- Grant db_owner to user_dbo
EXEC SP_ADDROLEMEMBER N'db_owner', N'user_dbo';
EXEC SP_ADDROLEMEMBER N'db_securityadmin', N'user_security';
-- Create an orders table as dbo
CREATE TABLE orders (
id INT NOT NULL IDENTITY(1,1),
orderdate DATETIME NOT NULL,
employee_id INT NOT NULL,
customer_id INT NOT NULL,
quantity INT NOT NULL,
CONSTRAINT PK_Orders PRIMARY KEY (id)
);
GO
-- temporarily switch to the context of user_dbo
EXECUTE AS USER = 'user_dbo';
SELECT USER_NAME(); -- This should return user_dbo
-- This will return 1 row for our orders table
SELECT t.[name]
FROM sys.tables AS t
WHERE t.[name] NOT LIKE 'sys%'
DROP TABLE orders;
-- revert back to our regular user
REVERT;
GO
db_securityadmin
Users in the db_securityadmin role can modify role permissions and manage permissions. Users in this role have, in theory, almost as much power members of db_owner. The only thing that a member of db_securityadmin can’t do is add users to the db_owner role. Members of db_securityadmin also cannot add users to fixed database roles (this requires membership in the db_owner role).
Why use db_securityadmin? You might want to use db_securityadmin when you need to grant privileges to a trusted user and allow them to manage privileges across an application. Take care when granting db_securityadmin to make sure that you trust the user to not give themselves additional permissions. This risk can be alleviated by adding auditing to the database to log when privileges are granted or revoked.
EXECUTE AS USER = 'user_security'; -- this will generate three errors since user_security isn't a member of db_owner. EXEC SP_ADDROLEMEMBER N'db_datareader', N'user_reader'; EXEC SP_ADDROLEMEMBER N'db_datawriter', N'user_writer'; EXEC SP_ADDROLEMEMBER N'db_backupoperator', N'user_backup'; REVERT; GO -- this will now succeed since you are a member of db_owner, after all EXEC SP_ADDROLEMEMBER N'db_datareader', N'user_reader'; EXEC SP_ADDROLEMEMBER N'db_datawriter', N'user_writer'; EXEC SP_ADDROLEMEMBER N'db_backupoperator', N'user_backup';
db_accessadmin
Members of the db_accessadmin role have the ability to change database access. They can grant and revoke access to Windows logins, Windows groups, and SQL Server logins. The users that they grant access to will be members of the Public role and will have all the privileges associated with that role.
Why use db_accessadmin? This role would be used when you have a user who is responsible for maintaining access to a specific database. When combined with db_securityadmin you have a user who is capable of granting and revoking general access to a database as well as controlling the security permissions for almost any user. This combination is quite powerful and should be granted carefully. With auditing in place, you can mitigate any risk of granting both roles to a single user.
db_backupoperator
Members of this role can create database backups. It’s important to note that they cannot, by default, restore the backups that they create. The only users that can restore a backup are members of the sysadmin and dbcreator server roles and the owner of the database (dbo).
Why use db_backupoperator? If you have an automated process that connects to the database and creates a backup it would be a good idea to have all backup operations connect to the database using a user/login that only has db_backupoperator access to prevent any unauthorized data access due to a user being compromised.
db_datareader/db_denydatareader
Members of the db_datareader role are able to read all data from all user tables. That’s right, everything. Even the super secret secrets.UserPayHistoryAndSocialSecurityNumbers table can be read by members of db_datareader.
Conversely, members of db_denydatareader are explicitly denied the ability to write to any user created tables. They live in the dark about the contents of the database.
Why use db_datareader? Let’s say the accounting department has a separate database. Everyone in accounting is able to create and run ad hoc reports directly against this database, but they shouldn’t be able to do anything else apart from see the contents of the database. Clearly granting db_owner access is out of the question. Your junior DBA is out sick today, so you can’t make him grant SELECT permissions to every table in the database while you go out to Chipotle for lunch. Instead, you can simply grant db_datareader access to the accounting department’s Windows group.
Why use db_denydatareader In addition to the accounting department’s requirement to be able to create ad hoc reports as needed, HR needs to be able to run the canned reports available to them through your carefully crafted stored procedures but they should not be able to run ad hoc reports. However, it turns out that they are also able to create and run ad hoc reports (thankfully your junior DBA emailed you about this yesterday before he got sick). By granting db_denydatareader permissions to the HR Windows group you can prevent members of the HR group from running the ad hoc reports (assuming Windows Forms authentication is being used) and get in touch with the application developers to have them hide ad hoc reports from the HR group in the application front end.
db_datawriter/db_denydatawriter
Much like db_datareader and db_denydatareader, the name of this role is largely self-explanatory: members of the db_datawriter role can INSERT, UPDATE, and DELETE data from any user created table. Likewise, members of the db_denydatawriter role are explicitly denied the ability to perform INSERT, UPDATE, and DELETE operations on any user created tables.
Why use db_datawriter? db_datawriter would be a good choice for a user or login that runs an automated ETL process on a regular basis. This ensures that access to all tables is maintained even when new tables are added and reduces maintenance overhead.
Why use db_denydatawriter If you want to limit write access for a login or user it is easy to add them to the db_denydatawriter role and allow specific access to a subset of tables. This might be desirable when users may need to make adjustments to certain tables - such as a bill, order, or account balance - but they do not need the ability to modify all data in the database.
db_ddladmin
Members of the db_ddladmin role are able to execute DDL commands (CREATE, ALTER, DROP) within the current database. It’s pretty self-explanatory - a member of db_ddladmin can run any CREATE, ALTER, or DROP command within the current database. They cannot create new databases, nor can they alter or drop the current database.
Why use db_ddladmin? Your company has brought on several consultants to help develop a new application. Their work will require that they are able to create, or change, new tables, views, indexes, and stored procedures. However, they don’t need the ability to create users or manage security. In this case, your best solution would be to add the consultants to the db_ddladmin role.
Public
The Public role is a bit different from all of the other roles. Every database user is a member of the Public role. If a user does not have any explicit permissions on a database object, they will inherit the permissions of the Public role.
Why use the public role? Going back to our example of the Accounting and HR departments, let’s say that there are now two databases: Accounting and HR. For the purposes of this example, only Accounting personnel should have access to the Accounting database and only HR personnel should have access to the HR database. What’s the best way to accomplish this?
The first step is to create a user for each department in their respective database. This user needs to be mapped to the appropriate Windows group. Once you have created the user and mapped it to the appropriate group, you can then add the user to the Public role.
Using this method it’s easy to add additional users and groups to the Public role without having to manage separate security settings for each one individually.
/***************************************************************************** * PUBLIC ROLE DEMONSTRATION ****************************************************************************/ CREATE DATABASE Accounting; GO USE Accounting; GO -- This is going to fail unless you have a LOGIN called group_Accounting CREATE USER user_Accounting FOR LOGIN group_Accounting; GO EXEC SP_ADDROLEMEMBER N'Public', N'user_Accounting'; GO CREATE DATABASE HumanResources; GO USE HumanResources; GO -- This is going to fail unless you have a LOGIN called group_HumanResources CREATE USER user_HumanResources FOR LOGIN group_HumanResources; GO EXEC SP_ADDROLEMEMBER N'Public', N'user_HumanResources'; GO
