Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Транзакция в триггере  [new]
vpozhidaev
Member

Откуда: Москва
Сообщений: 91
Есть соединение с сиквелом от бизнес приложения. Приложение напрямую через свой spid может работать только с модификацией строк таблиц. Из приложения необходимо открыть симметричные ключи шифрования, это можно сделать передав в таблицу пароль от сертификата, и на триггере запустить открытие ключей. Однако в таком случае ключи становятся открытыми как в таблице так и в профайлере, что само по себе не безопасно.

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

Вот такой способ при работе в одном SPID таблица есть:

CREATE TABLE [dbo].[testtemp](
[id] [int] NOT NULL,
[text] [varchar](30) NOT NULL)

CREATE trigger [dbo].[ti_testtemp]
ON [dbo].[testtemp]
instead of insert
AS
BEGIN
INSERT INTO #testtemp
select ID, text
from inserted
END

CREATE TABLE #testtemp(id INT, TEXT NVARCHAR(30))

insert into [dbo].[testtemp] (id, text)
select 1, 'first'

select *
from #testtemp

drop table #testtemp

А вот такой таблицы уже нет:

DROP trigger [dbo].[ti_testtemp]
CREATE trigger [dbo].[ti_testtemp]
ON [dbo].[testtemp]
instead of insert
AS
BEGIN
CREATE TABLE #testtemp(id INT, TEXT NVARCHAR(30))
INSERT INTO #testtemp
select ID, text
from inserted
END

insert into [dbo].[testtemp] (id, text)
select 1, 'first'

select *
from #testtemp

drop table #testtemp

Вопрос в том что как добиться того чтобы локальная таблица из триггера не удалялась после его завершения, а оставалась висеть на SPID приложения которое дергает таблицу.
20 ноя 12, 18:35    [13502890]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция в триггере  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
vpozhidaev
Вопрос в том что как добиться того чтобы локальная таблица из триггера не удалялась после его завершения, а оставалась висеть на SPID приложения которое дергает таблицу.
Никак.
20 ноя 12, 18:38    [13502908]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция в триггере  [new]
никак,
Guest
vpozhidaev
Вопрос в том что как добиться того чтобы локальная таблица из триггера не удалялась после его завершения, а оставалась висеть на SPID приложения которое дергает таблицу.

совсем никак
20 ноя 12, 18:39    [13502914]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция в триггере  [new]
vpozhidaev
Member

Откуда: Москва
Сообщений: 91
invm,

Печаль. Грусть. Тоска.

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

И есть ли какие нибудь команды для профайлера в sql 2012 и 2005 которые дают понять что текст текущего запроса надо скрыть. Никак не могу нагуглить хоть что нибудь по этому поводу.
20 ноя 12, 18:44    [13502943]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция в триггере  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31962
vpozhidaev
В таком случае появляется другой вопрос, где можно хранить приватную часть ключа на сервере, чтобы ее нельзя было посмотреть?
Можно хранить в контексте коннекта. см.
SET CONTEXT_INFO

vpozhidaev
И есть ли какие нибудь команды для профайлера в sql 2012 и 2005 которые дают понять что текст текущего запроса надо скрыть.
Если в тексте запроса есть слово password (например, хотя-бы в комментарии к запросу), то профайлер этот текст не покажет :-)
Но не могу гарантировать, что нельзя посмотреть как то ещё.
20 ноя 12, 18:50    [13502979]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция в триггере  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
vpozhidaev,

Не давать alter trace всем подряд?
20 ноя 12, 18:50    [13502981]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция в триггере  [new]
Crimean
Member

Откуда:
Сообщений: 13147
можно такую времянку создавать единоразово при установлении соединения с сервером
и, естественно, в триггере ее уже не создавать
да - это будет рекомпайл триггера каждый раз
но ваша задача будет решена - времянка будет живая пока жив конекшен
минус - надо кинуть явный БАТЧ для этого с CREATE TABLE # внутри
20 ноя 12, 18:53    [13503002]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция в триггере  [new]
vpozhidaev
Member

Откуда: Москва
Сообщений: 91
invm,

Да, это первое решение которое я предложил. Только вот руководство не разделяет этой точки зрения. Потому приходится решать через задницу простую казалось бы задачу.
20 ноя 12, 18:53    [13503009]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция в триггере  [new]
vpozhidaev
Member

Откуда: Москва
Сообщений: 91
alexeyvg,

А это до сих пор работает для 2012 сервера? Я проверял на /*password */ SELECT 1
в профайлере видно:
:BatchStarting /*password */ SELECT 1 потом 2 стейтмента старт и конец и конец батча.
20 ноя 12, 18:57    [13503029]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция в триггере  [new]
vpozhidaev
Member

Откуда: Москва
Сообщений: 91
Crimean,

Да, это отличная идея, однако приложение не обладает таким функционалом.

Я думал что может быть есть какое то событие на которое можно повесить триггер, к примеру при аудите входа на сервер создавать такую временную таблицу для этого соединения. И фильтровать такие события по логинам которые подсоединяются.
20 ноя 12, 18:59    [13503042]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция в триггере  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31962
vpozhidaev
alexeyvg,

А это до сих пор работает для 2012 сервера? Я проверял на /*password */ SELECT 1
в профайлере видно:
:BatchStarting /*password */ SELECT 1 потом 2 стейтмента старт и конец и конец батча.
Да, тоже проверил (в 2008 R2) - маскирует только exec sp_change_users_login :-(
20 ноя 12, 19:00    [13503046]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция в триггере  [new]
vpozhidaev
Member

Откуда: Москва
Сообщений: 91
Crimean,

и да кидание этого батча не минус, однако кроме того что приложение меняет строки в таблицах под своим spid'ом оно ничего не умеет. И все приходится делать или внутри триггеров на таблицы или если есть такое события о котором я написал выше.
20 ноя 12, 19:01    [13503058]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция в триггере  [new]
vpozhidaev
Member

Откуда: Москва
Сообщений: 91
alexeyvg,

Маскирует? у меня даже так ничего не замаскировано /*exec sp_change_users_login */ SELECT 1
Те же самые 4 батча. И все видно. В общем как я понимаю цель руководства скрывать все от сисадминов, чтобы никто ни через профайлер никак не мог сделать селект паролей. А исходники процедур с with encryption держать у себя.
20 ноя 12, 19:05    [13503085]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция в триггере  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31962
vpozhidaev
alexeyvg,

Маскирует? у меня даже так ничего не замаскировано /*exec sp_change_users_login */ SELECT 1
Я имел в виду, если она именно в запросе (не в виде комментария)
20 ноя 12, 19:13    [13503144]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция в триггере  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
vpozhidaev
Я думал что может быть есть какое то событие на которое можно повесить триггер, к примеру при аудите входа на сервер создавать такую временную таблицу для этого соединения. И фильтровать такие события по логинам которые подсоединяются.
Есть такой триггер, только не поможет -- временная таблица также будет убита при выходе из триггера.
20 ноя 12, 19:25    [13503246]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция в триггере  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
vpozhidaev,

Интересные проблемы...
А что, просто процедуру вызвать из приложения нельзя?
20 ноя 12, 19:45    [13503329]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция в триггере  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
invm
vpozhidaev
Вопрос в том что как добиться того чтобы локальная таблица из триггера не удалялась после его завершения, а оставалась висеть на SPID приложения которое дергает таблицу.
Никак.

Вообще по идее можно использовать временную таблицу, созданную не в триггере.
Но я не говорю, что нужно...
20 ноя 12, 19:48    [13503338]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция в триггере  [new]
Storkich
Member

Откуда: Москва "Крок"
Сообщений: 5
Видимо приложение с закрытым исходным кодом. Вот и лепят костыли.
20 ноя 12, 19:52    [13503348]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция в триггере  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
vpozhidaev
Был придуман способ создать временную таблицу только для этого SPID и работать через ассиметричные ключи, храня приватную часть ключа в временной таблице.
При желании и наличии полномочий можно прочитать данные из локальных временных таблиц других соединений.
20 ноя 12, 20:21    [13503434]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция в триггере  [new]
Crimean
Member

Откуда:
Сообщений: 13147
ой, криптованные-то объекты открываются на 1-2-3. CLR вам в руки. тама внутри ведите что хотите. тока аккуратно
по секрету - от грамотного "злого" одмина это не защита все равно - ключи на сервере - зло большое есть по определению
защититесь вы так только от "доброго" одмина
передавать-то ключ будете обычной SQL командой. а она трасится. дальше все просто
20 ноя 12, 21:31    [13503641]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция в триггере  [new]
vpozhidaev
Member

Откуда: Москва
Сообщений: 91
Crimean,

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

А про CLR что именно ты имел ввиду? Возможность их держать в себе какое то значение? которое будет сохраняться после 1 вызова и использоваться к примеру во 2-ом?
21 ноя 12, 16:47    [13508988]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция в триггере  [new]
vpozhidaev
Member

Откуда: Москва
Сообщений: 91
invm,

Да при желании можно и любую скриптованную процедуру перенакатить.
21 ноя 12, 16:48    [13508994]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция в триггере  [new]
Crimean
Member

Откуда:
Сообщений: 13147
vpozhidaev,

именно так
хоть CLR хоть DLL, ODS API даже в 2012 работает
21 ноя 12, 17:06    [13509183]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция в триггере  [new]
vpozhidaev
Member

Откуда: Москва
Сообщений: 91
Crimean,

Ты уже делал подобные вещи? не подскажешь откуда лучше взять образец того что необходимо? Если делал уже, и если не влом особо :)
21 ноя 12, 19:24    [13509867]     Ответить | Цитировать Сообщить модератору
 Re: Транзакция в триггере  [new]
vpozhidaev
Member

Откуда: Москва
Сообщений: 91
Crimean,

И да, спасибо тебе огромное в любом случае за помощь!
21 ноя 12, 19:24    [13509869]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить