(last updated: 2020-01-09 @ 18:20 EST / 2020-01-09 @ 23:20 UTC )
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 might be granting that permission on all objects, or maybe granting the permissions to all objects within a particular Schema.
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 more secure than using Impersonation as it does not, by default, extend the additional permissions to any modules executed within the signed module. Additional modules can be signed with the same Certificate to grant them the additional permissions, but that is your choice. With Impersonation, once the process changes the security context to another User (usually
dbo), then any modules executed within that process (i.e. nested calls), no matter how many levels deep, still execute as the privileged User. Let’s take a look.
Basic / Common Test Case
Scenario: We want to allow one or more Users and/or Database Roles to be able to truncate certain Tables, but not all Tables. We certainly do not want to allow anyone the ability to make structural changes to the Table.
Also, it is likely that, over time, at least one more Tables will be added that the User(s) and/or Role(s) should be able to truncate, and less likely, though not impossible, that one or more tables that they should be able to truncate now might be removed.
- At the very least, a User must have
ALTERpermission on a Table in order to execute
TRUNCATE TABLEon it. But granting
ALTER TABLEallows for doing several other things as well, even making changes to the Table.
- Having a hard-coded list of which Tables they should be able to Truncate (in a Stored Procedure, for example) is difficult to maintain as that will be forgotten. It won’t be noticed until someone tries to truncate a new Table and gets an error, then they will report the error, and nobody currently on the team was around when this solution was put into place so time is wasted while research is done examining the code, critiquing the code, insulting the developer who created the Stored Procedure (for not commenting enough, for commenting too much, for using tabs instead of spaces or the other way around, for not using some feature that is currently available but wasn’t when that Stored Procedure was written, for hard-coding a list in the first place — i.e. things that the current team members would never do), arguing about how to do it better, arguing about if it is worth the time to rewrite it or just add the one line that is needed, doing the work, testing the changes, etc, etc.
Create the low-privileged Login (Server-level) and User (Database-level), a simple Table, and a Stored Procedure that accepts a string parameter for the Table name and which will use Dynamic SQL to construct the statement to execute (since
TRUNCATE TABLE does not accept a variable for the Table name).
CREATE LOGIN [AppUser2] WITH PASSWORD = 'NevrGonnaGesMe'; CREATE USER [AppUser2] FOR LOGIN [AppUser2]; CREATE TABLE dbo.[StatsJanuary] ( StatsJanuaryID INT IDENTITY(1, 1) NOT NULL CONSTRAINT [PK_StatsJanuary] PRIMARY KEY, [Stat] INT ); INSERT INTO dbo.[StatsJanuary] ([Stat]) VALUES (333); INSERT INTO dbo.[StatsJanuary] ([Stat]) VALUES (4444); EXEC (N' CREATE PROCEDURE dbo.[Stats_Truncate] ( @TableToTruncate NVARCHAR(100), @TruncateOtherData BIT = 0 ) AS SET NOCOUNT ON; DECLARE @TableName sysname, @SQL NVARCHAR(MAX); BEGIN TRY IF (ISNULL(@TableToTruncate, N'''') = N'''') BEGIN RAISERROR(''@TableToTruncate cannot be NULL or empty'', 16, 1); END; SET @TableName = N''dbo.'' + QUOTENAME(N''Stats'' + @TableToTruncate); IF (OBJECT_ID(@TableName) IS NULL) BEGIN RAISERROR(''Invalid Table name: %s'', 16, 1, @TableName); END; SET @SQL = N''TRUNCATE TABLE '' + @TableName + N'';''; EXEC (@SQL); PRINT QUOTENAME(SESSION_USER) + N'' successfully executed:'' + NCHAR(13) + NCHAR(10) + @SQL + NCHAR(13) + NCHAR(10) + NCHAR(20); IF (@TruncateOtherData = 1) BEGIN EXEC dbo.[OtherData_Truncate]; END; END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000); SET @ErrorMessage = ERROR_MESSAGE(); RAISERROR(@ErrorMessage, 16, 1); RETURN -1; END CATCH; '); GRANT EXECUTE ON dbo.[Stats_Truncate] TO [AppUser2];
Now that we have the items created we can impersonate the
AppUser2 Login and attempt to execute the
dbo.Stats_Truncate Stored Procedure. If all goes well (don’t worry, it won’t), the Stored Procedure will display a "successfully executed…" message in the “Messages” tab.
EXECUTE AS LOGIN = 'AppUser2'; SELECT SESSION_USER AS [CurrentUser]; -- AppUser2 TRUNCATE TABLE dbo.[StatsJanuary]; /* Msg 1088, Level 16, State 7, Line XXXXX Cannot find the object "StatsJanuary" because it does not exist or you do not have permissions. */ EXEC dbo.[Stats_Truncate] N'January'; /* Msg 50000, Level 16, State 1, Procedure dbo.Stats_Truncate, Line XXXXX [Batch Start Line YYYYY] Invalid Table name: dbo.[StatsJanuary] */ REVERT; SELECT SESSION_USER AS [CurrentUser]; -- dbo
Oops. The example above shows that the
AppUser2 Login does not have permission to truncate the
dbo.StatsJanuary Table. In order to truncate a Table, one needs to have
ALTER permission on the Table (or on the Schema that the Table is in, or on the Database), which is a Database-level permission. Well, technically permissions directly on a Table are Object-level permissions, but we are still working with Database objects and not Server-level objects.
Setup for Test 2
We have seen that this User cannot truncate the Table. The following steps (none of which are difficult) should allow the User to perform this operation without compromising security.
-- 1) Create the Certificate: CREATE CERTIFICATE [Permission$AlterSchema] ENCRYPTION BY PASSWORD = 'UseBetterPassword!' WITH SUBJECT = 'ALTER SCHEMA 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].[Stats_Truncate] BY CERTIFICATE [Permission$AlterSchema] 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$AlterSchema] TO FILE = 'C:\TEMP\cert2.cer' WITH PRIVATE KEY ( FILE = 'C:\TEMP\cert2.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$AlterSchema'), '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$AlterSchema] 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) Create a User from the Certificate CREATE USER [Permission$AlterSchema] FROM CERTIFICATE [Permission$AlterSchema]; -- 6) Grant the User any permissions needed to perform this action -- and/or add the User to any fixed Database-Roles needed to perform -- this action. GRANT ALTER ON SCHEMA::[dbo] TO [Permission$AlterSchema];
Now that the Module Signing is in place, let’s try it again…
SELECT * FROM dbo.[StatsJanuary]; EXECUTE AS LOGIN = 'AppUser2'; SELECT SESSION_USER AS [CurrentUser]; -- AppUser2 EXEC dbo.[Stats_Truncate] N'January'; /* [AppUser2] successfully executed: TRUNCATE TABLE dbo.[StatsJanuary]; */ TRUNCATE TABLE dbo.[StatsJanuary]; /* Msg 1088, Level 16, State 7, Line XXXXX Cannot find the object "StatsJanuary" because it does not exist or you do not have permissions. */ REVERT; SELECT SESSION_USER AS [CurrentUser]; -- dbo SELECT * FROM dbo.[StatsJanuary];
Executing the “Test 2” queries shows us that:
- The process now works: the
dbo.StatsJanuaryTable can be truncated without causing an error.
- The permission to truncate the Table was not given to the "AppUser2" Login since that Login cannot perform that operation on its own.
Extended Test Case
Sometime after setting up the truncate Stored Procedure (i.e. the “Basic / Common Test Case”) it becomes necessary to execute another Stored Procedure that will also require permissions that the executing User does not have.
Setup for Test 3
To keep the example simple, we will just truncate another Table. The Stored Procedure we created for the first test,
dbo.Stats_Truncate, already has conditional logic that allows for calling a second Stored Procedure. At this point we will create another Table and that second Stored Procedure.
CREATE TABLE dbo.[OtherData] ( OtherDataID INT IDENTITY(1, 1) NOT NULL CONSTRAINT [PK_OtherData] PRIMARY KEY, [SomethingElse] NVARCHAR(10) ); INSERT INTO dbo.[OtherData] ([SomethingElse]) VALUES ('a'); INSERT INTO dbo.[OtherData] ([SomethingElse]) VALUES ('b'); EXEC (N' CREATE PROCEDURE dbo.[OtherData_Truncate] AS SET NOCOUNT ON; BEGIN TRY TRUNCATE TABLE dbo.[OtherData]; PRINT QUOTENAME(SESSION_USER) + N'' successfully truncated [dbo].[OtherData] !''; END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000); SET @ErrorMessage = ERROR_MESSAGE(); RAISERROR(@ErrorMessage, 16, 1); RETURN -1; END CATCH; ');
Now we can execute the following:
SELECT * FROM dbo.[OtherData]; EXECUTE AS LOGIN = 'AppUser2'; SELECT SESSION_USER AS [CurrentUser]; -- AppUser2 TRUNCATE TABLE dbo.[OtherData]; /* Msg 1088, Level 16, State 7, Line XXXXX Cannot find the object "OtherData" because it does not exist or you do not have permissions. */ EXEC dbo.[OtherData_Truncate]; /* Msg 229, Level 14, State 5, Procedure dbo.OtherData_Truncate, Line XXXXX [Batch Start Line YYYYY] The EXECUTE permission was denied on the object 'OtherData_Truncate', database 'tempdb', schema 'dbo'. */ EXEC dbo.[Stats_Truncate] N'January', 1; /* [AppUser2] successfully executed: TRUNCATE TABLE dbo.[StatsJanuary]; Msg 50000, Level 16, State 1, Procedure dbo.Stats_Truncate, Line XXXXX [Batch Start Line YYYYY] Cannot find the object "OtherData" because it does not exist or you do not have permissions. */ REVERT; SELECT SESSION_USER AS [CurrentUser]; -- dbo SELECT * FROM dbo.[OtherData];
Executing the “Test 3” queries shows us that:
- The User does not have permission to truncate the
- The User does not have permission to execute the new
- While the User does have implicit permission to execute
dbo.OtherData_Truncatevia ownership chaining when executing
dbo.Stats_Truncate(both Stored Procedures have the same owner), the User still cannot truncate the
dbo.OtherDataTable even though they can still truncate
Setup for Test 4
All we need to do now is simply sign the new Stored Procedure. This assumes that we either did not remove the Private Key (optional Step 4 above), or we removed it but then restored it in order to sign something else (restoring Private Keys is discussed in another post).
-- 7) Sign the other Module ADD SIGNATURE TO [dbo].[OtherData_Truncate] BY CERTIFICATE [Permission$AlterSchema] WITH PASSWORD = 'UseBetterPassword!';
SELECT * FROM dbo.[OtherData]; EXECUTE AS LOGIN = 'AppUser2'; SELECT SESSION_USER AS [CurrentUser]; -- AppUser2 TRUNCATE TABLE dbo.[OtherData]; -- Same error as before (in Test 3): Msg 1088 EXEC dbo.[OtherData_Truncate]; -- Same error as before (in Test 3): Msg 229 EXEC dbo.[Stats_Truncate] N'January', 1; /* [AppUser2] successfully executed: TRUNCATE TABLE dbo.[StatsJanuary]; [AppUser2] successfully truncated [dbo].[OtherData] ! */ REVERT; SELECT SESSION_USER AS [CurrentUser]; -- dbo SELECT * FROM dbo.[OtherData];
Executing the “Test 4” queries shows us that:
- The User still cannot directly truncate the Table or execute the new Stored Procedure
- The process now works: the
dbo.OtherDataTable can be truncated without causing an error.
- Had we used Impersonation in
dbo.Stats_Truncate, those elevated permissions would have carried through to
dbo.OtherData_Truncate, but only because there is no way to stop that from happening.
- Rather than signing
dbo.OtherData_Truncatewe could have counter-signed it. But, doing so would serve no purpose: the end-result would have been the same, and the difference between those two options is irrelevant since the User is not able to directly execute
Execute the following to remove all objects (Login, Users, Certificate, Tables, and Stored Procedures) created by the example code above.
USE [tempdb]; IF (USER_ID(N'AppUser2') IS NOT NULL) BEGIN DROP USER [AppUser2]; END; IF (SUSER_ID(N'AppUser2') IS NOT NULL) BEGIN DROP LOGIN [AppUser2]; END; IF (OBJECT_ID(N'dbo.Stats_Truncate') IS NOT NULL) BEGIN DROP PROCEDURE [Stats_Truncate]; END; IF (OBJECT_ID(N'dbo.OtherData_Truncate') IS NOT NULL) BEGIN DROP PROCEDURE [OtherData_Truncate]; END; IF (OBJECT_ID(N'dbo.StatsJanuary') IS NOT NULL) BEGIN DROP TABLE [StatsJanuary]; END; IF (OBJECT_ID(N'dbo.OtherData') IS NOT NULL) BEGIN DROP TABLE [OtherData]; END; IF (USER_ID(N'Permission$AlterSchema') IS NOT NULL) BEGIN DROP USER [Permission$AlterSchema]; END; IF (CERT_ID(N'Permission$AlterSchema') IS NOT NULL) BEGIN DROP CERTIFICATE [Permission$AlterSchema]; END; ');
The Alternative: Impersonation
Rather than using Module Signing, you could always go the easier (and less secure) route of using Impersonation. That requires specifying the
EXECUTE AS clause of the
CREATE PROCEDURE statement (or
CREATE FUNCTION, or
CREATE TRIGGER, etc). You can only specify Users (i.e. Database-level Principals) in the
EXECUTE AS clause, but that is fine for our purposes here as we are only interested in elevated Database-level permissions.
However, even though the
EXECUTE AS clause allows the executing User to do the Impersonation without having to be explicitly granted the IMPERSONATE permission, it provides no control over the scope of the new security context. Meaning, once a module created with the
EXECUTE AS clause is executed, the new security context is in effect until the process ends, and hence is extended to any sub-processes that may be initiated by this module: nested Stored Procedure calls, Dynamic SQL, Triggers, Functions, etc. On the contrary, additional permissions granted via Module Signing only apply to the module that has been signed and to Dynamic SQL executed within it. Whether or not those additional permissions should apply to any other modules that might be executed within the signed module is entirely up to you.
Also, while Impersonation by default is quarantined to the Database, if the Database property of
TRUSTWORTHY is set to
ON, then there is no quarantine. 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 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. Though to be fair, the overhead is in the low milliseconds range, so the difference won’t really be noticeable with less than 500k executions.
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. Impersonation) is open-ended and would apply elevated permissions to nested procedure calls. There might not be any nested calls initially, but code changes over time and you cannot prevent a future change that might include nested calls. Creating a regular User 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 Stored Procedure (compared to using
EXECUTE AS 'dbo'), but it won’t:
- prevent anyone from using
EXECUTE ASwith that User in other modules to gain access to whatever else the
ALTER SCHEMApermission controls, nor will it…
- carry along any permissions that the executing User had to begin with. Depending on what your module is doing, you might need to assign additional permissions that Users with
EXECUTEpermission on the module already had, but were lost when the security context switched to the special User that had only been granted this one elevated permission.
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 code can be called by coding using the
EXECUTE AS clause. Additionally, in Databases marked as
TRUSTWORTHY, modules created with
EXECUTE AS 'dbo' (or
EXECUTE AS OWNER and existing in a
dbo-owned Schema, etc) have access to all other DBs owned by the same SID, plus any Server-level permissions granted to the Login matching that SID.
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: