Context Switching or Database Impersonation using EXEC AS
//
//
The switching of execution context of the module is valid only for the duration of the module execution or as long as no REVERT statement is used or switching the context to another login/user or session is disconnected.
Example: - In this example, we create a stored procedure which will be called by a database user to log an error in a log table and will be executed in the execution context of the caller. This stored procedure will first check if the log table already exists, if not it will impersonate the database owner (which has privileges to create a table) to create the log table and grant permission to INSERT and SELECT from the table to the database user and finally revert the execution context back to caller.
|
USE AdventureWorks; GO – Create two temporary principals for demonstration. CREATELOGIN DBUserLogin WITHPASSWORD=’ABCD1234!@#$’; CREATELOGIN DBOwnerLogin WITHPASSWORD=’ZXCV9876(*&’; GO – Create two users and map them with created logins CREATEUSER DBUser FORLOGIN DBUserLogin; CREATEUSER DBOwner FORLOGIN DBOwnerLogin; GO – Give IMPERSONATE permissions on DBOwner to DBUser – so that DBUser can successfully set the execution context to DBOwner. GRANT IMPERSONATE ONUSER:: DBOwner TO DBUser; GO – Add DBOwner user to the db_owner role EXECsp_addrolemember’db_owner’,'DBOwner’ GO – Create a procedure which logs error message into log table if the table – already exists or else create the table first and logs the error message CREATEPROCEDURE dbo.LogError(@ErrorMessage VARCHAR(200)) WITHEXECUTEASCALLER AS BEGIN — Display the execution context (Login and User) of the user executing this section of the code for demonstration SELECTSUSER_NAME()AS [LoginName],USER_NAME()AS [UserName]; IFOBJECT_ID(‘GlobalLog’)ISNULL BEGIN — Switching execution context to DBOwner, a more privileged user to create the GlobalLog table EXECUTEASUSER=’DBOwner’; — Display the execution context (Login and User) of the user executing this section of the code for demonstration SELECTSUSER_NAME()AS [LoginName],USER_NAME()AS [UserName]; CREATETABLE GlobalLog ( [ID] INTPRIMARYKEYIDENTITY, ErrorMessage VARCHAR(200) ); — Grant user permission to insert and select from the newly created GlobalLog table GRANTSELECT, INSERTON GlobalLog TO DBUser; — Reverting back the execution context to the original caller to limit the permission REVERT; — Display the execution context (Login and User) of the user executing this section of the code for demonstration SELECTSUSER_NAME()AS [LoginName],USER_NAME()AS [UserName]; END — Display the execution context (Login and User) of the user executing this section of the code for demonstration SELECTSUSER_NAME()AS [LoginName],USER_NAME()AS [UserName]; — Insert the log record INSERTINTO GlobalLog( ErrorMessage )VALUES ( @ErrorMessage ) END GO – Grant user permission to execute the stored procedure GRANTEXECUTEON dbo.LogError TO DBUser |
No comments yet... Be the first to leave a reply!