Aracılığıyla paylaş

Kılavuz: Saklanmış Yordamları Sertifikayla İmzalama

Şunlar için geçerlidir:SQL ServerAzure SQL VeritabanıAzure SQL Yönetilen Örneği

Bu öğreticide, SQL Server tarafından oluşturulan bir sertifika kullanılarak saklı yordamları imzalama gösterilmektedir.

Uyarı

Bu öğreticideki kodu çalıştırmak için hem Karma Mod güvenliğinin yapılandırılmış hem de veritabanının AdventureWorks2025 yüklü olması gerekir.

Saklı yordamları sertifika kullanarak imzalamak, saklı yordam üzerinde izinler istemek istediğinizde ancak kullanıcıya bu hakları açıkça vermek istemediğinizde yararlıdır. Başka yollarla, örneğin EXECUTE AS deyimini kullanarak bu görevi gerçekleştirebilmenize rağmen, bir sertifika kullanmak, saklı yordamın orijinal çağıranını izleme yoluyla belirlemenizi sağlar. Bu, özellikle güvenlik veya Veri Tanımlama Dili (DDL) işlemleri sırasında yüksek düzeyde denetim sağlar.

Sunucu düzeyinde izinlere izin vermek için ana veritabanında bir sertifika oluşturabilir veya veritabanı düzeyinde izinlere izin vermek için herhangi bir kullanıcı veritabanında bir sertifika oluşturabilirsiniz. Bu senaryoda, temel tablolara erişim hakkı olmayan bir kullanıcının AdventureWorks2025 veritabanındaki bir saklı yordamdan yararlanması gerekiyor ve siz de nesne erişim izini izlemek istiyorsunuz. Diğer sahiplik zinciri yöntemlerini kullanmak yerine, temel nesneler üzerinde hakları olmayan bir sunucu ve veritabanı kullanıcı hesabı ve tablo ve saklı yordam haklarına sahip bir veritabanı kullanıcı hesabı oluşturacaksınız. Hem saklı yordam hem de ikinci veritabanı kullanıcı hesabı bir sertifika ile güvenli hale getirilecektir. İkinci veritabanı hesabının tüm nesnelere erişimi olur ve ilk veritabanı kullanıcı hesabına saklı yordama erişim izni verir.

Bu senaryoda önce bir veritabanı sertifikası, saklı yordam ve kullanıcı oluşturacak ve ardından aşağıdaki adımları izleyerek işlemi test edeceksiniz:

Bu örnekteki her kod bloğu satır içinde açıklanmıştır. Tam örneği kopyalamak için bu öğreticinin sonundaki Tam Örnek bölümüne bakın.

Önkoşullar

Bu öğreticiyi tamamlamak için SQL Server Management Studio, SQL Server çalıştıran bir sunucuya erişim ve AdventureWorks veritabanı gerekir.

SQL Server Management Studio'da veritabanını geri yükleme yönergeleri için bkz. Veritabanını geri yükleme.

1. Ortamı Yapılandırma

Örneğin ilk bağlamını ayarlamak için SQL Server Management Studio'da yeni bir Sorgu açın ve aşağıdaki kodu çalıştırarak AdventureWorks2025 veritabanını açın. Bu kod, veritabanı bağlamını AdventureWorks2025 olarak değiştirir ve parola kullanarak yeni bir sunucu oturum açma ve veritabanı kullanıcı hesabı ()TestCreditRatingUser oluşturur.

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

CREATE USER deyimi hakkında daha fazla bilgi için bkz. CREATE USER (Transact-SQL). CREATE LOGIN deyimi hakkında daha fazla bilgi için bkz. CREATE LOGIN (Transact-SQL).

2. Sertifika Oluşturma

Ana veritabanını bağlam olarak kullanarak, kullanıcı veritabanı kullanarak veya her ikisini birden kullanarak sunucuda sertifika oluşturabilirsiniz. Sertifikanın güvenliğini sağlamak için birden çok seçenek vardır. Sertifikalar hakkında daha fazla bilgi için bkz. CREATE CERTIFICATE (Transact-SQL).

Bir veritabanı sertifikası oluşturmak ve parola kullanarak güvenliğini sağlamak için bu kodu çalıştırın.

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. Sertifika Kullanarak Saklı Yordam Oluştur ve İmzala

Veritabanı şemasındaki Vendor tablodan Purchasing verileri seçen ve erişimi yalnızca kredi derecelendirmesi 1 olan şirketlerle kısıtlayan bir saklı yordam oluşturmak için aşağıdaki kodu kullanın. Saklı yordamın ilk bölümünde saklı yordamı çalıştıran kullanıcı hesabının bağlamının görüntülendiğine dikkat edin. Bu, yalnızca kavramları göstermektir. Gereksinimleri karşılamak için gerekli değildir.

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

Saklı yordamı bir parola kullanarak veritabanı sertifikasıyla imzalamak için bu kodu çalıştırın.

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

Saklı yordamlar hakkında daha fazla bilgi için bkz . Saklı Yordamlar (Veritabanı Altyapısı).

Saklı yordamları imzalama hakkında daha fazla bilgi için bkz. ADD SIGNATURE (Transact-SQL).

4. Sertifikayı Kullanarak Sertifika Hesabı Oluşturma

Sertifikadan veritabanı kullanıcısı (TestCreditRatingcertificateAccount) oluşturmak için bu kodu çalıştırın. Bu hesabın sunucu giriş yetkisi yoktur ve nihayetinde temel tablolara erişimi kontrol eder.

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

5. Sertifika Hesabı Veritabanı Hakları Verme

Temel tabloya ve saklı yordama TestCreditRatingcertificateAccount hakları vermek için bu kodu çalıştırın.

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

Nesnelere izin verme hakkında daha fazla bilgi için bkz. GRANT (Transact-SQL).

6. Erişim Bağlamını Görüntüleme

Saklı yordam erişimiyle ilişkili hakları görüntülemek için aşağıdaki kodu çalıştırarak kullanıcıya saklı yordamı çalıştırma hakları verin TestCreditRatingUser .

GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingUser;  
GO

Ardından, saklı yordamı sunucuda kullandığınız dbo oturum açma hesabı ile çalıştırmak için aşağıdaki kodu çalıştırın. Kullanıcı bağlam bilgilerinin çıkışını gözlemleyin. Dbo hesabını, grup üyeliği aracılığıyla değil kendi haklarına sahip bağlam olarak gösterir.

EXECUTE TestCreditRatingSP;  
GO

Aşağıdaki kodu çalıştırarak saklı yordamı çalıştırmak ve EXECUTE AS deyimini kullanarak TestCreditRatingUser hesabına geçmek için. Bu kez, kullanıcı bağlamının "USER MAPPED TO CERTIFICATE" bağlamına ayarlandığını göreceksiniz. Bu seçeneğin kapsanan bir veritabanında, Azure SQL Veritabanı'nda veya Azure Synapse Analytics'te desteklenmediğini unutmayın.

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

Saklı yordamı imzaladığınız için kullanılabilir denetimi gösterir.

Uyarı

Veritabanı içindeki bağlamları değiştirmek için EXECUTE AS kullanın.

7. Ortamı Sıfırlama

Aşağıdaki kod, geçerli hesabın REVERT bağlamını dbo'ya geri döndürmek için ifadesini kullanır ve ortamı sıfırlar.

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

REVERT deyimi hakkında daha fazla bilgi için bkz. REVERT (Transact-SQL).

Tam Örnek

Bu bölümde örnek kodun tamamı görüntülenir.

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

Ayrıca Bkz.

SQL Server Veritabanı Altyapısı ve Azure SQL Veritabanı için Güvenlik Merkezi

  • Last updated on