(last updated: 2021-11-12 @ 11:45 ET / 2021-11-12 @ 16:45 UTC )
CONTENTS
- Common “Problem” Scenarios
- Security Basics
- Impersonation
- Cross-Database Ownership Chaining
- TRUSTWORTHY ON
- REASONS TO AVOID THESE METHODS
- Module Signing
- BENEFITS OF MODULE SIGNING
- Asymmetric Keys and Certificates
- The Only Valid Uses of Impersonation
andTRUSTWORTHY
- Examples
- Conclusion
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
andTRUNCATE 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 theVIEW 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 eitherEXTERNAL_ACCESS
orUNSAFE
have always required specific Instance-level permissions (i.e.EXTERNAL ACCESS ASSEMBLY
orUNSAFE ASSEMBLY
). And, starting in SQL Server 2017, by default (unless the newCLR strict security
Instance-level configuration option is disabled), all Assemblies, even those marked asSAFE
, require theUNSAFE ASSEMBLY
permission.
Security Basics
Logins
- Instance (i.e. Server) -level
- System Catalog Views:
- System Functions:
- 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 toprincipal_id
of User (Database-level)- Standard / common principals:
- "sa" (type =
SQL_LOGIN
)- always
principal_id
= 1 andsid
= 0x01
- always
- "public" (type =
SERVER_ROLE
)- always
principal_id
= 2 andsid
= 0x02
- always
- "sa" (type =
Users
- Database-level
- System Catalog Views:
- System Functions:
- DATABASE_PRINCIPAL_ID() (originally
USER_ID()
but that is now deprecated!) USER_SID()
(no documentation: “undocumented” or “oops, forgot to document”?)- CURRENT_USER / USER / SESSION_USER (these 3 are nearly, if not entirely, identical)
- USER_NAME()
- DATABASE_PRINCIPAL_ID() (originally
- 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 toprincipal_id
of Login (Instance-level)- Standard / common principals:
- "public" (type =
DATABASE_ROLE
)- always
principal_id
= 0
- always
- "dbo" (type based on Login type:
SQL_LOGIN
->SQL_USER
,WINDOWS_LOGIN
->WINDOWS_USER
)- always
principal_id
= 1 sid
changes to SID of Login that owns the databasesid
should be the same asowner_sid
value insys.databases
, but can be different if DB is restored / attached to a different instance
- always
- "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
)- always
principal_id
= 2, butsid
changes to be the Login’s SID - by default, enabled in
[master]
,[msdb]
, and[tempdb]
- by default, disabled in
[model]
(and hence also in newly created databases) - Enable by using the following query:
sql
GRANT CONNECT TO [guest]; - Disable by using the following query:
sql
REVOKE CONNECT FROM [guest];
- always
- "public" (type =
PRINCIPAL_IDs
principal_id
s 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_id
s are only used for instance/server -level ownership, permissions, etc; database principal_id
s 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
- Binary:
- SID for SQL Server Login
- Binary:
0x043ABE8E2EB4C041869892A1C7EAA877
- String: does not apply to SQL Server Logins or Server Roles
- Binary:
Within SQL Server, SIDs are used to:
- 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)
- 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
- Assigned to individual
principal_id
s: Logins, Users, Roles, etc - Instance-level (Logins, Server Roles): sys.server_permissions
- Database-level (Users, Database Roles): sys.database_permissions
- Assigned to individual
- Actual
- All SIDs that are currently associated with the process
- 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
- Using SQL Server Login: SIDs of
server_principal_id
and optional Roles - Instance-level: sys.login_token
- Database-level: sys.user_token
- Combination of all permissions granted to all
principal_id
s associated with all applicable SIDs - sys.fn_my_permissions
- HAS_PERMS_BY_NAME()
- All SIDs that are currently associated with the process
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
insys.objects
isNOT NULL
- THEN use
principal_id
insys.objects
- ELSE use
principal_id
insys.schemas
forschema_id
that matches the value insys.objects
- THEN use
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 theEXECUTE 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:
- 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.
-
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
andUNSAFE
Assemblies, but starting in SQL Server 2017 it is now also used even forSAFE
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
anddb_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
- Security risk:
- 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.
- can be used any time — you cannot control when someone executes the
- 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)
- If
TRUSTWORTHY
:- Bigger security risk
- Can also spoof Logins, such as "sa"! — If the database owner is
sa
, then any process running atdbo
becomessa
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
orUNSAFE
; all Assemblies are eligible to be marked as either of those elevated permission sets.
- Can also spoof Logins, such as "sa"! — If the database owner is
- Bigger security risk
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 insys.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:
- 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. -
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 simpleSELECT * FROM dmv
and makes sense to have encapsulated as a module. Sally should be able toSELECT
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 enableTRUSTWORTHY
in order to get that Instance-level permission (since addingWITH EXECUTE AS N'dbo'
to theCREATE PROCEDURE
statement is confined to the Database-level, unless evilTRUSTWORTHY
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 haveVIEW 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 theprocSelectT1
intermediate Stored Procedure topublic
- grant
EXECUTE
onprocSelectT1
only to theucsSelectT1
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 theprincipal_id
column ofsys.objects
, and if that isNULL
(which is the default value, or can be set usingSCHEMA OWNER
) then theprincipal_id
column ofsys.schemas
for theschema_id
that matchessys.objects.schema_id
for that module. Things that can change ownership:- Changing the object’s direct owner:
- to a different explicit value, or
- to an explicit value from
NULL
, or toNULL
from an explicit value IF the explicit value is different than theprincipal_id
column ofsys.schemas
for that module’s Schema
- Changing the object’s Schema IF the
principal_id
column ofsys.objects
isNULL
and IF theprincipal_id
column ofsys.schemas
has different values between the old and new Schemas - Changing the object’s Schema’s owner IF the
principal_id
column ofsys.objects
isNULL
- Changing the Database’s owner IF:
- the object is directly owned by
dbo
, or - the
principal_id
column ofsys.objects
isNULL
and the Schema is owned bydbo
- the object is directly owned by
- Changing the object’s direct owner:
N'dbo'
: and the owner of the Database changes. This is due to a value of1
(fordbo
) being stored in theprincipal_id
column ofsys.objects
.SELF
and the User creating the module is effectivelydbo
(according to theCURRENT_USER
built-in function): and the owner of the Database changes. This is due to a value of1
(fordbo
) being stored in theprincipal_id
column ofsys.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 theALTER 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
- Thumbprint (hash of Public Key, also stored in
- 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:
- RSA_512
- RSA_1024
- RSA_2048
- RSA_3072
- 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:
- Serial Number: unique ID of the Certificate
- Subject: essentially a description
- Start Date: UTC; default = GETUTCDATE();
- 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:
- Look in
sys.crypt_properties
forthumbprint
(hash of Public Key) andcrypt_property
(signature of the signed module) based onmajor_id
(in this case, matchesobject_id
of current module) - Look in
sys.certificates
for public key andsid
based onthumbprint
(from step 1) - 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:- If “not identical” then quit (do not apply any additional permissions, which might result in an error)
- If “identical”, continue
- Use
sid
(from step 2) to check for a matching Login and/or User:- If a Login is found, add its instance-level permissions to the current security context
- 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:
- 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 issueREVERT
within the Dynamic SQL while still allowing you toREVERT 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;
- 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.
- 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:- 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.
- Each module in the chain, starting with the module created using the
WITH EXECUTE AS
clause, needs to be signed with the Certificate. - IF Instance-level access is needed:
- Copy the Certificate (no need to copy Private Key) to
[master]
- Create a Login from that Certificate
- Grant
AUTHENTICATE SERVER
permission to the Cert-based Login - Grant any additional permissions needed and/or add Login to any potential Instance-level Roles
- Copy the Certificate (no need to copy Private Key) to
- IF access is needed in another Database:
- Copy the Certificate (no need to copy Private Key) to the other Database(s)
- Create a User from that Certificate in the other Database(s)
- Grant
AUTHENTICATE
permission to the Cert-based User in the other Database(s) - 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 theEXECUTE 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
- When required:
- Service Broker queues — When specifying an Activation Procedure,
EXECUTE AS
is required, andCALLER
is not an available option. (CREATE QUEUE) - 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. - 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.
- Service Broker queues — When specifying an Activation Procedure,
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:
- Gaining Instance-level permissions
- Accessing another Database
- Loading a custom SQLCLR Assembly (whether prior to, or as of, SQL Server 2017)
- Loading an existing, unsigned (most likely
SAFE
) Assembly into SQL Server 2017 (or newer) from a pre-SQL Server 2017 Instance - Loading an unsupported .NET Framework library (seriously!)
- 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
- Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level (post here on Sql Quantum Leap)
Database-level Permissions
- Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Database-level (post here on Sql Quantum Leap)
-
Proc Inserts via Dynamic SQL into Table with Trigger that Inserts into Other Table (self-guided demo script on PasteBin.com)
Cross-Database Access
-
How to grant execute permissions to a stored procedure but not the underlying databases (answer on DBA.StackExchange)
-
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
[…] Solomon Rutzky explains how you can use module signing to avoid the security risks which come with i…: […]
[…] PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining […]
[…] PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining […]
[…] is enabled. Then we will enable the database property of TRUSTWORTHY. While it is generally a bad idea to enable TRUSTWORTHY, doing so is one of the ways to get Assemblies to load if “CLR strict security” is […]
[…] able to create the certificate from a VARBINARY literal actually allows us to maintain our “No TRUSTWORTHY” goal in SQL Server 2017 and beyond when working with self-contained deployment scripts 1 […]
[…] These are both yet more reasons to use Module Signing instead of EXECUTE AS! […]
[…] PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining […]
EXCELLENT! It could only be better if one could be there to hear you present this in person. Thank you!
Thank you very much! That is very kind of you to say. I did present some of this info last year (2020-05-16), which you can find on YouTube here: https://youtu.be/OxBEh8W1g8w . Also, I will be presenting it again in just over 2 months at the PASS Data Community Summit: https://passdatacommunitysummit.com/sessions?search=rutzky . I will add a link to the article for that presentation after it is posted. Thanks again and take care 😺
An developer asked me to simplify the access to tabels on an other DB.
For example, he requested a possibility to get access with the login/user “Access1” from DB1 to tables in DB2, but he don’t wanna create the user “Access1” in DB2 and give explicitly select permissions on every single table where he needs access in DB2.
So it seems that ownership chaining fits his needs, because with this he needs only to create “Access1” with public rights in DB2.
This because if i impose him to use Module singing, than he has more work to do than simple giving the single permissions for every table just directly to the user “Access1” in DB2.
He don’t uses dynamic SQL.
I am right or i am missing some point?