Part of having good security is giving users the fewest / least permissions possible in order to execute the code. However, we always seem to find ourselves needing to allow someone to execute something that requires one or two higher-level permissions. Yet, in order to allow for just one extra operation (or maybe even more specific: one extra permission on just one object), our only option seems to be granting a permission that allows for doing a whole range of other things that we do not want to allow. Or, in the case of wanting a specific permission on a specific object, our only option seems to be granting that permission on all objects, or maybe even across all Databases.

Fortunately, there is a facility that allows for very granular security and can handle nearly all situations. Module Signing, introduced in SQL Server 2005, uses Certificates and/or Asymmetric Keys to selectively apply additional permissions to code: Stored Procedures, Triggers, Scalar UDFs, and Multi-statement TVFs. This is far more secure than using Impersonation as it does not require setting the Database property of TRUSTWORTHY to ON (a huge security risk) in order to attain Server-level permissions. Let’s take a look.

Test Case

Scenario: We have a Table that has an audit Trigger on it. The Trigger tries to capture certain connection info, per each event (not each row), of the User that executed the statement.

Problem: Accessing the DMV containing the connection info requires a Server-level permission: VIEW SERVER STATE ¹ . Adding WITH EXECUTE AS OWNER to the CREATE TRIGGER statement will only work if we also set the Database option of TRUSTWORTHY to ON, but the security risks of turning that on are not acceptable.

Initial Setup

Create the low-privileged Login (Server-level) and User (Database-level), a simple Table, and a Trigger on that Table that merely prints a Connection property to the “Messages” tab.

USE [tempdb];
SET NOCOUNT, ANSI_NULLS, QUOTED_IDENTIFIER ON;
CREATE LOGIN [AppUser] WITH PASSWORD = 'NevrGonnaGesMe';
CREATE USER [AppUser] FOR LOGIN [AppUser];
CREATE TABLE dbo.TableWithTrigger
(
  [TableWithTriggerID] INT IDENTITY(1, 1) NOT NULL
    CONSTRAINT [PK_TableWithTrigger] PRIMARY KEY,
  [Something] NVARCHAR(50) NULL,
  [InsertTime] DATETIME2 NOT NULL
    CONSTRAINT [DF_TableWithTrigger_InsertTime] DEFAULT (SYSDATETIME())
);
GRANT INSERT ON [dbo].[TableWithTrigger] TO [AppUser];
EXEC(N'CREATE TRIGGER dbo.TableWithTrigger_AuditINSERT
  ON dbo.TableWithTrigger
  AFTER INSERT
AS
SET NOCOUNT ON;
DECLARE @NetAddress NVARCHAR(50);
SELECT  @NetAddress = conn.[client_net_address]
FROM    sys.dm_exec_connections conn
WHERE   conn.[session_id] = @@SPID;
PRINT ''Audited Net Address: '' + @NetAddress;
');

Test 1

Now that we have the items created we can impersonate the AppUser Login and attempt to insert a row into dbo.TableWithTrigger. If all goes well (don’t worry, it won’t), the Trigger will display the “net address” that we are connecting from.

EXECUTE AS LOGIN = 'AppUser';
SELECT SESSION_USER;
-- AppUser
INSERT INTO dbo.[TableWithTrigger] ([Something]) VALUES (N'test');
/*
Msg 300, Level 14, State 1, Procedure TableWithTrigger_AuditINSERT,
      Line XXXXX [Batch Start Line YYYYY]
VIEW SERVER STATE permission was denied on object 'server',
      database 'master'.
Msg 297, Level 16, State 1, Procedure TableWithTrigger_AuditINSERT,
      Line XXXXX [Batch Start Line YYYYY]
The user does not have permission to perform this action.
*/REVERT;
SELECT SESSION_USER;
-- dbo

Oops. The example above shows that the AppUser Login does not have permission to access the sys.dm_exec_connections DMV. In order to access that DMV one needs the VIEW SERVER STATE permission, which is a Server-level permission. We know this not only from the name of the permission (not all permissions are that obvious), but from the fact that the error states that the permission was “denied on object ‘server'”.

The reason that the error is coming from the [master] Database is due to this being a Server-level issue, and many Server-level operations require being in the [master] Database. Logins can be created (well, as long as the Login doesn’t require an object that exists within the [master] DB) and dropped from any Database, but managing permissions for Logins requires that the current Database be set to [master].

Setup for Test 2

We have seen that the error does occur for this User. The following steps (none of which are difficult) should allow the User to access the DMV without compromising security.

-- 1) Create the Certificate:
CREATE CERTIFICATE [Permission$ViewServerState]
    ENCRYPTION BY PASSWORD = 'UseBetterPassword!'
    WITH SUBJECT = 'VIEW SERVER STATE permission',
    EXPIRY_DATE = '2099-12-31';
/*
The password is used to protect only the Private Key, not the Public
Key or the Certificate in general.
The default expiration date is 1 year from the date the Certificate is
created. However, in many situations, such as with Module Signing, the
expiration date is actually ignored. Still, I prefer to set the
expiration date to 2099.
*/-- 2) Sign the Module:
ADD SIGNATURE
    TO [dbo].[TableWithTrigger_AuditINSERT]
    BY CERTIFICATE [Permission$ViewServerState]
    WITH PASSWORD = 'UseBetterPassword!';
/*
Verify using the following query:
SELECT OBJECT_NAME(cp.[major_id]) AS [name], *
FROM   sys.crypt_properties cp;
*/-- 3) Backup the Certificate and Private Key to files (optional if
--    using SQL Server 2012 or newer, else required):
BACKUP CERTIFICATE [Permission$ViewServerState]
  TO FILE = 'C:\TEMP\cert.cer'
  WITH PRIVATE KEY
  (
     FILE = 'C:\TEMP\cert.pvk',
     DECRYPTION BY PASSWORD = 'UseBetterPassword!', -- decrypt the PVK
     ENCRYPTION BY PASSWORD = 'FilePassword!' -- encrypt PVK in file
  );
-- and/or to a VARBINARY string to be copied and pasted somewhere safe:
SELECT CERTPRIVATEKEY(CERT_ID(N'Permission$ViewServerState'),
                      'NewPassword!', -- encrypt the value returned
                      'UseBetterPassword!'); -- decrypt the Private Key
/*
You backup the Certificate so that you can recreate / restore it later.
The BACKUP command specifies two files because Certificates are two
pieces: the "Certificate" (confusingly the same name) is the Public Key
plus the meta-data (Expiration Date, Subject, etc), which is assumed,
and the Private Key, which is optional. For the Private Key, the
decryption and encryption passwords can be the same. They are different
here to make it visually clear that one of the passwords was used when
creating the Certificate, and the other is for something else. Using a
different password is more secure.
The CERTPRIVATEKEY built-in function was added in SQL Server 2012. If
you are using SQL Server 2005, 2008, or 2008 R2, then:
1) you do not have the CERTPRIVATEKEY function
2) this step is NOT optional; it is needed for Step 5 
*/-- 4) Remove the Private Key (optional):
ALTER CERTIFICATE [Permission$ViewServerState]
    REMOVE PRIVATE KEY;
/*
Do this to prevent anyone from using the Certificate to sign other
modules so that they can also have this permission.
But, signing also requires knowing the password. If the password is
unknown to all but the DBAs, then it should be safe to keep the Private
Key in the Certificate. If it is removed and you need to sign something
new, or re-sign an object that has been changed and hence lost its
signature, then the Private Key can be restored using the info from
Step 3.
*/-- 5) Copy Certificate to [master] (no need to include Private Key):
DECLARE @Cert NVARCHAR(4000) =
         CONVERT(NVARCHAR(4000),
                 CERTENCODED(CERT_ID(N'Permission$ViewServerState')),
                 1);
EXEC (N'USE [master];
CREATE CERTIFICATE [Permission$ViewServerState]
FROM BINARY = ' + @Cert);
/*
The CERTENCODED built-in function is not available in SQL Server 2005,
2008, or 2008 R2. When using those versions, execute the following:
EXEC (N'USE [master];
CREATE CERTIFICATE [Permission$ViewServerState]
  FROM FILE = ''C:\TEMP\cert.cer'';
');
*/-- 6) Create Login and Grant Permission:
EXEC (N'USE [master];
CREATE LOGIN [Permission$ViewServerState]
    FROM CERTIFICATE [Permission$ViewServerState];
GRANT VIEW SERVER STATE TO [Permission$ViewServerState];
');

Test 2

Now that the Module Signing is in place, let’s try it again…

EXECUTE AS LOGIN = 'AppUser';
SELECT SESSION_USER;
INSERT INTO dbo.[TableWithTrigger] ([Something]) VALUES (N'test');
/*
Audited Net Address: <named pipe>
*/SELECT conn.[client_net_address]
FROM   sys.dm_exec_connections conn
WHERE  conn.[session_id] = @@SPID;
/*
Msg 300, Level 14, State 1, Line XXXXX
VIEW SERVER STATE permission was denied on object 'server',
    database 'master'.
Msg 297, Level 16, State 1, Line XXXXX
The user does not have permission to perform this action.
*/REVERT;
SELECT SESSION_USER;

Executing the Test 2 queries shows us that:

1. The process now works: the DMV can be accessed without causing an error.
2. The permission to access the DMV was not given to the “AppUser” Login since that Login cannot access the DMV.

Cleanup

Execute the following to remove all objects (Logins, Users, Certificates, Table, and Trigger) created by the example code above.

USE [tempdb];
IF (USER_ID(N'AppUser') IS NOT NULL)
BEGIN
    DROP USER [AppUser];
END;
IF (SUSER_ID(N'AppUser') IS NOT NULL)
BEGIN
    DROP LOGIN [AppUser];
END;
IF (OBJECT_ID(N'dbo.TableWithTrigger') IS NOT NULL)
BEGIN
    DROP TABLE [TableWithTrigger];
END;
IF (CERT_ID(N'Permission$ViewServerState') IS NOT NULL)
BEGIN
    DROP CERTIFICATE [Permission$ViewServerState];
END;
IF (SUSER_ID(N'Permission$ViewServerState') IS NOT NULL)
BEGIN
    DROP LOGIN [Permission$ViewServerState];
END;
EXEC (N'USE [master];
IF (CERT_ID(N''Permission$ViewServerState'') IS NOT NULL)
BEGIN
    DROP CERTIFICATE [Permission$ViewServerState];
END;
');

The Alternative: Impersonation

Rather than using Module Signing, you could always go the easier (and far less secure) route of using Impersonation. That requires specifying the EXECUTE AS clause of the CREATE TRIGGER statement (or CREATE FUNCTION, or CREATE PROCEDURE, etc). You can only specify Users (i.e. Database-level Principals) in the EXECUTE AS clause, so you would specify one that has a matching Login (i.e. a Server-level Principal) that has the VIEW SERVER STATE permission (either explicitly, or implicitly via a permission such as CONTROL SERVER or membership in a fixed Server role such as sysadmin).

However, Impersonation by default is quarantined to the Database, so you would need to set the Database property of TRUSTWORTHY to ON. But, doing that is a huge security risk, especially if the owner of the Database (i.e. the SID of the “dbo” User) has a matching Login that is highly privileged. Enabling TRUSTWORTHY allows all code in that Database to use Server-level permissions if there is a Login with a Security ID (SID) that matches the User specified in the EXECUTE AS clause of the CREATE object statement. Meaning, any code in that Database that uses WITH EXECUTE AS OWNER (assuming the owner is dbo) or EXECUTE AS 'dbo' now has Server-level permissions, even if the EXECUTE AS was only being used to grant higher Database-level permissions. And, given how many Databases are owned by sa, enabling TRUSTWORTHY effectively grants all EXECUTE AS 'dbo' code in that Database full sysadmin privileges.

And, if the security risk of using Impersonation wasn’t bad enough, it also doesn’t perform as well as Module Signing. This is due to the security context switching: changing out the current Login / User and their permissions with the impersonated account and its permissions, and then reverting the account and permissions back to the original when the module ends. With Module Signing, some additional permissions are added to the security context and then removed when the module ends. This adding and removing of extra permissions also increases the execution time, but not by nearly as much as Impersonation.

Conclusion

Granting high-level permissions can be as granular as you like. Since you assign the permissions to the code instead of to Logins / Users, the discrete operations of the module(s) that you sign are effectively those granular permissions. Putting that in terms of the example shown in this post:

AND, there is no side-effect (security-wise) of granting the additional permission(s) via Module Signing. This is a very important point because the alternative (i.e. enabling TRUSTWORTHY) only works by being a huge security hole: if it allowed the EXECUTE AS clause to reach up to the Server-level to get the VIEW SERVER STATE permission, then the process would have all of the other Server-level permissions of the Login associated with the EXECUTE AS User. Creating a Login that only has that one permission (like we did in the example above when setting up the Module Signing) will reduce the scope of the security issues for that Trigger, but it won’t:

1. prevent anyone from using EXECUTE AS with that User in other modules to gain access to whatever else the VIEW SERVER STATE permission controls, nor will it…
2. prevent anyone from using EXECUTE AS with some other User that has an associated Login that is highly privileged (EXECUTE AS OWNER and EXECUTE AS 'dbo' are effectively EXECUTE AS sa for any Database that is owned by sa, and there is a good chance that your Databases are indeed owned by sa).

Essentially, Module Signing only applies the additional permissions to what has been signed (which requires a password that can be kept private, and a private key that can be removed), and nothing more. On the other hand, there is no mechanism to limit what code can use EXECUTE AS, or what Users can be the EXECUTE AS User, when enabling TRUSTWORTHY.

AND, in addition to being more granular and more controllable, Module Signing also performs better than Impersonation. (I will provide the test code and results in a future post.)

Given that Module Signing is more secure and more performant than Impersonation, it is definitely worth the extra 30 seconds it takes to set it up (compared to setting up Impersonation).

For more information on Module Signing, please see:

• ModuleSigning.Info
• PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining