Поделиться через

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

  • Статья

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

В этой статье описывается, как выполнять криптографические операции непосредственно в столбцах с использованием 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

Следующие шаги

См. также