Context Switching or Database Impersonation using EXEC AS

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.

Continues…

Pages: 1 2 3




Array

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 |