Udostępnij za pośrednictwem

Konfigurowanie szyfrowania kolumn bezpośrednio za pomocą Transact-SQL

  • Artykuł

Dotyczy: SQL Server 2019 (15.x) i nowsze - tylko dla systemu Windows Azure SQL Database

W tym artykule opisano sposób wykonywania operacji kryptograficznych bezpośrednio na kolumnach przy użyciu funkcji Always Encrypted z bezpiecznymi enklawami przy pomocy instrukcji ALTER TABLE /ALTER COLUMN. Aby uzyskać podstawowe informacje dotyczące szyfrowania danych lokalnie i ogólnych wymagań wstępnych, zobacz Jak skonfigurować szyfrowanie kolumn lokalnie przy użyciu funkcji Always Encrypted z bezpiecznymi enklawami.

Za pomocą instrukcji ALTER TABLE lub ALTER COLUMN można ustawić docelową konfigurację szyfrowania dla kolumny. Po wykonaniu instrukcji bezpieczna enklawa po stronie serwera będzie szyfrować, ponownie szyfrować lub odszyfrowywać dane przechowywane w kolumnie, w zależności od bieżącej i docelowej konfiguracji szyfrowania określonej w definicji kolumny w instrukcji .

  • Jeśli kolumna nie jest obecnie zaszyfrowana, zostanie zaszyfrowana, jeśli określisz klauzulę ENCRYPTED WITH w definicji kolumny.
  • Jeśli kolumna jest obecnie zaszyfrowana, zostanie ona odszyfrowana (przekonwertowana na kolumnę w postaci zwykłego tekstu), jeśli nie określisz klauzuli ENCRYPTED WITH w definicji kolumny.
  • Jeśli kolumna jest obecnie zaszyfrowana, zostanie ona ponownie zaszyfrowana, jeśli określisz klauzulę ENCRYPTED WITH, a określony typ szyfrowania kolumny lub klucz szyfrowania kolumny różnią się od aktualnie używanego typu szyfrowania lub klucza szyfrowania kolumny.

Notatka

Nie można łączyć operacji kryptograficznych z innymi zmianami w jednej instrukcji ALTER TABLE/ALTER COLUMN, z wyjątkiem zmiany kolumny na NULL lub NOT NULLlub zmiany sortowania. Na przykład nie można zaszyfrować kolumny AND zmienić typu danych kolumny w jednej instrukcji ALTER TABLE/ALTER COLUMN Transact-SQL. Użyj dwóch oddzielnych instrukcji.

Każde zapytanie korzystające z bezpiecznej enklawy po stronie serwera, w tym instrukcję ALTER TABLE/ALTER COLUMN, która wyzwala szyfrowanie na miejscu, należy wysyłać za pośrednictwem połączenia z włączonymi Always Encrypted i obliczeniami enklawy.

W pozostałej części tego artykułu opisano sposób wyzwalania szyfrowania w miejscu przy użyciu instrukcji ALTER TABLE/ALTER COLUMN z programu SQL Server Management Studio. Alternatywnie, z narzędzia Azure Data Studio lub aplikacji możesz wydać ALTER TABLE/ALTER COLUMN.

Notatka

Obecnie polecenie cmdlet Invoke-Sqlcmd w module SqlServer PowerShell i sqlcmdnie obsługują używania ALTER TABLE/ALTER COLUMN dla operacji kryptograficznych w miejscu.

Wykonaj szyfrowanie w miejscu za pomocą Transact-SQL w SSMS

Wymagania wstępne

Kroki

  1. Otwórz okno zapytania z włączonym mechanizmem "Always Encrypted" oraz obliczeniami enklawy w połączeniu do bazy danych. Aby uzyskać szczegółowe informacje, zobacz Włączanie i wyłączanie funkcji Always Encrypted dla połączenia z bazą danych.

  2. W oknie zapytania wydaj instrukcję ALTER TABLE/ALTER COLUMN, określając docelową konfigurację szyfrowania dla kolumny, którą chcesz zaszyfrować, odszyfrować lub ponownie zaszyfrować. Jeśli szyfrujesz lub ponownie szyfrujesz kolumnę, użyj klauzuli ENCRYPTED WITH. Jeśli kolumna jest kolumną ciągu (na przykład char, varchar, nchar, nvarchar), może być również konieczne zmianę sortowania na sortowanie BIN2.

    Notatka

    Jeśli klucz główny kolumny jest przechowywany w usłudze Azure Key Vault, możliwe jest, że zostanie wyświetlony monit o zalogowanie się na platformie Azure.

  3. Wyczyść pamięć podręczną planu dla wszystkich pakietów i procedur składowanych, które uzyskują dostęp do tabeli, w celu odświeżenia informacji o szyfrowaniu parametrów.

    ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

    Notatka

    Jeśli nie usuniesz planu dla zapytania, którego dotyczy to zapytanie z pamięci podręcznej, pierwsze wykonanie zapytania po zaszyfrowaniu może zakończyć się niepowodzeniem.

    Użyj ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE lub DBCC FREEPROCCACHE, aby dokładnie wyczyścić pamięć podręczną planu, ponieważ może to spowodować tymczasowe obniżenie wydajności zapytań. Aby zminimalizować negatywny wpływ czyszczenia pamięci podręcznej, można selektywnie usunąć plany tylko dla zapytań, które podlegają wpływowi.

  4. Wywołaj sp_refresh_parameter_encryption, aby zaktualizować metadane parametrów każdego modułu (procedura składowana, funkcja, widok, wyzwalacz), które są przechowywane w sys.parameters i mogły zostać unieważnione wskutek szyfrowania kolumn.

Przykłady

Szyfrowanie kolumny w miejscu

W poniższym przykładzie przyjęto założenie:

  • CEK1 jest kluczem szyfrowania kolumn z możliwością wykorzystania enklawy.
  • Kolumna SSN jest tekstem nieszyfrowanym i obecnie używa domyślnego porządku sortowania bazy danych, takiego jak Latin1, porządku innego niż BIN2 (na przykład Latin1_General_CI_AI_KS_WS).

Instrukcja szyfruje kolumnę SSN przy użyciu szyfrowania deterministycznego i klucza szyfrowania kolumny z włączoną obsługą enklawy. Zastępuje również domyślne sortowanie bazy danych odpowiadającym sortowaniem BIN2 (na tej samej stronie kodowej).

Operacja jest wykonywana w trybie online (ONLINE = ON). Zwróć również uwagę na wywołanie ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, które odtwarza plany zapytań i jest pod wpływem zmiany schematu tabeli.

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;
GO

Ponownie zaszyfruj kolumnę w miejscu, aby zmienić typ szyfrowania

W poniższym przykładzie przyjęto założenie:

  • Kolumna SSN jest szyfrowana przy użyciu szyfrowania deterministycznego i klucza szyfrowania kolumn z obsługą enklawy CEK1.
  • Obecna kolejność ustawiona na poziomie kolumny to Latin1_General_BIN2.

Poniższa instrukcja ponownie szyfruje kolumnę przy użyciu szyfrowania losowego i tego samego klucza (CEK1)

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;
GO

Ponownie zaszyfruj kolumnę w miejscu, aby obrócić klucz szyfrowania kolumny

W poniższym przykładzie przyjęto założenie:

  • Kolumna SSN jest szyfrowana przy użyciu szyfrowania losowego i klucza szyfrowania kolumn obsługującego enklawę CEK1.
  • CEK2 jest kluczem szyfrowania kolumn obsługującym enklawy (różni się od CEK1).
  • Obecny porządek sortowania na poziomie kolumny to Latin1_General_BIN2.

Poniższa instrukcja ponownie szyfruje kolumnę przy użyciu CEK2.

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;
GO

Odszyfrowywanie kolumny w miejscu

W poniższym przykładzie przyjęto założenie:

  • Kolumna SSN jest szyfrowana przy użyciu klucza szyfrowania kolumn obsługującego enklawę.
  • Obecna forma sortowania ustawiona na poziomie kolumny to Latin1_General_BIN2.

Poniższa instrukcja odszyfrowuje kolumnę i zachowuje sortowanie bez zmian. Alternatywnie możesz zmienić sortowanie. Na przykład zmień sortowanie na sortowanie inne niż BIN2 w tej samej instrukcji.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
WITH (ONLINE = ON);
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Następne kroki

Zobacz też