By default, when a user establishes a connection to SQL server, a session starts and ends when the user disconnects from SQL Server. All operations and activities during an established session are performed under the security context of the logged on user; it means SQL Server checks permission of the user to perform any operation. So how allow a user to perform high privileged activities without allowing the user to have high privilege access?
SQL Server 2005/2008 provides the ability to change the execution/security context with the EXEC or EXECUTE AS clause. You can explicitly change the execution context by specifying a login or user name in an EXECUTE AS statement for batch execution or by specifying the EXECUTE AS clause in a module (stored procedure, triggers and user-defined functions) definition. Once the execution context is switched to another login or user name, SQL Server verifies the permission against the specified login or user (specified with EXECUTE AS statement) for subsequent execution instead of the execution context of current user. In other words, the SQL Server login or database user name will be impersonated for the remainder of the session or until the execution context is reverted, or the module completes execution or the session is disconnected. This enables DBAs to allow users with limited privileges to change the execution context to high privileged accounts only when required and switch back to the original execution context with limited privileges once the high privileged operations are completed. The REVERT clause to switches back the execution context to the caller of the last EXECUTE AS statement.
|
Note: The EXEC AS command replaces the SETUSER command(which was the only option untill SQL Server 2000) because of its inherent limitations; for example, it is restricted to members of the sysadmin fixed server role or db_owner fixed database role only, also no windows accounts are allowed, it does not allow an execution context stack to be created etc. For exhaustive list of differences between EXEC AS and SETUSER click here. |
There are five types of database impersonation (context switch) that can be performed using EXEC AS:
· EXECUTE AS CALLER– This is the default behavior for all types of modules except Service Broker queues. Under this option, all the statements inside the module are executed under the execution context of the user executing the module. As a result, the caller must have appropriate permission on the module as well as on all the objects being referenced by that module.
· EXECUTE AS OWNER – With this option, all the statements inside the module are executed under the execution context of the creator of the module or the owner of the module. If the module does not belong to an owner then in that case the execution context of the schema owner of the module will be used. One point to note is the owner must be an individual user, not a Windows group or database role. You cannot use EXECUTE AS OWNER for DDL triggers with server scope or logon triggers in which case EXECUTE AS login_name should be used.
· EXECUTE AS SELF–This option is similar to use of EXECUTE AS user_name, where the specified user is the person who has created or modified the module. The person who modifies the module might not be necessarily the same as its owner. In other words it designates the user who creates has last modified the module definition as the one whose security context will be used to execute it. For Service Broker queues, EXECUTE AS SELF is the default option.
· EXECUTE AS ‘user_name’–With this option, the execution context of a particular or specified database user is used for the duration of module execution or until revert statement.This means that permissions on all referenced objects by the module are verified against the specifieduser_name, rather than against the original caller of the module. user_name must exist in the current database and must be a singleton account, not a Windows group or database role. This option willalso not work with DDL triggers with server scope or logon triggers; rather you need to use EXECUTE ASlogin_nameoption as discussed below.
One point to note is that by default the scope of using EXECUTE AS user_name applies at database level only after impersonation (context switch) – if you refer an object outside the scope of the database, it will throw an error.
· EXECUTE AS ‘login_name’ – With this option the execution context of a specified SQL Server login is used for the duration of module execution or until revert. This means that permissions on all referenced objects by the module are verified against the specified login_name, rather than against the original caller of the module. login_name cannot be a group, role, certificate, key, or built-in account. Unlike the user_name option which has database scope, the login_name is server scoped and can only be specified for DDL triggers with server scope or logon triggers.
As I said, EXECUTE AS login_name has server wide scope it means after the context switch, the impersonated user can access all server resources which login_name has access on.
|
Note: · The sys.sql_modules or sys.assembly_modules catalog views store the user id of the execution context in the execute_as_principal_id column. · The caller needs to have IMPERSONATE permission to impersonate a login or user. |
No matter what option is being used inside a module definition, the caller of the module must have EXECUTE permission on the module.
The SQL Server database engine first verifies that the caller of the module has EXECUTE permission or not, if yes then:
· If the module has EXECUTE AS CALLER, which is the default option, the additional permissions are also checked against the caller of the module for the objects which are being accessed by the module.
· If the module has an option other than EXECUTE AS CALLER, then the additional permission checks for objects which are being accessed by the module are performed against the login or user account specified with the EXECUTE AS clause. In effect, the caller of the module is impersonating the specified login or user.