• Article

Extending Database Impersonation by Using EXECUTE AS

SQL Server supports the ability to impersonate another principal either explicitly by using the stand-alone EXECUTE AS statement, or implicitly by using the EXECUTE AS clause on modules. The stand-alone EXECUTE AS statement can be used to impersonate server-level principals, or logins, by using the EXECUTE AS LOGIN statement. The stand-alone EXECUTE AS statement can also be used to impersonate database level principals, or users, by using the EXECUTE AS USER statement.

Implicit impersonations that are performed through the EXECUTE AS clause on modules impersonate the specified user or login at the database or server level. This impersonation depends on whether the module is a database-level module, such as a stored procedure or function, or a server-level module, such as a server-level trigger.

Understanding Impersonation Scope

When impersonating a principal by using the EXECUTE AS LOGIN statement, or within a server-scoped module by using the EXECUTE AS clause, the scope of the impersonation is server-wide. This means that after the context switch, any resource within the server that the impersonated login has permissions on can be accessed.

However, when impersonating a principal by using the EXECUTE AS USER statement, or within a database-scoped module by using the EXECUTE AS clause, the scope of impersonation is restricted to the database by default. This means that references to objects outside the scope of the database will return an error. To understand the reason for this default behavior, consider the following scenario.

It is possible that the owner of a database, while having full permissions within that database, does not have any permissions outside the scope of the database. Therefore, SQL Server does not allow the database owner to impersonate, or grant someone else the ability to impersonate, another user in order to access resources beyond the scope of the current permissions of the database owner.

For example, consider two databases in a hosting environment and each database belongs to a separate owning entity. Database1 is owned by Bob and Database2 is owned by Fred. Neither Bob nor Fred wants the other to access resources within their respective databases. As the owner of Database1, Bob can create a user for Fred in his database and because he has full permissions within Database 1, Bob can also impersonate user Fred. However, because of the security restrictions imposed by SQL Server, Bob cannot access Fred’s database under the impersonated context. Without these default restrictions in place, Bob would be able to access Fred’s data without his knowledge. This is why the scope of database-level impersonations is bounded by the database by default.

However, in certain scenarios it may be useful to selectively extend the scope of impersonation beyond the database. For example, this would be the case with an application that uses two databases and requires access to one database from the other database.

Consider the case of a marketing application that invokes a stored procedure named GetSalesProjections in the Marketing database and the stored procedure has an execution context switch defined in it. The stored procedure calls into the Sales database to retrieve sales information from the SalesStats table. By default, this scenario will not work, because an execution context established inside one database is not valid outside that database. However, the developers of the marketing application do not want users of the marketing application to have direct access to the Sales database or have permissions on any objects within it. The ideal solution would be to use the EXECUTE AS clause in the stored procedure to impersonate a user that has the required permissions in the Sales database. However, the default restrictions currently in place prevent this. So, the question is how can the developers resolve this problem.

In SQL Server, you can selectively extend the scope of the database impersonation established within a database by establishing a trust model between the two databases. However, before describing this trust model and how the scope of the impersonation can be selectively extended, you should understand authentication and the role of authenticators in SQL Server.

Understanding Authenticators

Authentication is the process by which a particular principal establishes and proves its identity to a system. An authenticator is an entity that authenticates, or vouches for, the authenticity of a particular principal. For example, when a connection is made to SQL Server, the login that is established for that connection is authenticated by the instance of SQL Server.

Consider the case in which a user explicitly switches context at the server-level using the EXECUTE AS LOGIN statement. This requires impersonation permissions at the server level. These permissions allow the grantee of the permission, the caller of the EXECUTE AS LOGIN statement, the ability to impersonate the specified login everywhere within the instance of SQL Server. In effect, the statement allows the caller to simulate the act of logging in as that impersonated login. The owner of the server-level scope of permissions is the sysadmin that owns the instance of SQL Server. In this case of server-level impersonation, the authenticator is the sysadmin or the instance of SQL Server itself.

However, consider the case in which context is established, because of an EXECUTE AS USER statement or an EXECUTE AS clause on a database-scoped module. In these cases, impersonation permissions within the scope of the database are checked. The default for the scope of the IMPERSONATE permissions on users is the database itself, the owner of which is the dbo. Also, the authenticator of these impersonations is the database owner. Additionally, it is the database owner who, in effect, establishes the identity of the impersonated user and vouches for its authenticity. Because the database owner owns the complete database, the impersonated context is considered authentic everywhere within that specific database. However, outside that database, the impersonated context is not valid.

How Authenticators are Used

Authenticators are used to determine whether an established context is valid within a particular scope. Frequently, the authenticator is either the system administrator (SA) or the instance of SQL Server, or with databases, the dbo. The authenticator is effectively the owner of the scope within which the context for a particular user or login is established. This authenticator information is captured within the token information maintained for the login and user and is visible through the sys.user_token and sys.login_token views. For more information, see Understanding Execution Context.

Note

If no authenticator information is returned in the token view, the authenticator is the instance of SQL Server. This is true if there is no context switching or if the impersonation is at the server level.

An execution context that has the owner of a scope as its authenticator is valid across that scope. This is because the owner of a scope, for example, a database, is implicitly trusted by all entities within the scope. The context is also valid across other scopes, for example, other databases or the instance of SQL Server itself, in which the authenticator is trusted. Therefore, the validity of the impersonated user context outside the scope of the database depends on whether the authenticator for the context is trusted within the target scope. This trust is established by granting the authenticator AUTHENTICATE permission if the target scope is another database, or AUTHENTICATE SERVER permission if the target scope is an instance of SQL Server.

Extending the Scope of Impersonation

In order to extend the scope of an impersonation from within a database to a target scope, such as another database or the instance of SQL Server, the following conditions must be met.

  • The authenticator must be trusted in the target scope.

  • The source database has to be marked as trustworthy.

Trusting the Authenticator

Using the previous Sales and Marketing databases example, the following illustration shows the stored procedure GetSalesProjections in the Marketing database accessing data in the SalesStats table in the Sales database. The stored procedure contains the clause EXECUTE AS USER MarketingExec. The owner of the Sales database is SalesDBO and the owner of the Marketing database is MarketingDBO.

EXECUTE AS switches execution context of a module

When the GetSalesProjections stored procedure is invoked by a user, the EXECUTE AS clause implicitly switches the execution context of the stored procedure from the calling user to the MarketingExec user. The authenticator for this context is MarketingDBO, the owner of the Marketing database. By default, this procedure can access any resources within the Marketing database that the MarketingExec user is allowed to access. However, in order to access a table in the Sales database, the Sales database must trust the authenticator MarketingDBO.

You can do this by creating a user in the Sales database named MarketingDBO that maps to the MarketingDBO login and then granting that user AUTHENTICATE permission on the Sales database. As a result, any execution context that has the grantee of this permission as its authenticator is valid within the database. Because the authenticator MarketingDBO is granted AUTHENTICATE permission in the Sales database, the context for user MarketingExec established by the EXECUTE AS clause in the GetSalesProjections stored procedure in the Marketing database is trusted in the Sales database.

While this example demonstrates extending the scope of impersonation to allow access to an object in an external database, it is also possible to extend scope of impersonation to the instance of SQL Server. For example, if the procedure were to create a login, which is a server-level action that requires a server-wide permission, the AUTHENTICATE SERVER permission would have to be granted to the authenticator of the context. This has the semantic that any context that has the grantee of the AUTHENTICATE SERVER permission as its authenticator is trusted across the whole instance of SQL Server**,** as if the context were logging in to the instance of SQL Server directly.

Trusting the Database

In SQL Server, the trust model goes one more step in order to provide additional security and granularity to the act of extending the scope of database-level impersonation. You can use the AUTHENTICATE permission as a way for the target scope to trust the authenticator of a context, but you can also determine whether the instance of SQL Server trusts the source database and the contents within it.

To illustrate this, assume that the MarketingDBO principal owns another database named Conference. Also assume that MarketingDBO wants the execution contexts that are specified within the Marketing database to have access to the resources in the Sales database. However, it does not want any contexts that are established in the Conference database to have any access to the Sales database.

To achieve this requirement, the database that contains the module in which an impersonation context is used to access resources outside the database must be marked as trustworthy. The TRUSTWORTHY property indicates whether the instance of SQL Server trusts the database and the contents within it. The TRUSTWORTHY property serves two purposes:

  1. It reduces the threat coming from databases that are attached to the instance of SQL Server and can potentially contain malicious modules defined to execute under the context of a high privilege user.

    This is accomplished by making sure that attached databases are not marked trustworthy by default. It is also accomplished by making sure that access to resources outside the database through potentially malicious modules requires that the database be marked trustworthy. Setting the TRUSTWORTHY property on a database is restricted to members of the sysadmin fixed server role.

  2. It allows the administrator of the instance of SQL Server to distinguish among databases that should be allowed to access external resources and those that should not when the databases have the same owner and that owner is trusted as an authenticator in some scope.

This behavior can be controlled by using the TRUSTWORTHY property. For example, assume that you have a situation where impersonated contexts from one database, Database 1, should be trusted while contexts from another database, Database 2, should not be trusted, and both have the same owner who is trusted as an authenticator in the target scope. The TRUSTWORTHY property can be set to ON for database1 and set to OFF for database2 to make sure that modules in Database 2 cannot access resources outside that database.

The following illustration shows the use of the TRUSTWORTHY database property to control access to resources outside the scope of the source database. MarketingDBO is granted AUTHENTICATE permissions in the Sales database and is the owner of both the Marketing and Conference databases. The GetSalesProjections stored procedure in the Marketing database can successfully access the Sales database, because it meets the two security requirements: the authenticator, MarketingDBO, is trusted in the target scope, and the source database, Marketing, is trustworthy. Attempts to access the Sales database from the Conference database are denied, because only one requirement is met: the authenticator, MarketingDBO, is trusted in the target scope.

Controlling database access to external resources

Whenever an attempt is made to access a resource outside the scope of the database by using an impersonated context, the instance of SQL Server verifies that the database from where the request originated is trustworthy and that the authenticator is trusted.

Certificates and Asymmetric Keys as Authenticators

An impersonation context established within a database can be extended to access resources outside the scope of the database by using the database owner as an authenticator. This requires that the database owner be trusted by the external resource and that the database itself is also trustworthy. However, this approach implies that when a trusted database owner is granted AUTHENTICATE or AUTHENTICATE SERVER permissions in the target scope and the calling database is trustworthy, any impersonated context established in that database is valid across the target scope that trusts the database owner.

A more granular level of trust may be required. Assume that the business requirement indicates trusting just a few modules in the source database by using the EXECUTE AS clause to access the target resource, but not trusting the whole source database. For example, assume the SalesDBO wants to make sure that only the GetSalesProjections stored procedure could access the SalesStats table as the MarketingExec user, but does not want everyone in the Marketing database with impersonate permissions on MarketingExec to be able to access resources in the Sales database. Trusting the MarketingDBO and setting the Marketing database to trustworthy will not accomplish that task. To provide an additional level of granularity to accomplish that requirement, the trust model allows for certificates or asymmetric keys to be used as authenticators. This takes advantage of a technique called signing. For more information about signing, see ADD SIGNATURE (Transact-SQL).

Using Signatures

Signatures on a module verify that the code within a module can only be modified by a person with access to the private key that is used to sign the module. Considering the guarantees of the signing process, you can trust the certificate or asymmetric key specified in the signature. More accurately, you can trust the owner of the certificate or asymmetric key, instead of just the database owner.

Trusting the signed module is accomplished by granting the AUTHENTICATE or AUTHENTICATE SERVER permission to the user in the target scope that is mapped to the certificate or asymmetric key.

With this approach, an execution context established within a module that is signed by using a trusted certificate is valid in the target scope in which the certificate is trusted.

For example, assume that the procedure GetSalesProjections is signed by using a certificate named C1. Certificate C1 must be present in the Sales database and a user, for example, CertUser1, must be mapped to certificate C1. CertUser1 is then granted AUTHENTICATE permissions in the Sales database.

When the procedure is invoked, its signature is verified to make sure that it was not tampered with when it was signed. If the signature is verified, the context established by the EXECUTE AS clause in the module has the certificate C1 as its authenticator. If the signature is not verified, the authenticator is not added to the token and the attempt to access the external resource fails.

The following illustration shows the use of a signed module to control access to resources outside the scope of the source database. The procedure GetSalesProjections in the Marketing database is signed by using a certificate named C1. Certificate C1 is present in the Sales database and user CertUser is mapped to the certificate. CertUser1 is granted AUTHENTICATE permissions in the Sales database.

Certificate used to restrict database access

The trust on this authenticator is verified in the same way as the trust is verified when the database owner is the authenticator. That is, it is verified by checking the AUTHENTICATE SERVER or AUTHENTICATE permission. However, because the trust is established at a granular level and the module cannot be changed without modifying the signature, there is no need for the TRUSTWORTHY property on the database to be verified.

This reduces the threat that comes from attached databases with malicious code in them. The attacker would have to sign the module with the private key that corresponds to the certificate that is already trusted . However, the attacker does not have access to this key. Also, if an existing trusted module is modified or a new one is created, the module will not have a valid trusted signature.

For more information, see Module Signing (Database Engine).

Rules for Extending Database Impersonation Scope

To summarize, the impersonation scope of a context established within a database can be extended to other scopes if and only if the following is true:

  • The authenticator, either the database owner or a certificate or asymmetric key used to sign the module, must be trusted in the target scope. You can do this by granting AUTHENTICATE or AUTHENTICATE SERVER permissions to the principal that maps to the database owner, the certificate, or the asymmetric key.

  • If the authenticator is the database owner, the source database must be marked as trustworthy. You can do this by setting the TRUSTWORTHY property to ON for the database.

Selecting the Trust Mechanism for Your Needs

There are advantages and disadvantages to both the database owner approach and the signature approach. The best mechanism for your needs depends on your business requirements and your business environment.

Database Owner Approach

The database owner approach for establishing trust has the following advantages and disadvantages:

  • It does not require any understanding of cryptography concepts such as certificates or signatures.

  • It does not provide as much granularity as the signature-based approach.

  • Attaching a database to an instance of SQL Server sets the TRUSTWORTHY property on the database to OFF. Modules trusted by the database owner will not be valid until the system administrator explicitly sets the TRUSTWORTHY property to ON. This implies that there is potentially some intervention required by the system administrator, before the attached database can function as you want and access other databases.

Signature Approach

The signature approach for establishing trust has the following advantages and disadvantages:

  • It can provide a granular level of trust, but applies only to context switches that are performed within signed modules.

  • The signature cannot be applied to context switches that are established through the stand-alone statements EXECUTE AS USER and EXECUTE AS LOGIN. These statements require the database owner-based approach to extend the scope of trust.

  • It is possible for the application vendor or developer to sign the module with a private key, but remove the private key before shipping the modules or the database. This works because the private keys are only used to sign the modules . For signature verification purposes, the public keys associated with the module are sufficient.

  • Attaching a database has no effect on modules that are trusted because of their signatures. They will work without additional requirements.