db_securityadmin is a fixed database role created by SQL Server. Members of this role can modify role membership and manage permissions for all securables within the database it assigned with the role.
With Grant Option permission gives a database principal the ability to grant a specific permission to other database principals on a specific securable. This method allows granular permission assignment without broad security permission like db_securityadmin database role.
However, there a few interesting behaviors to watch out to prevent surprises.
Let's see some examples. By default, the database role are owned by dbo user. When a member of db_securityadmin grants permission on a securable to a principal, the grantor of the permission 'usually' is assigned as dbo. More details later.
First, log in with the account which is a member of sysadmin role. In the example below, the sysadmin account is used to execute query unless when it impersonates other principal.
CREATE DATABASE TEST; GO USE TEST; GO CREATE TABLE dbo.tbl1 (col1 tinyint DEFAULT 1); INSERT INTO dbo.tbl1 VALUES (DEFAULT); GO --db_securityadmin CREATE USER UserSecurityAdmin WITHOUT LOGIN; EXEC sp_addrolemember db_securityadmin, UserSecurityAdmin; EXEC sp_addrolemember db_securityadmin, UserSecurityAdmin; --db_securityadmin and with grant option CREATE USER UserSecurityAdminGrantOption WITHOUT LOGIN; EXEC sp_addrolemember db_securityadmin, UserSecurityAdminGrantOption; GRANT SELECT ON dbo.tbl1 TO UserSecurityAdminGrantOption WITH GRANT OPTION; --with grant option CREATE USER UserGrantOption WITHOUT LOGIN; GRANT SELECT ON dbo.tbl1 TO UserGrantOption WITH GRANT OPTION; --user CREATE USER TestUser WITHOUT LOGIN; CREATE USER TestUser2 WITHOUT LOGIN; GO
Next, we grant TestUser permission on dbo.tbl1 table by each principal.
EXECUTE AS USER = 'UserSecurityAdmin'; GRANT SELECT ON dbo.tbl1 TO TestUser; REVERT; EXECUTE AS USER = 'UserGrantOption'; GRANT SELECT ON dbo.tbl1 TO TestUser; REVERT; GO
Run the script below to verify the permission. This script will be used to verify permission throughout this post.
SELECT
USER_NAME(p.grantee_principal_id) AS [user],
USER_NAME(p.grantor_principal_id) AS [grantor],
p.[state_desc],
p.[permission_name],
p.class_desc,
OBJECT_SCHEMA_NAME(p.major_id) + '.' + OBJECT_NAME(p.major_id) AS [securable]
FROM sys.database_permissions p
WHERE p.[permission_name] <> 'CONNECT' AND
p.grantee_principal_id IN
(
DATABASE_PRINCIPAL_ID('UserSecurityAdmin'),
DATABASE_PRINCIPAL_ID('UserSecurityAdminGrantOption'),
DATABASE_PRINCIPAL_ID('UserGrantOption'),
DATABASE_PRINCIPAL_ID('db_GrantOption'),
DATABASE_PRINCIPAL_ID('memberGrantOption'),
DATABASE_PRINCIPAL_ID('TestUser'),
DATABASE_PRINCIPAL_ID('TestUser2')
)
ORDER BY [user], [state_desc];
GO
Notice that TestUser is granted with SELECT permission with different grantor? One with dbo as grantor and another with UserGrantOption. When permission is granted by member of db_securityadmin database role, it is granted with dbo. The permission granted by UserGrantOption has the user as the permission grantor.
Now grant permission with UserSecurityAdminGrantOption with and without the 'As' option.
EXECUTE AS USER = 'UserSecurityAdminGrantOption'; GRANT SELECT ON dbo.tbl1 TO TestUser; GRANT SELECT ON dbo.tbl1 TO TestUser AS UserSecurityAdminGrantOption; REVERT; GO
Notice that there is a new entry with UserSecurityAdminGrantOption as grantor. It is important to be aware that permission granted WITH GRANT OPTION or using the 'As' option resulted the user assigned as grantor. is from the permission granted with 'As' option. The previous example shows us that permission granted with db_securityadmin (without 'As' option) is assigned with dbo as grantor. Since there is already a similar SELECT permission with dbo as grantor, there are now three permission for TestUser with its respective grantor.
UserSecurityAdmin is member of db_securityadmin database role but not with the WITH GRANT OPTION like UserSecurityAdminGrantOption. If it try to use the 'As' option,
EXECUTE AS USER = 'UserSecurityAdmin'; GRANT SELECT ON dbo.tbl1 TO TestUser AS UserSecurityAdmin; REVERT; GO
It failed with this error
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'tbl1', because it does not exist or you do not have permission.
We know that UserSecurityAdmin is a member of db_securityadmin, it should have the capability/permission to modify any permission on any principal. It appears that either the 'AS' option does not consider the inherit security permission from the fixed database role or 'As' option could be only used for user explicitly granted WITH GRANT OPTION permission.
Attempt to grant WITH GRANT OPTION to db_securityadmin fixed database role returns this error.
Msg 4617, Level 16, State 1, Line 1
Cannot grant, deny or revoke permissions to or from special roles.
Now we see that a user could be granted multiple times with different grantor on a similar permission. That mean if UserSecurityAdmin revoke the SELECT permission from TestUser, TestUser is still able to select from the table since it still granted with SELECT permission from other grantor.
EXECUTE AS USER = 'UserSecurityAdmin'; REVOKE SELECT ON dbo.tbl1 FROM TestUser; REVERT; EXECUTE AS USER = 'TestUser'; SELECT * FROM dbo.tbl1; REVERT; GO
TestUser still has the permission to SELECT from dbo.tbl1 table. In order to prevent TestUser from selecting from the table, UserSecurityAdmin could issue a DENY statement.
EXECUTE AS USER = 'UserSecurityAdmin'; DENY SELECT ON dbo.tbl1 TO TestUser; REVERT; EXECUTE AS USER = 'TestUser'; SELECT * FROM dbo.tbl1; REVERT; GO
Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'tbl1', database 'TEST', schema 'dbo'.
Remember that there are multiple SELECT permissions have been granted to TestUser? However, DENY permission always take precedence. The other way to remove the TestUser permission to the table is to revoke all its GRANT SELECT permissions. Here is the part to watch out. In order to revoke the permission, the principal who is the grantor of the permission is required to be used to execute the statement to revoke the permission. Here is the example. Let's use the UserSecurityAdmin who is a member of db_securityadmin role to revoke the permission.
EXECUTE AS USER = 'UserSecurityAdmin'; REVOKE SELECT ON dbo.tbl1 TO TestUser; REVERT; GO
If we use another account with db_securityadmin or member of sysadmin server role to revoke the permission,
--Grant permission as UserSecurityAdmin EXECUTE AS USER = 'UserSecurityAdmin'; DENY SELECT ON dbo.tbl1 TO TestUser; REVERT; --Revoke account with sysadmin account REVOKE SELECT ON dbo.tbl1 TO TestUser; GO
The account that is executing this statement is the member of sysadmin role, which by default as dbo user. The statement above only revoke the DENY permission that is granted by UserSecurityAdmin (grantor dbo) on the table from TestUser. It does not revoke permission that was granted by other users.
One way to revoke the permission granted by UserGrantOption is using the 'AS' option, or execute as UserGrantOption and revoke the permission. In order to grant permission as other principal, the principal executing the query requires IMPERSONATE permission on the other principal. Member of db_securityadmin inherit this permission on all principals of the database.
--Execute as UserGrantOption EXECUTE AS USER = 'UserGrantOption'; REVOKE SELECT ON dbo.tbl1 FROM TestUser; REVERT; --Execute as db_securityadmin with AS option EXECUTE AS USER = 'UserSecurityAdmin '; REVOKE SELECT ON dbo.tbl1 FROM TestUser AS UserGrantOption; REVERT; GO
You may think we could log in with the grantor account and revoke the permission. However, it may not work that way. Take UserSecurityAdminGrantOption as an example. Since this account is a member of db_securityadmin, the grantor is assigned as 'dbo' if the permission is granted without the Grant 'AS' option. If the 'AS' option was used to grant the permission, UserSecurityAdminGrantOption is set as the grantor. That means, if we are going to revoke the permission that was previously granted with the 'AS' option, we need to use the 'AS' option to revoke the permission.
EXECUTE AS USER = 'UserSecurityAdminGrantOption'; --This does not work REVOKE SELECT ON dbo.tbl1 FROM TestUser; --This work REVOKE SELECT ON dbo.tbl1 FROM TestUser AS UserSecurityAdminGrantOption; REVERT; GO
There is another way to revoke permission previously granted with the 'AS' option. The REVOKE statement has two options,
1) GRANT OPTION FOR
2) CASCADE
When GRANT OPTION FOR option is used, it has to be used together with CASCADE option. This revokes the specific permission previously granted to other principals by this principal. To better illustrate the use of this option, here is the example.
--Grant SELECT for both TestUser, TestUser2 --execute as UserGrantOption EXECUTE AS USER = 'UserGrantOption'; GRANT SELECT ON dbo.tbl1 TO TestUser, TestUser2; REVERT; --Or execute as member of db_securityadmin and use AS option EXECUTE AS USER = 'UserSecurityAdmin'; GRANT SELECT ON dbo.tbl1 TO TestUser, TestUser2 AS UserGrantOption; REVERT; GO
If we try to revoke with GRANT OPTION FOR with CASCADE option executed as UserGrantOption,
--Pay attention that the revoke is on UserGrantOption, not TestUser --execute as UserGrantOption EXECUTE AS USER = 'UserGrantOption'; REVOKE GRANT OPTION FOR SELECT ON dbo.tbl1 FROM UserGrantOption CASCADE; REVERT;
It returns an error.
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
SQL Server does not allow principal to change its own permission. In order to achieve that, use another principal that has the permission, like member of db_securityadmin.
--Execute as member of db_securityadmin and use AS option EXECUTE AS USER = 'UserSecurityAdmin'; REVOKE GRANT OPTION FOR SELECT ON dbo.tbl1 FROM UserGrantOption CASCADE; REVERT; GO
Notice that SELECT permission for both TestUser and TestUser2 have been revoked from the dbo.tbl1 table. However, UserGrantOption itself still have the SELECT permission but not GRANT SELECT permission.
If the intention is to revoke all the SELECT permission from UserGrantOption and the permission it granted, use CASCADE without GRANT OPTION FOR option.
EXECUTE AS USER = 'UserSecurityAdmin'; --Grant WITH GRANT OPTION permission back to UserGrantOption GRANT SELECT ON dbo.tbl1 TO UserGrantOption WITH GRANT OPTION; --Grant SELECT permission to users GRANT SELECT ON dbo.tbl1 TO TestUser, TestUser2 AS UserGrantOption; --Revoke SELECT permission on UserGrantOption and other users granted by it REVOKE SELECT ON dbo.tbl1 FROM UserGrantOption CASCADE; REVERT; GO
EXECUTE AS USER = 'UserSecurityAdmin'; --Grant UserGrantOption the ability to grant permission GRANT SELECT ON dbo.tbl1 TO UserGrantOption WITH GRANT OPTION; --Grant SELECT permission to users GRANT SELECT ON dbo.tbl1 TO TestUser, TestUser2 AS UserGrantOption; --Revoke SELECT permission on UserGrantOption REVOKE SELECT ON dbo.tbl1 FROM UserGrantOption; REVERT; GO
This error is returned.
Msg 4611, Level 16, State 1, Line 1 To revoke or deny grantable privileges, specify the CASCADE option. This brings up a concern.
This bring up a potential problem. What if an user who has been delegated to grant permission and has granted permission to other principals, decided to leave the company? If you try to drop the user.
DROP USER UserGrantOption; GO
You get this error.
Msg 15284, Level 16, State 1, Line 1 The database principal has granted or denied permissions to objects in the database and cannot be dropped.
In order to drop the user, all the permission (GRANT or DENY) granted by this user required to be revoked before the user can be dropped. Now, dropping a user suddenly affect other users' permissions.
Back to topic, db_securityadmin and WITH GRANT OPTION could be used to delegate user to grant permission. If db_securityadmin method is used, the grantor of the permission is dbo, not to the user granting the permission. This allow dropping the user without affecting other user permission (Note that You can't drop dbo user). However, as we have seen this is not as simple for WITH GRANT OPTION as the grantor is assigned to the user granting the permission.
If granular permission delegation is required or desired in your environment, and you want to avoid this concern, there are a few ways.
1) Use another user to grant similar permissions to all affected principals. This increase security maintenance cost and may lead to accidental mistake.
2) Use a specified Windows group. Create a user (group) and maps it to this Windows group and grant permission with this user. This allow permission grantor ties to a group instead of a specific user. The member of the group is managed at the Active directory. However, the user can't grant the permission directly as the WITH GRANT OPTION is assigned to the group.
--If WindowUserGrantOption is member of WindowsGrantOptionGroup Windows group. --Grant windows group WITH GRANT OPTION GRANT SELECT ON dbo.tbl1 TO WindowsGrantOptionGroup WITH GRANT OPTION; --Won't work EXECUTE AS USER = 'WindowUserGrantOption'; GRANT SELECT ON dbo.tbl1 TO TestUser; REVERT; --Work EXECUTE AS USER = 'WindowUserGrantOption'; GRANT SELECT ON dbo.tbl1 TO TestUser AS WindowsGrantOptionGroup; REVERT;
GO
The grantor of the permission will be assigned to WindowsGrantOptionGroup.
3) Similar to Windows group, use user defined database role. Instead of granting permission as the user, grant permission as the database role.
--Create user database role --The grant option assigned to the role, not the user CREATE ROLE db_GrantOption; GRANT SELECT ON dbo.tbl1 TO db_GrantOption WITH GRANT OPTION; --Add user as member of the role CREATE USER memberGrantOption WITHOUT LOGIN; EXEC sp_addrolemember db_GrantOption, memberGrantOption; --Grant TestUser SELECT permission as database role, not the user itself EXECUTE AS USER = 'memberGrantOption'; GRANT SELECT ON dbo.tbl1 TO TestUser AS db_GrantOption; REVERT; GO
Notice that the TestUser permission grantor is assigned to db_GrantOption database role. Dropping the user could be done without affecting the other users' permission that was previously granted as the grantor of the permission is assigned to the database role.
To wrap up, delegating permission task with db_securityadmin is more common from my experience. It avoids the complication of the delegated user leaving the company as permission grantor is assigned dbo rather than to specific user. However it may not be suitable for environment required granular permission delegation. WITH GRANT OPTION provide an alternative to delegate security task at a much granular level. However, since the grantor of the permission is tied to specific user, it is important to understand how it works and plan accordingly to prevent unforeseen consequences.
No comments:
Post a Comment