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
ON (a huge security risk) in order to attain Server-level permissions. Let’s take a look.
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 1 . Adding
WITH EXECUTE AS OWNER to the
CREATE TRIGGER statement will only work if we also set the Database option of
ON, but the security risks of turning that on are not acceptable.
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; ');
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
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]; ');
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:
- The process now works: the DMV can be accessed without causing an error.
- The permission to access the DMV was not given to the “AppUser” Login since that Login cannot access the DMV.
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
However, Impersonation by default is quarantined to the Database, so you would need to set the Database property of
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
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
TRUSTWORTHY effectively grants all
EXECUTE AS 'dbo' code in that Database full
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.
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:
- prevent anyone from using
EXECUTE ASwith that User in other modules to gain access to whatever else the
VIEW SERVER STATEpermission controls, nor will it…
- prevent anyone from using
EXECUTE ASwith some other User that has an associated Login that is highly privileged (
EXECUTE AS OWNERand
EXECUTE AS 'dbo'are effectively
EXECUTE AS safor any Database that is owned by
sa, and there is a good chance that your Databases are indeed owned by
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
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:
- PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining
While it is true that the DMV in question does need the
VIEW SERVER STATEpermission, for this particular case it is not required to use the DMV in the first place. The Connection info for the current Session can be obtained from the CONNECTIONPROPERTY built-in function. If this were not example code then that would be the preferred option as it does not require any additional steps to get working. Coincidentally, this fact is itself a good example of the benefit of looking through the available built-in functions first to possibly avoid wasting time over-engineering a solution :-). ↩