TSQL-Tasks: Service Broker. Настройка диалогов между базами с отключенным trustworthy

Очень часто можно столкнуться с ситуацией, когда требуется реплицировать данные из одной базы данных MS SQL в другую. Лучше всего для этого использовать компонент Service Broker, так как его работа гораздо быстрее запросов через связанные сервера или обычной репликации. И сам компонент очень гибкий. При настройке объектов брокера зачастую владелец обеих баз данных меняется на sa и в базах данных включается свойство trustworthy. Это несколько снижает безопасность, поскольку можно создавать unsafe-сборки, потенциально позволяя злоумышленникам обращаться со стороны сервера к любым внешним ресурсам. Я хочу показать что всего этого можно избежать, если воспользоваться сертификатами. И именно на основе сертификатов настроить безопасность брокера. В этой статье разобран такой пример, когда используется Service Broker для создания асинхронного триггера, который логирует изменения таблицы в другой базе данных. Для этой задачи использование Service Broker в триггере таблицы чрезвычайно удобно, так как изменения реплицируются асинхронно, не заставляя пользователя ждать, когда произойдет запись в таблицу с логами. Для начала создадим две базы данных.

create database SrcData with trustworthy off

create database TrgData with trustworthy off

Базы созданы с отключенным свойством trustworthy. Теперь включим в обеих базах Service Broker:

alter database SrcData set enable_broker with rollback immediate

alter database TrgData set enable_broker with rollback immediate

Теперь создадим таблицу, изменения которой будут логироваться:

use SrcData

if object_id ( N'dbo.Data', N'U' ) is null

begin

create table dbo.Data

(

iRowId int identity ( 1, 1 ) not null,

vcName varchar ( 100 ) not null,

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

constraint AK_Data_vcName unique nonclustered ( vcName asc ) on [PRIMARY]

) on [PRIMARY]

end

go

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

use SrcData

if not exists

(

select *

from sys.service_message_types

where name = 'Src'

)

begin

create message type Src

authorization dbo

validation = well_formed_xml

end

if not exists

(

select *

from sys.service_message_types

where name = 'Trg'

)

begin

create message type Trg

authorization dbo

validation = well_formed_xml

end

if not exists

(

select *

from sys.service_contracts

where name = 'Repl'

)

begin

create contract Repl

authorization dbo

(

Src sent by initiator,

Trg sent by target

)

end

if object_id ( 'dbo.Src', 'SQ' ) is null

begin

create queue dbo.Src

with

status = on,

retention = off

on [DEFAULT]

end

if not exists

(

select *

from sys.services

where name = 'Src'

)

begin

create service Src

authorization dbo

on queue dbo.Src ( Repl )

end

if not exists

(

select *

from sys.remote_service_bindings

where name = 'Repl'

)

begin

create remote service binding Repl

authorization dbo

to service 'Trg'

with user = ReplOper,

anonymous = off;

end

go

Также понадобится пользователь, который будет владеть сертификатом для возможности проверки подлинности при обратных сообщениях из второй базы данных. Дополнительно сделаем заготовку для процедуры рассылки сообщений и предоставим пользователю необходимые права для работы со службой брокера:

use SrcData

if object_id ( 'dbo.SendData', 'P' ) is null

exec ( 'create proc dbo.SendData as return' )

if database_principal_id ( 'ReplOper' ) is null

begin

create user ReplOper without login

end

if database_principal_id ( 'Repl' ) is null

begin

create role Repl

authorization dbo

end

alter role Repl add member ReplOper

grant exec on dbo.SendData to Repl

grant receive on dbo.Src to Repl

grant send on service::Src to Repl

go

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

use SrcData

if not exists

(

select *

from sys.symmetric_keys

where name = '##MS_DatabaseMasterKey##'

)

begin

create master key encryption by password = 'ReplSend@1'

end

set dateformat ymd

if cert_id ( 'Src' ) is null

begin

create certificate Src

authorization dbo

with

subject = 'Передача данных',

start_date= '2015-09-05',

expiry_date = '2050-12-31'

active for begin_dialog = on

end

backup certificate Src to file = 'C:\Users\Src.cer'

go

Создается резервная копия сертификата, которая понадобится в базе данных назначения. Теперь создадим объекты брокера и сертификат во второй базе данных, TrgData. Этот процесс примерно такой же как и для SrcData, только здесь не требуется создавать привязку удаленной службы. Имена типов сообщений и контракта должны точно такими же как и в базе данных источнике. Также потребуется пользователь, для простоты назовем его также ReplOper. Создадим и заготовку для процедуры, разбирающей очередь сообщений.

use TrgData

if not exists

(

select *

from sys.service_message_types

where name = 'Src'

)

begin

create message type Src

authorization dbo

validation = well_formed_xml

end

if not exists

(

select *

from sys.service_message_types

where name = 'Trg'

)

begin

create message type Trg

authorization dbo

validation = well_formed_xml

end

if not exists

(

select *

from sys.service_contracts

where name = 'Repl'

)

begin

create contract Repl

authorization dbo

(

Src sent by initiator,

Trg sent by target

)

end

if object_id ( 'dbo.OperData', 'P' ) is null

exec ( N'create proc dbo.OperData as return' )

if database_principal_id ( 'ReplOper' ) is null

begin

create user ReplOper without login

end

if object_id ( 'dbo.Trg', 'SQ' ) is null

begin

create queue dbo.Trg

with

status = on,

retention = off,

activation

(

status = on,

procedure_name = dbo.OperData,

max_queue_readers = 4,

exec as 'ReplOper'

)

on [DEFAULT]

end

if not exists

(

select *

from sys.services

where name = 'Src'

)

begin

create service Trg

authorization dbo

on queue dbo.Trg ( Repl )

end

if database_principal_id ( 'Repl' ) is null

begin

create role Repl

authorization dbo

end

alter role Repl add member ReplOper

grant exec on dbo.OperData to Repl

grant receive on dbo.Trg to Repl

grant send on service::Trg to Repl

if not exists

(

select *

from sys.symmetric_keys

where name = '##MS_DatabaseMasterKey##'

)

begin

create master key encryption by password = 'ReplOper@1'

end

set dateformat ymd

if cert_id ( 'Trg' ) is null

begin

create certificate dbo

authorization dbo

with

subject = 'Прием данных',

start_date= '2015-09-05',

expiry_date = '2050-12-31'

active for begin_dialog = on

end

backup certificate Trg to file = 'C:\Users\Trg.cer'

go

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

use SrcData

if cert_id ( 'Trg' ) is null

begin

create certificate Trg

authorization ReplOper

from file = 'C:\Users\Trg.cer'

end

use TrgData

if cert_id ( 'Src' ) is null

begin

create certificate Src

authorization ReplOper

from file = 'C:\Users\Src.cer'

end

go

Приступим наконец к программированию процедуры для рассылки сообщений:

use SrcData

alter proc dbo.SendData

(

@xData xml

)

with exec as 'ReplOper'

begin

begin try

declare @uidDialog uniqueidentifier, @vcErr nvarchar ( max )

begin dialog conversation @uidDialog

from service Src to service 'Trg', '57B73C90-4F89-4AE7-9702-A4B37D83E46A'

on contract Repl

with encryption = off

;

send on conversation @uidDialog

message type Src ( @xData )

end try

begin catch

set @vcErr = concat ( error_message (), ' Строка: ', error_line (), '. Процедура: ', error_procedure (), '. Номер: ', error_number () )

; throw 60000, @vcErr, 1

end catch

end

go

При создании диалога, указываем не только имя удаленной службы но и идентификатор типа uniqueidentifier для экземпляра службы Service Broker в базе данных TrgData. Это требуется на случай если на сервере существуют другие базы данных с такими же именами служб. Узнать этот идентификатор можно, сделав такой запрос:

select service_broker_guid

from sys.databases

where name = 'TrgData'

Код ниже создает триггер на таблицу dbo.Data, который будет вызывать процедуру dbo.SendData.

if object_id ( 'dbo.TR_Data_UID', 'TR' ) is null

exec ( 'create trigger dbo.TR_Data_UID on dbo.Data for insert, update, delete as return' )

alter trigger dbo.TR_Data_UID on dbo.Data for insert, update, delete

begin

if @@rowcount = 0

begin

return

end

declare @xData xml

set @xData = (

select iOperType = 1, ins.iRowId, ins.vcName, dtDate = getdate (), vcUser = user_name ()

from

inserted ins

left outer join

deleted del on ins.iRowId = del.iRowId

where del.iRowId is null

for xml path ( 'data' ), root ( 'store' )

)

if @xData is not null

begin

exec dbo.SendData @xData

end

set @xData = (

select iOperType = 2, ins.iRowId, ins.vcName, dtDate = getdate (), vcUser = user_name ()

from

inserted ins

inner join

deleted del on ins.iRowId = del.iRowId

for xml path ( 'data' ), root ( 'store' )

)

if @xData is not null

begin

exec dbo.SendData @xData

end

set @xData = (

select iOperType = 3, del.iRowId, del.vcName, dtDate = getdate (), vcUser = user_name ()

from

inserted ins

right outer join

deleted del on ins.iRowId = del.iRowId

where ins.iRowId is null

for xml path ( 'data' ), root ( 'store' )

)

if @xData is not null

begin

exec dbo.SendData @xData

end

go

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

use TrgData

if object_id ( N'dbo.Users', N'U' ) is null

begin

create table dbo.Users

(

iUserId int identity ( 1, 1 ) not null,

vcUser varchar ( 100 ) not null,

constraint PK_Users_iUserId primary key clustered ( iUserId asc ) on [PRIMARY],

constraint AK_Users_vcUser unique nonclustered ( vcUser asc ) on [PRIMARY]

) on [PRIMARY]

end

if object_id ( N'dbo.DataLog', N'U' ) is null

begin

create table dbo.DataLog

(

iItemId int identity ( 1, 1 ) not null,

iRowId int not null,

vcName varchar ( 100 ) not null,

iOperType tinyint not null,

dtDate datetime not null,

iUserId int not null,

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

constraint FK_Data_iUserId foreign key ( iUserId ) references dbo.Users ( iUserId ) on update cascade on delete no action

) on [PRIMARY]

end

go

Последний шаг состоит в подготовке процедуры активации очереди dbo.Trg. Процедура будет читать сообщения очереди в формате XML и сохранять их в реляционных таблицах.

alter proc dbo.OperData

with exec as 'ReplOper'

begin

declare @uidDialog uniqueidentifier, @xMess xml, @iXml int, @vcErr nvarchar ( max )

begin try

;

receive top ( 1 ) @uidDialog = conversation_handle, @xMess = message_body

from dbo.Trg

if @@rowcount = 0

begin

return

end

exec sp_xml_preparedocument @iXml out, @xMess

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

drop table #Data

create table #Data

(

iItemId int identity ( 1, 1 ) not null,

iOperType tinyint not null,

iRowId int not null,

vcName varchar ( 100 ) not null,

dtDate datetime not null,

vcUser varchar ( 200 ) not null,

primary key clustered ( vcUser asc, iItemId asc ) on [PRIMARY]

) on [PRIMARY]

insert into #Data

(

iOperType,

iRowId,

vcName,

dtDate,

vcUser

)

select

iOperType,

iRowId,

vcName,

dtDate,

vcUser

from openxml ( @iXml, '/store/data', 1 )

with

(

iOperType tinyint 'iOperType',

iRowId int 'iRowId',

vcName varchar ( 100 ) 'vcName',

dtDate datetime 'dtDate',

vcUser varchar ( 200 ) 'vcUser'

)

insert into dbo.Users ( vcUser )

select distinct data.vcUser

from

#Data data

left outer join

dbo.Users us on data.vcUser = us.vcUser

where us.iUserId is null

insert into dbo.DataLog ( iRowId, vcName, iOperType, dtDate, iUserId )

select data.iRowId, data.vcName, data.iOperType, data.dtDate, us.iUserId

from

#Data data

inner join

dbo.Users us on data.vcUser = us.vcUser

end conversation @uidDialog

end try

begin catch

set @vcErr = concat ( error_message (), ' Номер: ', error_number (), '. Строка: ', error_line (), '. Процедура: ', error_procedure () )

end catch

if isnull ( @iXml, 0 ) <> 0

begin

exec sp_xml_removedocument @iXml

end

if isnull ( @vcErr, '' ) <> ''

begin

; throw 60000, @vcErr, 1

end

go

Подобную асинхронную передачу данных можно настраивать и в том случае, если базы данных находятся на разных экземплярах sql-сервера. В этом случае потребуется дополнительно на каждом из серверов создать конечные точки, используя синтаксис create endpoint ... Конечные точки должны иметь тип Service Broker и быть привязаны к определенным портам. Учетным записям необходимо предоставить право на подключение к этим конечным точкам (grant connect on endpoint::...). При настройке безопасности на основе сертификатов данные о сертификате необходимо также прописать в параметрах конечной точки. Также в каждой базе данных потребуется создать маршрут, используя синтаксис create route ... В параметрах маршрута прописывается служба локального сервера и сетевой адрес удаленного сервера, на который требуется передавать данные.
Настройка безопасности с использованием сертификатов при передаче данных с помощью компонента Service Broker между различными серверами позволяет не только не заботиться о свойстве trustworthy, но и о том, под какими учетными записями запускаются сервера: локальными или доменными, административными или нет. Более того сами сервера могут находиться в недружественных доменах или вообще вне доменов.

TSQL-Tasks

Страницы

Страницы

суббота, 5 сентября 2015 г.

Service Broker. Настройка диалогов между базами с отключенным trustworthy

Комментариев нет:

Отправить комментарий

Страницы

Страницы

суббота, 5 сентября 2015 г.

Service Broker. Настройка диалогов между базами с отключенным trustworthy

Комментариев нет:

Отправить комментарий

суббота, 5 сентября 2015 г.