Transparent Data Encryption


Ранее этот пост находился на форуме сообщества Russian SQL Server User Group по адресу http://sqlclub.ru/forum/viewtopic.php?f=6&t=1106, что позволяло во время демонстрации ссылаться на скрипт, чтобы слушатели могли не только посмотреть, но и впоследствии самостоятельно воспроизвести демку для закрепления материала. К сожалению, безответственный администратор по имени Сергей Заворуев положил сайт sqlclub.ru и ударился в бега, став недоступным ни по e-mail, ни по телефону, чем немало подставил сообщество. Хотелось бы предостеречь будущих возможных деловых партнеров Сергея Заворуева (http://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


Теперь любой любопытный покупатель открывает его любым двоичным редактором:



image001 


image003 


image005 


image007


Я специально поставил супротив себе сумму 0x12345, чтобы было легче ее найти.


image009 




Ну и все. Вот он, допустим, я, а чуть раньше – сумма моей проводки. И все остальные тоже, как на ладони. К тому же если человек не хочет париться с бинарщиной, он может просто поставить себе 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. Зато теперь злоумышленник, сперев файл данных, не увидит внутри него никакой осмысленной информации, т.к. данные зашифрованы.


image011 

-----------------------------------------------------------------------------------------------------------------------------------------

 


Поскольку я являюсь активным участником 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, рецепт тоже на поверхности - перезапустить сервер. Конечно, многие могут издать недовольный вопль, что сервер в продакшн и перезапустить его это не нос почесать, однако если вы решились на такой серьезный шаг, как шифрование базы в середине, видимо, оно того стоит. Об этом можно почитать более подробно в блоге Яна , также очень полезно будет почитать его статью с оценкой накладных расходов, которые влечет включение процесса шифрования.


Comments (1)

  1. CBuH says:

    SQL Server 2008 Express Edition я так понял не поддерживает прозрачного шифрования, потому что получаю следующее сообщение :

    Сообщение 33117, уровень 16, состояние 1, строка 2

    Transparent Data Encryption is not available in the edition of this SQL Server instance. See books online for more details on feature support in different SQL Server editions.

Skip to main content