Задание для самостоятельной работы 4.2 Шифрование информации в таблицах баз данных Ситуация:

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

Задание:

61. Создайте в базе данных AdventureWorks симметричный ключ PayKey для применения с алгоритмом AES_256. Ключ должен быть защищен паролем P@ssw0rd.

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

63. Выполните запрос, который бы вернул все данные из зашифрованной таблицы HumanResources.EmployeePayHistoryEncrypted.

Примечание

Зашифрованную информацию нельзя поместить в столбцы типа Int, Money и т.п. Поэтому создание таблицы EmployeePayHistoryEncrypted придется производить вручную. Для целей этого задания используйте для всех столбцов этой таблицы один и тот же тип данных — nvarchar(100). Кроме того, несимвольные типы данных необходимо преобразовать в символьные (например, nvarchar(100)) перед передачей их шифрующей функции.

Решение: к пункту 1 — создание симметричного ключа:

Команда на создание симметричного ключа в соответствии с поставленными условиями может выглядеть так:

USE AdventureWorks

GO

CREATE SYMMETRIC KEY PayKey WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = 'P@ssw0rd'

К пункту 2 — создание зашифрованной копии таблицы:

Код для создания зашифрованной копии таблицы HumanResources.EmployeePayHistory может выглядеть так:

USE AdventureWorks

GO

-- Создаем таблицу для вставки шифрованных данных

CREATE TABLE HumanResources.EmployeePayHistoryEncrypted

(EmployeeID nvarchar(100),

RateChangeDate nvarchar(100),

Rate nvarchar(100),

PayFrequency nvarchar(100),

ModifiedDate nvarchar(100))

GO

-- Открываем симметричный ключ

OPEN SYMMETRIC KEY PayKey DECRYPTION By PASSWORD = 'P@ssw0rd';

-- Вставляем данные в таблицу при помощи INSERT INTO

INSERT INTO AdventureWorks.HumanResources.EmployeePayHistoryEncrypted

(EmployeeID,RateChangeDate,Rate,PayFrequency,ModifiedDate)

SELECT

EncryptByKey(Key_GUID('PayKey'), CONVERT(nvarchar(100), EmployeeID)),

EncryptByKey(Key_GUID('PayKey'), CONVERT(nvarchar(100), RateChangeDate)),

EncryptByKey(Key_GUID('PayKey'), CONVERT(nvarchar(100), Rate)),

EncryptByKey(Key_GUID('PayKey'), CONVERT(nvarchar(100), PayFrequency)),

EncryptByKey(Key_GUID('PayKey'), CONVERT(nvarchar(100), ModifiedDate))

FROM AdventureWorks.HumanResources.EmployeePayHistory

К пункту 3 — запрос к зашифрованным данным:

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

OPEN SYMMETRIC KEY PayKey DECRYPTION By PASSWORD = 'P@ssw0rd';

SELECT

Convert (Nvarchar(100), DecryptByKey(EmployeeID)) As EmployeeID,

Convert (Nvarchar(100), DecryptByKey(RateChangeDate)) As RateChangeDate,

Convert (Nvarchar(100), DecryptByKey(Rate)) As Rate,

Convert (Nvarchar(100), DecryptByKey(PayFrequency)) As PayFrequency,

Convert (Nvarchar(100), DecryptByKey(ModifiedDate)) As ModifiedDate

FROM [AdventureWorks].[HumanResources].[EmployeePayHistoryEncrypted]

Примечание

В реальной жизни, вам, возможно, потребовалось бы произвести дополнительные преобразования, чтобы вернуть информацию в виде значений с типами данных int, money и т.п. В этом примере для простоты мы возвращаем все данные как nvarchar(100).