Tutorial: Signing Stored Procedures with a Document

  • Article
  • 6 minutes to read

Applies to: yesSQL Server (all supported versions) YesAzure SQL Database YesAzure SQL Managed Instance

This tutorial illustrates signing stored procedures using a certificate generated past SQL Server.

Annotation

To run the code in this tutorial you must have both Mixed Mode security configured and the AdventureWorks2017 database installed.

Signing stored procedures using a certificate is useful when you lot desire to require permissions on the stored procedure but you do not want to explicitly grant a user those rights. Although you can accomplish this job in other ways, such as using the EXECUTE As statement, using a certificate allows you to utilize a trace to notice the original caller of the stored process. This provides a high level of auditing, especially during security or Information Definition Linguistic communication (DDL) operations.

Yous can create a document in the master database to permit server-level permissions, or you can create a certificate in any user databases to allow database-level permissions. In this scenario, a user with no rights to base tables must admission a stored procedure in the AdventureWorks2017 database, and you lot desire to inspect the object admission trail. Rather than using other ownership chain methods, you will create a server and database user account with no rights to the base of operations objects, and a database user account with rights to a table and a stored procedure. Both the stored procedure and the second database user account will be secured with a certificate. The second database business relationship volition have access to all objects, and grant access to the stored procedure to the first database user account.

In this scenario yous will outset create a database certificate, a stored process, and a user, and then you will test the procedure post-obit these steps:

Each code block in this example is explained in line. To copy the complete example, see Consummate Example at the stop of this tutorial.

Prerequisites

To consummate this tutorial, you demand SQL Server Management Studio, admission to a server that'southward running SQL Server, and an AdventureWorks database.

  • Install SQL Server Management Studio.
  • Install SQL Server 2017 Developer Edition.
  • Download AdventureWorks2017 sample databases.

For instructions on restoring a database in SQL Server Direction Studio, see Restore a database.

1. Configure the Surround

To gear up the initial context of the example, in SQL Server Management Studio open a new Query and run the post-obit code to open the Adventureworks2017 database. This code changes the database context to AdventureWorks2017 and creates a new server login and database user business relationship (TestCreditRatingUser), using a countersign.

              Use AdventureWorks2017;   Go   -- Set up up a login for the test user   CREATE LOGIN TestCreditRatingUser      WITH Countersign = 'ASDECd2439587y'   Become   CREATE USER TestCreditRatingUser   FOR LOGIN TestCreditRatingUser;   GO

For more information on the CREATE USER statement, see CREATE USER (Transact-SQL). For more information on the CREATE LOGIN statement, see CREATE LOGIN (Transact-SQL).

2. Create a Certificate

You tin create certificates in the server using the principal database as the context, using a user database, or both. In that location are multiple options for securing the certificate. For more than information on certificates, run into CREATE Certificate (Transact-SQL).

Run this code to create a database certificate and secure it using a countersign.

              CREATE Document TestCreditRatingCer      ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'         WITH Bailiwick = 'Credit Rating Records Access',          EXPIRY_DATE = '12/31/2022';  -- Error 3701 will occur if this date is non in the future Go

3. Create and Sign a Stored Procedure Using the Document

Use the following code to create a stored procedure that selects data from the Vendor table in the Purchasing database schema, restricting access to but the companies with a credit rating of one. Note that the beginning section of the stored process displays the context of the user account running the stored process, which is to demonstrate the concepts only. It is non required to satisfy the requirements.

              CREATE Process TestCreditRatingSP   Equally   Brainstorm      -- Show who is running the stored procedure      SELECT SYSTEM_USER 'system Login'      , USER AS 'Database Login'      , Proper noun AS 'Context'      , TYPE      , USAGE       FROM sys.user_token            -- Now get the data      SELECT AccountNumber, Name, CreditRating       FROM Purchasing.Vendor      WHERE CreditRating = 1   END   Get

Run this lawmaking to sign the stored process with the database certificate, using a countersign.

              ADD SIGNATURE TO TestCreditRatingSP       Past CERTIFICATE TestCreditRatingCer       WITH Password = 'pGFD4bb925DGvbd2439587y';   GO

For more information on stored procedures, meet Stored Procedures (Database Engine).

For more than information on signing stored procedures, see Add SIGNATURE (Transact-SQL).

4. Create a Certificate Account Using the Certificate

Run this lawmaking to create a database user (TestCreditRatingcertificateAccount) from the certificate. This account has no server login, and will ultimately control access to the underlying tables.

              USE AdventureWorks2017;   Get   CREATE USER TestCreditRatingcertificateAccount      FROM Certificate TestCreditRatingCer;   Go

5. Grant the Certificate Business relationship Database Rights

Run this code to grant TestCreditRatingcertificateAccount rights to the base table and the stored procedure.

              GRANT SELECT       ON Purchasing.Vendor       TO TestCreditRatingcertificateAccount;   GO      GRANT EXECUTE       ON TestCreditRatingSP       TO TestCreditRatingcertificateAccount;   Become

For more data on granting permissions to objects, meet GRANT (Transact-SQL).

half dozen. Display the Admission Context

To display the rights associated with the stored process access, run the post-obit code to grant the rights to run the stored procedure to the TestCreditRatingUser user.

              GRANT EXECUTE       ON TestCreditRatingSP       TO TestCreditRatingUser;   GO

Next, run the following lawmaking to run the stored procedure equally the dbo login you lot used on the server. Notice the output of the user context information. It will show the dbo account as the context with its own rights and non through a group membership.

              EXECUTE TestCreditRatingSP;   GO

Run the following code to use the EXECUTE AS statement to become the TestCreditRatingUser account and run the stored procedure. This time you will see the user context is set to the USER MAPPED TO Certificate context. Note that this option is not supported in a contained database or Azure SQL Database or Azure Synapse Analytics.

              EXECUTE As LOGIN = 'TestCreditRatingUser';   Go   EXECUTE TestCreditRatingSP;   Go

This shows you the auditing bachelor because you lot signed the stored procedure.

Notation

Employ EXECUTE Every bit to switch contexts within a database.

7. Reset the Environment

The following code uses the REVERT statement to return the context of the current business relationship to dbo, and resets the environment.

              REVERT;   GO   DROP PROCEDURE TestCreditRatingSP;   Go   Drib USER TestCreditRatingcertificateAccount;   GO   DROP USER TestCreditRatingUser;   GO   Driblet LOGIN TestCreditRatingUser;   Get   Drib Document TestCreditRatingCer;   Go

For more information about the REVERT argument, see REVERT (Transact-SQL).

Complete Instance

This section displays the complete example code.

              /* Step 1 - Open the AdventureWorks2017 database */   USE AdventureWorks2017;   Get   -- Ready upwards a login for the test user   CREATE LOGIN TestCreditRatingUser      WITH PASSWORD = 'ASDECd2439587y'   Become   CREATE USER TestCreditRatingUser   FOR LOGIN TestCreditRatingUser;   GO      /* Step 2 - Create a certificate in the AdventureWorks2017 database */   CREATE Document TestCreditRatingCer      ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'         WITH Subject field = 'Credit Rating Records Access',          EXPIRY_DATE = '12/31/2021';   -- Mistake 3701 will occur if this date is not in the future Become      /* Pace three - Create a stored procedure and   sign it using the certificate */   CREATE Procedure TestCreditRatingSP   As   Begin      -- Shows who is running the stored process      SELECT SYSTEM_USER 'system Login'      , USER As 'Database Login'      , Proper noun Every bit 'Context'      , TYPE      , USAGE       FROM sys.user_token;            -- Now get the data      SELECT AccountNumber, Name, CreditRating       FROM Purchasing.Vendor      WHERE CreditRating = i;   Finish   GO      Add together SIGNATURE TO TestCreditRatingSP       BY Document TestCreditRatingCer       WITH Password = 'pGFD4bb925DGvbd2439587y';   Go      /* Step four - Create a database user for the certificate.    This user has the ownership chain associated with it. */   USE AdventureWorks2017;   Go   CREATE USER TestCreditRatingcertificateAccount      FROM CERTIFICATE TestCreditRatingCer;   Become      /* Stride 5 - Grant the user database rights */   GRANT SELECT       ON Purchasing.Vendor       TO TestCreditRatingcertificateAccount;   Go      GRANT EXECUTE      ON TestCreditRatingSP       TO TestCreditRatingcertificateAccount;   Become      /* Step 6 - Test, using the EXECUTE Equally statement */   GRANT EXECUTE       ON TestCreditRatingSP       TO TestCreditRatingUser;   Go      -- Run the process equally the dbo user, notice the output for the type   EXEC TestCreditRatingSP;   Become      EXECUTE As LOGIN = 'TestCreditRatingUser';   Go   EXEC TestCreditRatingSP;   GO      /* Pace seven - Make clean upward the example */   REVERT;   Get   Drop PROCEDURE TestCreditRatingSP;   Go   Drib USER TestCreditRatingcertificateAccount;   Go   Drib USER TestCreditRatingUser;   GO   DROP LOGIN TestCreditRatingUser;   Go   DROP Document TestCreditRatingCer;   Go

Encounter Also

Security Center for SQL Server Database Engine and Azure SQL Database