PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining

(last updated: 2021-11-12 @ 11:45 ET / 2021-11-12 @ 16:45 UTC )

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 riskier mechanisms of temporarily granting additional privileges.

What follows is an overview of a presentation that I have given several 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, and TRUSTWORTHY:

Module Signing or: How I Learned to Stop Using TRUSTWORTHY / 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 and TRUNCATE TABLE). In these cases, it is usually required to be in at least one Instance-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 Instance-level permission. However, VIEW SERVER STATE allows access to all data from many DMVs and there is no built-in way to pick and choose with DMVs to apply the permission to.

• 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.

• Loading Assemblies (SQLCLR)

  Loading SQLCLR Assemblies that have a PERMISSION_SET of either EXTERNAL_ACCESS or UNSAFE have always required specific Instance-level permissions (i.e. EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY). And, starting in SQL Server 2017, by default (unless the new CLR strict security Instance-level configuration option is disabled), all Assemblies, even those marked as SAFE, require the UNSAFE ASSEMBLY permission.

 
 

Security Basics

 

Logins

• Instance (i.e. Server) -level
• System Catalog Views:
  1. sys.server_principals
  2. sys.server_role_members
  3. sys.server_permissions
  4. sys.sql_logins
• System Functions:
  1. SUSER_ID()
  2. SUSER_SID()
  3. SYSTEM_USER
  4. SUSER_NAME()
  5. SUSER_SNAME()
  6. ORIGINAL_LOGIN()
  7. LOGINPROPERTY()
• Connect to SQL Server via Login (not User) (unless using a Contained Database)
• Can be granted (or denied) Instance-level permissions (not Database-level permissions)
• principal_id not related to principal_id of User (Database-level)
• Standard / common principals:
  1. "sa" (type = SQL_LOGIN)
     1. always principal_id = 1 and sid = 0x01
  2. "public" (type = SERVER_ROLE)
     1. always principal_id = 2 and sid = 0x02

 

Users

• Database-level
• System Catalog Views:
  1. sys.database_principals
  2. sys.database_role_members
  3. sys.database_permissions
• System Functions:
  1. DATABASE_PRINCIPAL_ID() (originally USER_ID() but that is now deprecated!)
  2. USER_SID() (no documentation: “undocumented” or “oops, forgot to document”?)
  3. CURRENT_USER / USER / SESSION_USER (these 3 are nearly, if not entirely, identical)
  4. USER_NAME()
• Connect to SQL Server via User (not Login) only when using a Contained Database
• Can be granted (or denied) Database-level permissions (not Instance-level permissions)
• SID matches Login’s SID, but Name can be different
• principal_id not related to principal_id of Login (Instance-level)
• Standard / common principals:
  1. "public" (type = DATABASE_ROLE)
     1. always principal_id = 0
  2. "dbo" (type based on Login type: SQL_LOGIN -> SQL_USER, WINDOWS_LOGIN -> WINDOWS_USER)
     1. always principal_id = 1
     2. sid changes to SID of Login that owns the database
     3. sid should be the same as owner_sid value in sys.databases, but can be different if DB is restored / attached to a different instance
  3. "guest" if no permission or membership in Instance-level Role allowing connect to all DBs and no User entry and guest account is enabled (type = SQL_USER)
     1. always principal_id = 2, but sid changes to be the Login’s SID
     2. by default, enabled in [master], [msdb], and [tempdb]
     3. by default, disabled in [model] (and hence also in newly created databases)
     4. Enable by using the following query:
        sql
GRANT CONNECT TO [guest];
     5. Disable by using the following query:
        sql
REVOKE CONNECT FROM [guest];

 

PRINCIPAL_IDs

principal_ids are INT values that only exist within SQL Server. These are the PK fields in the sys.server_principals and sys.database_principals system catalog views. You will find these values in other system catalog views to assign ownership, permissions, etc.

These values only exist in their specific context: server principal_ids are only used for instance/server -level ownership, permissions, etc; database principal_ids are only used for database-level ownership, permissions, etc, and only in that one particular database. principal_id values in one context do not equate to values in any other context; and if they do happen to be the same, that is only by coincidence. Meaning, there is no way to map a Login to any of its associated Users by using its principal_id.

 

Security Identifiers (SIDs)

SIDs are used at the OS / Active Directory level to uniquely identify resources: logins, groups, computers, etc. SID values are based on a meaningful structure that includes the type of resource that they are and other information about them. They are used in the Registry, in ACLs, etc, and can be represented in either a binary form or a string form (SDDL).

SIDs created outside of SQL Server are brought into SQL Server when creating Logins based on Windows Logins or Windows Groups. SIDs created within SQL Server, especially those used for Server Roles and SQL Server Logins (and their optional, associated Users), have no meaning outside of SQL Server.

SIDs for SQL Server logins:
1. are essentially GUIDs (i.e. UNIQUEIDENTIFIER)
1. can be provided via the SID = argument of the CREATE LOGIN statement

• SID for Windows Login
  • Binary: 0x010500000000000515000000D71B9613B3D76E5C450BF23DEA030000
  • String: S-1-5-21-328604631-1550768051-1039272773-1002
• SID for SQL Server Login
  • Binary: 0x043ABE8E2EB4C041869892A1C7EAA877
  • String: does not apply to SQL Server Logins or Server Roles

Within SQL Server, SIDs are used to:

1. link the Login in SQL Server to the Windows Login or Windows Group that the Login is based on (for Windows Logins, not SQL Server Logins)
2. link Logins (all types; instance-level) to their associated Users (if any; database-level)

The undocumented (hence, unsupported) built-in function SID_BINARY() can be used to translate an SID from string form into its binary form, but only for “properly” structured SIDs (i.e. does not work with SIDs used for SQL Server Logins or Server Roles):

SELECT SID_BINARY(N'S-1-5-21-328604631-1550768051-1039272773-1002');
-- 0x010500000000000515000000D71B9613B3D76E5C450BF23DEA030000

SIDs are stored in SQL Server as VARBINARY(85).

 

Permissions

DENY overrides any GRANT. The only exception is when a DENY is on an object referenced in an object that a principal has been granted access to, and the owner of both objects is the same. In this case, the permissions are only checked for the initial / outer object being accessed, and then Ownership Chaining (see immediately below) takes over and assumes permissions have been granted on all referenced objects having the same owner. Technically speaking, this isn’t a DENY not overriding a GRANT, it’s merely that the DENY is never seen in the first place.

• Potential
  1. Assigned to individual principal_ids: Logins, Users, Roles, etc
  2. Instance-level (Logins, Server Roles): sys.server_permissions
  3. Database-level (Users, Database Roles): sys.database_permissions
• Actual
  1. All SIDs that are currently associated with the process
     1. Using Windows Login: SIDs of Windows SID and/or SIDs of any Windows Groups that the Windows account is a member of and have been added to SQL Server as Windows Logins
     2. Using SQL Server Login: SIDs of server_principal_id and optional Roles
     3. Instance-level: sys.login_token
     4. Database-level: sys.user_token
  2. Combination of all permissions granted to all principal_ids associated with all applicable SIDs
  3. sys.fn_my_permissions
  4. HAS_PERMS_BY_NAME()

Download the official poster sized chart of all Database Engine Permissions for Microsoft SQL Server 2017 and Azure SQL Database (1.44 MB PDF file).

 

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, and cannot be disabled; 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.

Fewer permissions checks on objects should improve performance (even if only slightly). However, not checking permissions on objects within the ownership chain does allow for an explicit DENY to be ignored (just something to be aware of).

By default, Ownership chaining does not work across databases (see “Cross-Database Ownership Chaining”).

Using Dynamic SQL breaks ownership chaining, and permissions will be reevaluated as the Dynamic SQL is being compiled. The permissions error is often solved by using EXECUTE AS (i.e. Impersonation), but that is a security risk.

 
 

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. These change are reflected in the values returned by the Instance-level SUSER_NAME() / SYSTEM_USER and Database-level USER_NAME() / SESSION_USER functions.

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.

Originally the User (not Login) context could be impersonated using the SETUSER statement. However, this statement was deprecated as of at least SQL Server 7.0 (I don’t have earlier documentation to check), with no replacement provided until SQL Server 2005. So, do not ever use SETUSER (when using SQL Server 2005 or newer)!

Becoming a different account is accomplished using EXECUTE AS, added in SQL Server 2005. There are two or three ways to use EXECUTE AS (three ways to specify, but two have identical behavior):

 

Clause

The EXECUTE AS clause is part of the CREATE statement for: Stored Procedures, DML 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 Instance-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.

When working with Instance-level Triggers (i.e. DDL and Logon Triggers), the principal_id to impersonate can only be a Instance-level Login.

 

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 Instance-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 Instance-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 (N'string') AS { LOGIN | USER } = N' name ' ), the requirements and behavior are the same as with the EXECUTE AS statement (specifically within the context of a sub-process). This is why there are really only two ways to do Impersonation.

 

Reverting Within a Sub- (i.e. Nested) Process

While the REVERT statement does not work within a sub-process (a.k.a. a nested process), you can use the EXECUTE AS CALLER; statement to revert back to the caller of that sub-process. And, to get back to the original security context of the sub-process, that is the only time that REVERT; does work within a sub-process.

For example:

SELECT 1, SESSION_USER AS [User], SYSTEM_USER AS [Login]; -- Original
EXEC(N'
  SELECT 2, SESSION_USER AS [User], SYSTEM_USER AS [Login]; -- Impersonated
  REVERT;
  SELECT 3, SESSION_USER AS [User], SYSTEM_USER AS [Login]; -- Impersonated
  EXECUTE AS CALLER;
  SELECT 4, SESSION_USER AS [User], SYSTEM_USER AS [Login]; -- Original
  REVERT;
  SELECT 5, SESSION_USER AS [User], SYSTEM_USER AS [Login]; -- Impersonated
') AS LOGIN = N'principal_being_impersonated';
SELECT 6, SESSION_USER AS [User], SYSTEM_USER AS [Login]; -- Original

 
 

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 Instance-level). The simplest way around this restriction is to enable Cross-Database Ownership Chaining, added in SQL Server 2000. There are two ways to accomplish this:

1. Enable for all Databases at the Instance-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 Instance-level setting is enabled, this setting has no effect. When the Instance-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 (i.e. the “caller”) 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), Cross-Database ownership chaining doesn’t work, same as with regular ownership chaining. In this case, a combination of Impersonation (i.e. EXECUTE AS) and TRUSTWORTHY ON will “fix” the error, but then you are no longer using Cross-Database Ownership Chaining (it can even be disabled), and are instead using something that is a much larger security risk.

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.

 
 

TRUSTWORTHY ON

Added in SQL Server 2005, this is a Database property that tells the Instance to trust User SIDs from this Database. Hence, a SID at the Instance-level, or in another Database, that happens to be the same as a SID in a TRUSTWORTHY Database will be “trusted” to be the same account (meaning that the reason that the SIDs match is due to the accounts actually being the same, and not due to coincidence at best, or a hacking attempt at worst). With this setting enabled, the process is not quarantined to the current Database. The process is allowed to interact with the Instance and/or other Databases, just so long as there is a matching SID in that location. When using Impersonation, the SID of the process is the SID of the impersonated User / Login, not the caller’s SID.

This setting is OFF by default.

Enable or disable using:

ALTER DATABASE { Name | CURRENT } SET TRUSTWORTHY { ON | OFF } ;

This setting is often used to:

• gain Instance-level permissions
• make loading SQLCLR Assemblies easier (prior to SQL Server 2017 it was used for just EXTERNAL_ACCESS and UNSAFE Assemblies, but starting in SQL Server 2017 it is now also used even for SAFE Assemblies).

While setting TRUSTWORTHY ON is quicker and easier than setting up proper security, it’s a huge security risk, and is never required (not even for loading unsupported .NET Framework libraries).

 
 

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 (official documentation incorrectly states that the User only needs to be in the other Database(s) when Dynamic SQL is being used; User must exist even for regular / static SQL)
  • db_ddladmin and 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 (including memory optimized / Natively Compiled procedures)
• Triggers
• Multi-statement Table-Valued Functions (a.k.a. msTVFs)
• Scalar Functions (a.k.a. UDFs)
• SQLCLR Assemblies
• SQLCLR objects (Stored Procedures, Scalar User-Defined Functions, Table-Valued Functions, and Triggers)

The following types of modules cannot be signed:

• Views
• Inline Table-Valued Functions (a.k.a. iTVFs; these are essentially Views that accept parameters)
• SQLCLR User-Defined Aggregates (UDAs)
• SQLCLR User-Defined Types (UDTs)
• Instance-level triggers (DDL and Logon; 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)

Undocumented objects that can be signed:

• Tables (this seems to only be for detecting if any schema changes were attempted via ALTER TABLE; blog post about this coming soon)
• Databases (this is a shortcut to signing all signable objects within the database; blog post about this coming soon)
• Database-level DDL triggers (these are not supposed to be signable, however, when signing a database, these objects are included; blog post about this coming soon).
  Please note that this is undocumented / unsupported behavior. The object signature will not appear in sys.crypt_properties, and there is likely no means of dropping the signature outside of dropping and recreating the DDL trigger.

The actual signatures are stored in sys.crypt_properties.

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. Multi-statement 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 Instance-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 Instance-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 Instance-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 Instance-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 effectively dbo (according to the CURRENT_USER built-in function): 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

 

Asymmetric Keys and Certificates

 

Common Aspects

• Consist of a Private Key and Public Key pair
• Can have the Private Key removed
• Common Properties:
  • Thumbprint (hash of Public Key, also stored in sys.crypt_properties along with actual signature per object)
  • SID (used as the SID when creating a Login and/or User from the Key or Certificate)
  • principal_id (the "owner" / authorizer of the Key or Certificate)
  • name
• Create from File (.snk / .cer, or .dll) or Assembly
• Provide password or use Database Master Key (DMK)

 

Asymmetric Keys

• Where found:

      SELECT * FROM [sys].[asymmetric_keys];
      

• Properties:
  • public_key
• Can create from Key Store / EKM
  BUT, EKM created Keys not supported for Module Signing
• Can specify Algorithm:
  1. RSA_512
  2. RSA_1024
  3. RSA_2048
  4. RSA_3072
  5. RSA_4096
• Cannot backup   😾

  This alone makes Asymmetric Keys more difficult to use for Module Signing as it is harder to copy between databases.

  Please vote for: Add function to extract Asymmetric Key similar to CERTENCODED for Certificates

• Cannot restore Private Key   😾

 

Certificates

• Where found:

      SELECT * FROM [sys].[certificates];
      

• Asymmetric Key + extra properties
• Properties:
  1. Serial Number: unique ID of the Certificate
  2. Subject: essentially a description
  3. Start Date: UTC; default = GETUTCDATE();
  4. Expiration Date: UTC; default = 1 year from Start
• Module Signing ignores Expiration Date
• Can backup !!
• Can restore Private Key !!

 

How Asymmetric Keys and Certificates are Used

Encryption

• Message + Public Key ➔ 0x…
• 0x… + Private Key ➔ Message

Signing

• Message (i.e. the Code) + Private Key ➔ Signature
• Message (i.e. the Code) + Signature + Public Key ➔ SAME vs NOT Same (i.e. change detection)

Module Signing in SQL SERVER

Execute signed module:

1. Look in sys.crypt_properties for thumbprint (hash of Public Key) and crypt_property (signature of the signed module) based on major_id (in this case, matches object_id of current module)
2. Look in sys.certificates for public key and sid based on thumbprint (from step 1)
3. Use crypt_property (from step 1) and public key (from step 2) along with definition of module (source code of current module) to determine if current definition is identical to what it was when the module was signed:
   1. If “not identical” then quit (do not apply any additional permissions, which might result in an error)
   2. If “identical”, continue
4. Use sid (from step 2) to check for a matching Login and/or User:
   1. If a Login is found, add its instance-level permissions to the current security context
   2. If a User is found, add its database-level permissions to the current security context

 

The Only Valid Uses of Impersonation and TRUSTWORTHY ON

 

Impersonation

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

1. Creating a sandbox for Dynamic SQL to execute in. This allows for restricting an application Login / User to an even narrower set of permissions than they hopefully already have (assuming that normally they can only execute a specific set of stored procedures). You can use the WITH COOKIE INTO @variable option to prevent someone from being able to issue REVERT within the Dynamic SQL while still allowing you to REVERT WITH COOKIE = @variable; after the Dynamic SQL executes. For example:

       DECLARE @SQL NVARCHAR(MAX),
               @CIsForCookie VARBINARY(8000);
   
       SET @SQL = N'some T-SQL here, yo!';
   
       EXECUTE AS USER = 'SandBoxedUser' WITH COOKIE INTO @CIsForCookie;
   
       EXEC(@SQL);
   
       REVERT WITH COOKIE = @CIsForCookie;
       

    

2. 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.
    
3. To allow Module Signing to work outside of the current / active Database (i.e. reach up to the Instance-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 Instance-level access is needed:
      1. Copy the Certificate (no need to copy Private Key) 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 Instance-level Roles
   4. IF access is needed in another Database:
      1. Copy the Certificate (no need to copy Private Key) 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
          

   Three 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 Instance-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.
   • When using an Application Role / “AppRole”. After executing sp_setapprole, the Instance-level context can no longer be used, even though error messages will refer to the Login.
   • When setting a T-SQL Job Step of a SQL Server Agent job to "Execute as User"
   • Logging directly into a Contained database
      
4. 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. This is no longer an issue starting with SQL Server 2016.
   3. Database-level and Server-level (i.e. Instance-level) Triggers — DDL and Logon triggers cannot be signed, unfortunately (please vote for: “Allow signing Database DDL Triggers and Server DDL and Logon Triggers – ADD SIGNATURE”). In these cases, you can use EXECUTE AS, or have the trigger execute a stored procedure that is properly signed (in which case, depending on what you are doing, you may need to pass along the output from EVENTDATA to that signed stored procedure).

      Please note: Database-level DDL triggers can technically be signed, but this is undocumented / unsupported behavior and can only be accomplished by signing the database, which is also undocumented behavior.

 

TRUSTWORTHY

The ONLY valid use of setting a Database to TRUSTWORTHY ON is for development and testing. That’s it.

This might be the easier option to implement, but it is never the only option available. Therefore, there is never any reason to enable TRUSTWORTHY for a Database in production.

Despite what you might think, or have read, or have been told, TRUSTWORTHY ON is not required for:

1. Gaining Instance-level permissions
2. Accessing another Database
3. Loading a custom SQLCLR Assembly (whether prior to, or as of, SQL Server 2017)
4. Loading an existing, unsigned (most likely SAFE) Assembly into SQL Server 2017 (or newer) from a pre-SQL Server 2017 Instance
5. Loading an unsupported .NET Framework library (seriously!)
6. or any other situation I have ever encountered, either personally or via answering someone’s question

 
 

Examples

Below are several step-by-step guides that I have written. More links will be added over time.

Instance-level Permissions

1. Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level (post here on Sql Quantum Leap)

Database-level Permissions

1. Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Database-level (post here on Sql Quantum Leap)

2. Proc Inserts via Dynamic SQL into Table with Trigger that Inserts into Other Table (self-guided demo script on PasteBin.com)

Cross-Database Access

1. How to grant execute permissions to a stored procedure but not the underlying databases (answer on DBA.StackExchange)

2. Can’t use msdb.dbo.sp_send_dbmail when in service broker – executes as guest? (answer on DBA.StackExchange)

 
 

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

---