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.