Закодированные столбцы SQL в разделе WHERE

Я хочу применить шифрование на уровне столбца SQL с использованием симметричных ключей. Первоначальные шаги, необходимые для создания основного ключа базы данных, сертификатов и симметричных ключей, кажутся прямолинейными, и я успешно тестировал данные шифрования/дешифрования с помощью Symmetric Keys.

Однако, как только данные зашифрованы, я не знаю, как лучше всего запросить его. Например.

SELECT PlainTextA, PlainTextB, PlainTextC 
WHERE CONVERT(varchar, DECRYPTBYKEY(EncyptedColumn)) = @SearchTerm

наверняка приведет к полному сканированию таблицы?

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

SELECT PlainTextA, PlainTextB, PlainTextC 
WHERE EncyptedColumn = ENCRYPTBYKEY(KEY_GUID('KeyName'), @SearchTerm)

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

Приветствуются любые предложения.

Ответ 1

Типичным способом является сохранение как зашифрованного значения, так и однонаправленного хэша значения. Когда вы ищете определенное значение, вы будете искать хэш. Таким образом, вы можете эффективно запрашивать, без необходимости расшифровывать каждую строку, чтобы найти интересующее вас значение:

create table Table (
EncryptedColumn varbinary(max),
HashValue binary(20),
PlainA int,
PlainB varchar(256),
PlainC Datetime);

create index ndxTableHash on Table(HashValue);

select PlainA, plainB, PlainC
from table
where HashValue = HashBytes('SHA1', @searchTerm);

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

select PlainA, plainB, PlainC
from table
where HashValue = HashBytes('SHA1', @searchTerm)
and DecryptByKey(..., EncryptedColumn) = @searchTerm;

Также см. Индексирование зашифрованных данных и SQL Server 2005: поиск зашифрованных данных.

Ответ 2

Один из параметров, который у вас есть, - это добавить новый столбец в таблицу (или иметь представление WITH SCHEMABINDING с вычисленным столбцом в нем и индексировать это) с односторонним HASH значения поиска. Это не должно быть сильным хешем - что-то простое, как CHECKSUM будет работать. Затем вы получаете значение поиска в вашем поиске и фильтруете его хешем, который индексируется. Таким образом, вы можете выставлять что-то доступное для поиска и индексируемое без фактического отображения самого значения.

Однако, если есть другой способ сделать это напрямую, мне бы хотелось узнать, что это такое:)

Ответ 3

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

SELECT PlainTextA, PlainTextB, PlainTextC from TheView 
WHERE DecryptedColumn = @SearchTerm