Oktatóanyag: Tárolt eljárások aláírása tanúsítvánnyal

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Ez az oktatóanyag a tárolt eljárások SQL Server által létrehozott tanúsítvány használatával történő aláírását mutatja be.

Megjegyzés:

A kód ebben az oktatóanyagban való futtatásához konfigurálva kell lennie a vegyes módú biztonságnak és az AdventureWorks2025 adatbázisnak is.

A tárolt eljárások tanúsítvány használatával történő aláírása akkor hasznos, ha engedélyeket szeretne kérni a tárolt eljáráshoz, de nem szeretné explicit módon megadni a felhasználónak ezeket a jogosultságokat. Bár ezt a feladatot más módokon is elvégezheti, például az EXECUTE AS utasítás használatával, a tanúsítvány használatával nyomkövetéssel megkeresheti a tárolt eljárás eredeti hívóját. Ez magas szintű naplózást biztosít, különösen a biztonsági vagy adatdefiníciós (DDL) műveletek során.

Létrehozhat egy tanúsítványt a főadatbázisban a kiszolgálószintű engedélyek engedélyezéséhez, vagy létrehozhat egy tanúsítványt bármely felhasználói adatbázisban az adatbázisszintű engedélyek engedélyezéséhez. Ebben a forgatókönyvben egy olyan felhasználónak, aki nem rendelkezik alaptáblákhoz való jogosultsággal, hozzá kell férnie egy tárolt eljáráshoz az AdventureWorks2025 adatbázisban, és naplózni szeretné az objektumelérési útvonalat. Ahelyett, hogy más tulajdonosi lánc metódusokat használ, létrehoz egy kiszolgáló- és adatbázis-felhasználói fiókot, amely nem rendelkezik az alapobjektumokhoz való jogokkal, valamint egy adatbázis-felhasználói fiókot, amely jogosultsággal rendelkezik egy táblához és egy tárolt eljáráshoz. A tárolt eljárás és a második adatbázis felhasználói fiókja is tanúsítványsal lesz védve. A második adatbázisfiók hozzáféréssel rendelkezik az összes objektumhoz, és hozzáférést biztosít a tárolt eljáráshoz az első adatbázis felhasználói fiókjához.

Ebben a forgatókönyvben először létrehoz egy adatbázistanúsítványt, egy tárolt eljárást és egy felhasználót, majd az alábbi lépéseket követve teszteli a folyamatot:

Ebben a példában minden kódblokkot sorba foglalunk. A teljes példa másolásához tekintse meg az oktatóanyag végén található Teljes példa című témakört.

Előfeltételek

Az oktatóanyag elvégzéséhez az SQL Server Management Studióra, az SQL Servert futtató kiszolgálóhoz és egy AdventureWorks-adatbázishoz kell hozzáférnie.

• Telepítse SQL Server Management Studio.
• Telepítse SQL Server 2017 Developer Edition.
• Töltse le AdventureWorks-mintaadatbázisokat.

Az adatbázis SQL Server Management Studióban való visszaállításával kapcsolatos utasításokért tekintse meg az adatbázis visszaállítása című témakört.

1. A környezet konfigurálása

A példa kezdeti környezetének beállításához az SQL Server Management Studióban nyisson meg egy új lekérdezést, és futtassa az alábbi kódot az AdventureWorks2025 adatbázis megnyitásához. Ez a kód jelszóval módosítja az adatbázis-környezetet AdventureWorks2025 , és létrehoz egy új kiszolgálói bejelentkezési és adatbázis-felhasználói fiókot (TestCreditRatingUser).

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

A CREATE USER utasítással kapcsolatos további információkért lásd: CREATE USER (Transact-SQL). A CREATE LOGIN utasítással kapcsolatos további információkért lásd: CREATE LOGIN (Transact-SQL).

2. Tanúsítvány létrehozása

Létrehozhat tanúsítványokat a kiszolgálón a főadatbázis környezetként való használatával, felhasználói adatbázis használatával vagy mindkettővel. A tanúsítvány biztonságossá tételének több lehetősége is van. További információ a tanúsítványokról: CREATE CERTIFICATE (Transact-SQL).

Futtassa ezt a kódot egy adatbázistanúsítvány létrehozásához és jelszóval történő biztonságossá tételéhez.

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

3. Tárolt eljárás létrehozása és aláírása a tanúsítvány használatával

Az alábbi kóddal olyan tárolt eljárást hozhat létre, amely adatokat választ ki az VendorPurchasing adatbázissémában lévő táblából, és csak az 1 kredit minősítéssel rendelkező vállalatok számára korlátozza a hozzáférést. Vegye figyelembe, hogy a tárolt eljárás első szakasza a tárolt eljárást futtató felhasználói fiók környezetét jeleníti meg, amely csak a fogalmakat mutatja be. A követelményeknek nem kell megfelelnie.

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  

Futtassa ezt a kódot a tárolt eljárás adatbázis-tanúsítvánnyal való aláírásához jelszóval.

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

A tárolt eljárásokról további információt a Tárolt eljárások (Adatbázismotor) című témakörben talál.

A tárolt eljárások aláírásával kapcsolatos további információkért lásd az ADD SIGNATURE (Transact-SQL) című témakört.

4. Tanúsítványfiók létrehozása a tanúsítvány használatával

Futtassa ezt a kódot, hogy létrehozzon egy adatbázis-felhasználót (TestCreditRatingcertificateAccount) a tanúsítvány alapján. Ez a fiók nem rendelkezik kiszolgálói bejelentkezéssel, és végső soron szabályozni fogja a mögöttes táblákhoz való hozzáférést.

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

5. A tanúsítványfiók adatbázis-jogosultságainak megadása

Futtassa ezt a kódot az alaptábla és a tárolt eljárás jogosultságainak megadásához TestCreditRatingcertificateAccount .

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

További információ az objektumok engedélyeinek megadásáról: GRANT (Transact-SQL).

6. A hozzáférési környezet megjelenítése

A tárolt eljáráshoz való hozzáféréshez kapcsolódó jogosultságok megjelenítéséhez futtassa az alábbi kódot, hogy a felhasználó számára biztosítsa a tárolt eljárás futtatására TestCreditRatingUser vonatkozó jogosultságokat.

GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingUser;  
GO  

Ezután futtassa a következő kódot a tárolt eljárás futtatásához az általa használt dbo felhasználói névvel a szerveren. Figyelje meg a felhasználói környezet információinak kimenetét. A dbo-fiókot a saját jogokkal rendelkező környezetként jeleníti meg, nem csoporttagságon keresztül.

EXECUTE TestCreditRatingSP;  
GO  

Futtassa az alábbi kódot az EXECUTE AS utasítás használatával a TestCreditRatingUser fiókká váláshoz és a tárolt eljárás futtatásához. Ezúttal azt fogja látni, hogy a felhasználói környezet a USER MAPPED TO CERTIFICATE környezetre van állítva. Vegye figyelembe, hogy ez a beállítás nem támogatott a tárolt adatbázisokban, az Azure SQL Database-ben vagy az Azure Synapse Analyticsben.

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

Ez megmutatja az elérhető ellenőrzési lehetőségeket, mivel aláírta a tárolt eljárást.

Megjegyzés:

Az EXECUTE AS használatával válthat az adatbázison belüli környezetek között.

7. A környezet alaphelyzetbe állítása

Az alábbi kód az REVERT utasítás használatával adja vissza az aktuális fiók környezetét a dbo-nak, és alaphelyzetbe állítja a környezetet.

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

A REVERT utasítással kapcsolatos további információkért lásd: REVERT (Transact-SQL).

Teljes példa

Ez a szakasz a teljes példakódot jeleníti meg.

/* Step 1 - Open the AdventureWorks2022 database */  
USE AdventureWorks2022;  
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 AdventureWorks2022 database */  
CREATE CERTIFICATE TestCreditRatingCer  
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
      WITH SUBJECT = 'Credit Rating Records Access',   
      EXPIRY_DATE = '12/31/2021';   -- Error 3701 will occur if this date is not in the future
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 AdventureWorks2022;  
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  

Lásd még:

Biztonsági központ SQL Server adatbázismotorhoz és Azure SQL Database-hez