SQL Server 2005: procedure signing demo

re: SQL Server 2005: procedure signing demo

Chrisr — Fri, 17 Jun 2005 18:38:00 GMT

I think I am missing something when it comes to providing data security. The above example is really nice when permission P is not SELECT\UPDATE\INSERT\DELETE for a given table. However, I can prevent a user from directly accessing data on a table by implementing a stored procedure and only granting execute privs to the user on the procedure. This works if the procedure is signed or un-signed. I can also see how it would be beneficial if ownership chaining is involved with the stored procedure to be executed. But I don't see the advantage of signing a procedure when that procedure operates against tables in its own schema and I wish to prevent granting direct privs to a user on the tables.

re: SQL Server 2005: procedure signing demo

lcris — Fri, 17 Jun 2005 20:03:47 GMT

You're right, if what you want to do can be achieved by ownership chaining, then you don't need to sign your procedure. However, the ability to sign procedures allows scenarios that are not possible with ownership chaining.

re: SQL Server 2005: procedure signing demo

brian — Tue, 20 Sep 2005 21:20:44 GMT

Hi, I am missing the connection between Alice and u_certSignCreatePrincipal. Do all users that have EXECUTE have permissions to run the proc or just Alice?

re: SQL Server 2005: procedure signing demo

lcris — Wed, 21 Sep 2005 02:14:37 GMT

alice is just a low-privileged principal that is used to demo the fact that she can create logins via the proc, without explicitly having been granted the permission to create logins.

u_certSignCreatePrincipal is used to grant database permissions to the certificate. l_certSignCreatePrincipal is used to grant server permissions to the certificate. Both these principals are mapped to the certificate via the FROM CERTIFICATE clause.

In the example, alice is explicitly granted EXECUTE permission on the procedure.

re: SQL Server 2005: procedure signing demo

toddsriley — Mon, 11 Aug 2008 17:10:27 GMT

Great article. I am looking into using certificates and procedure signing as a temporary means of getting around granting direct permissions to the sp_OA procedures to our developers. Eventually, we will, of course, move to using CLR procedures instead of sp_OA but as it stands, I just need a way to give these permissions to our developers through stored procedure signing.

Here’s what I have been able to do and where I am running into issues. I started by creating a stored procedure that was similar to the situation we have in our real production environment. The procedure simply uses the sp_send_dbmail stored procedure to send me an email:

USE [my_database]

CREATE PROCEDURE dbo.P_Send_Mail_Test

BEGIN

EXECUTE msdb.dbo.sp_send_dbmail

@profile_name = 'Name',

@recipients = 'myemail@whatever.com',

@body = 'Will this certification test work?',

@subject = 'Certification Test'

END

Then I execute the proc to ensure that it works. It does. Then I grant permissions to a user in our database who is not a member of the sysadmin server role, impersonate him, then try to execute the procedure – “you do not have permissions to execute sp_send_dbmail”. This was expected. So I reverted to my own permissions and went to the msdb database. I created a master key that was encrypted by a password and then a certificate:

CREATE CERTIFICATE My_Certificate

WITH SUBJECT = 'User Certificate to Extend Impersonation',

EXPIRY_DATE = '12/31/2009';

Then I added a signature to sp_send_dbmail by the certificate I had created and backed the cert up to a file location. (I had also removed the private key before backing up a few of the times I tried this). I created a user from the cert in msdb, granted execute permissions on sp_send_dbmail to the user and also granted authenticate to the user.

I went back to my_database and created cert with same name from file location:

CREATE CERTIFICATE My_Certificate FROM FILE = 'C:\cert_sign.cer'

HERE IS WHERE I AM HAVING MY PROBLEM. When I then try to sign my procedure, P_Send_Mail_Test, I get an error:

ADD SIGNATURE TO P_Send_Mail_Test BY CERTIFICATE My_Certificate

Msg 15556, Level 16, State 1, Line 2

Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.

In other articles, I have seen examples where the author makes use of a private key and not the master key. I would be more than happy to try this but do not know how to create a private key. I even tried, when signing the procedure, using the signature from crypt_properties – didn’t work. And also tried providing the password I used when creating the master key:

ADD SIGNATURE TO P_Send_Mail_Test BY CERTIFICATE User_Certificate

WITH PASSWORD = 'WhateverItWas'

Without being able to sign my procedure, I don’t think this will work as P_Send_Mail_Test calls another procedure (namely sp_send_dbmail) from within it.

Any information you might be able to give me would be most helpful.

Thanks,

Todd

re: SQL Server 2005: procedure signing demo

lcris — Mon, 11 Aug 2008 21:42:21 GMT

A private key is created whenever you create a certificate, but it has to be explicitly backed up (it's backed up separately from the certificate/public key). In my example, I removed the private key after signing the procedure with it, because I didn't need it for any other signature.

In your example, you would want to grant permissions to the P_Send_Mail_Test code, so you should sign this procedure, not sp_send_dbmail.

But the issue is a bit more complex, because what you are trying to do is to enable cross database access via signatures. That is a more complex example of signature use than what I showed in this post. For cross-database access, I have an example here: http://blogs.msdn.com/lcris/archive/2006/10/24/sql-server-2005-demo-for-enabling-database-impersonation-for-cross-database-access.aspx. I used this example in the PASS presentation on execution context that you can find here: http://blogs.msdn.com/lcris/archive/2006/12/08/sql-server-2005-security-presentations-at-pass-pre-conference.aspx. Have a look at these resources - there is also a very good BOL article on this topic that I also reference in the presentation: http://msdn.microsoft.com/en-us/library/ms188304.aspx.