Transact-SQL ile sütun şifrelemeyi yerinde yapılandırma

Şunlar için geçerlidir: Windows Azure SQL Veritabanı'nda SQL Server 2019 (15.x) ve sonraki sürümleri

Bu makalede, güvenli kuşatmalarla Always Encrypted ve ALTER TABLE Deyimi/ALTER COLUMN deyimini kullanarak sütunlarda yerinde şifreleme işlemlerinin nasıl gerçekleştirildiği açıklanmaktadır. Yerinde şifreleme ve genel önkoşullar hakkında temel bilgiler için bkz. Güvenli kuşatmalarla Always Encrypted kullanarak yerinde sütun şifrelemeyi yapılandırma.

ALTER TABLE veya ALTER COLUMN deyimiyle bir sütun için hedef şifreleme yapılandırmasını ayarlayabilirsiniz. İfade yürütüldüğünde, sunucu tarafındaki güvenli bölüm, ifadede belirtilen sütun tanımındaki geçerli ve hedef şifreleme yapılandırmasına göre, sütunda depolanan verileri şifreler, yeniden şifreler veya şifresini çözer.

• Sütun şu anda şifrelenmemişse, sütun tanımında yan tümcesini ENCRYPTED WITH belirtirseniz şifrelenir.
• Sütun halihazırda şifreli ise ve sütun tanımında ENCRYPTED WITH yan tümcesi belirtilmezse, sütun şifresi çözülür (düz metin sütununa dönüştürülür).
• Sütun şu anda şifrelenmişse, yan tümcesini ENCRYPTED WITH belirtirseniz yeniden şifrelenir ve belirtilen sütun şifreleme türü veya sütun şifreleme anahtarı şu anda kullanılan şifreleme türünden veya sütun şifreleme anahtarından farklıdır.

Not

Şifreleme işlemlerini, sütunu NULL veya NOT NULL olarak değiştirme veya harmanlamayı değiştirme dışında, tek bir ALTER TABLE/ALTER COLUMN deyimdeki diğer değişikliklerle birleştiremezsiniz. Örneğin, bir sütunu şifreleyemez ve tek ALTER TABLE/ALTER COLUMN bir Transact-SQL deyiminde sütunun veri türünü değiştiremezsiniz. İki ayrı deyim kullanın.

Sunucu tarafı güvenli kapanım kullanan tüm sorgularda olduğu gibi, Always Encrypted ve kapanım hesaplamaları etkin olan bir bağlantı üzerinden yerinde şifrelemeyi tetikleyen bir deyim göndermeniz ALTER TABLE/ALTER COLUMN gerekir.

Bu makalenin geri kalanında SQL Server Management Studio'daki ALTER TABLE/ALTER COLUMN deyimi kullanılarak yerinde şifrelemenin nasıl tetikleneceği açıklanır. Alternatif olarak, Visual Studio Code'dan veya uygulamanızdan ALTER TABLE/ALTER COLUMN de çıkarabilirsiniz.

Not

Şu anda SqlServer PowerShell modülündeki Invoke-Sqlcmd cmdlet'i ve sqlcmd, yerinde şifreleme işlemlerini ALTER TABLE/ALTER COLUMN desteklemez.

SSMS'de Transact-SQL ile yerinde şifreleme gerçekleştirme

Önkoşullar

• Güvenli kuşatmalarla Always Encrypted kullanarak sütun şifrelemeyi yerinde yapılandırma bölümünde açıklanan önkoşullar.
• SQL Server Management Studio'nun (SSMS) en son sürümünü yükleyin.

Adımlar

1. Veritabanı bağlantısında Always Encrypted ve enclave hesaplamaları etkinleştirilmiş olarak bir sorgu penceresi açın. Ayrıntılar için bkz. Veritabanı bağlantısı için Always Encrypted'ı etkinleştirme ve devre dışı bırakma.

2. Sorgu penceresinde deyimini ALTER TABLE/ALTER COLUMN çalıştırın. Şifrelemek, şifresini çözmek veya yeniden şifrelemek istediğiniz bir sütun için hedef şifreleme yapılandırmasını belirtin. Sütunu şifreliyor veya yeniden şifreliyorsanız, ENCRYPTED WITH yan tümcesini kullanın. Sütununuz bir dize sütunuysa (örneğin, char, varchar, nchar), nvarcharharmanlamayı bin2 harmanlaması olarak da değiştirmeniz gerekebilir.

   Not

   Sütun ana anahtarınız Azure Key Vault'ta depolanıyorsa Azure'da oturum açmanız istenebilir.

3. Tabloya erişen tüm toplu işlemler ve saklı yordamlar için plan önbelleğini temizleyerek parametre şifreleme bilgilerini yenileyin.

   ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
   

   Not

   Etkilenen sorgunun planını önbellekten kaldırmazsanız, şifrelemeden sonra sorgunun ilk yürütmesi başarısız olabilir.

   Plan önbelleğini dikkatlice temizlemek için ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE veya DBCC FREEPROCCACHE kullanın, zira bu, geçici olarak sorgu performansında düşüşe neden olabilir. Önbelleği temizlemenin olumsuz etkisini en aza indirmek için, yalnızca etkilenen sorgular için planları seçmeli olarak kaldırabilirsiniz.

4. sys.parameters içinde kalıcı olan ve sütunları şifreleyerek geçersiz kılınabilen her modülün parametrelerine (saklı yordam, işlev, görünüm, tetikleyici) yönelik meta verileri güncelleştirmek için sp_refresh_parameter_encryption'yi çağırın.

Örnekler

Bir sütunu yerinde şifreleyin

Aşağıdaki örnekte şunlar varsayılır:

• CEK1, kapanım özellikli bir sütun şifreleme anahtarıdır.
• SSN sütunu düz metindir ve şu anda Latin1, BIN2 olmayan harmanlama gibi varsayılan veritabanı harmanlamasını kullanır (örneğin, Latin1_General_CI_AI_KS_WS).

Bu ifade, SSN sütununu belirleyici şifreleme ve enclave destekli sütun şifreleme anahtarını yerinde kullanarak şifreler. Ayrıca, karşılık gelen (aynı kod sayfasında) BIN2 harmanlaması ile varsayılan veritabanı harmanlamasının üzerine yazar.

İşlem çevrimiçi gerçekleştirilir (ONLINE = ON). Tablo şeması değişikliğinden etkilenen sorguların planlarını yeniden oluşturan ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE çağrısına dikkat edin.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] CHAR (11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (
    COLUMN_ENCRYPTION_KEY = [CEK1],
    ENCRYPTION_TYPE = DETERMINISTIC,
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
) NOT NULL WITH (ONLINE = ON);
GO

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Şifreleme türünü değiştirmek için sütunu yerinde yeniden şifreleme

Aşağıdaki örnekte şunlar varsayılır:

• SSN sütunu, CEK1belirleyici şifreleme ve kapanım özellikli bir sütun şifreleme anahtarı kullanılarak şifrelenir.
• Geçerli sıralama, sütun düzeyinde ayarlanmış olarak Latin1_General_BIN2.

Aşağıdaki deyim, rastgele şifreleme ve aynı anahtar (CEK1) kullanarak sütunu yeniden şifreler.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] CHAR (11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (
    COLUMN_ENCRYPTION_KEY = [CEK1],
    ENCRYPTION_TYPE = RANDOMIZED,
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
) NOT NULL;
GO

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Sütun şifreleme anahtarını döndürmek amacıyla sütunu yerinde yeniden şifreleyin.

Aşağıdaki örnekte şunlar varsayılır:

• SSN sütunu rastgele şifreleme ve CEK1kapanım özellikli bir sütun şifreleme anahtarı kullanılarak şifrelenir.
• CEK2, güvenlik bölgesi etkin bir sütun şifreleme anahtarıdır (CEK1'den farklıdır).
• Geçerli sıralama, sütun düzeyinde ayarlanmış olarak Latin1_General_BIN2.

Aşağıdaki ifade CEK2 ile sütunu yeniden şifreler.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] CHAR (11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (
    COLUMN_ENCRYPTION_KEY = [CEK2],
    ENCRYPTION_TYPE = RANDOMIZED,
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
) NOT NULL;
GO

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Bir sütunun şifresini yerinde çözme

Aşağıdaki örnekte şunlar varsayılır:

• SSN sütunu, kapanım özellikli bir sütun şifreleme anahtarı kullanılarak şifrelenir.
• Geçerli sıralama, sütun düzeyinde ayarlanmış olarak Latin1_General_BIN2.

Aşağıdaki deyim sütunun şifresini çözer ve sıralama düzenini değişmeden tutar. Alternatif olarak harmanlamayı değiştirmeyi de seçebilirsiniz. Örneğin, harmanlamayı aynı deyimde BIN2 olmayan bir harmanlama olarak değiştirin.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] CHAR (11) COLLATE Latin1_General_BIN2
WITH (ONLINE = ON);
GO

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

İlgili içerik

• Güvenli kuşatmalarla Always Encrypted ile ilgili yaygın sorunları giderme
• Güvenli kuşatmalarla Always Encrypted kullanarak sütun şifrelemeyi yerinde yapılandırma
• Mevcut şifrelenmiş sütunlar için güvenli kuşatmalarla Always Encrypted'ı etkinleştirme
• Güvenli kuşatmalarla Always Encrypted kullanmaya başlama
• Güvenli bölgeler kullanarak Transact-SQL ifadeleri çalıştırma
• Güvenli kuşatmalarla Always Encrypted kullanarak sütunlarda dizin oluşturma ve kullanma
• Güvenli kuşatmalarla Always Encrypted kullanarak uygulama geliştirme