Signing Stored Procedures for Server Level Permissions

What’s the Problem

Stored procedures are often used to allow users to access data in tables that they do not have explicit permissions to use. The mechanism that makes this possible is ownership chaining, whereby in the context of the stored procedure, the permissions of its owner are used (often dbo). This means users only have a limited set of explicit permissions (i.e. EXEC on the stored procedure), but are able to execute CRUD statements using the stored procedures.

However, we sometimes need to be able to do things in a stored procedure that are not supported via ownership chaining (by design), such as:

• Truncate and bulk load tables
• Dynamic SQL
• Cross database queries to another database which the user does not have access to, without using TRUSTWORTHY, or enable cross database ownership chaining.
• Start an agent job, or doing other limited admin tasks such as monitoring.

Although it is possible to achieve some of this using the EXEUTE AS clause on the stored procedure declaration, this can have some issues

• Only DB scoped permissions, not server scoped permissions
• EXECUTE AS changes the execution context of the stored procedure, which can be problematic when auditing is required.

Signed Stored Procedures

We can use certificates or asymmetric keys to sign the stored procedures. However, we will concentrate on the use of certificates here, as they can be backed up and restored, unlike asymmetric keys.

Once we have created a certificate, we can use it to create logins/users, adding them to roles and granting them permissions as we would any other login/user. These users cannot be impersonated, and only apply their permissions in the context of stored procedures (and trigger and functions) signed with the same certificate used to create the user.

Many of the examples I looked at signed the stored procedure using a password protected certificate. When dealing with certificates, one of the most common problems is dealing with the password that protects the private key. However, the diagram below shows how we can leverage the SQL Server Encryption Hierarchy and use the Service Master Key and Database Master Keys to protect the certificates. When done properly the certificate password is only needed for importing a certificate from the filesystem. This makes the dev-ops process transparent; as long as the deployment is done by a sysadmin, then the certificate can be used to sign the stored procedure. No other users have access to the private key of the certificate, so it is secure.

The examples on MSDN show only how to create a database scoped user from a certificate to assign database level permissions via signing. We will look at using certificate based logins to allow server level roles and permissions to be used by a signed stored procedure.

Demo

Anyway, on to the demo. Let’s create a database, CertificatesTestDB, and setup a database master keys on master and CertificatesTestDB.

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterDatabaseKey123!';
GO

CREATE DATABASE CertificatesTestDB;
GO
  
USE CertificatesTestDB;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'CertificatesTestDBDatabaseKey123!';
GO

Now we can create the certificate. We need to add it to master to create logins using it, and to the user databases to create users with it or to sign stored procedures. We have to use the same certificate for both databases, so we backup the certificate to disk, encrypting the private key with a password, before importing the backup to create it in the other database.

USE master
CREATE CERTIFICATE TestCert
    WITH SUBJECT = 'A Test Certificate';

BACKUP CERTIFICATE TestCert TO FILE = 'C:\\temp\\TestCert.cer'
WITH PRIVATE KEY (FILE = 'C:\\temp\\TestCert.pvk'
                 ,ENCRYPTION BY PASSWORD = 'Backup Certificate Password 123456789!'
                 );
 
 
USE CertificatesTestDB
 
CREATE CERTIFICATE TestCert FROM FILE = 'C:\\temp\\TestCert.cer'
WITH PRIVATE KEY (FILE = 'C:\\temp\\TestCert.pvk'
                 ,DECRYPTION BY PASSWORD = 'Backup Certificate Password 123456789!'
                 );
 
-- Delete the backed up cert
EXEC xp_cmdshell 'del C:\\temp\\TestCert.cer';
EXEC xp_cmdshell 'del C:\\temp\\TestCert.pvk';

_**Note:** It is possible to copy a certificate from one database to another without backing up the certificate on 2012 or greater - see [MSDN](https://docs.microsoft.com/en-us/sql/t-sql/functions/certencoded-transact-sql) for more info._

Now we can create a login with the certificate and add it to a server role. For this demo we will add it to the bulkadmin fixed server role. We will also create a login for a restricted user, so we can test signed stored procedure.

USE master

-- Create a login with limited permissions for testing
CREATE LOGIN TestLogin WITH PASSWORD = 'P@ssw0rd123!P@ssw0rd123!';

-- Create a login based on the certificate, and add them to the bulkadmin server role
CREATE LOGIN TestCertLogin
    FROM CERTIFICATE TestCert;
 
EXEC sp_addsrvrolemember @loginame = 'TestCertLogin',
                         @rolename = 'bulkadmin';
 
 
USE CertificatesTestDB

-- Create a database user from the login for testing 
CREATE USER TestUser FOR LOGIN TestLogin;
GO

-- Create a database user from the certificate login 
CREATE USER TestCertUser FOR LOGIN TestCertLogin;
GO

Now that we have the logins and users setup, lets create a simple stored procedure that will show us the context we are running under, and our effective permissions.

CREATE PROCEDURE dbo.TestCertPermissions
AS
BEGIN
    SELECT USER_NAME(),
           SUSER_SNAME(),
           ORIGINAL_LOGIN();

    SELECT *
    FROM fn_my_permissions (NULL, 'SERVER')
    UNION ALL
    SELECT *
    FROM fn_my_permissions (NULL, 'DATABASE');
END
GO

Before we sign the stored procedure, lets run it. I executed it on my test server as myself (a sysadmin) and as the limited test login

-- Execute it using my login
EXEC dbo.TestCertPermissions;

-- Execute it using the restricted login
EXECUTE AS USER = 'TestUser';
EXEC dbo.TestCertPermissions;
REVERT;

The results show that my user has a lot of permissions (sysadmin), and the restricted user has only the CONNECT permission, and rights to view the definition of any encryption keys. This just means the user can view the public key, to use the encryption keys the user needs access the private key, which requires CONTROL permissions on the certificate or asymmetric key.

Now we sign the stored procedure, and run stored procedure as both the restricted user and the restricted login.

ADD SIGNATURE TO dbo.TestCertPermissions
    BY CERTIFICATE TestCert;

EXECUTE AS USER = 'TestUser';
EXEC dbo.TestCertPermissions;
REVERT;
 
EXECUTE AS LOGIN = 'TestLogin';
EXEC dbo.TestCertPermissions;
REVERT;

The results show that under the context of the restricted user, only the permissions from database scoped users mapped to a certificate are applied. However, when we run the procedure under the context of the restricted login, the permissions applied to both logins and users mapped to the certificate are applied.

One thing to note here, is that unlike the use of the EXECUTE AS clause, the context for a signed stored procedure does not change. Instead the permissions are effectively added to the user’s permissions inside the scope of the stored procedure. This means that dynamic SQL and nested procedure calls inherit those permissions, so we must make sure that we properly parameterise any dynamic SQL.

Final Thoughts

We have seen how we can sign a stored procedure with a certificate to give user permission to do things that we don’t want to explicitly grant them permission to do. In another blog post we will look as using signed stored procedures for cross database queries.

It is good to be aware of the principle of least privilege when using certificates to assign permissions to a stored procedure. If there are different sets of permissions needed by different stored procedures, such as BULK ADMIN for one and VIEW SERVER STATE for another, then if possible use two logins mapped to different certificates.

The full code sample is available on GitHub

• Next Post →