Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Using Certificates to Sign Stored Procedures

By Jonathan Roberts,

This article applies to SQL Server 2005 and above.

Introduction

If a stored procedure uses dynamic SQL not only do you have to give the user of the stored procedure execute permissions to the procedure but it is standard practice to also grant them access to the tables that the dynamic SQL uses, for example if the dynamic SQL is selecting from table [Production].[Product] then the user would be granted SELECT access to the table. If the table were being deleted from using dynamic SQL then you would have to grant the user DELETE access to the table. These extra rights can open up applications to SQL injection and give the users of the stored procedure rights to access tables that you wouldn't necessarily want to give them. If your database application is a public web site then the users could be accessing your database through the internet and if they can find somewhere on the site that is vulnerable to SQL injection then the site is open to attack. A way to prevent having to grant the user access to the tables is to sign the store procedures that use dynamic SQL with a certificate.

Steps

The basic steps are:

  1. Create a certificate
  2. Create a user from the certificate
  3. Grant the access to the tables to the Certificate User
  4. Sign stored procedures with the Certificate.

1. Create a certificate

CREATE CERTIFICATE myCertificateName
ENCRYPTION BY PASSWORD = 'myPassword1234'
WITH SUBJECT = 'Certificate for signing stored procedures',
START_DATE = '2010-01-01',
EXPIRY_DATE = '2100-01-01'

The password used to create the certificate doesn't really need to be that secure for protecting from SQL injection but it has to confirm to the minimum requirements of operating system logins so may need numbers and non-alphanumeric characters to be accepted. It is used later when signing stored procedures.

2. Create a user from the certificate

CREATE USER myCertificateUser
  FROM CERTIFICATE myCertificateName


 

3. Grant the access to the tables to the Certificate User

GRANT SELECT ON myTable1 TO myCertificateUser
GRANT SELECT ON myTable2 TO myCertificateUser ...

The tables to include here are the ones that are used in dynamic SQL in the stored procedures..

4. Sign stored procedures with the Certificate

ADD SIGNATURE TO myProcedure1 BY CERTIFICATE myCertificateName WITH PASSWORD = 'myPassword1234'
ADD SIGNATURE TO myProcedure2 BY CERTIFICATE myCertificateName WITH PASSWORD = 'myPassword1234'
...

Note: The password used to sign the certificate must be the same one used to create the certificate.

Now when you grant a user EXECUTE permissions to a stored procedure they will be able to execute the dynamic SQL within the stored procedures without needing any access to the tables granted directly to them.

The following query can be used to see which stored procedures are signed by which certificates :

SELECT Object_Name(CP.major_id) ProcedureName,
       C.name CertificateName
  FROM sys.certificates C
 INNER JOIN sys.crypt_properties CP
         ON CP.thumbprint = C.thumbprint

A script can be created that is run after every release of stored procedures to an environment this script can be encapsulated within a stored procedure, so all you would need to do is run it after each release of database code. The example given below is a stored procedure that could be run after stored procedures are installed on a system. I have included code to drop all the signatures from procedures, delete the user and certificate if they exist and then reapply them, this is useful if you just want to delete the certificate as it cannot be dropped until all stored procedures have their signature dropped.

USE AdventureWorks
GO
-- **********************************************************************
-- PROCEDURE GrantCertificateAccess
-- Usage: EXEC GrantCertificateAccess 'myNewPassword'
-- **********************************************************************
CREATE PROCEDURE [dbo].[GrantCertificateAccess] ( @Password AS sysname = 'myPassword&34*' )AS BEGIN DECLARE @sSql nvarchar(4000)
DECLARE @NewLine nvarchar(4000)
SET @NewLine = CHAR(13) + CHAR(10)
DECLARE @CertificateName AS sysname
SET @CertificateName = 'myUserCertificate'

DECLARE @CertificateUserName AS sysname
SET @CertificateUserName = 'myCertificateUser'

DECLARE @ProcedureName AS sysname

DECLARE ProcedureCursor CURSOR
FOR 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

OPEN ProcedureCursor

--*****************************************************
-- Drop the signatures signed by the Certificate from stored procedures
--*****************************************************
FETCH NEXT
FROM ProcedureCursor
INTO @ProcedureName

PRINT '-- Dropping existing Signatures from Procedures...'
WHILE @@Fetch_Status = 0
BEGIN
SET @sSql = 'DROP SIGNATURE FROM ' + QuoteName(@ProcedureName) + ' BY CERTIFICATE ' + @CertificateName
PRINT @sSql
EXEC sp_executesql @sSql

FETCH NEXT
FROM ProcedureCursor
INTO @ProcedureName
END
--END WHILE

CLOSE ProcedureCursor
DEALLOCATE ProcedureCursor PRINT @NewLine + '-- Dropping Certificate User...' IF EXISTS(SELECT * 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 * 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 * 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 PRINT @NewLine + '-- Granting Select to Certificate User...' SET @sSql = 'GRANT SELECT ON [Production].[Product] TO ' + QuoteName(@CertificateUserName) PRINT @sSql EXEC (@sSql) PRINT @NewLine + 'Signing procedures...' SET @ProcedureName='SearchProducts_SQLInjectable' SET @sSql = 'ADD SIGNATURE TO ' + QuoteName(@ProcedureName) + ' BY CERTIFICATE ' + QuoteName(@CertificateName) + ' WITH PASSWORD = ''' + @Password + '''' PRINT @sSql EXEC (@sSql) SET @ProcedureName='SearchProducts_SQLNonInjectable' SET @sSql = 'ADD SIGNATURE TO ' + QuoteName(@ProcedureName) + ' BY CERTIFICATE ' + QuoteName(@CertificateName) + ' WITH PASSWORD = ''' + @Password + '''' PRINT @sSql EXEC (@sSql) SET @ProcedureName='SearchProducts_SQLNonInjectableWithDebug' SET @sSql = 'ADD SIGNATURE TO ' + QuoteName(@ProcedureName) + ' BY CERTIFICATE ' + QuoteName(@CertificateName) + ' WITH PASSWORD = ''' + @Password + '''' PRINT @sSql EXEC (@sSql) END GO

The granting of EXECUTE rights to stored procedures could also be included within this procedure so that running it would grant all the required access to users for the entire database application. Make sure you don't grant execute permissions to this stored procedure to users of the database though.

Conclusion

This article has shown how to create a certificate and sign stored procedures. It is desirable to sign stored procedures if they use dynamic sql as it removes the necessity of having to grant a user rights to tables, instead the rights are granted individually to stored procedures via a certificate. 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, see my previous article Use Dynamic SQL to Improve Query Performance.

Total article views: 7672 | Views in the last 30 days: 7
 
Related Articles
FORUM

Server Permissions in a Stored Procedure with a Certificate

Granting a stored procedure the permissions to create database logins

FORUM

Error in Printing the document using SQL CLR Stored Procedure

Error in Printing the document using SQL CLR Stored Procedure

ARTICLE

Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail

Learn how to create a certificate signed stored procedure to solve common permissions problems using...

ARTICLE

Grant EXEC on all Stored Procedures to a Role

Easily grant exec permission to all stored procedures to a database role in SQL 2005 or higher.

FORUM

granting execute permissions to ALL stored procedure

how can I grant access to all stored procedures at once

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones