Tutorial: Menandatangani Prosedur Tersimpan dengan Sertifikat
Berlaku untuk: SQL ServerAzure SQL Database Azure 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.
- Instal SQL Server Management Studio.
- Instal Edisi Pengembang SQL Server 2017.
- Unduh database sampel 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