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.

Problems:

1. At the very least, a User must have ALTER permission on a Table in order to execute TRUNCATE TABLE on it. But granting ALTER TABLE allows for doing several other things as well, even making changes to the Table.
2. 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.

Initial Setup

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];

Test 1

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];

Test 2

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:

1. The process now works: the dbo.StatsJanuary Table can be truncated without causing an error.
2. 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;
');

Test 3

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:

1. The User does not have permission to truncate the dbo.OtherData Table directly
2. The User does not have permission to execute the new dbo.OtherData_Truncate Stored Procedure
3. While the User does have implicit permission to execute dbo.OtherData_Truncate via ownership chaining when executing dbo.Stats_Truncate (both Stored Procedures have the same owner), the User still cannot truncate the dbo.OtherData Table even though they can still truncate dbo.StatsJanuary

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 will be the topic of future post).

-- 7) Sign the other Module
ADD SIGNATURE
    TO [dbo].[OtherData_Truncate]
    BY CERTIFICATE [Permission$AlterSchema]
    WITH PASSWORD = 'UseBetterPassword!';

Test 4

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:

1. The User still cannot directly truncate the Table or execute the new Stored Procedure
2. The process now works: the dbo.OtherData Table can be truncated without causing an error.

Please note:

• 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_Truncate we 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 dbo.OtherData_Truncate.

Cleanup

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

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

1. prevent anyone from using EXECUTE AS with that User in other modules to gain access to whatever else the ALTER SCHEMA permission controls, nor will it…
2. 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 EXECUTE permission 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:

• ModuleSigning.Info
• PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining
• Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level