Transparent Data Encryption
Ранее этот пост находился на форуме сообщества Russian SQL Server User Group по адресу https://sqlclub.ru/forum/viewtopic.php?f=6&t=1106, что позволяло во время демонстрации ссылаться на скрипт, чтобы слушатели могли не только посмотреть, но и впоследствии самостоятельно воспроизвести демку для закрепления материала. К сожалению, безответственный администратор по имени Сергей Заворуев положил сайт sqlclub.ru и ударился в бега, став недоступным ни по e-mail, ни по телефону, чем немало подставил сообщество. Хотелось бы предостеречь будущих возможных деловых партнеров Сергея Заворуева (https://1stat.ru/?show=whois\&person=Sergey V Zavoruev), разработка, продвижение и сопровождение сайтов, сетевые работы любой сложности, о риске, который они на себя берут, затевая с ним совместные проекты. Может внезапно кидануть и смыться.
---------------------------------------------------------------------------------------------------------------------
Пусть имеем БД с очень чувствительной информацией. Назовем ее TDE_Test:
use tempdb
if exists(select 1 from sys.databases where name = 'TDE_Test') begin
alter database TDE_Test set SINGLE_USER with rollback immediate
drop database TDE_Test
end
go
create database TDE_Test
go
use TDE_Test
go
create table Transactions (
id int identity primary key
, customer varchar(20)
, amount int
, tx_date datetime2
)
go
insert Transactions (customer, amount, tx_date) values
('Leshik', 0x12345, '2008-04-07')
, ('Roma', 200000, '2008-04-07')
, ('Leshik', -5000, '2008-04-08')
, ('Gaidar', -1000000, '2008-04-08')
select * from Transactions
И пусть mdf стырил некий крот и загнал на Горбухе.
use tempdb
exec sp_detach_db @dbname= 'TDE_Test', @skipchecks= 'true'
--exec sp_configure 'show advanced', 1
--reconfigure with override
--exec sp_configure 'xp_cmdshell', 1
--reconfigure with override
exec xp_cmdshell 'copy "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TDE_Test.mdf" c:\Demo\TDE_Test.mdf'
exec sp_attach_single_file_db @dbname = 'TDE_Test', @physname = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TDE_Test.mdf'
use TDE_Test
Теперь любой любопытный покупатель открывает его любым двоичным редактором:
Я специально поставил супротив себе сумму 0x12345, чтобы было легче ее найти.
Ну и все. Вот он, допустим, я, а чуть раньше – сумма моей проводки. И все остальные тоже, как на ладони. К тому же если человек не хочет париться с бинарщиной, он может просто поставить себе SQL Server, приаттачить к нему mdf и читать все с комфортом из базы.
-----------------------------------------------------------------------------------------------------------------------------------------
В 2000-м от этого можно было спасаться, положив файлы БД на EFSный том. В SQL Server 2005 появились внутренние средства борьбы под названием раздельное шифрование записей
alter table Transactions add [Зашифрованная сумма] varbinary(100)
create symmetric key MyKey /* authorization [.\Administrator] */
with algorithm = TRIPLE_DES encryption by password = 'Abra Cadabra'
open symmetric key MyKey decryption by password = 'Abra Cadabra'
update Transactions set
[Зашифрованная сумма] = EncryptByKey(Key_GUID('MyKey'), cast(Amount as varbinary(100)), 1, cast(id as binary(4))),
Amount = NULL
--3-й п-р явл-ся опциональным и означает "использовать аутентификатор".
--Т.н. аутентификатор предназначен для предотвр-я манипуляций с зашифрованными данными.
--Напр., взять сумму из одной проводки и, как есть, в зашифр. виде переставить в другую.
--Во избежание такого рода атак хэш аутентификатора (контекстной к записи информации,
--как правило, РК) шифруется вместе с суммой. При расшифровке также подставляется
--аутентификатор, и если он не совпадет с оригинальным, будет ошибка.
--Теперь в зашифрованной сумме стоит полная белиберда
select * from Transactions
--Чтобы ее прочитать в осмысленном виде, требуется ключ MyKey, причем как текущему пользователю, так и кому-либо еще
if exists(select 1 from sys.server_principals where name = 'Usr') drop login Usr
create login Usr with password = 'Pwd', default_database = TDE_Test
create user Usr from login Usr
exec sp_addrolemember @rolename = 'db_datareader', @membername = 'Usr'
close all symmetric keys
--Выдаем юзеру Usr права на открытие / закрытие ключа MyKey
grant references on symmetric key::MyKey to Usr
execute as user = 'Usr'
select SUSER_SNAME()
open symmetric key MyKey decryption by password = 'Abra Cadabra'
select *,
cast(DecryptByKey([Зашифрованная сумма], 1, cast(id as binary(4))) as money) as [Расшифрованная сумма]
from Transactions
close all symmetric keys
revert
-----------------------------------------------------------------------------------------------------------------------------------------
SQL Server 2008 идет в этом плане дальше, позволяя обеспечить прозрачное шифрование не отдельных записей, а файлов БД целиком. Страницы шифруются на лету перед записью на диск и расшифровываются перед чтением в память.
Все, что для этого требуется сделать – ALTER DATABASE TDE_Test SET ENCRYPTION ON
--Ключ уровня базы, по определению, может быть защищен только серверным сертификатом.
--Нельзя, напр., написать так:
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY password = 'Abra Cadabra'
-----------------------------------------------------------------------------------------------------------------------------------------
--Поэтому вначале придется создать серверный сертификат:
use master
--Но еще вначале нужно создать мастер-ключ:
create master key encryption by password = 'Abra Cadabra'
--потому что без него попытка создания сертификата даст ошибку
--Please create a master key in the database or open the master key in the session before performing this operation.
CREATE CERTIFICATE MySrvCrt WITH SUBJECT = 'Мой серверный сертификат'
use TDE_Test
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY server certificate MySrvCrt
ALTER DATABASE TDE_Test SET ENCRYPTION ON
--Включено шифрование страниц на лету.
При этом log также шифруется. Кроме того, если мы посмотрим
SELECT DB_NAME(database_id), create_date, set_date, opened_date, key_algorithm, key_length
FROM sys.dm_database_encryption_keys
WHERE encryption_state = 3
(Encryption_state:
0 = No database encryption key present, no encryption
1 = Unencrypted
2 = Encryption in progress
3 = Encrypted
4 = Key change in progress
5 = Decryption in progress)
(No column name) create_date set_date opened_date key_algorithm key_length
tempdb 2008-04-05 19:19:02.313 NULL 2008-04-05 19:19:02.313 AES 256
TDE_Test 2008-04-05 19:18:19.427 2008-04-05 19:19:02.313 2008-04-05 19:18:19.427 AES 128
то увидим, что включение прозрачной шифрации пользовательской базы включило автоматическую шифрацию tempdb. Что справедливо, т.к. в tempdb могут находиться куски данных пользовательских баз, когда, напр., SQL Server создает в ней промежуточные таблицы во время выполнения сложного запроса. С другой – это означает доп. нагрузку на tempdb. Зато теперь злоумышленник, сперев файл данных, не увидит внутри него никакой осмысленной информации, т.к. данные зашифрованы.
-----------------------------------------------------------------------------------------------------------------------------------------
Поскольку я являюсь активным участником Russian SQL Server User Group и регулярно посещаю наши мероприятия, я узнаю различные дополнительные вещи, которые не лежат на поверхности в BOL, но которые очень полезно бывает знать, чтобы не получить ненароком по лбу рукояткой граблей. Так, благодаря Яну и Николаю я должен обратить внимание для полноты картины, что включение прозрачного шифрования над базой означает шифрование лога и tempdb, однако не означает, что исторические данные в них также будут зашифрованы. В логе и tempdb шифроваться будет все, но с того момента, как мы включили шифрование над базой. Следовательно, если мы сейчас пойдем в лог, то увидим нашу секретную транзакцию
insert Transactions (customer, amount, tx_date) values
('Leshik', 0x12345, '2008-04-07')
, ('Roma', 200000, '2008-04-07')
, ('Leshik', -5000, '2008-04-08')
, ('Gaidar', -1000000, '2008-04-08')
в открытом виде. Имейте это, пожалуйста, в виду, потому что это существенно. Понятно, как с этим бороться. Коммитим все транзакции и делаем бэкап, чтобы очистить неактивную часть лога, а чтобы гарантировать, что не осталось критичных незашифрованных кусков в tempdb, рецепт тоже на поверхности - перезапустить сервер. Конечно, многие могут издать недовольный вопль, что сервер в продакшн и перезапустить его это не нос почесать, однако если вы решились на такой серьезный шаг, как шифрование базы в середине, видимо, оно того стоит. Об этом можно почитать более подробно в блоге Яна , также очень полезно будет почитать его статью с оценкой накладных расходов, которые влечет включение процесса шифрования.