Как выполнить сортировку по зашифрованному столбцу (детерминированное шифрование — SQL 2016)

Как выполнить «Упорядочить по» в зашифрованном столбце (детерминированное шифрование — SQL Server 2016)?

Я получаю сообщение об ошибке при выполнении в SSMS 2017 (с необходимыми настройками для AE)

SELECT * 
FROM [dbo].[X] 
ORDER BY lastName

Столбец lastName определяется следующим образом:

[lastName] [varchar](60) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [X]

Я получаю сообщение об ошибке:

Сообщение 33299, уровень 16, состояние 2, строка 9
Несоответствие схемы шифрования для столбцов/переменных 'lastName'. Схема шифрования для столбцов/переменных (encryption_type = 'DETERMINISTIC', шифрование_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'X', column_encryption_key_database_name = 'X'), и выражение рядом со строкой '3' ожидает, что это будет (encryption_type = ' PLAINTEXT') (или слабее).


sql-server always-encrypted sql-server-2016 ssms-2017
person se7vanj    schedule 18.08.2017    source источник
comment
Все, что может видеть сервер, — это зашифрованное значение. Даже если он отсортирует эти значения, нет никакой гарантии, что при последующей расшифровке этих значений тот же порядок сортировки будет иметь смысл. Например, если A зашифровано как 23, B зашифровано как 17, а C зашифровано как 49, вы действительно хотите, чтобы сервер выполнял эту сортировку (чтобы значения, которые вы в конечном итоге читаете, выходили в порядке B, A, C)?   -  person Damien_The_Unbeliever    schedule 18.08.2017
comment
Запросы могут выполнять сравнение на равенство для столбцов, зашифрованных с использованием детерминированного шифрование, но никакие другие операции (например, больше/меньше, сопоставление с образцом с использованием оператора LIKE или арифметические операции).. Это имеет смысл, если вы думаете о том, как база данных могла бы реализовать упорядочение зашифрованных элементов — то есть безопасно.   -  person Jeroen Mostert    schedule 18.08.2017
comment
В зависимости от того, для чего вам нужно ORDER BY, может быть обходной путь, наиболее очевидным из которых является выполнение сортировки на стороне клиента после расшифровки. (SSMS не имеет собственной логики обработки, поэтому она не может делать такие приятные вещи.)   -  person Jeroen Mostert    schedule 18.08.2017

Ответы (1)


arrow_upward
0
arrow_downward

Порядок не поддерживается для зашифрованных столбцов.

Дополнительные сведения можно найти на странице эта статья

Компонент Database Engine никогда не работает с открытыми текстовыми данными, хранящимися в зашифрованных столбцах, но по-прежнему поддерживает некоторые запросы к зашифрованным данным, в зависимости от типа шифрования столбца. Always Encrypted поддерживает два типа шифрования: рандомизированное шифрование и детерминированное шифрование.

Детерминированное шифрование всегда создает одно и то же зашифрованное значение для любого заданного текстового значения. Использование детерминированного шифрования позволяет осуществлять точечный поиск, объединение на равенство, группировку и индексирование зашифрованных столбцов. Однако это также может позволить неавторизованным пользователям угадывать информацию о зашифрованных значениях путем изучения шаблонов в зашифрованном столбце, особенно если существует небольшой набор возможных зашифрованных значений, таких как Истина/Ложь или регион Север/Юг/Восток/Запад. Детерминированное шифрование должно использовать сопоставление столбцов с порядком сортировки binary2 для символьных столбцов.

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

person Nikhil Vithlani - Microsoft    schedule 18.08.2017
comment
Итак, в общем случае, когда хост-приложение WebAPI взаимодействует с базой данных SQL 2016, а потребительский веб-сайт вызывает хост-API, что можно здесь предложить? Мы не хотим убивать ни API, ни потребителя, чтобы получить все записи, а затем сделать это самостоятельно? И основная проблема заключается в том, что когда потребитель запрашивает данные с разбивкой на страницы в запросе к API, API нелегко обрабатывать и возвращать обратно в разбивке на страницы, и это заставляет обрабатывать незащищенные данные в памяти! По-видимому, использование CTE также не поможет в этом случае, и нет обходного пути в SQL! - person se7vanj; 18.08.2017
comment
@se7vanj: 1) механизм базы данных, который может сортировать и разбивать на страницы, 2) механизм базы данных, который не может раскрыть незашифрованные данные, даже если у ненадежных сторон есть доступ - выберите один. Если Always Encryption заходит слишком далеко, рассмотрите меньшие альтернативы, такие как Прозрачное шифрование данных для защиты хранимых данных, размещения сервера в помещении или обеспечения его безопасности другими способами (шифрование образов ВМ, контроль доступа и т. д.) - person Jeroen Mostert; 18.08.2017
comment
@JeroenMostert - я считаю, что есть некоторые схемы шифрования, которые поддерживают возможность сортировки (поиск: order preserving encryption), но я не верю, что такие схемы стали популярными, и я уверен, что их нет в SQL Server. Но идея сортировки и разбиения на страницы и обеспечения безопасности данных не является на 100 % научной фантастикой. - person Damien_The_Unbeliever; 21.08.2017
comment
@Damien_The_Unbeliever: спасибо за информацию. Вопрос со справочной информацией о crypto.se. Широкому внедрению препятствует неизвестность того, сколько информации эти схемы утекают в практических приложениях. (Я уверен, что было бы также сложно реализовать его эффективно, особенно если вы хотите разбивать на страницы, а не просто сравнивать отдельные элементы.) - person Jeroen Mostert; 21.08.2017