Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Using Certificates to Sign Stored Procedures Expand / Collapse
Author
Message
Posted Monday, June 21, 2010 2:29 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:16 PM
Points: 130, Visits: 108
One benefit is that the Certificate and certificate user account are managed outside of the stored procedure code and can be updated or the user account changed without having to change the stored proc code for every procedure that uses thecert. "With Execute as" is embedded in the code so each proc would have to be altered in the case that you needed to change the user (ex. user account is compromised by a terminated employee).
Post #940628
Posted Monday, June 21, 2010 2:49 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
SQL_Elvis (6/21/2010)
One benefit is that the Certificate and certificate user account are managed outside of the stored procedure code and can be updated or the user account changed without having to change the stored proc code for every procedure that uses thecert. "With Execute as" is embedded in the code so each proc would have to be altered in the case that you needed to change the user (ex. user account is compromised by a terminated employee).

Definitely true with SQL Server authentication. With Windows Authentication, Security Groups are a little easier, but certificates are still an option.


Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #940643
Posted Monday, June 21, 2010 3:27 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
SQL_Elvis (6/21/2010)
One benefit is that the Certificate and certificate user account are managed outside of the stored procedure code and can be updated or the user account changed without having to change the stored proc code for every procedure that uses thecert. "With Execute as" is embedded in the code so each proc would have to be altered in the case that you needed to change the user (ex. user account is compromised by a terminated employee).


I think you get the same benefit by using "proxy" logins, that can't actually log-in to the DB, so the login itself can't be compromised:

CREATE USER [Proxy_User] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]

Then you just assign the SP to EXECUTE AS that login:

CREATE PROCEDURE [dbo].[usp_xxx] (@SQL VARCHAR(MAX))
WITH EXECUTE AS N'Proxy_User'

But I don't see how either using the certificates or the proxy login protects you from SQL injection attacks when using dynamic SQL, as the SP can still do anything that the certificate user/proxy login has permissions to do. What it does do is make it so we don't have to give end-users direct access to the data, or have the application login with an account that can be compromised.
Post #940656
Posted Monday, June 21, 2010 5:41 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:45 AM
Points: 338, Visits: 1,431
UMG Developer (6/21/2010)
But I don't see how either using the certificates or the proxy login protects you from SQL injection attacks when using dynamic SQL, as the SP can still do anything that the certificate user/proxy login has permissions to do.

Yes that's true but I did add at the bottom of the article that "Signing stored procedures is a step to making your application SQL injection proof, you would also need to make sure that any stored procedures that use dynamic SQL do not allow injection, this can be achieved using parameterised dynamic SQL". So the steps to make sure your application not SQL injectable is both to not granting users any access rights to tables and also making sure that any stored procedures that use dynamic SQL use parameters.
Post #940700
Posted Monday, June 21, 2010 6:03 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:45 AM
Points: 338, Visits: 1,431
janyalee (6/21/2010)
Using a Certificate Versus Stored Procedure WITH EXECUTE AS
Hello Jonathan, I appreciated this article. It got me wondering, what signing with a certificate gets us that SP WITH EXECUTE AS functionality does not?
Either way we grant myCertUserName read access to the underlying tables called by the sp.
CREATE PROCEDURE dbo.usp_Demo
WITH EXECUTE AS 'CompanyDomain\myCertUserName'
AS
SELECT user_name();
GO
Thank you in advance for your feedback.

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.
Post #940703
Posted Monday, June 21, 2010 6:10 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
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.
Post #940705
Posted Tuesday, June 22, 2010 2:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
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"
Post #940818
Posted Tuesday, June 22, 2010 7:40 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 17,812, Visits: 15,738
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
Post #941444
Posted Monday, June 10, 2013 12:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 1, 2013 10:27 AM
Points: 3, Visits: 42
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
Post #1461726
Posted Wednesday, June 19, 2013 5:00 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:45 AM
Points: 338, Visits: 1,431
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?
Post #1465067
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse