Как зашифровать / расшифровать данные в некоторых столбцах таблицы, и когда вставляется новая запись, она также должна быть зашифрована

Question

Я знаю, как это, чтобы вставить новую запись

INSERT INTO dbo.Customer_data (Customer_id, Customer_Name, Credit_card_number)
VALUES (25665, 'mssqltips4', EncryptByKey( Key_GUID('SymmetricKey1'), CONVERT(varchar,'4545-58478-1245') ) ); 

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

INSERT INTO dbo.Customer_data (Customer_id, Customer_Name, Credit_card_number)
VALUES (25665, 'mssqltips4','4545-58478-1245') ) );       

Answer 1

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

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

CREATE TABLE [dbo].[Customer_data_encrypted]
(
    [customer_id] PRIMARY KEY -- you can create foreign key to the original one, too
   ,[name] VARBANRY(..)
   ,[cretit_card_numbe] VARBINARY(..)
);

Затем создайте INSTEAD OF INSERT UPDATE DELETE Триггер на исходной таблице. Логика в триггере проста:

1. при удалении удалить из обеих таблиц
2. при обновлении / вставке - зашифровать данные и вставить в новую таблицу; использовать какую-то маску для исходной таблицы (например, *** или же 43-****-****-****)

Затем выполните первоначальную миграцию, чтобы переместить данные из исходной таблицы в новую, а затем замаскируйте их.

Выполнение вышеуказанных шагов приятно, потому что:

1. каждая вставка / обновление исходной таблицы продолжают работать
2. Вы можете создать триггер с EXECUTE AS OWNER чтобы иметь доступ к симметричным ключам и вносить изменения непосредственно в инструкцию T-SQL, не открывая сертификаты или пользователями, которые не имеют к ним доступа
3. во всех ссылках reads вы собираетесь получить данные маски, так что вы не беспокоитесь о критическом нарушении приложения
4. триггер дает вам возможность легко создавать и изменять информацию

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