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


Continues…

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |