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

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

Применимо к: SQL Server 2019 (15.x) и более поздних версий в Базе данных SQL Windows Azure

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

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

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

Примечание.

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

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

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

Примечание.

В настоящее время командлет 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;

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

В следующем примере предполагается:

  • Столбец 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;

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

В следующем примере предполагается:

  • Столбец 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;

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

В следующем примере предполагается:

  • Столбец 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;

Связанный контент

  • Last updated on