Şifrelenmiş Sütunlarda Verileri Çoğaltma (SQL Server Management Studio)

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

Çoğaltma, şifrelenmiş sütun verilerini yayımlamanızı sağlar. Abonede bu verilerin şifresini çözmek ve kullanmak için Yayımcı'daki verileri şifrelemek için kullanılan anahtarın abonede de bulunması gerekir. Çoğaltma, şifreleme anahtarlarını taşımak için güvenli bir mekanizma sağlamaz. Şifreleme anahtarını Abone'de el ile yeniden oluşturmanız gerekir. Bu konu, Publisher'da bir sütunu şifrelemeyi ve şifreleme anahtarının Abone'de kullanılabilir olduğundan emin olunmasını gösterir.

Temel adımlar şunlardır:

Publisher'da simetrik anahtarı oluşturun.
Sütun verilerini simetrik anahtarla şifreleyin.
Tabloyu şifrelenmiş sütunla yayımlayın.
Yayına abone olun.
Aboneliği başlatın.
Abone üzerinde simetrik anahtarı, 1. adımda olduğu gibi ALGORITMA, KEY_SOURCE ve IDENTITY_VALUE için aynı değerleri kullanarak yeniden oluşturun.
Şifrelenmiş sütun verilerine erişin.

Not

Sütun verilerini şifrelemek için simetrik anahtar kullanmanız gerekir. Simetrik anahtarın kendisi Yayımcı ve Abone'de farklı yollarla güvenli hale getirilebilir.

Şifrelenmiş sütun verileri oluşturmak ve çoğaltmak için

Publisher'da SIMETRIK ANAHTAR OLUŞTURyürütün.

Önemli

KEY_SOURCE değeri, simetrik anahtarı yeniden oluşturmak ve verilerin şifresini çözmek için kullanılabilecek değerli verilerdir. KEY_SOURCE her zaman güvenli bir şekilde depolanması ve taşınması gerekir.
Yeni anahtarı açmak için OPEN SYMMETRIC KEY öğesini yürütün.
Publisher'da sütun verilerini şifrelemek için EncryptByKey işlevini kullanın.
Anahtarı kapatmak için SİMETRİK ANAHTARI KAPAT komutunu yürütün.
Şifrelenmiş sütunu içeren tabloyu yayımlayın. Daha fazla bilgi için Bir Yayın Oluşturmakısmına bakın.
Yayına abone olun. Daha fazla bilgi için bkz. Çekme Aboneliği Oluşturma veya Anında İletme Aboneliği Oluşturma.
Aboneliği başlatın. Daha fazla bilgi için bkz. İlk Anlık Görüntünün Oluşturulması ve Uygulanması.
Abonede, 1. adımda olduğu gibi ALGORITMA, KEY_SOURCE ve IDENTITY_VALUE için aynı değerleri kullanarak SİMETRİK ANAHTAR OLUŞTUR oluşturun. ENCRYPTION BY için farklı bir değer belirtebilirsiniz.

Önemli

KEY_SOURCE değeri, simetrik anahtarı yeniden oluşturmak ve verilerin şifresini çözmek için kullanılabilecek değerli verilerdir. KEY_SOURCE her zaman güvenli bir şekilde depolanması ve taşınması gerekir.
OPEN SYMMETRIC KEY komutunu, yeni anahtarı açmak için yürütün.
Abonede çoğaltılan verilerin şifresini çözmek için DecryptByKey işlevini kullanın.
Anahtarı kapatmak için SIMETRIK ANAHTARI KAPAT çalıştır.

Örnekler

A. Yayın veritabanında anahtar oluşturma

Bu örnek bir simetrik anahtar, simetrik anahtarın güvenliğini sağlamaya yardımcı olmak için kullanılan bir sertifika ve bir ana anahtar oluşturur. Bu anahtarlar yayın veritabanında oluşturulur. Daha sonra SalesOrderHeader tablosunda şifrelenmiş bir sütun (EncryptedCreditCardApprovalCode) oluşturmak için kullanılır. Bu sütun, şifrelenmemiş CreditCardApprovalCode sütunu yerine AdvWorksSalesOrdersMerge yayınında yayımlanır. Mümkün olduğunda, kullanıcılardan çalışma zamanında güvenlik kimlik bilgilerini girmelerini iste. Kimlik bilgilerini bir betik dosyasında depolamanız gerekiyorsa, yetkisiz erişimi önlemek için dosyanın güvenliğini sağlamalısınız.


-- Execute at the Publisher on the publication database.
USE AdventureWorks2022;
GO

-- Create the database master key if it doesn't exist.
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys 
    WHERE [name] LIKE '%DatabaseMasterKey%')
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create the cert_keyProtection certificate if it doesn't exist.
IF NOT EXISTS (SELECT * FROM sys.certificates 
    WHERE [name] = 'cert_keyPublisher')
CREATE CERTIFICATE [cert_keyPublisher] 
    WITH SUBJECT = 'Publisher Key Protection';

-- Create the key_ReplDataShare symmetric key if it doesn't exist.
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys 
    WHERE [name] = 'key_ReplDataShare')
CREATE SYMMETRIC KEY [key_ReplDataShare] WITH
    KEY_SOURCE = 'My key generation bits. This is a shared secret!',
    ALGORITHM = AES_256, 
    IDENTITY_VALUE = 'Key Identity generation bits. Also a shared secret'
    ENCRYPTION BY CERTIFICATE [cert_keyPublisher];
GO 

-- Open the encryption key.
OPEN SYMMETRIC KEY [key_ReplDataShare]
    DECRYPTION BY CERTIFICATE [cert_keyPublisher];
GO

-- Create a new CreditCardApprovalCode column in the SalesOrderHeader table.
ALTER TABLE Sales.SalesOrderHeader 
    ADD EncryptedCreditCardApprovalCode VARBINARY(256) NULL;
GO

-- Insert encrypted data from the CreditCardApprovalCode column.
UPDATE Sales.SalesOrderHeader
SET EncryptedCreditCardApprovalCode
    = EncryptByKey(Key_GUID('key_ReplDataShare'), CreditCardApprovalCode);
GO

CLOSE SYMMETRIC KEY [key_ReplDataShare];
GO

DECLARE @publication AS sysname;
DECLARE @table1 AS sysname;
DECLARE @table2 AS sysname;
DECLARE @table3 AS sysname;
DECLARE @salesschema AS sysname;
DECLARE @hrschema AS sysname;
DECLARE @filterclause AS nvarchar(1000);
SET @publication = N'AdvWorksSalesOrdersMerge'; 
SET @table1 = N'Employee'; 
SET @table2 = N'SalesOrderHeader'; 
SET @table3 = N'SalesOrderDetail'; 
SET @salesschema = N'Sales';
SET @hrschema = N'HumanResources';
SET @filterclause = N'Employee.LoginID = HOST_NAME()';

-- Add a filtered article for the Employee table.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table1, 
  @source_object = @table1, 
  @type = N'table', 
  @source_owner = @hrschema,
  @schema_option = 0x0004CF1,
  @description = N'article for the Employee table',
  @subset_filterclause = @filterclause;

-- Add an article for the SalesOrderHeader table that is filtered
-- based on Employee and horizontally filtered.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table2, 
  @source_object = @table2, 
  @type = N'table', 
  @source_owner = @salesschema, 
  @vertical_partition = N'true',
  @schema_option = 0x0034EF1,
  @description = N'article for the SalesOrderDetail table';

-- Add an article for the SalesOrderDetail table that is filtered
-- based on SaledOrderHeader.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table3, 
  @source_object = @table3, 
  @source_owner = @salesschema,
  @description = 'article for the SalesOrderHeader table', 
  @identityrangemanagementoption = N'auto', 
  @pub_identity_range = 100000, 
  @identity_range = 100, 
  @threshold = 80,
  @schema_option = 0x0004EF1;

-- Add all columns to the SalesOrderHeader article.
EXEC sp_mergearticlecolumn 
  @publication = @publication, 
  @article = @table2, 
  @force_invalidate_snapshot = 1, 
  @force_reinit_subscription = 1;

-- Remove the credit card Approval Code column.
EXEC sp_mergearticlecolumn 
  @publication = @publication, 
  @article = @table2, 
  @column = N'CreditCardApprovalCode', 
  @operation = N'drop', 
  @force_invalidate_snapshot = 1, 
  @force_reinit_subscription = 1;

-- Add a merge join filter between Employee and SalesOrderHeader.
EXEC sp_addmergefilter 
  @publication = @publication, 
  @article = @table2, 
  @filtername = N'SalesOrderHeader_Employee', 
  @join_articlename = @table1, 
  @join_filterclause = N'Employee.BusinessEntityID = SalesOrderHeader.SalesPersonID', 
  @join_unique_key = 1, 
  @filter_type = 1, 
  @force_invalidate_snapshot = 1, 
  @force_reinit_subscription = 1;

-- Add a merge join filter between SalesOrderHeader and SalesOrderDetail.
EXEC sp_addmergefilter 
  @publication = @publication, 
  @article = @table3, 
  @filtername = N'SalesOrderDetail_SalesOrderHeader', 
  @join_articlename = @table2, 
  @join_filterclause = N'SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID', 
  @join_unique_key = 1, 
  @filter_type = 1, 
  @force_invalidate_snapshot = 1, 
  @force_reinit_subscription = 1;
GO

B. Abonelik veritabanında anahtar oluşturma

Bu örnek, ilk örnekteki ALGORITMA, KEY_SOURCE ve IDENTITY_VALUE için aynı değerleri kullanarak abonelik veritabanında aynı simetrik anahtarı yeniden oluşturur. Bu örnekte, şifrelenmiş sütunu çoğaltmak için AdvWorksSalesOrdersMerge yayınına zaten abone olduğunuz varsayılmaktadır. Mümkün olduğunda, kullanıcılardan çalışma zamanında güvenlik kimlik bilgilerini girmelerini iste. Kimlik bilgilerini bir betik dosyasında depolamanız gerekiyorsa, yetkisiz erişimi önlemek için depolama ve aktarım sırasında dosyanın güvenliğini sağlamalısınız.


-- Execute at the Subscription on the subscription database.
USE AdventureWorks2022Replica;
GO

-- Create the database master key if it doesn't exist.
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys 
    WHERE [name] LIKE '%DatabaseMasterKey%')
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create the cert_keySubscriber certificate if it doesn't exist.
-- This can be a different certificate than at the Publisher.
IF NOT EXISTS (SELECT * FROM sys.certificates 
    WHERE [name] = 'cert_keySubscriber')
CREATE CERTIFICATE [cert_keySubscriber] 
    WITH SUBJECT = 'Subscriber Key Protection';

-- Create the key_ReplDataShare symmetric key if it doesn't exist.
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys 
    WHERE [name] = 'key_ReplDataShare')
CREATE SYMMETRIC KEY [key_ReplDataShare] WITH
    KEY_SOURCE = 'My key generation bits. This is a shared secret!',
    ALGORITHM = AES_256, 
    IDENTITY_VALUE = 'Key Identity generation bits. Also a shared secret'
    ENCRYPTION BY CERTIFICATE [cert_keySubscriber];
GO 

-- Open the encryption key.
OPEN SYMMETRIC KEY [key_ReplDataShare]
    DECRYPTION BY CERTIFICATE [cert_keySubscriber];
GO

-- Return the column that was encrypted at the Publisher and also decrypt it.
SELECT SalesOrderID AS 'Order Number', EncryptedCreditCardApprovalCode AS 'Encrypted Approval Code', 
    CONVERT(VARCHAR(15), DecryptByKey(EncryptedCreditCardApprovalCode)) AS 'Decrypted Approval Code'
FROM Sales.SalesOrderHeader;
GO

CLOSE SYMMETRIC KEY [key_ReplDataShare];
GO

Geri Bildirim

Bu sayfayı yararlı buldunuz mu?

Last updated on 2025-03-23