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