Настройка шифрования столбцов на месте с помощью Transact-SQL

  • Статья

Область применения: SQL Server 2019 (15.x) и более поздних версий — только база данных SQL Azure Для Windows

В этой статье описывается использование Always Encrypted с безопасными анклавами для выполнения криптографических операций на месте в столбцах с помощью инструкции ALTER TABLE/ALTER COLUMN. Основные сведения о шифровании на месте и общие предварительные требованиях см. в статье Настройка шифрования столбцов на месте с помощью Always Encrypted с безопасными анклавами.

С помощью инструкции ALTER TABLE или ALTER COLUMN можно задать целевую конфигурацию шифрования для столбца. При выполнении инструкции защищенный анклав на стороне сервера шифрует, повторно шифрует или расшифровывает данные, хранящиеся в столбце, в зависимости от текущей и целевой конфигурации шифрования, указанной в определении столбца в инструкции.

  • Если столбец в настоящий момент не зашифрован, он будет зашифрован, если в определении столбца указано предложение ENCRYPTED WITH.
  • Если столбец зашифрован, он будет расшифрован (преобразован в столбец с открытым текстом), если в определении столбца не указано предложение ENCRYPTED WITH.
  • Если столбец зашифрован, он будет повторно зашифрован, если указано предложение ENCRYPTED WITH, а указанный тип шифрования столбца или ключ шифрования столбца отличаются от используемого в настоящий момент типа шифрования или ключа шифрования столбца.

Заметка

Криптографические операции нельзя сочетать с другими изменениями в одной инструкции ALTER TABLE/ALTER COLUMN. Исключением является изменение столбца на NULL или NOT NULL либо изменение параметров сортировки. Например, вы не можете зашифровать столбец и изменить тип данных столбца в одной инструкции Transact-SQL ALTER TABLE/ALTER COLUMN. Используйте для этого две отдельных инструкции.

Как и любой запрос, использующий защищенный анклава на стороне сервера, инструкция ALTER TABLE/ALTER COLUMN, которая инициирует шифрование на месте, должна отправляться через подключение с включенной функцией Always Encrypted и вычислениями анклава.

В оставшейся части этой статьи описывается запуск шифрования на месте с помощью инструкции ALTER TABLE/ALTER COLUMN из SQL Server Management Studio. Кроме того, можно выполнить ALTER TABLE/ALTER COLUMN из Azure Data Studio или из приложения.

Заметка

В настоящее время командлет Invoke-Sqlcmd в модуле SqlServer PowerShell и sqlcmd не поддерживают использование ALTER TABLE/ALTER COLUMN для криптографических операций на месте.

Выполнение шифрования на месте с помощью Transact-SQL в среде SSMS

Предварительные требования

Шаги

  1. Откройте окно запроса с поддержкой вычислений в анклаве и Always Encrypted для подключения к базе данных. Дополнительные сведения см. в разделе "Включение и отключение Always Encrypted" для подключения к базе данных.

  2. В окне запроса выполните инструкцию ALTER TABLE/ALTER COLUMN, указав целевую конфигурацию шифрования для столбца, который необходимо зашифровать, расшифровать или зашифровать повторно. Если вы шифруете или повторно шифруете столбец, используйте ENCRYPTED WITH предложение. Если столбец является строковым столбцом (например, char, varchar, nchar, nvarchar), также может потребоваться изменить тип сортировки на BIN2.

    Заметка

    Если главный ключ столбца хранится в Azure Key Vault, вам может быть предложено выполнить вход в Azure.

  3. Очистите кэш планов от всех пакетов и хранимых процедур, которые обращаются к таблице, чтобы обновить сведения о параметрах шифрования.

    ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

    Заметка

    Если вы не удалите план затронутого запроса из кэша, первое выполнение запроса после шифрования может завершиться сбоем.

    Внимательно используйте ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE или DBCC FREEPROCCACHE для очистки кэша планов, так как эта операция может привести к временному ухудшению производительности запросов. Чтобы свести к минимуму негативное влияние очистки кэша, вы можете выборочно удалять планы только для затронутых запросов.

  4. Вызовите sp_refresh_parameter_encryption, чтобы обновить метаданные параметров каждого модуля (хранимая процедура, функция, представление, триггер), которые сохранены в sys.parameters и могут быть аннулированы в результате шифрования столбцов.

Примеры

Шифрование столбца на месте

В приведенном ниже примере предполагается:

  • CEK1 — это ключ шифрования столбцов с поддержкой анклава.
  • Столбец SSN содержит открытый текст и в настоящее время использует сортировку базы данных по умолчанию, такую как Latin1, а не параметры сортировки BIN2 (например, Latin1_General_CI_AI_KS_WS).

Инструкция шифрует SSN столбец с помощью детерминированного шифрования и ключа шифрования столбца с поддержкой анклава. Она также перезаписывает сортировку базы данных по умолчанию соответствующими (в той же кодовой странице) параметрами сортировки BIN2.

Операция выполняется в сети (ONLINE = ON). Также обратите внимание на вызов ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, который восстанавливает планы запросов, затронутые изменением схемы таблицы.

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

Повторное шифрование столбца на месте для изменения типа шифрования

В приведенном ниже примере предполагается:

  • SSN столбец зашифрован с помощью детерминированного шифрования и ключа шифрования столбца с поддержкой анклава CEK1.
  • Текущие параметры сортировки, установленные на уровне столбца, имеют тип Latin1_General_BIN2.

Приведенная ниже инструкция повторно шифрует столбец с помощью случайного шифрования и того же ключа (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

Повторное шифрование столбца на месте для смены ключа шифрования столбца

В приведенном ниже примере предполагается:

  • SSN столбец зашифрован с помощью случайного шифрования и ключа шифрования столбца с поддержкой анклава CEK1.
  • CEK2 — это ключ шифрования столбца с поддержкой анклава (отличный от CEK1).
  • Текущие параметры сортировки, установленные на уровне столбца, имеют тип Latin1_General_BIN2.

Приведенная ниже инструкция повторно шифрует столбец с помощью 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

Расшифровка столбца на месте

В приведенном ниже примере предполагается:

  • SSN столбец зашифрован с помощью ключа шифрования столбца с поддержкой анклава.
  • Текущие параметры сортировки, установленные на уровне столбца, имеют тип Latin1_General_BIN2.

Приведенная ниже инструкция расшифровывает столбец и оставляет без изменений параметры сортировки. При необходимости можно изменить параметры сортировки. Например, изменить параметры сортировки на отличные от BIN2, в той же инструкции.

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

Далее

См. также