ENCRYPTBYKEY (Transact-SQL)

Статья
06/18/2013

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

Значок ссылки на раздел Синтаксические обозначения в Transact-SQL

Синтаксис

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

Аргументы

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.

Замечания

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

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

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

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

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

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

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

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

Примеры

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

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

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

USE AdventureWorks2012;
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

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

USE AdventureWorks2012;

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