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

Настройка среды

Прежде чем мы начнем, давайте подготовим среду. Мы создадим новую базу данных и таблицу с именем CustomerInfo:

CREATE DATABASE CustomerData;
GO

USE CustomerData;
GO

CREATE TABLE CustomerData.dbo.CustomerInfo (
    CustID INT PRIMARY KEY,
    CustName VARCHAR(30) NOT NULL,
    BankACCNumber VARCHAR(10) NOT NULL
);
GO

INSERT INTO CustomerData.dbo.CustomerInfo (CustID, CustName, BankACCNumber)
VALUES
    (1, 'Раджендра', '11111111'),
    (2, 'Манодж', '22222222'),
    (3, 'Шьям', '33333333'),
    (4, 'Акшита', '44444444'),
    (5, 'Кашиш', '55555555');

Шаги шифрования уровня столбца

Мы будем следовать этим шагам для реализации шифрования уровня столбца SQL Server:

1. Создать ключ главной базы данных
2. Создать самоподписанный сертификат для SQL Server
3. Настроить симметричный ключ для шифрования
4. Зашифровать данные столбца
5. Запросить и проверить шифрование

Создать ключ главной базы данных

На первом шаге нам нужно создать ключ главной базы данных для защиты закрытых ключей и асимметричных ключей. Мы можем использовать следующий SQL-запрос для создания ключа главной базы данных:

USE CustomerData;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SQLShack@1';

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

SELECT name AS KeyName, symmetric_key_id AS KeyID, key_length AS KeyLength, algorithm_desc AS KeyAlgorithm
FROM sys.symmetric_keys;

Создать самоподписанный сертификат

На следующем шаге мы создаем самоподписанный сертификат с помощью оператора CREATE CERTIFICATE. Этот сертификат будет использоваться для шифрования. Выполните следующий запрос для создания сертификата:

USE CustomerData;
GO

CREATE CERTIFICATE Certificate_test WITH SUBJECT = 'Защита моих данных';

Мы можем проверить сертификат, используя представление каталога sys.certificates:

SELECT name AS CertName, certificate_id AS CertID, pvt_key_encryption_type_desc AS EncryptType, issuer_name AS Issuer
FROM sys.certificates;

Настроить симметричный ключ

На этом шаге мы определим симметричный ключ для шифрования. Симметричный ключ использует один ключ как для шифрования, так и для расшифровки. Мы используем оператор CREATE SYMMETRIC KEY для настройки симметричного ключа:

CREATE SYMMETRIC KEY SymKey_test
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Certificate_test;

Мы можем проверить существующие ключи, используя представление каталога sys.symmetric_keys:

SELECT name AS KeyName, symmetric_key_id AS KeyID, key_length AS KeyLength, algorithm_desc AS KeyAlgorithm
FROM sys.symmetric_keys;

Шифрование данных

Теперь, когда мы создали необходимые ключи шифрования, мы можем приступить к шифрованию данных столбца. В нашей таблице CustomerData столбец BankACCNumber имеет тип данных VARCHAR(10). Мы добавим новый столбец с типом данных VARBINARY(MAX) для хранения зашифрованных данных:

ALTER TABLE CustomerData.dbo.CustomerInfo
ADD BankACCNumber_encrypt VARBINARY(MAX);

Для шифрования данных нам нужно открыть симметричный ключ и использовать функцию EncryptByKey. Выполните следующие SQL-запросы:

OPEN SYMMETRIC KEY SymKey_test
DECRYPTION BY CERTIFICATE Certificate_test;

UPDATE CustomerData.dbo.CustomerInfo
SET BankACCNumber_encrypt = EncryptByKey(Key_GUID('SymKey_test'), BankACCNumber);

Наконец, мы можем закрыть симметричный ключ с помощью оператора CLOSE SYMMETRIC KEY:

CLOSE SYMMETRIC KEY SymKey_test;

Расшифровка данных

Для расшифровки зашифрованных данных нам нужно открыть симметричный ключ и использовать функцию DecryptByKey. Выполните следующие SQL-запросы:

OPEN SYMMETRIC KEY SymKey_test
DECRYPTION BY CERTIFICATE Certificate_test;

SELECT CustID, CustName, BankACCNumber_encrypt AS 'Зашифрованные данные', CONVERT(varchar, DecryptByKey(BankACCNumber_encrypt)) AS 'Расшифрованный номер банковского счета'
FROM CustomerData.dbo.CustomerInfo;

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

Заключение

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