Using Certificates to Sign Stored Procedures

  • Comments posted to this topic are about the item Using Certificates to Sign Stored Procedures

  • Hi Jonathon

    That was a nice article.

    I have a doubt.

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

    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.

    Note:- I have not gone thru BOL or any other documentation before asking this. Please bear with me.

    "Keep Trying"

  • Excellent article. Well written with clear examples.

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

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

    Jonathan is correct. In fact it would be a bad practice to do so. One of the primary reasons for using stored procedure database access is to avoid ever having to grant direct access to database data. You don't have to implement certificate access to prevent direct table access.

    Still a nice article that offers up some good information.

    Tom Garth
    Vertical Solutions[/url]

    "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
  • I am trying to allow a non-sysadmin user to run the create login command. Is this possible? I have done the steps in your article but my user still can't create logins. The login created by the certificate has the security administrator role. The user is dbo in his database and I've signed the stored procedure that he runs. We would only use this for the one stored procedure on the test server. Can this be done?

  • Great Article!

    I have a couple of suggestions to make the stored proc more flexible.

    1.Make the certificate name and certificate user name parameters so that it could be used for multiple certificate/users without changing code within the Proc.

    2.If you changed the cursor to be a temp table or table variable that is populated from the select, it could then be used to loop through instead of a cursor. You could then use the same table variable to loop back through to add the signatures with the newly recreated certificate user.

    3.Add a loop similar to above for the granting of the table permissions so that it can be dynamically granted to the cert user based on what it was before the user was dropped.

    Sample procedure code:

    -- **********************************************************************

    -- PROCEDURE GrantCertificateAccess

    -- Drops all certificate signatures and recreates them for existing certificate signed stored procs

    -- for the cert value, cert user, and the new password passed in through parameters

    -- Exeample Execution: EXEC GrantCertificateAccess 'myCertName', 'myCertUserName','myNewPassword'

    -- **********************************************************************

    CREATE PROCEDURE [dbo].[GrantCertificateAccess]

    (

    @CertificateName AS sysname = 'myCertName',

    @CertificateUserName AS sysname = 'myCertUserName',

    @Password AS sysname = 'myNewPassword'

    )AS

    BEGIN

    --Declare Variables

    DECLARE @sSql varchar(4000)

    DECLARE @NewLine varchar(4000)

    SET @NewLine = CHAR(13) + CHAR(10)

    DECLARE @ProcRowsToProcess int

    DECLARE @TableRowsToProcess int

    DECLARE @currentrow int

    DECLARE @ProcedureName AS sysname

    DECLARE @TableName AS varchar(100)

    DECLARE @Permission AS varchar(20)

    --Table variable for list of Procedures with Certificate Signature

    DECLARE @ProcList TABLE

    (ProcedureName VARCHAR(200), Row_ID int not null primary key identity(1,1))

    --Table variable for table permissions granted to Certificate User

    DECLARE @TableList TABLE

    (TableName VARCHAR(200), Permission VARCHAR(20), Row_ID int not null primary key identity(1,1))

    --Populate the table variable for the Procedure list

    INSERT INTO @ProcList (ProcedureName)

    SELECT Object_Name(CP.major_id) ProcedureName

    FROM sys.certificates C

    INNER JOIN sys.crypt_properties CP ON CP.thumbprint = C.thumbprint

    WHERE C.Name = @CertificateName

    SET @ProcRowsToProcess=@@ROWCOUNT

    --Populate the table variable for the Table permission list

    INSERT INTO @TableList (TableName, Permission)

    SELECT '['+db_name()+'].['+Object_Name(p.major_id)+']' AS tablename,

    p.permission_name

    FROM sys.database_permissions p

    LEFT OUTER JOIN sys.all_objects o ON p.major_id = o.OBJECT_ID

    INNER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id

    WHERE o.type = 'U' --only pull permissions for user tables

    AND p.state_desc = 'GRANT'

    AND p.permission_name IN ('SELECT','INSERT','UPDATE','DELETE')

    AND dp.NAME = @CertificateUserName

    --initialize this variable for the current loop

    SET @CurrentRow=0

    --*********************************************************************

    -- Drop the signatures signed by the Certificate from stored procedures

    --*********************************************************************

    PRINT '-- Dropping existing Signatures from Procedures...'

    WHILE @CurrentRow < @ProcRowsToProcess

    BEGIN

    SET @CurrentRow = @CurrentRow + 1

    SELECT @ProcedureName = ProcedureName

    FROM @ProcList

    WHERE Row_ID = @currentrow

    SET @sSql = 'DROP SIGNATURE FROM ' + QuoteName(@ProcedureName) + ' BY CERTIFICATE ' + @CertificateName

    PRINT @sSql

    EXEC sp_executesql @sSql

    END --END WHILE

    PRINT @NewLine + '-- Dropping Certificate User...'

    IF EXISTS(SELECT 1

    FROM sysusers

    WHERE name = @CertificateUserName)

    BEGIN

    SET @sSql = 'DROP USER ' + @CertificateUserName

    PRINT @sSql

    EXEC(@sSql)

    END --END IF

    PRINT @NewLine + '-- Dropping Certificate...'

    IF EXISTS(SELECT 1

    FROM sys.certificates C

    WHERE C.[name] = @CertificateName)

    BEGIN

    SET @sSql = 'DROP CERTIFICATE ' + QuoteName(@CertificateName)

    PRINT @sSql

    EXEC (@sSql)

    END --END IF

    -- Create a new certificate if it does not exist

    IF NOT EXISTS(SELECT 1

    FROM sys.certificates C

    WHERE C.[name] = @CertificateName)

    BEGIN

    PRINT @NewLine + '-- Creating new certificate...'

    SET @sSql = 'CREATE CERTIFICATE ' + QuoteName(@CertificateName) + @NewLine

    + ' ENCRYPTION BY PASSWORD = ''' + @Password + '''' + @NewLine

    + ' WITH SUBJECT = ''Certificate for stored procedures using dynamic SQL'',' + @NewLine

    + ' START_DATE = ''2010-01-01'',' + @NewLine

    + ' EXPIRY_DATE = ''2100-01-01'''

    PRINT @sSql

    EXEC (@sSql)

    PRINT @NewLine + '-- Creating User...'

    SET @sSql = 'CREATE USER ' + QuoteName(@CertificateUserName) + ' FROM CERTIFICATE ' + QuoteName(@CertificateName)

    EXEC (@sSql)

    PRINT @sSql

    END --END IF

    --initialize this variable for the current loop

    SET @CurrentRow=0

    --**********************************************************

    -- Add back in the table permissions to the certificate user

    --**********************************************************

    PRINT @NewLine + '-- Granting Select to Certificate User...'

    WHILE @CurrentRow < @TableRowsToProcess

    BEGIN

    SET @CurrentRow = @CurrentRow + 1

    SELECT @TableName = TableName, @Permission = Permission

    FROM @TableList

    WHERE Row_ID = @currentrow

    SET @sSql = 'GRANT ' + @Permission + ' ON ' + @TableName + ' TO ' + QuoteName(@CertificateUserName)

    PRINT @sSql

    EXEC (@sSql)

    END --END WHILE

    --initialize this variable for the current loop

    SET @CurrentRow=0

    --****************************************************

    -- Add back in the signatures to the stored procedures

    --****************************************************

    PRINT 'Signing procedures...'

    WHILE @CurrentRow < @ProcRowsToProcess

    BEGIN

    SET @CurrentRow = @CurrentRow + 1

    SELECT @ProcedureName = ProcedureName

    FROM @ProcList

    WHERE Row_ID = @currentrow

    SET @sSql = 'ADD SIGNATURE TO ' + QuoteName(@ProcedureName) + ' BY CERTIFICATE ' + QuoteName(@CertificateName) + ' WITH PASSWORD = ''' + @Password + ''''

    PRINT @sSql

    EXEC (@sSql)

    END --END WHILE

    END--END Procedure

    GO

  • Judy Scheinuk (6/21/2010)


    I am trying to allow a non-sysadmin user to run the create login command. Is this possible? I have done the steps in your article but my user still can't create logins. The login created by the certificate has the security administrator role. The user is dbo in his database and I've signed the stored procedure that he runs. We would only use this for the one stored procedure on the test server. Can this be done?

    I've never used a certificate to grant create logins to a stored procedure but you would have to grant ALTER ANY LOGIN to the certificate user that is used to sign the stored procedure.

  • Thank you so much! It all works now. Granting the login the system administrator role was not necessary - just the Grant ALTER ANY LOGIN to xxxxx.

    Thanks for the excellent article.

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

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

  • 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[/url]

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

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

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

Viewing 15 posts - 1 through 15 (of 19 total)

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