Tutorial: Menandatangani Prosedur Tersimpan dengan Sertifikat

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Tutorial ini menggambarkan penandatanganan prosedur tersimpan menggunakan sertifikat yang dihasilkan oleh SQL Server.

Catatan

Untuk menjalankan kode dalam tutorial ini, Anda harus memiliki keamanan Mode Campuran yang dikonfigurasi dan database terinstal AdventureWorks2022 .

Menandatangani prosedur tersimpan menggunakan sertifikat berguna ketika Anda ingin memerlukan izin pada prosedur tersimpan tetapi Anda tidak ingin secara eksplisit memberikan pengguna hak tersebut. Meskipun Anda dapat menyelesaikan tugas ini dengan cara lain, seperti menggunakan pernyataan EXECUTE AS, menggunakan sertifikat memungkinkan Anda menggunakan jejak untuk menemukan pemanggil asli prosedur tersimpan. Ini memberikan tingkat audit yang tinggi, terutama selama operasi keamanan atau Data Definition Language (DDL).

Anda bisa membuat sertifikat di database master untuk mengizinkan izin tingkat server, atau Anda bisa membuat sertifikat di database pengguna apa pun untuk mengizinkan izin tingkat database. Dalam skenario ini, pengguna tanpa hak atas tabel dasar harus mengakses prosedur tersimpan AdventureWorks2022 dalam database, dan Anda ingin mengaudit jejak akses objek. Daripada menggunakan metode rantai kepemilikan lainnya, Anda akan membuat akun pengguna server dan database tanpa hak atas objek dasar, dan akun pengguna database dengan hak atas tabel dan prosedur tersimpan. Prosedur tersimpan dan akun pengguna database kedua akan diamankan dengan sertifikat. Akun database kedua akan memiliki akses ke semua objek, dan memberikan akses ke prosedur tersimpan ke akun pengguna database pertama.

Dalam skenario ini Anda akan terlebih dahulu membuat sertifikat database, prosedur tersimpan, dan pengguna, lalu Anda akan menguji proses dengan mengikuti langkah-langkah berikut:

Setiap blok kode dalam contoh ini dijelaskan secara sejalan. Untuk menyalin contoh lengkap, lihat Contoh Lengkap di akhir tutorial ini.

Prasyarat

Untuk menyelesaikan tutorial ini, Anda memerlukan SQL Server Management Studio, akses ke server yang menjalankan SQL Server, dan database AdventureWorks.

Untuk petunjuk tentang memulihkan database di SQL Server Management Studio, lihat Memulihkan database.

1. Mengonfigurasi Lingkungan

Untuk mengatur konteks awal contoh, di SQL Server Management Studio buka Kueri baru dan jalankan kode berikut untuk membuka AdventureWorks2022 database. Kode ini mengubah konteks database menjadi AdventureWorks2022 dan membuat login server baru dan akun pengguna database (TestCreditRatingUser), menggunakan kata sandi.

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

Untuk informasi selengkapnya tentang pernyataan CREATE USER, lihat CREATE USER (Transact-SQL). Untuk informasi selengkapnya tentang pernyataan CREATE LOGIN, lihat MEMBUAT LOGIN (Transact-SQL).

2. Buat Sertifikat

Anda dapat membuat sertifikat di server menggunakan database master sebagai konteks, menggunakan database pengguna, atau keduanya. Ada beberapa opsi untuk mengamankan sertifikat. Untuk informasi selengkapnya tentang sertifikat, lihat MEMBUAT SERTIFIKAT (Transact-SQL).

Jalankan kode ini untuk membuat sertifikat database dan mengamankannya menggunakan kata sandi.

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. Membuat dan Menandatangani Prosedur Tersimpan Menggunakan Sertifikat

Gunakan kode berikut untuk membuat prosedur tersimpan yang memilih data dari Vendor tabel dalam Purchasing skema database, membatasi akses hanya ke perusahaan dengan peringkat kredit 1. Perhatikan bahwa bagian pertama dari prosedur tersimpan menampilkan konteks akun pengguna yang menjalankan prosedur tersimpan, yaitu hanya menunjukkan konsep. Tidak diperlukan untuk memenuhi persyaratan.

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

Jalankan kode ini untuk menandatangani prosedur tersimpan dengan sertifikat database, menggunakan kata sandi.

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

Untuk informasi selengkapnya tentang prosedur tersimpan, lihat Prosedur Tersimpan (Mesin Database).

Untuk informasi selengkapnya tentang penandatanganan prosedur tersimpan, lihat MENAMBAHKAN TANDA TANGAN (Transact-SQL).

4. Buat Akun Sertifikat Menggunakan Sertifikat

Jalankan kode ini untuk membuat pengguna database (TestCreditRatingcertificateAccount) dari sertifikat. Akun ini tidak memiliki login server, dan pada akhirnya akan mengontrol akses ke tabel yang mendasar.

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

5. Berikan Hak Database Akun Sertifikat

Jalankan kode ini untuk memberikan TestCreditRatingcertificateAccount hak ke tabel dasar dan prosedur tersimpan.

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

Untuk informasi selengkapnya tentang memberikan izin ke objek, lihat GRANT (Transact-SQL).

6. Tampilkan Konteks Akses

Untuk menampilkan hak yang terkait dengan akses prosedur tersimpan, jalankan kode berikut untuk memberikan hak untuk menjalankan prosedur tersimpan TestCreditRatingUser kepada pengguna.

GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingUser;  
GO

Selanjutnya, jalankan kode berikut untuk menjalankan prosedur tersimpan sebagai login dbo yang Anda gunakan di server. Amati output informasi konteks pengguna. Ini akan menunjukkan akun dbo sebagai konteks dengan haknya sendiri dan bukan melalui keanggotaan grup.

EXECUTE TestCreditRatingSP;  
GO

Jalankan kode berikut untuk menggunakan EXECUTE AS pernyataan untuk menjadi TestCreditRatingUser akun dan jalankan prosedur tersimpan. Kali ini Anda akan melihat konteks pengguna diatur ke konteks USER MAPPED TO CERTIFICATE. Perhatikan bahwa opsi ini tidak didukung dalam database mandiri atau Azure SQL Database atau Azure Synapse Analytics.

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

Ini menunjukkan kepada Anda audit yang tersedia karena Anda menandatangani prosedur tersimpan.

Catatan

Gunakan EXECUTE AS untuk mengalihkan konteks dalam database.

7. Reset Lingkungan

Kode berikut menggunakan REVERT pernyataan untuk mengembalikan konteks akun saat ini ke dbo, dan mengatur ulang lingkungan.

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

Untuk informasi selengkapnya tentang pernyataan REVERT, lihat REVERT (Transact-SQL).

Contoh Lengkap

Bagian ini menampilkan kode contoh lengkap.

/* 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

Lihat Juga

Security Center untuk Mesin Database SQL Server dan Azure SQL Database