Stored Procedure Signing Using Certificates

In this post, Data Education founder and trainer Adam Machanic discusses stored procedure signing using certificates. Here, he explains the attractive flexibility of creating a certificate-based proxy.

As mentioned in the previous post, proxy logins and users can be created based on certificates. Creating a certificate-based proxy is by far the most flexible way of applying permissions using a stored procedure, as the permissions are additive. One or more certificates can be used to sign a stored procedure, and each certificate will apply its permissions to the others already present, rather than replacing the permissions as happens when impersonation is done using EXECUTE AS.

To create a proxy user using a certificate, first create the certificate, and then create the user using the FOR CERTIFICATE syntax:

CREATE CERTIFICATE Greg_Certificate
WITH SUBJECT='Certificate for Greg'
GO

CREATE USER Greg
FOR CERTIFICATE Greg_Certificate
GO

Once the proxy user is created, it can be granted permissions to any resource in the database, just like any other database user. But a side effect of having created the user based on a certificate is that the certificate itself can also be used to propagate permissions granted to the user. This is where stored procedure signing comes into play.

To illustrate this, the following table can be created, and access granted to the Greg user:

CREATE TABLE GregsData
(
    DataColumn INT
)
GO

GRANT ALL ON GregsData
TO Greg
GO

A stored procedure can then be created that selects from the table, but for the sake of this example, the stored procedure will be owned by a user called Steve, in order to break any possible ownership chain that might result from creating both the table and the stored procedure in the same default schema:

CREATE PROCEDURE SelectGregsData
AS
BEGIN
    SET NOCOUNT ON

    SELECT *
    FROM GregsData
END
GO

CREATE USER Steve
WITHOUT LOGIN
GO

ALTER AUTHORIZATION ON SelectGregsData TO Steve
GO

Even if granted permission to execute this stored procedure, a third user will be unable to successfully do so, as the stored procedure does not propagate permissions to the GregsData table:

CREATE USER Linchi
WITHOUT LOGIN
GO

GRANT EXECUTE ON SelectGregsData TO Linchi
GO

EXECUTE AS USER='Linchi'
GO

--This will fail -- SELECT permission denied
EXEC SelectGregsData
GO

In order to make the stored procedure work for the Linchi user, permissions to the GregsData table must be propagated through the stored procedure. This can be done by signing the procedure using the same certificate that was used to create the Greg user. Signing a stored procedure is done using the ADD SIGNATURE command:

ADD SIGNATURE TO SelectGregsData
BY CERTIFICATE Greg_Certificate

Once the procedure is signed with the certificate, the procedure has the same permissions that the Greg user has; in this case, that means that any user with permission to execute the procedure will be able to select rows from the GregsData table when running the stored procedure.

The flexibility of certificate signing becomes apparent when you consider that you can sign a given stored procedure with any number of certificates, each of which can be associated with different users and therefore different permission sets. This means that even in an incredibly complex system with numerous security roles, it will still be possible to write stored procedures to aggregate data across security boundaries.

Keep in mind when working with certificates that any time the stored procedure is altered, all signatures will be automatically revoked by SQL Server. Therefore, it is important to keep signatures scripted with stored procedures, such that when the procedure is modified, the permissions can be easily kept in sync.

It is also important to know how to find out which certificates, and therefore which users, are associated with a given stored procedure. SQL Server’s Catalog Views can be queried to find this information, but getting the right query is not especially obvious. The following query, which returns all stored procedures, the certificates they are signed with, and the users associated with the certificates, can be used as a starting point:

SELECT
    OBJECT_NAME(cp.major_id) AS signed_module,
    c.name AS certificate_name,
    dp.name AS user_name
FROM sys.crypt_properties AS cp
INNER JOIN sys.certificates AS c ON c.thumbprint = cp.thumbprint
INNER JOIN sys.database_principals dp ON SUBSTRING(dp.sid, 13, 32) = c.thumbprint

This query is somewhat difficult to understand, so it is worth explaining here. The sys.crypt_properties view contains information about which modules have been signed by certificates. Each certificate has a 32-byte cryptographic hash, its thumbprint, which is used to find out which certificate was used to sign the module, via the sys.certificates view. Finally, each database principal has a security identifier, the final 32 bytes of which is the thumbprint if the principal was created from a certificate.