Recently, while working with database security, I learned database roles as how the each rule used. Apart from the server level roles if we need to require to assign access/rights to the particular database level, then we need to go through database level roles.
Following are the fixed database level roles as per
MSDN,
db_owner :Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.
db_securityadmin :Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.
db_accessadmin :Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
db_backupoperator :Members of the db_backupoperator fixed database role can back up the database.
db_ddladmin :Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriter :Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader : Members of the db_datareader fixed database role can read all data from all user tables.
db_denydatawriter :Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.
db_denydatareader : Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.
You can see the image in all above fixed database roles. Now we will see how can we use the roles and bind with users. Let's create a small demo with examples. Here I am creating required objects used for demos, So let's do that.
USE demo
GO
CREATE TABLE SampleTable1
(
Id int,
Name varchar(10)
)
GO
CREATE TABLE SampleTable2
(
Id int,
Name varchar(10)
)
GO
CREATE PROCEDURE SampleSP1
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM SampleTable1
End
GO
CREATE PROCEDURE SampleSP2
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM SampleTable2
End
GO
Now here I want to assign the access to user as follows,
1. User can see the data from SampleTable2 table.
2. User can perform select/insert/update operation on SampleTable1 table, not delete operation.
3. Can execute stored procedure SampleSP1.
4. Can not execute stored procedure SampleSP2.
Workaround 1:If we assigned fixed database roles to User like db_datareader, and db_datawriter for a particular database, then user can perform all DML operations on all tables, views in the database. Even if we assigned EXECUTE permission to the user then the user can execute all the stored procedures. After all these permissions we need to deny permission from the user for some of the tables and stored procedures which are not required to be accessed.
Workaround 2:Instead of doing above such stuffs, We will create a new custom database level role and assigned requited access to use for the objects.
USE demo
GO
CREATE LOGIN [SupportUser]
WITH PASSWORD=N'SupportUser',
DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
CREATE USER [SupportUser] FOR LOGIN [SupportUser]
GO
CREATE ROLE [SupportRole]
Go
GRANT SELECT ON SampleTable2 TO [SupportRole];
GRANT SELECT,INSERT,UPDATE ON SampleTable1 to [SupportRole];
GRANT EXEC ON SampleSP1 to [SupportRole]
GO
EXEC sp_addrolemember N'SupportRole', N'SupportUser'
GO
Let's connect the SQL Server instance with this newly created user and see the access rights,
USE demo
GO
PRINT 'Inserting in SampleTable1'
GO
INSERT INTO SampleTable1
(
Id,
Name
)
SELECT
1,
'Sample1'
GO
PRINT 'Inserting in SampleTable2'
GO
INSERT INTO SampleTable2
(
Id,
Name
)
SELECT
1,
'Sample2'
GO
PRINT 'Deleting from SampleTable1'
GO
DELETE FROM SampleTable1
GO
PRINT 'Viewing from SampleTable1'
GO
SELECT * FROM SampleTable2
GO
SELECT * FROM SampleTable1
GO
PRINT 'Executing SampleSP11'
GO
EXEC SampleSP1
GO
PRINT 'Executing SampleSP2'
GO
EXEC SampleSP2
GO
You can see the below image to see the access by running user,
The main benefit of the custom database role is role can be assigned to multiple users. You can see below script where I have assigned the same role to different users. So once role created it can be assigned to multiple users.
USE demo
GO
CREATE LOGIN [DBAUser]
WITH PASSWORD=N'DBAUser',
DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
CREATE USER [DBAUser] FOR LOGIN [DBAUser]
GO
EXEC sp_addrolemember N'SupportRole', N'DBAUser'
GO
Hope you like this post.
