(last updated: 2018-03-27)

Despite features added in SQL Server 2005 (yes, 2005!) that allow for very flexible, granular, and robust security, it is still quite common for people to be using the older and/or riskier mechanisms of temporarily granting additional privileges.

What follows is an overview of a presentation that I have given a few times, and will continue presenting, on the topic of Module Signing, and why it is far superior to using any combination of Cross-Database Ownership Chaining, Impersonation, or TRUSTWORTHY:

Module Signing or: How I Learned to Stop Using EXECUTE AS and Love Certificates

Common “Problem” Scenarios

Proper security starts with giving users the lowest set of permissions necessary. Quite often we run into a situation where a user is not able to execute something with their minimal permissions. Those situations are most often the following:

• Need Elevated Permission that is not Grantable

  Certain operations do not have a specific, defined permission that can be granted (e.g. SET IDENTITY_INSERT ON). In these cases, it is usually required to be in at least one Server-level Role (e.g. sysadmin) or Database-level Role (e.g. db_owner), or have been granted a high-level permission (e.g. CONTROL SERVER), etc.

• Need Elevated Permission that is not Granular

  Certain operations do have a specific, defined permission, BUT that permission allows access to far more than is desired. For example, sys.dm_exec_query_stats requires the VIEW SERVER STATE Server-level permission. However, VIEW SERVER STATE allows access to most DMVs.

• Cross-Database Operations

  By default, a process is confined to the Database in which it is initiated. However, it is not uncommon to need to access objects in other Databases. Sometimes, the user doesn’t even have access to the other Database(s).

• Dynamic SQL

  Dynamic SQL breaks ownership chaining, including Cross-Database Ownership Chaining.

Ownership Chaining

Ownership chaining is the basic security mechanism that allows Stored Procedures, Views, etc to be an API to the data model (i.e. the Tables) so that permissions do not need to be granted to any Tables. Anyone who has permission to access a particular object will be able to access any objects referenced in that object as long as the referenced objects are owned by the User that owns the initial object. Object ownership is determined by:

• IF principal_id in sys.objects is NOT NULL
  • THEN use principal_id in sys.objects
  • ELSE use principal_id in sys.schemas for schema_id that matches the value in sys.objects

Ownership chaining does not need to be enabled; it is simply how SQL Server works. Assumed permissions apply to DML, SELECT, and EXEC[UTE] statements, but not to DDL statements or statements like SET IDENTITY INSERT ON, TRUNCATE TABLE, etc.

Using Dynamic SQL breaks ownership chaining and permissions will be reevaluated as the Dynamic SQL is being compiled.

Cross-Database Ownership Chaining

By default, ownership chaining only works within a Database and does not extend out to other Databases (or even up to the Server-level). The simplest way around this restriction is to enable Cross-Database Ownership Chaining. There are two ways to accomplish this:

1. Enable for all Databases at the Server-level:

     EXEC sp_configure 'cross db ownership chaining', 1;
     RECONFIGURE;
     

   When this setting is enabled, all Databases are enabled and cannot be disabled individually. When this setting is disabled, Databases can be enabled individually, at the Database-level.

2. Enable for individual Databases at the Database-level:

     ALTER DATABASE { [database_name] | CURRENT } SET DB_CHAINING ON;
     

   When the Server-level setting is enabled, this setting has no effect. When the Server-level setting is disabled, this setting is used to enable Databases individually.

Cross-Database ownership chaining requires that the same SID for the owner of the objects exist in both Databases. This should probably be expected, because otherwise the objects cannot truly have the same owner. What might not be expected is that the User accessing the object also needs to exist in the other Database(s). They don’t need to have any permissions assigned in the other Database(s), they just need to exist with the same SID. However, they will have access to everything that is available to the public Role.

When using Dynamic SQL to access the other Database(s), then Cross-Database ownership chaining doesn’t work, same as with regular ownership chaining.

To be clear, Cross-Database Ownership Chaining, just like ownership chaining, cannot be used to elevate permissions. It can only be used to assume basic permissions to allow for DML, SELECT, and EXEC[UTE] statements.

Impersonation

Impersonation can be thought of as being “Instead Of” permissions. This is due to the current set of permissions being switched out (completely) for the set of permissions associated with the Login or User being impersonated. The session is operating with a new set of permissions instead of the old ones. Also, the Security ID (SID) of the executing User changes to be the SID of the User being impersonated.

I consider this means of managing permissions to be “account-based” security since it is a real Login or User that is being impersonated in order to assume their permissions. The user starts out with their permissions but then becomes an entirely different account with an entirely different set of permissions.

Becoming a different account is accomplished using EXECUTE AS. There are two ways to use EXECUTE AS:

Clause

The EXECUTE AS clause is part of the CREATE statement for: Stored Procedures, Triggers, and most types of Functions. The EXECUTE AS clause is not available for Views or T-SQL Inline Table-Valued Functions (iTVFs).

The principal_id to impersonate using this clause can only be a Database-level User.

When using this clause, whoever is able to execute this module does not need to have explicit IMPERSONATE permission granted on the to-be-impersonated User. This allows you to specify EXECUTE AS OWNER or EXECUTE AS N'dbo' without requiring that you allow the executing User to impersonate this principal whenever they want (which would be the case if you granted IMPERSONATE permission to the User).

The impersonated context is in effect until the module ends. Executing REVERT within a module creating with this clause has no effect.

By default, the process is confined / quarantined to the local / current Database while the impersonated context is in effect. The only way to break out of the quarantine to reach up to the Server-level or across to another Database, as the same User, is to set the Database property of TRUSTWORTHY to ON. Otherwise, with TRUSTWORTHY set to OFF, you are only permitted to access Databases in which the guest User has been enabled (by granting it the CONNECT permission). The system Databases of master, msdb, and tempdb all have the guest User enabled, but model does not. When using another Database as the guest User you can only access what has been granted to the public Role.

Statement

The EXECUTE AS statement can be executed in any T-SQL batch or within Stored Procedure and Triggers. It cannot be executed within a View or any type of Function.

Unlike the EXECUTE AS clause, the statement can be used to impersonate Server-level Logins as well as Database-level Users. Another difference is that when using the statement, explicit IMPERSONATE permission is required on the principal being impersonated.

When using the EXECUTE AS LOGIN statement, you can USE any Database in which a User, having the same SID as the impersonated Login, has been created. In this case, you would be the same User you were in the original Database, and not the guest User, even when TRUSTWORTHY is set to OFF in the original Database.

When using the EXECUTE AS USER statement, the process is confined / quarantined to the local / current Database by default. Just like with the EXECUTE AS clause, the only way to break out of the quarantine to reach up to the Server-level or across to another Database, as the same User, is to set the Database property of TRUSTWORTHY to ON. Otherwise, with TRUSTWORTHY set to OFF, you are only permitted to access Databases in which the guest User has been enabled (by granting it the CONNECT permission). The system Databases of master, msdb, and tempdb all have the guest User enabled, but model does not. When using another Database as the guest User you can only access what has been granted to the public Role.

The impersonated context is in effect until either the sub-process or Session ends (depending on when EXECUTE AS was executed), or REVERT is executed (at the same nesting level). The impersonated context cannot be reverted in:

• a sub-process, even if just for that sub-process. Meaning, executing REVERT in a sub-process has absolutely no effect, not even within the sub-process.
• a Database other than the originating Database. Meaning, REVERT can only be executed when the current / active Database is the same Database that was current / active when the EXECUTE AS statement was executed.

AS { LOGIN | USER } Clause of the EXECUTE ('string') Command

When doing context switching via the EXECUTE command (i.e. EXECUTE ('string') AS { LOGIN | USER } = ' name ' ), the requirements and behavior are the same as with the EXECUTE AS statement. This is why there are really only two ways to do Impersonation.

REASONS TO AVOID THESE METHODS

Admittedly, using Cross-Database Ownership Chaining and/or Impersonation and/or TRUSTWORTHY are quicker and easier to implement than Module Signing. However, the relative simplicity in understanding and implementing these options comes at a cost: the security of your system, and to a lesser degree, performance.

• Cross-DB Ownership Chaining:
  • Security risk:
    • Can access whatever the same owner can access in the other Database(s)
    • Executing User must exist in the other Database(s) and will have access to whatever has been granted to the public Role
  • db_ddladmin & db_owner users can create objects for other owners
  • Users with CREATE DATABASE permission can create new databases and attach existing databases — this gives them full control to create a new Database in which they can create Users that exist in Databases that they want to gain access to, and then create objects for those Users that access the “restricted” Databases.
  • Dynamic SQL requires TRUSTWORTHY ON
• Impersonation:
  • If IMPERSONATE permission is required:
    • can be used any time — you cannot control when someone executes the EXECUTE AS statement
    • No granular control over permissions — Login or User can do / access everything that the account they are impersonating can do / access.
  • Cross-DB operations need TRUSTWORTHY ON (when impersonating a User, not a Login)
  • Need to use ORIGINAL_LOGIN() for Auditing
  • Elevated permissions last until process / sub-process ends or REVERT is executed
  • A larger hit / drag on performance than Module Signing (test results coming soon, in a separate post)
• TRUSTWORTHY:
  • Bigger security risk
    • Can also spoof Logins, such as “sa”! — If the database owner is sa, then any process running at dbo becomes sa and then has full control over the server and access to all Databases.
    • If using SQLCLR Assemblies, no per-Assembly control of ability to be marked as either EXTERNAL_ACCESS or UNSAFE; all Assemblies are eligible to be marked as either of those elevated permission sets.

The common theme across all three areas is no control, within a Database, over who or what can make use of the feature / option, or when it can be used.

Module Signing

Module Signing can be thought of as being “In Addition To” permissions. This is due to the current set of permissions remaining (unchanged) while one or more new permissions are added to the current security context. The session is operating with the original set of permissions combined with a new set of permissions. Also, the Security ID (SID) of the executing User does not change.

I consider this means of managing permissions to be “code-based” security since permissions are effectively being granted to modules instead of accounts. Even though a Login and/or User, created from the Asymmetric Key or Certificate, are required to contain the additional permissions, the Key / Certificate -based principals are merely containers for the new permissions (i.e. proxies) and can neither connect to SQL Server nor be impersonated. The executing User does not change (SID and permissions remain the same). But, once code that has been signed begins executing, permissions granted to a principal created from the same Certificate or Asymmetric Key that was used to sign the code are added to the security context.

The following types of modules can be signed:

• Stored Procedures
• Triggers
• Multi-statement Table-Valued Functions (a.k.a. msTVFs)
• Scalar Functions
• SQLCLR Assemblies

Neither Inline Table-Valued Functions (a.k.a. iTVFs) nor Views can be signed. And, unfortunately, DDL Triggers and Logon Triggers also cannot be signed (please see, and hopefully vote for, my attempt to get this fixed: Allow signing Database DDL Triggers and Server DDL and Logon Triggers – ADD SIGNATURE).

Signing a module is accomplished using ADD SIGNATURE. There are two ways to use ADD SIGNATURE:

Signature

Adding a signature is simply applying a Certificate or Asymmetric Key to a module. Using a regular signature (i.e. not including the word COUNTER in the ADD SIGNATURE statement) allows any module, signed with the specified Certificate or Asymmetric Key, to inherit any permissions granted to any principal created from that same Certificate or Asymmetric Key.

The additional permissions apply to only the signed module(s). Any modules referenced by the signed module do not get the additional permissions. Remember, Module Signing is a very precise mechanism, and permissions are only applied to modules that have been explicitly signed. If modules referenced by the signed module also need additional permissions, then they will also need to be signed.

Counter-Signature

When modules referenced by the signed module also need additional permissions, then you have a choice for how to proceed. You can sign the referenced modules, as noted above, or you can counter-sign it. Counter-signing is very similar to regular signing except that it can only be used to keep the additional permissions (associated with the Certificate or Asymmetric Key), that were provided to a calling module, flowing down to a referenced module.

Meaning, if Module A is signed and calls Module B, by default Module B will not have any of the extra permissions that were given to Module A via the signature. However, if Module B were to be counter-signed using the same Certificate or Asymmetric Key that was used to sign Module A, then Module B would also have those extra permissions. Now, the difference between signing Module B and counter-signing Module B is that, when counter-signing Module B, those extra permissions will only be there if Module B is called by Module A; if Module B is executed directly, or from some other module that isn’t signed by the same Certificate or Asymmetric Key, then the extra permissions will not be added to the process.

While this is a rather interesting feature / ability, it seems to have very limited usefulness. The only benefit would be for scenarios in which:

• a User has EXECUTE permission on multiple objects where one calls another that calls yet another ( A -> B -> C ), and
• one or more modules ( B and / or C ) beyond the initially called module ( A ) need additional permissions, and
• for some reason you want to force the User to always start with the first module ( A )

Some possible scenarios that would benefit from counter-signing are:

1. Stored Procedure A contains Dynamic SQL (which breaks ownership chaining). User Bob is allowed to execute Stored Proc A which will work correctly when referencing objects that Bob owns or has been given explicit permissions on. There are other objects that Bob neither owns nor has permissions on, and you want to restrict which ones can be acted upon by Stored Procedure A (when executed by Bob), or you need to enforce a particular workflow or set of business rules for those other objects.

   If you counter-sign Stored Procedure A, then you can sign Stored Procedure B which executes Stored Procedure A in predefined ways. Giving Bob EXECUTE permission on both Stored Procedures allows Bob to use Stored Procedure A in an open-ended manner, but only on objects that Bob could manipulate anyway. But, Bob would only be able to use Stored Procedure A on restricted objects by executing Stored Procedure B which has additional steps to control the interaction with those restricted objects. And, if Bob attempts to alter Stored Procedure B so that it will do something else, the signature will be dropped and it will no longer be able to access the restricted objects.

2. Multistatement Table-Valued Function (TVF) C selects from a Dynamic Management View (DMV) that only returns rows related to the current Login unless the current Login is a member of one of the Server-level Roles that allows for getting all rows (e.g. sys.dm_exec_sessions, sys.dm_exec_requests, etc). It also joins a few other tables and has some derived fields (i.e. column based on an expression) such that it is not a simple SELECT * FROM dmv and makes sense to have encapsulated as a module. Sally should be able to SELECT from TVF C as it will only return rows for Sally. Sally also needs to execute a maintenance Stored Procedure D that uses the results of TVF C, but this maintenance operation requires seeing all rows that could be returned from that DMV, not just for Sally. You do not want to duplicate the complex query that is in the TVF as that is the reason you created the TVF: code re-use. The DMV requires Server-level permission in order to return all rows (e.g. VIEW SERVER STATE) and you correctly do not want to enable TRUSTWORTHY in order to get that Server-level permission (since adding WITH EXECUTE AS N'dbo' to the CREATE PROCEDURE statement is confined to the Database-level, unless evil TRUSTWORTHY is enabled).

   If you sign Stored Procedure D, counter-sign TVF C, and GRANT VIEW SERVER STATE to a Login created from the Certificate used to sign and counter-sign C and D, then TVF C will have VIEW SERVER STATE only when referenced within Stored Procedure D. When Sally selects from TVF C outside of Stored Procedure D, that Server-level permission will not be added to the process because the TVF was merely counter-signed.

Please ignore the “Countersignatures” example scenario in the Microsoft documentation for ADD SIGNATURE as it is an invalid case. It would be simple enough to just:

• not grant EXECUTE on the procSelectT1 intermediate Stored Procedure to public
• grant EXECUTE on procSelectT1 only to the ucsSelectT1 Certificate-based User
• sign both Stored Procedures with the Certificate

A counter-signature doesn’t provide any benefit for that example scenario. If you haven’t yet seen that example, don’t bother ;-).

BENEFITS OF MODULE SIGNING

• Privileged principals (i.e. Certificated- / Asymmetric Key- based Login and/or User) cannot log in / connect or be impersonated (they are merely proxies / containers for permissions)
• Very granular permissions
• No security holes (e.g. TRUSTWORTHY, Impersonation, and Cross-Database Ownership Chaining)
• Signature is dropped if code is changed !! This effectively forces a code review since the operation will no longer work as the additional permissions are gone until the module is re-signed. If re-signing requires intervention by a DBA, then that provides the DBA an opportunity to investigate what changed and decide if the module should be re-signed or needs to be fixed before being re-signed.
• Signature is invalidated (but not dropped!) IF the module was created using WITH EXECUTE AS …
  • OWNER: and the effective owner of the module changes. Ownership is determined by the principal_id column of sys.objects, and if that is NULL (which is the default value, or can be set using SCHEMA OWNER) then the principal_id column of sys.schemas for the schema_id that matches sys.objects.schema_id for that module. Things that can change ownership:
    1. Changing the object’s direct owner:
       1. to a different explicit value, or
       2. to an explicit value from NULL, or to NULL from an explicit value IF the explicit value is different than the principal_id column of sys.schemas for that module’s Schema
    2. Changing the object’s Schema IF the principal_id column of sys.objects is NULL and IF the principal_id column of sys.schemas has different values between the old and new Schemas
    3. Changing the object’s Schema’s owner IF the principal_id column of sys.objects is NULL
    4. Changing the Database’s owner IF:
       1. the object is directly owned by dbo, or
       2. the principal_id column of sys.objects is NULL and the Schema is owned by dbo
  • N’dbo’: and the owner of the Database changes. This is due to a value of 1 (for dbo) being stored in the principal_id column of sys.objects.
  • SELF and the User creating the module is dbo: and the owner of the Database changes. This is due to a value of 1 (for dbo) being stored in the principal_id column of sys.objects.
     

  Signature will become valid again if the effective owner is restored to the value it was when the module was signed.

• Elevated permissions confined to signed code. Unlike Impersonation where the elevated permissions persist until the process / sub-process ends, or the REVERT command is executed, the permissions granted via the signature do not, by default, extend beyond the signed module. This means that you have full control over what code gets the extra permissions. If additional modules in a chain need to be granted additional privileges, then they can either be signed or counter-signed.

• Multiple Signatures can be used to combine permission “sets”:

  You can have a Certificate-based Login that has been granted VIEW SERVER STATE and a separate Certificate-based User that has been granted the ALTER TABLE permission and then sign a Stored Procedure with both for Bulk operations. This approach would allow you to use either the Login or the User (or both) separately to sign other modules that require one of those permissions but not both at the same time. This works due to Module Signing being “in addition to” permissions.

  On the other hand, using Impersonation would require either using the same account in multiple scenarios, even if some of those scenarios didn’t need all of those permissions, or creating three accounts, one for each permission individually and one with the two permissions combined. This is due to only being able to impersonate one account at a time, and the fact that Impersonation is “instead of” permissions.

• Less of a drag on performance than Impersonation

The Only Valid Uses of Impersonation

Starting with SQL Server 2005, the only reasons to use Impersonation are:

1. For testing, to temporarily become a lower-privileged User to make sure that the security setup actually works as intended. In these cases, it is usually the EXECUTE AS LOGIN statement that is used to temporarily become the testing account.

2. To allow Module Signing to work outside of the current / active Database (i.e. reach up to the Server-level and/or over to other Databases) when the process is initiated as a Database-only context. In these cases, it is the EXECUTE AS clause that is used. The requirements to get this working are:

   1. It doesn’t matter who the to-be-impersonated User is, it is just required to have this clause in order to break out of the very strict quarantine imposed by this context. It can be a User created without a Login.
   2. Each module in the chain, starting with the module created using the WITH EXECUTE AS clause, needs to be signed with the Certificate.
   3. IF Server-level access is needed:
      1. Copy the Certificate to master
      2. Create a Login from that Certificate
      3. Grant AUTHENTICATE SERVER permission to the Cert-based Login
      4. Grant any additional permissions needed and/or add Login to any potential Server-level Roles
   4. IF access is needed in another Database:
      1. Copy the Certificate to the other Database(s)
      2. Create a User from that Certificate in the other Database(s)
      3. Grant AUTHENTICATE permission to the Cert-based User in the other Database(s)
      4. Grant any additional permissions needed and/or add User to any potential Database-level Roles

   Two examples of a Database-only context are:

   • When using Service Broker. The activation procedure runs in the context of the EXECUTE AS clause of the Queue, which can only be a User, and cannot see the Server-level even if there is a corresponding Login with the same SID.
   • When impersonating a User. This is the case when either the EXECUTE AS clause, or the EXECUTE AS USER statement, is used.
3. When required:
   1. Service Broker queues — When specifying an Activation Procedure, EXECUTE AS is required, and CALLER is not an available option. (CREATE QUEUE)
   2. Natively compiled stored procedures (and maybe scalar user-defined functions?) in SQL Server 2014 — The documentation states that EXECUTE AS CALLER was not supported in SQL Server 2014.

Conclusion

Starting with the release of SQL Server 2005, it is no longer necessary to use Cross-Database Ownership Chaining, Impersonation, or TRUSTWORTHY to grant additional permissions and/or perform cross-Database functionality. Those mechanisms existed because there was no other way. But, Module Signing, introduced in SQL Server 2005, uses Certificates (or Asymmetric Keys) to overcome all of the deficiencies of those other methods.

Also, the difference in effort-level between the two approaches is far less than what most people believe. Module Signing seems to have a somewhat bad reputation of being too complicated. And to be fair, when I first started using Module Signing it took a little while to fully comprehend it because it is not terribly intuitive. But, the problem is mostly due to a lack of documentation and explanation, a situation that I am trying to address via posts like this one and the presentation noted at the top of this post, etc.

So please do not be scared off by whatever preconceived notions you might have about complexity or effort-level regarding Module Signing. Try implementing it once or twice and it will begin to make sense rather quickly. The benefits of Module Signing are well worth a few extra minutes spent on a few extra steps!

For more information (including links to many examples of actual, non-demo applications of this approach), please visit: ModuleSigning.info