Tutorial: Signing Stored Procedures with a Certificate

1.Configure the Environment

Aby ustawić początkowe kontekście w przykładzie w SQL Server Management Studio Otwórz nową kwerendę i uruchom poniższy kod, aby otworzyć AdventureWorks bazy danych.Ten kod zmienia kontekst bazy danych do AdventureWorks i tworzy nowy serwer logowania i bazy danych konta użytkownika (TestCreditRatingUser), za pomocą hasła.

USE AdventureWorks;
GO
-- Set up a login for the test user
CREATE LOGIN TestCreditRatingUser
   WITH PASSWORD = 'ASDECd2439587y'
GO
CREATE USER TestCreditRatingUser
FOR LOGIN TestCreditRatingUser;
GO

Aby uzyskać więcej informacji na temat instrukcja CREATE USER zobacz CREATE USER (języka Transact-SQL). Aby uzyskać więcej informacji na temat instrukcja CREATE LOGIN zobacz CREATE LOGIN (Transact-SQL).

2.Create a Certificate

Możesz tworzyć certyfikaty serwerów za pomocą wzorzec bazy danych w kontekście, przy użyciu baza danych użytkownika lub oba.Istnieje kilka opcji dotyczących zabezpieczania certyfikat.Aby uzyskać więcej informacji na temat certyfikatów Zobacz CREATE certyfikat (języka Transact-SQL).

Uruchomić ten kod, aby utworzyć certyfikat bazy danych i zabezpieczyć go za pomocą hasła.

CREATE CERTIFICATE TestCreditRatingCer
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
      WITH SUBJECT = 'Credit Rating Records Access', 
      EXPIRY_DATE = '12/05/2010';
GO

3.Create and Sign a Stored Procedure Using the Certificate

Pozwala utworzyć procedura przechowywana, aby zaznaczyć dane z następującego kodu Dostawcy tabelaZakupu bazy danych schematu, ograniczanie dostępu do tylko przedsiębiorstwa o zdolności kredytowej 1.Należy zauważyć, że procedury przechowywanej w pierwszej sekcji jest wyświetlane w kontekście konta użytkownika, uruchomiona procedura przechowywana jest zademonstrowanie gotowego tylko pojęcia.Nie jest wymagane do spełnienia wymagań.

CREATE PROCEDURE TestCreditRatingSP
AS
BEGIN
   -- Show who is running the stored procedure
   SELECT SYSTEM_USER 'system Login'
   , USER AS 'Database Login'
   , NAME AS 'Context'
   , TYPE
   , USAGE 
   FROM sys.user_token   

   -- Now get the data
   SELECT AccountNumber, Name, CreditRating 
   FROM Purchasing.Vendor
   WHERE CreditRating = 1
END
GO

Uruchomić ten kod do podpisywania procedura przechowywana przy użyciu certyfikat bazy danych, za pomocą hasła.

ADD SIGNATURE TO TestCreditRatingSP 
   BY CERTIFICATE TestCreditRatingCer
    WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';
GO

Aby uzyskać więcej informacji na temat procedur przechowywanych zobacz Stored Procedures (Database Engine).

Aby uzyskać więcej informacji na temat schematów bazy danych zobacz Schemas (Database Engine).

Aby uzyskać więcej informacji na temat podpisywania procedur przechowywanych zobacz Dodawanie podpisu (języka Transact-SQL).

4.Create a Certificate Account Using the Certificate

Uruchomić ten kod, aby utworzyć (użytkownika do bazy danychTestCreditRatingcertificateAccount) z certyfikat. To konto posiada identyfikator logowania nie serwera i ostatecznie będzie sterować dostępem do tabel.

USE AdventureWorks;
GO
CREATE USER TestCreditRatingcertificateAccount
   FROM CERTIFICATE TestCreditRatingCer;
GO

5.Grant the Certificate Account Database Rights

Uruchomić ten kod, aby udzielić TestCreditRatingcertificateAccount prawa do tabela bazowa i procedura przechowywana.

GRANT SELECT 
   ON Purchasing.Vendor 
   TO TestCreditRatingcertificateAccount;
GO

GRANT EXECUTE 
   ON TestCreditRatingSP 
   TO TestCreditRatingcertificateAccount;
GO

Aby uzyskać więcej informacji na temat przyznawania uprawnień do obiektów Zobacz GRANT (Transact-SQL).

6.Display the Access Context

Aby wyświetlić prawa skojarzone z dostępem do procedura przechowywana, uruchom poniższy kod, aby udzielić praw, aby uruchomić procedura przechowywana, aby TestCreditRatingUser użytkownik.

GRANT EXECUTE 
   ON TestCreditRatingSP 
   TO TestCreditRatingUser;
GO

Następnie należy uruchomić następujący kod do uruchomienia procedura przechowywana jako dbo logowania używane na serwerze.Zwróć dane wyjściowe informacje kontekstu użytkownika.Będzie on wyświetlany dbo konto jako kontekst ze swoich własnych praw, a nie przez członkostwo w grupie.

EXECUTE TestCreditRatingSP;
GO

Uruchom poniższy kod do używania jako wykonać instrukcja staje się TestCreditRatingUser konto i wykonywania procedura przechowywana. Tym razem pojawi kontekst użytkownika jest ustawiona na kontekst USER MAPOWANEGO na certyfikat.

EXECUTE AS LOGIN = 'TestCreditRatingUser';
GO
EXECUTE TestCreditRatingSP;
GO

To pokazuje inspekcji dostępne, ponieważ podpisane procedura przechowywana.

7.Reset the Environment

W poniższym kodzie użyto instrukcja operacji PRZYWRACANIA zwrócić kontekście bieżącego konta do dboi resetuje środowiska.

REVERT;
GO
DROP PROCEDURE TestCreditRatingSP;
GO
DROP USER TestCreditRatingcertificateAccount;
GO
DROP USER TestCreditRatingUser;
GO
DROP LOGIN TestCreditRatingUser;
GO
DROP CERTIFICATE TestCreditRatingCer;
GO

Aby uzyskać więcej informacji na temat instrukcja operacji PRZYWRACANIA zobacz REVERT (języka Transact-SQL).

Przykład pełnej

W tej sekcji jest wyświetlana pełna przykładowy kod źródłowy.

/* Step 1 - Open the AdventureWorks database */
USE AdventureWorks;
GO
-- Set up a login for the test user
CREATE LOGIN TestCreditRatingUser
   WITH PASSWORD = 'ASDECd2439587y'
GO
CREATE USER TestCreditRatingUser
FOR LOGIN TestCreditRatingUser;
GO

/* Step 2 - Create a certificate in the AdventureWorks database */
CREATE CERTIFICATE TestCreditRatingCer
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
      WITH SUBJECT = 'Credit Rating Records Access', 
      EXPIRY_DATE = '12/05/2010';
GO

/* Step 3 - Create a stored procedure and
sign it using the certificate */
CREATE PROCEDURE TestCreditRatingSP
AS
BEGIN
   -- Shows who is running the stored procedure
   SELECT SYSTEM_USER 'system Login'
   , USER AS 'Database Login'
   , NAME AS 'Context'
   , TYPE
   , USAGE 
   FROM sys.user_token   

   -- Now get the data
   SELECT AccountNumber, Name, CreditRating 
   FROM Purchasing.Vendor
   WHERE CreditRating = 1
END
GO

ADD SIGNATURE TO TestCreditRatingSP 
   BY CERTIFICATE TestCreditRatingCer
    WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';
GO

/* Step 4 - Create a database user for the certificate. 
This user has the ownership chain associated with it. */
USE AdventureWorks;
GO
CREATE USER TestCreditRatingcertificateAccount
   FROM CERTIFICATE TestCreditRatingCer;
GO

/* Step 5 - Grant the user database rights */
GRANT SELECT 
   ON Purchasing.Vendor 
   TO TestCreditRatingcertificateAccount;
GO

GRANT EXECUTE
   ON TestCreditRatingSP 
   TO TestCreditRatingcertificateAccount;
GO

/* Step 6 - Test, using the EXECUTE AS statement */
GRANT EXECUTE 
   ON TestCreditRatingSP 
   TO TestCreditRatingUser;
GO

-- Run the procedure as the dbo user, notice the output for the type
EXEC TestCreditRatingSP;
GO

EXECUTE AS LOGIN = 'TestCreditRatingUser';
GO
EXEC TestCreditRatingSP;
GO

/* Step 7 - Clean up the example */
REVERT;
GO
DROP PROCEDURE TestCreditRatingSP;
GO
DROP USER TestCreditRatingcertificateAccount;
GO
DROP USER TestCreditRatingUser;
GO
DROP LOGIN TestCreditRatingUser;
GO
DROP CERTIFICATE TestCreditRatingCer;
GO

See Also

Concepts