ENCRYPTBYKEY (Transact-SQL)

  • Статья

Применимо к:SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics

Производит шифрование данных при использовании симметричного ключа.

Соглашения о синтаксисе Transact-SQL

Примечание.

Этот синтаксис не поддерживается бессерверным пулом SQL в Azure Synapse Analytics.

Синтаксис

  
EncryptByKey ( key_GUID , { 'cleartext' | @cleartext }  
    [, { add_authenticator | @add_authenticator }  
     , { authenticator | @authenticator } ] )

Примечание.

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Аргументы

key_GUID
Идентификатор GUID ключа, используемого для шифрования cleartext. uniqueidentifier.

'cleartext'
Данные, которые шифруются ключом.

@cleartext
Переменная типа nvarchar, char, varchar, binary, varbinary или nchar, содержащая данные, которые будут зашифрованы с помощью ключа.

add_authenticator
Указывает, будет ли вместе с аргументом cleartext зашифрована структура проверки подлинности. При использовании структуры проверки подлинности аргумент должен иметь значение 1. int.

@add_authenticator
Указывает, будет ли вместе с аргументом cleartext зашифрована структура проверки подлинности. При использовании структуры проверки подлинности аргумент должен иметь значение 1. int.

authenticator
Данные, на основе которых формируется структура проверки подлинности. sysname.

@authenticator
Переменная, содержащая данные, на основе которых формируется структура проверки подлинности.

Типы возвращаемых данных

Переменная типа varbinary с максимальным размером 8000 байт.

Возвращает значение NULL, если ключ не открыт, не существует или является устаревшим ключом RC4, а база данных не находится на уровне совместимости 110 или выше.

Возвращает NULL, если cleartext имеет значение NULL.

Замечания

Функция EncryptByKey использует симметричный ключ. Этот ключ должен быть открыт. Если симметричный ключ уже открыт в текущем сеансе, не нужно открывать его снова в контексте запроса.

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

ИД_сотрудника Стандартная_должность Базовый_оклад
345 Помощник копировщика Fskj%7^edhn00
697 Финансовый директор M0x8900f56543
694 Контролер ввода данных Cvc97824%^34f

Не взламывая шифр, злоумышленник может получить важные сведения из контекста, в котором хранятся зашифрованные данные. Но, поскольку оплата труда финансового директора заведомо выше, чем оплата труда помощника копировщика, из этого следует, что значение, зашифрованное как «M0x8900f56543», должно быть больше, чем значение, зашифрованное как «Fskj%7^edhn00». В таком случае любой пользователь с разрешением ALTER для данной таблицы может дать помощнику копировщика повышение, заменив его данные в поле Базовый_оклад простым копированием данных из поля Базовый_оклад финансового директора. Этот прием называется прямой подменой и обходит шифрование как таковое.

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

Если при шифровании данных указана структура проверки подлинности, то для дешифровки данных функцией DecryptByKey потребуются эти же данная структура проверки подлинности. При шифровании хэш данных проверки подлинности шифруется вместе с данными. При дешифровке эта же структура проверки подлинности должны быть переданы функции DecryptByKey. Если эти данные не совпадают, дешифровка завершится ошибкой. Это будет означать, что значение перемещено с другого места со времени шифрования. В качестве структуры проверки подлинности рекомендуется использовать столбец, содержащий уникальное и неизменное значение. Если значение структуры проверки подлинности изменится, можно утратить доступ к данным.

Симметричное кодирование и декодирование осуществляется относительно быстро и подходит для работы с большими объемами данных.

Важно!

Использование функций шифрования SQL Server совместно с параметром ANSI_PADDING OFF может привести к потере данных из-за неявных преобразований. Дополнительные сведения об ANSI_PADDING см. в статье SET ANSI_PADDING (Transact-SQL).

Примеры

Возможности, продемонстрированные в следующих примерах, работают с ключами и сертификатами, созданными в разделе Шифрование столбца данных.

А. Шифрование строки симметричным ключом

Следующий пример показывает, как добавить столбец к таблице Employee, а затем зашифровать значение номера социального страхования, который хранится в столбце NationalIDNumber.

USE AdventureWorks2022;  
GO  
  
-- Create a column in which to store the encrypted data.  
ALTER TABLE HumanResources.Employee  
    ADD EncryptedNationalIDNumber varbinary(128);   
GO  
  
-- Open the symmetric key with which to encrypt the data.  
OPEN SYMMETRIC KEY SSN_Key_01  
   DECRYPTION BY CERTIFICATE HumanResources037;  
  
-- Encrypt the value in column NationalIDNumber with symmetric key  
-- SSN_Key_01. Save the result in column EncryptedNationalIDNumber.  
UPDATE HumanResources.Employee  
SET EncryptedNationalIDNumber  
    = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);  
GO

B. Шифрование записи вместе со значением проверки подлинности

USE AdventureWorks2022;  
  
-- Create a column in which to store the encrypted data.  
ALTER TABLE Sales.CreditCard   
    ADD CardNumber_Encrypted varbinary(128);   
GO  
  
-- Open the symmetric key with which to encrypt the data.  
OPEN SYMMETRIC KEY CreditCards_Key11  
    DECRYPTION BY CERTIFICATE Sales09;  
  
-- Encrypt the value in column CardNumber with symmetric   
-- key CreditCards_Key11.  
-- Save the result in column CardNumber_Encrypted.    
UPDATE Sales.CreditCard  
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11'),   
    CardNumber, 1, CONVERT( varbinary, CreditCardID) );  
GO

См. также

DECRYP ТБ YKEY (Transact-SQL)
CREATE SYMMETRIC KEY (Transact-SQL)
ALTER SYMMETRIC KEY (Transact-SQL)
DROP SYMMETRIC KEY (Transact-SQL)
Иерархия средств шифрования
HASHBYTES (Transact-SQL)