Problem
I heard about user context switching and user impersonation in SQL Server stored procedures, but I am not sure how to use them. Please briefly explain top-line issues for implementing these features. Also, provide some code walk-throughs demonstrating the use of these features for evaluating security settings versus data access through select statements contrasted with stored procedures.
Solution
Context switching refers to modifying the context in which code operates from one user to another. The same code can function differently across user contexts because database role memberships and permissions may differ between users. The Execute As statement in T-SQL can implement context switching. Each freshly implemented Execute As statement adds a new user context to a user context stack. You can move back in the context stack by implementing a revert statement. You can get more detail on using Execute As from this Microsoft Docs page. Additionally, this prior MSSQLTips.com tip drills down on how to use Execute As from inside a stored procedure.
User impersonation is a feature that lets one user perform tasks with the permissions of another user while still retaining the original permissions of the user doing the impersonation. SQL Server has a special permission, named impersonate, that enables one user to operate with the permissions of another user as well as their own permissions. As with other permissions, the impersonate permission can be granted, revoked, and denied. See this Microsoft Docs page to learn more about the impersonate permission; these other Microsoft Docs pages drill down on granting, revoking, and denying permissions.
At a top-line level, the differences between the Execute As statement and the impersonate permission are these.
- The Execute As statement switches the permissions from one user to another until either another Execute As statement for switching to a new user or a revert statement for moving back one context in the user context stack.
- The impersonate permission adds the permissions of one user to another user. One user can concurrently impersonate the permissions of one or more other users. There is no notion of a stack for the impersonate permission. The permission for one user to impersonate another can be granted, revoked, or denied.
SQL Server DBAs can use context switching and impersonate permission features to evaluate the capabilities of users with different security settings. For example, an intermediate or senior DBA can switch contexts from one user to another to assess which security settings give the right trade-off between user capabilities and appropriate security.
This tip demonstrates how to code a workbench for evaluating and changing security settings for user contexts. You will first learn how to setup a workbench of security settings. Then, you’ll learn how to verify the impact of the security settings. Finally, you will learn how to dismantle custom security settings, so they are removed after testing is completed.
Assigning and monitoring SQL Server security settings for users
The following script demonstrates the setup of a test workbench for evaluating security settings. This script is meant to operate in the context of a login, such as a sysadmin role member, who can create login and user accounts, custom roles, and assign database permissions.
- The script operates in the CodeModuleTypes database. This is because a referenced table (soh_col_subset) in this tip was previously created and populated in the database within a prior tip – Storing Results Sets from a Stored Procedure. Like other objects in this tip, the table resides in the dbo schema. Please use code from the prior tip to make the table available in your version of the CodeModuleTypes database.
- The script begins by creating three logins (login1, login2, and login3) with three associated users (user1, user2, and user3).
- User1 is added to the db_owner fixed-database role.
- User2 is added to the db_datareader fixed-database role.
- User3 is not assigned any fixed-database or custom role memberships.
- Next, a create role statement designates a custom role named proc_executor. A grant statement for running execute statements permits proc_executor role members to run stored procedures.
- An alter role statement adds user3 to the proc_executor role.
- Next, a grant statement directly assigns to user3 permission to impersonate user2.
- The next code block creates a stored procedure named uspMyFourthStoredProcedure within the dbo schema of the CodeModuleTypes database. This stored procedure selects the top five rows from the soh_col_subset table. The stored procedure enables a couple of data access tests, which are illustrated in the next section.
The last two select statements in the following script are for monitoring user accounts and permissions.
- The first select statement is an inner join of the sys.database_principals with the sys.database_permissions tables. This kind of select statement can monitor permissions for database users. The where clause limits the results set to rows of interest for this tip.
- The second select statement outer joins the sys.database_role_members and sys.database_principals tables. The results set from this query displays the database role memberships of user accounts. The where clause limits the results set to those of interest for this tip.
use CodeModuleTypes
go
-- operate in sysadmin or default db_owner context
--Create three temporary principals
create login login1 WITH PASSWORD = 'J345#$)thb';
create login login2 WITH PASSWORD = 'Uor80$23b';
create login login3 WITH PASSWORD = 'Yie79$12v';
go
create user user1 FOR login login1; -- for db_owner member
create user user2 FOR login login2; -- for db_datareader
create user user3 FOR login login3; -- for no fixed db role membership
go
-- add role memberships to users
EXEC sp_addrolemember'db_owner', 'user1'
EXEC sp_addrolemember'db_datareader', 'user2'
go
-- create role for stored procedure executor
-- grant execute to the role
create role proc_executor;
grant execute to proc_executor
go
-- add user3 to the role
alter role proc_executor ADD MEMBER user3;
go
-- add impersonate permission for user3 of user 2
grant impersonate on user::user2 to user3;
go
-- create a stored procedure from sysadmin login
-- that selects from the soh_col_subset table
if object_id('dbo.uspMyFourthStoredProcedure') is not null
drop proc dbo.uspMyFourthStoredProcedure
go
create proc dbo.uspMyFourthStoredProcedure
AS
select top 5 * from soh_col_subset
go
-- principal database permissions
select
p.name prin_name
,p.principal_id
,d.class_desc
,d.grantor_principal_id
,d.grantee_principal_id
,d.permission_name
,d.state_desc
from sys.database_principals AS p
join sys.database_permissions as d on d.grantee_principal_id = p.principal_id
where d.major_id >=0
order by p.name
-- shows database role name with associated database user names
select DP2.name [Database User Name], DP1.name [Database Role Name],DP1.type
from sys.database_role_members AS DRM
right outer join sys.database_principals AS DP1 on DRM.role_principal_id = DP1.principal_id
left outer join sys.database_principals AS DP2 on DRM.member_principal_id = DP2.principal_id
where DP2.name is not null and DP1.type = 'R'
ORDER BY DP1.name;
When evaluating custom security settings, it is often very convenient to be able to examine database principals as well as the relationships of database principals to database permissions and database role memberships.
- It may be helpful to start by briefly summarizing key security terms as they are applied in this tip.
- A database principal can include such objects as a user account in a database (user1, user2, user3) and other objects too.
- A database role pertains to such items as a fixed-database role (db_owner role or db_datareader) or a custom database role (proc_executor).
- A database permission is an explicit permission to operate some narrowly defined and homogenous set of database capabilities, such as permission to connect to a database, invoke the exec statement, or impersonate another user. You can review a list of database permission names from this SQL Docs page.
- The top pane from the next-to-last select statement in the preceding script shows the relationships between principals and permissions.
- The principals in the top pane include the database owner (dbo), the custom role named proc_executor, and the database users – namely, user1, user2, and user3.
- When a principal name (prin_name) appears more than once, it is because that principal has more than one permission.
- The CONNECT permission indicates a user is able to connect to the CodeModuleTypes database. Each of the four users (dbo, user1, user2, and user3) has a CONNECT permission.
- The IMPERSONATE permission name indicates that the permissions from a grantor principal are available to a grantee principal. For example, the last row indicates that user3 with a principal_id value of 7 is granted permission to impersonate user2 with a principal_id value of 6.
- The EXECUTE permission name means a principal can invoke exec statements, such as for running stored procedures.
- The bottom results pane below maps database user names to database role memberships.
- The dbo user name designates the database owner. Every database must have an owner who is a member of the db_owner fixed-database role.
- Another user, namely user1, also belongs to the db_owner fixed-database role.
- User2 is a member of the db_datareader fixed-database role.
- User3 is a member of the custom proc_executor role; user3 does not belong to any fixed-database roles. Therefore, user3 cannot read tables via a select statement in the CodeModuleTypes database.
Demonstrating SQL Server user context switching with the Execute As statement
The following script demonstrates the syntax for the basics of user context switching with the Execute As statement. The script is meant to run after the preceding script that creates several logins and associated database users along with a custom role, as well as selected role and permission assignments; these collectively compose a security workbench to be tested. You will learn the basics of how to implement user context switching as well as how to recover from an error. One purpose for a script like the one in this section is to evaluate different security settings. For example, you can answer questions like: does your security versus functionality requirements call for a user like user2, user3, or something else altogether?
- The script starts with an Execute As statement that switches the user context to that for user3 from a user associated with the sysadmin fixed server role. Recall that the preceding script block would likely run in the context of a sysadmin role member. Immediately after the first context switch in the script below, a select statement confirms the names for the current login and user, namely login3 and user3.
- Next, a select statement runs in a try…catch block.
- The select statement is for the soh_col_subset table to which user3 does not have read permission.
- Therefore, control passes from the try block to the catch block where a select statement displays error_number and error_message. You will learn more about these in a review of the results set from the script below.
- Then, another context switch is implemented with a second Execute As statement.
- This context switch is from user3 to user2. This context switch is possible because user3 has impersonate permission for user2. Therefore, the context switch succeeds because user3 has permission to make the switch by virtue of its impersonate permission for user2. A select statement confirms that the new user context is for user2.
- The attempt to run a select statement against the soh_col_subset table succeeds because the code runs in the context of user2, which is a member of the db_datareader role.
- The next statement is a revert statement which moves the user context one level back in the user context switch stack. Therefore, the user context switches from user2 back to user3. Again, a select statement confirms the names for the current login and user.
- Within the context of user3, a successful attempt is made to run uspMyFourthStoredProcedure within the dbo schema.
- The attempt to execute the stored procedure succeeds because user3 is a member of the proc_executor role, which was previously granted permission to invoke stored procedures in the CodeModuleTypes database (see the alter role and grant permission statements from the preceding script). Additionally, SQL Server runs the stored procedure in the context of its owner through ownership chaining. See this prior tip for coverage of permissions to run stored procedures and rights granted to objects within the stored procedure. You can grow your exposure to ownership chaining from this Microsoft Docs page.
- The last segment of the following script is another Execute As statement followed by a try…catch block for executing uspMyFourthStoredProcedure.
- The Execute As statement transfers control to user2 from user3.
- An exec statement for the stored procedure runs within a try segment of a try…catch block.
- Because user2 does not have permission to run stored procedures, the code transfers control to the catch block, which displays the error_number and error_message for the error.
-- run in user3 context
EXECUTE AS user = 'user3'
--Verify the execution context.
select suser_name() [login name], user_name() [user name];
-- select for user3 is denied on the 'soh_col_subset' table
-- because user3 is not a member of db_datareader
-- fixed-database role
begin try
select top 5 * from soh_col_subset
end try
begin catch
select
error_number() [Error Number]
,error_message() [Error Msg]
end catch
-- run in user2 context because user3 has
-- permission to impersonate user2
EXECUTE AS user = 'user2'
--Verify the execution context.
select suser_name() [login name], user_name() [user name];
-- select for user2 succeeds on the 'soh_col_subset' table
-- because user2 is a member of db_datareader fixed-database role
select top 5 * from soh_col_subset
-- revert to context before user2 (this is context for user3)
revert;
select suser_name() [login name], user_name() [user name];
-- exec for stored procedure succeeds because user 3 belongs to proc_executor role
exec dbo.uspMyFourthStoredProcedure
-- run in user2 context because user3 still
-- has permission to impersonate user2
EXECUTE AS user = 'user2'
--Verify the execution context.
select suser_name() [login name], user_name() [user name];
-- exec for stored procedure fails because user2 has no path to executing a stored procedure
begin try
exec dbo.uspMyFourthStoredProcedure
end try
begin catch
select
error_number() [Error Number]
,error_message() [Error Msg]
end catch
The following screen shot displays the results set from the preceding script. The bullets below summarize the operation of the script via a review of the output from the script.
- The first pane of the results set displays the login and user names immediately after the first Execute As statement. After the context switch, the code operates in the context of user3.
- The second pane in the results set displays the outcome of an attempt to perform a select statement from the soh_col_subset table within a try…catch block. The select statement in the try segment fails with an error number of 229 because user3 does not have permission to select from any tables in the CodeModuleTypes database. The second pane also presents the error message for error number 229.
- However, user3 does have permission to invoke an Execute As statement that switches the user context to user2. The login and user names from the select statement after the Execute As statement within the third pane confirm the operation of the Execute As statement.
- The fourth pane in the results set shows the output from an attempt to select from the soh_col_subset table. Because this attempt operates in the context of user2, which is a member of the db_datareader fixed-database role, the attempt succeeds and output displays.
- The fifth pane shows the login and user names after a revert statement transfers the user context back one in the user context stack to user3.
- The sixth pane displays the output from uspMyFourthStoredProcedure. This stored procedure contains a select statement for the top five rows from the soh_col_subset table. The exec statement for the stored procedure succeeds because user3 is a member of the proc_executor role, which has permission to invoke the Execute statement throughout the database.
- The seventh pane shows the login and user names after an Execute As statement transfers the user context to user2 from user3.
- The last block of code from the preceding script tries to invoke uspMyFourthStoredProcedure from the context of user3 within a try…catch block. The attempt fails with an error number of 229. The error message displaying in the eighth pane of the results set explains why. Permission to invoke uspMyFourthStoredProcedure is denied because user2 does not have Execute permission in the dbo schema of the CodeModuleTypes database.
Restoring from a script with test accounts and permissions
After running code for evaluating security with context switching, it is very useful for you to restore your database to its status before setting up and/or even modifying a test workbench. Without the removal of workbench settings, you are very likely to incur conflicts for objects that you may want to create or use differently in other subsequent workbench tests. The following script shows an approach to this task within the context of the current tip. Notice that the most granular security settings are removed first, such as the impersonate permission for user3 and the Execute permission for the proc_executor role. The last objects to be removed are logins.
- The first statement is for two revert statements. These statements switch the user context back to the high-level account used to set up the test workbench. In general, run as many successive revert statements as necessary to transfer control to the initial account for initially configuring a test workbench. Recall that you can verify the current login and user names with this query:
select suser_name() [login name], user_name() [user name]
- Next, revoke the impersonate permission and revoke the execute permission for proc_executor.
- Then, drop user3 from the proc_executor custom role. After that, drop the proc_executor custom role.
- You can then drop uspMyFourthStoredProcedure from the dbo schema.
- Next, you can drop user1 and user2, respectively, from the db_owner and db_datareader roles.
- Finally, you can drop the test user accounts (user1, user2, and user3) followed by dropping the test login accounts (login1, login2, and login3).
-- Remove temporary principals and associated permissions
-- and role memberships
-- revert to sysadmin account
revert;revert;
--Verify the execution context.
select suser_name() [login name], user_name() [user name];
revoke impersonate on user::user2 to user3
revoke execute to proc_executor
--alter role proc_executor drop member user2;
alter role proc_executor drop member user3;
drop role proc_executor
if object_id(‘dbo.uspMyFourthStoredProcedure’) is not null
drop proc dbo.uspMyFourthStoredProcedure
go
exec sp_droprolemember'db_owner', 'user1'
exec sp_droprolemember'db_datareader', 'user2'
go
drop user user1;
drop user user2;
drop user user3;
drop login login1;
drop login login2;
drop login login3;
Next Steps
- You can try out the code examples for this tip with the table created in the Storing Results Sets from a Stored Procedure tip. Recall that the table’s name is soh_col_subset.
- Next, copy the script(s) that you want to test and modify. Initially examine the output from the test scripts to verify the code performs as described. Then, modify the code as your curiosity leads you.
- Finally, modify the script(s) to reference another source database with users, roles, and tables that follow from your requirements.
