Скрытые таблицы. Разделяемые пространства блокировок и шифрование.

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

Эти секретные данные вообще не хранятся на сервере. Они хранятся где-то за его пределами, может быть в файле или где-то еще.

Требуется, чтобы было некое соединение, которое сможет осуществлять доступ к этим данным и перемещать их в секретную таблицу сервера. Причем по требованиям безопасности необходимо, чтобы к таблице смогли обратиться лишь некоторые пользователи, знающие секретный пароль. А сама таблица доступна лишь в течение некоторого промежутка времени, которое задается соединением-источником.

Для решения задачи сперва создадим на сервере самоподписанный сертификат, защищенный паролем:

if cert_id ( N'CertForBoundSess' ) is not null

begin

       ;

       throw 50000, N'В базе данных уже существует сертификат с именем "CertForBoundSess".', 1

       return

end

create certificate CertForBoundSess

       encryption by password = N'$erfwer#^#$gtet'

       with

             subject = N'Сертификат для шифрования дескрипторов пространства блокировок',

             start_date = '2014-01-01 00:00:00.000',

             expiry_date = '2014-12-31 23:59:59.999'

Пароль будет известен только тем пользователям, которые должны иметь доступ к конфиденциальным данным.

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

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

Расчет понятен: пока транзакция открыта, все данные заблокированы. Поскольку секретная таблица создается в транзакции, то ее никто не сможет прочитать даже с nolock-ом. Когда время жизни таблицы истечет, произойдет откат транзакции и таблица с данными исчезнет.

Есть одна особенность. Если в процедуре открыть транзакцию и не зафиксировать и не откатить ее, то процедура завершит свою работу с ошибкой. Поэтому в первой процедуре транзакций нет:

if sessionproperty ( N'quoted_identifier' ) = 0

       set quoted_identifier on

go

if sessionproperty ( N'ansi_nulls' ) = 0

       set ansi_nulls on

go

if object_id ( N'dbo.TranslateSecretData', N'P' ) is null

       exec ( N'create proc dbo.TranslateSecretData as return 1' )

go

alter proc dbo.TranslateSecretData

as

begin

       set nocount on

       set xact_abort off

       declare @FileName varchar ( 300 ) = 'C:\Users\Edynak\Desktop\data.txt', @sql nvarchar ( max ), @ErrMsg nvarchar ( max ), @out_token varchar ( 255 )

       if object_id ( N'tempdb..##SecretData', N'U' ) is not null

             drop table ##SecretData

       begin try

             create table ##SecretData

             (

                    iRowId              int                               not null,

                    vcCustomerName      nvarchar ( 100 )    not null,

                    SalaryAmount float                      not null,

                    primary key clustered ( iRowId asc ) on [PRIMARY],

                    unique nonclustered ( vcCustomerName asc ) on [PRIMARY]

             ) on [PRIMARY]

            

             set @sql = N'

                    bulk insert ##SecretData

                    from ''' + @FileName + '''

                    with

                    (

                           firstrow        = 1,

                           datafiletype    = ''char'',

                           codepage        = 1251,

                           batchsize       = 5000000,

                           rowterminator   = ''\n'',

                           fieldterminator = '';''

                    )

             '

             exec sp_executesql @sql

            

             exec sp_getbindtoken @out_token out

            

             insert into dbo.BoundSessionTokens ( varSessionToken, dtDate )

                    values ( encryptbycert ( cert_id ( N'CertForBoundSess' ), @out_token ), getdate () )

       end try

       begin catch

             if xact_state () <> 0

             begin

                    rollback tran

             end

             select 'data'

             set @ErrMsg = N'Номер ошибки: ' + isnull ( cast ( error_number () as nvarchar ( 100 ) ), N'' ) +

                    N'описание ошибки: ' + isnull ( error_message (), N'' ) +

                    N'номер строки: ' + isnull ( cast ( error_line () as nvarchar ( 100 ) ), N'' ) +

                    N'состояние ошибки: ' + isnull ( cast ( error_state () as nvarchar ( 100 ) ), N'' ) +

                    N'серьёзность ошибки: ' + isnull ( cast ( error_severity () as nvarchar ( 100 ) ), N'' )

             ;

             throw 50000, @ErrMsg, 1

       end catch

end

go

Создадим таблицу для хранения дескрипторов:

create table dbo.BoundSessionTokens

(

       iRowId              int    identity ( 1, 1 )   not null,

       varSessionToken     varbinary ( 8000 )         not null,

       dtDate              datetime                   not null,

       constraint PK_BoundSessionTokens_iRowId primary key clustered ( iRowId asc ) on [PRIMARY]

) on [PRIMARY]

Нам необходимо открыть транзакцию, запустить процедуру dbo.TranslateSecretData, сделать задержку и откатить транзакцию. Если все это делать внутри одной процедуры, то пространство блокировок соединения будет все время занято, и к нему никто не сможет подключиться. Поэтому эти шаги мы инкапсулируем в clr-сборку. Вот код хранимой процедуры на основе clr:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class SecretDataWork

{

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void CreateSecretTable (SqlInt32 SecNum)

    {

        SqlCommand cmd = null;

        SqlConnection cn = null;

        try

        {

            cn = new SqlConnection(@"Data Source=EDYNAK\SHADOW;Database=secret.data;Trusted_Connection=Yes");

            cn.Open();

            cmd = new SqlCommand();

            cmd.Connection = cn;

            cmd.CommandType = System.Data.CommandType.Text;

            cmd.CommandText = @"if @@trancount > 0 rollback tran if object_id ( N'tempdb..##SecretData', N'U' ) is not null drop table ##SecretData";

            cmd.ExecuteNonQuery();

            System.Threading.Thread.Sleep((int)SecNum * 1000);

        }

        catch(Exception ex)

        {

            SqlContext.Pipe.Send("Произошла ошибка: " + ex.ToString());

        }

        finally

        {

            cmd.CommandText = @"if @@trancount > 0 rollback tran";

            cmd.ExecuteNonQuery();

            cn.Close();

        }

    }

}

В этом коде при вызове последнего метода ExecuteNonReader проверяется открыта ли транзакция, и только в этом случае делается ее откат. Это важно, поскольку соединения, которые подключатся к данному пространству блокировок сами окажутся в том же транзакционном контексте, и потому могут спокойно как откатить, так и зафиксировать транзакцию. В последнем случае таблица ##SecretData останется в базе данных tempdb для всеобщего обозрения. Так что соединению-приемнику этого делать не следует. Если же это все-таки случилось, то в методе CreateSecretTable, в блоке finally, происходит проверка наличия таблицы ##SecretData. И, если она существует, то она удаляется.
Поскольку в методе происходит создание нового соединение, то для сборки требуется проставить уровень разрешений external_access. Теперь выполним развертывание сборки на сервере:

create assembly SecretData from 'C:\Документы\Временные\tmp\Database7\Database7\bin\Debug\Database7.dll'

       with permission_set = external_access

create proc dbo.MakeSecretTable ( @SecNum int )

       as external name SecretData.SecretDataWork.CreateSecretTable

В процедуре dbo.TranslateSecretData дескриптор пространства блокировок сохраняется в специальной таблице (естественно, в зашифрованном виде). Другое соединение должно прочитать с nolock зашифрованный десриптор из таблицы, расшифровать его, подключиться к пространству блокировок исходного соединения. После этого соединение-приемник сможет делать запросы к таблице ##SecretData. Задача решается таким запросом:

declare @Password nvarchar ( 100 ) = '$erfwer#^#$gtet'

declare @bind_token varchar ( 255 )

select top 1 @bind_token = cast ( decryptbycert ( cert_id ( 'CertForBoundSess' ), varSessionToken, @Password ) as varchar ( 255 ) )

from dbo.BoundSessionTokens with ( nolock )

order by iRowId desc

      

exec sp_bindsession @bind_token

select *

from ##SecretData

Этот код нельзя инкапсулировать в хранимой процедуре, поскольку после вызова процедуры sp_bindsession пакет работает в контексте транзакции. Если гипотетическая процедура в конце своей работы ни откатит ни зафиксирует транзакцию, то ее работа завершится с ошибкой. При этом может откатиться транзакция, что приведет к потере данных для всех соединений. Если же процедура самостоятельно зафиксирует транзакцию, то таблица ##SecretData станет всем доступна. Если в процедуре сделать откат транзакции, то данные будут потеряны для других соединений, которые знают пароль от сертификата. Как и ранее здесь поможет clr-сборка. Сделаем на основе clr-сборки табличную функцию, которая получает в качестве параметра пароль сертификата и возвращает таблицу с данными. Добавим к классу SecretDataWork методы GetSecretData и MyFillRowMethodс (предварительно надо добавить ссылку на пространство имен System.Collections):

    [SqlFunction(DataAccess = DataAccessKind.Read, FillRowMethodName = "MyFillRowMethod", IsDeterministic = true, TableDefinition = "RowId int, CustomerName nvarchar ( 100 ), Salary money")]

    public static IEnumerable GetSecretData(string password)

    {

        ArrayList elements = new ArrayList();

        SqlCommand cmd = null;

        try

        {

            using (SqlConnection cnCont = new SqlConnection(@"Data Source=EDYNAK\SHADOW;Database=secret.data;Trusted_Connection=Yes"))

            {

                cnCont.Open();

                cmd = new SqlCommand();

                cmd.Connection = cnCont;

                cmd.CommandType = System.Data.CommandType.Text;

                cmd.CommandText = @"

                       declare @bind_token varchar ( 255 )

                       select top 1 @bind_token = cast ( decryptbycert ( cert_id ( 'CertForBoundSess' ), varSessionToken, N'" + password + @"' ) as varchar ( 255 ) )

                       from dbo.BoundSessionTokens with ( nolock )

                       order by iRowId desc

      

                       exec sp_bindsession @bind_token

                ";

                cmd.ExecuteNonQuery();

                cmd.CommandText = @"

                       select *

                       from ##SecretData

                ";

                SqlDataReader dr = cmd.ExecuteReader();

                object[] column = null;

                while (dr.Read())

                {

                    column = new object[3];

                    column[0] = dr[0];

                    column[1] = dr[1];

                    column[2] = dr[2];

                    elements.Add(column);

                }

                dr.Close();

            }

        }

        catch (Exception ex)

        {

        }

        return elements;

    }

    public static void MyFillRowMethod(Object theItem, out int RowId, out string CustomerName, out double Salary)

    {

        object[] row = (object[])theItem;

        RowId = (int)row[0];

        CustomerName = (string)row[1];

        Salary = (double)row[2];

    }

Теперь необходимо выполнить обновление сборки и процедуры. А также создать табличную функцию:

drop function dbo.GetSecretTable

go

drop proc dbo.MakeSecretTable

go

drop assembly SecretData

go

create assembly SecretData from 'C:\Документы\Временные\tmp\Database7\Database7\bin\Debug\Database7.dll'

       with permission_set = external_access

go

create proc dbo.MakeSecretTable ( @SecNum int )

       as external name SecretData.SecretDataWork.CreateSecretTable

go

create function dbo.GetSecretTable ( @password nvarchar ( 100 ) )

       returns table ( RowId int, CustomerName nvarchar ( 1000 ), Salary float )

       as external name SecretData.SecretDataWork.GetSecretData

go

Теперь если запустить такой код:
exec dbo.MakeSecretTable 20

, то в другом соединении можно лишь в течение 20 секунд смотреть данные, запуская такой запрос:

select *

from dbo.GetSecretTable ( '$erfwer#^#$gtet' )

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