Digitally Signing a Stored Procedure To Allow It To Run With Elevated Permissions

signedprocs7

Have you ever had the need to give elevated permissions via a stored procedure above that what the user calling the procedure might have?

Perhaps you’ve got a table in your database that contains top secret information and you only want that information to be accessed by via a proc, you’re users are denied access to the underlying table.

signedprocs1

We really don’t want our user ‘MoneyPenny’ to be able to access that top secret table in our database so we’ll deny her access to it.


DENY SELECT ON spys TO MoneyPenny

So now if she ever ties to access that table, she’ll just get the message,

signedprocs2

What happens if we need MoneyPenny to be able to access a spy’s code name?

For whatever reason, the need has come up for MoneyPenny to be able to match up a spy’s id with their code name.  But as we need to make sure that she can only ever see their code name and never their real name, we’ll create a stored procedure for her to use.


CREATE PROC ID2CodeName (@ID INT)
AS

BEGIN

SELECT CodeName
FROM Spys
WHERE ID = @ID

END

and grant execute to MoneyPenny


GRANT EXECUTE ON ID2CodeName TO MoneyPenny

Looks simple enough but whenever she tries to run it, she’s getting the error…

signedprocs2

Signed Stored Procedures

One way that we can do this is by signing the procedure (you can also use this with functions and triggers) with a certificate or an asymmetric key.

In this post I’m only going to look into signing a stored procedure with a certificate but the same ideas can be applied for the other objects and with an asymmetric key.

So…

We’re going to create a certificate and sign our stored proc using that certificate.  We’ll then create a user based on the certificate and grant the new certificate user the appropriate permissions to run the stored procedure.

Once that’s done, when we run the procedure, we’ll be using the permission set of the newly created user.

Create a Certificate


CREATE CERTIFICATE ID2CodeName_cert
ENCRYPTION BY PASSWORD = 'P455w0rd'
WITH SUBJECT = 'Certificate to sign ID2CodeName Procedure'

If you now check out sys.certificates, you should see your newly created certificate.


 SELECT * FROM sys.certificates

Sign the Stored Procedure

Now that the certificate has been created, we can use it to sign our procedure.  You’ll need to use the password that you used to encrypt the certificate so make sure you keep that nice and safe.


ADD SIGNATURE TO [dbo].[ID2CodeName]
BY CERTIFICATE ID2CodeName_cert WITH PASSWORD = 'P455w0rd'

You can see all the objects that have been signed by running the following code,


SELECT OBJECT_NAME(crypt_properties.major_id) AS ObjectName,
 certificates.name AS CertificateName,
 crypt_properties.crypt_type_desc
FROM sys.crypt_properties
JOIN sys.certificates ON crypt_properties.thumbprint = certificates.thumbprint
WHERE crypt_type_desc = 'SIGNATURE BY CERTIFICATE'

Running that, we should see the proc that we’ve just signed.

signedprocs6

Create Certificate User

The next step that we need to do is to create a certificate user…


CREATE USER CertUser FROM CERTIFICATE ID2CodeName_cert

… and grant that user all the nesassary permissions to run the procedure (in our case, that’s SELECT on the spy table).


GRANT SELECT ON spys TO CertUser

Test It Out!

Now that we’ve set all that up, whenever the ID2CodeName procedure is called, it’ll run using the permissions of the CertUser (even with it’s running as EXECUTE AS CALLER).  Lets see what happens when MoneyPenny tries to execute the proc now…


EXEC ID2CodeName 2

signedprocs4

And there we have it!

One Thing To Note:
If You Change The Proc It’ll Need to Be Resigned

That’s because the digital signature is really just the query hash, encrypted using the certificate.

If the procedure changes, the query hash will also change.  That means that it’ll no longer match the signature and the signature will be invalid.

If this happens the permissions will revert back to the caller’s permissions.

What If The Procedure Accesses Multiple Databases?

In this case, you’ll need to make sure that the certificate is imported into every database that you need to access.  Once it’s there you can create a user tied to that certificate in that same way that we did above.

One thought on “Digitally Signing a Stored Procedure To Allow It To Run With Elevated Permissions

Add yours

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: