Using Certificates to Sign Stored Procedures

Question

Using Certificates to Sign Stored Procedures

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

UMG Developer SSChampion Points: 13482 More actions · Answer 1

Jonathan AC Roberts (6/21/2010)
Both methods achieve the same thing but using certificates is generally recommended. Some things are different for example if there is auditing a table with a trigger and the function user_name() is used then the function would return the EXECUTE AS user not the actual caller of the stored procedure. Also, if another stored procedure is called from within the stored procedure it would execute that stored procedure with the EXECUTE AS user rather than the actual user.

You can, and probably should use the ORIGINAL_LOGIN() function in any logging you do in triggers so that you can get the actual user in the cases of EXECUTE AS.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

Jonathan AC Roberts (6/21/2010)
ChiragNS (6/21/2010)
We granted select permissions to mycertificateuser. Do we need to give permissions to all the other users who are going to execute the procedure (i think not.. thats the point of this article).
You need to grant EXECUTE permission to the stored procedure to all users who need to execute the procedure but you do not need to grant them any access to the tables if you use a certificate.
User xyz has been given execute permissions to the stored procedure and no select permissions on the underlying tables. When xyz executes this procedure does the exectution context belong to mycertificateuser. How does xyz get access to the underlying tables in the dynamic sql in hte stored procedure.
The procedure is executed by user 'xyz' but the stored procedure gets its permission to select from the table through the certificate.

Thank you Jonathan

"Keep Trying"

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Thanks for taking the time to share this with us.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

James Woodhouse Grasshopper Points: 15 More actions · Answer 4

Hi Jonathan. Good article - however, I have run into a hitch.

I'm trying to use table functions to encapsulate logic for re-use.

If the signed procedure accesses the tables directly, the certificate and the certificate user allow this to occur.

However, you can't sign a table function, and a procedure which calls a table function which then selects from the table... no joy here either.

It's like the security link between the procedure and the user is lost in translation.

Can you tell me if I'm on a no hoper here? Or is there a way to do this?

Regards,

James

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 5

Hi James,

I'm not sure exactly what you are trying to do. Is it that you have a stored procedure that calls an inline table function and it's the inline table function that accesses a table with dynamic sql? Maybe if you can paste the two routines in this thread or some more details?