Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 различить вставку, осуществляемую триггером  [new]
o-o
Guest
у начальницы творческий подъем, сказала, истребим в продакшене всех db_owner-ов
(100% что этого не будет, но почему бы не помечтать),
останутся лишь db_datareader-ы и db_datawriter-ы.
и теперь она желает "защитить" таблицу, куда пишет мой закриптованный DDL-триггер
от "посторонних" вставок.
т.е. навесить на саму таблицу лога DML триггер на INSERT,
чтобы писать в лог мог только DDL-триггер.

я вообще не знаю, как такое реализовать.
есть ли нечто, указывающее на вставку DDL-триггером,
и чтобы это было видно внутри DML-триггера?
-------------------------------
у меня есть черезжопная идея: добавить бинарную колонку в лог,
и чтоб в нее DDL-триггер писал нечто осмысленное, переменное во времени, закриптованное.
тогда при просмотре таблицы, раскриптовывая ту колонку, станет видно,
триггер ее заполнил или кто-то от балды туда чего-то навалил или скопировал байтики из соседней строки,
т.к. значение должно быть уникальным, ну что-то + datetime2, например.
не совсем то, что хочет мадам, но неважно

+
казалось бы, какой вообще может быть DDL,
если кругом одни только чтецы/писатели?
на самом деле, у начальницы после навешивания моего триггера открылись глаза:
никто не видит "приходящих по ночам юзеров", хотя они есть
ночью происходит примерно такое: приходит джоб, создает юзера, запихивает его в бэкапоператор, бэкапит.
потом то же самое проделывает для перестроения индексов и таблиц с компрессией.
проделавший работу юзер удаляется, и она таких юзеров никогда не видит в ГУИ.
теперь у нас мания шпионажа, раз вскрылись "тайные агенты", от них надо обезопаситься,
а что у них хватит прав на отрубание любого триггера, мы слышать не хотим

короче, к моим темам прошу относиться "философски": хотелки не из серьезной системы поступают,
а из цирка, поэтому бесполезность можно и не обсуждать
5 дек 13, 12:43    [15245465]     Ответить | Цитировать Сообщить модератору
 Re: различить вставку, осуществляемую триггером  [new]
Glory
Member

Откуда:
Сообщений: 104751
Не проще ли настроить аудит ?
5 дек 13, 12:54    [15245587]     Ответить | Цитировать Сообщить модератору
 Re: различить вставку, осуществляемую триггером  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
o-o,

ну, trigger_nestlevel() и для ddl-триггеров работает. только иметь в виду, что object_id триггера надо из sys.triggers получать - функцией object_id() не получится.
5 дек 13, 13:13    [15245781]     Ответить | Цитировать Сообщить модератору
 Re: различить вставку, осуществляемую триггером  [new]
Glory
Member

Откуда:
Сообщений: 104751
daw
что object_id триггера надо из sys.triggers получать - функцией object_id() не получится.

@@procid
5 дек 13, 13:14    [15245792]     Ответить | Цитировать Сообщить модератору
 Re: различить вставку, осуществляемую триггером  [new]
o-o
Guest
Glory,

в каком виде аудит?
DDL-триггер -- это и есть наш аудит.
изначально требовалось только залогировать, кто и когда процедуры правил.
ее идея, как я понимаю, обезопасить таблицу аудита от каких-то левых вставок
5 дек 13, 13:15    [15245802]     Ответить | Цитировать Сообщить модератору
 Re: различить вставку, осуществляемую триггером  [new]
Glory
Member

Откуда:
Сообщений: 104751
o-o
Glory,

в каком виде аудит?
DDL-триггер -- это и есть наш аудит.

BOL - Understanding SQL Server Audit
5 дек 13, 13:17    [15245824]     Ответить | Цитировать Сообщить модератору
 Re: различить вставку, осуществляемую триггером  [new]
o-o
Guest
так, иду пробовать @@procid и trigger_nestlevel().
спасибо Glory и daw.

...да не воспринимайте вы ее так серьезно, ничего из этого никогда не попадет в продакшн,
я изучаю для себя, а ее фантазии мне только направления указывают, где у меня пробелы.
я даже сомневаюсь, верит ли она сама в то, что придумывает.
5 дек 13, 13:21    [15245863]     Ответить | Цитировать Сообщить модератору
 Re: различить вставку, осуществляемую триггером  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
Glory,

@@procid не пойдет. для функции trigger_nestlevel, которую можно вызвать в триггере на таблицу логов, нужен object_id ddl-триггера.
5 дек 13, 13:25    [15245919]     Ответить | Цитировать Сообщить модератору
 Re: различить вставку, осуществляемую триггером  [new]
Glory
Member

Откуда:
Сообщений: 104751
daw
@@procid не пойдет. для функции trigger_nestlevel, которую можно вызвать в триггере на таблицу логов, нужен object_id ddl-триггера.

@@procid должна давать команда вставки в таблицу логов.
Потому что вставку в эту таблицу должны по условию задачи делать только другие триггера.
5 дек 13, 13:28    [15245952]     Ответить | Цитировать Сообщить модератору
 Re: различить вставку, осуществляемую триггером  [new]
o-o
Guest
сейчас еще посмотрю, что такое @@procid,
а пока вот такой вариант работает:

+
CREATE TABLE ddl_hist(
	id int IDENTITY(1,1) NOT NULL,
	dt datetime NULL DEFAULT getdate(),
	user_ varchar(40) NULL DEFAULT suser_sname(),
	event_ varchar(100) NULL,
	tsql_ varchar(max) NULL,
 CONSTRAINT PK_ddl_hist PRIMARY KEY CLUSTERED(id))
 
create trigger [tg_ddl_hist]
on database
--with encryption
for ddl_database_level_events
as
set nocount on;
declare @d xml;
set @d = EVENTDATA();
insert into ddl_hist(event_, tsql_) 
values ( @d.value('(EVENT_INSTANCE/EventType)[1]','varchar(100)'),
         @d.value('(EVENT_INSTANCE/TSQLCommand)[1]','varchar(max)'))


create trigger dbo.ddl_hist_ins
on dbo.ddl_hist
instead of insert as

begin try
	if TRIGGER_NESTLEVEL ( ( select object_id from sys.triggers
	where name = 'tg_ddl_hist' ), 'AFTER' , 'DDL' ) = 1
		insert into dbo.ddl_hist(event_, tsql_)
		select event_, tsql_ from inserted
end try
begin catch
	if @@trancount > 0
	rollback transaction
end catch	
5 дек 13, 14:37    [15246686]     Ответить | Цитировать Сообщить модератору
 Re: различить вставку, осуществляемую триггером  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
Можно таблицу аудита унести в отдельную БД. Взаимодействие с ней организовать, например, через Service Broker.
5 дек 13, 14:47    [15246784]     Ответить | Цитировать Сообщить модератору
 Re: различить вставку, осуществляемую триггером  [new]
o-o
Guest
invm,

для начала у нас нет серверных прав, в т.ч. CREATE ANY DATABASE,
но когда совсем нечем будет заняться, почитаю, что за зверь Service Broker, спасибо.
5 дек 13, 15:06    [15246935]     Ответить | Цитировать Сообщить модератору
 Re: различить вставку, осуществляемую триггером  [new]
o-o
Guest
вот полдня прошло, а до меня так и не дошло, как можно передавать из одного триггера в другой его собственный @@procid.
только "вставляя" и @@procid тоже?
у меня получилось так:
 CREATE TABLE ddl_hist(
	id int IDENTITY(1,1) NOT NULL,
	dt datetime NULL DEFAULT getdate(),
	user_ varchar(40) NULL DEFAULT suser_sname(),
	event_ varchar(100) NULL,
	tsql_ varchar(max) NULL,
	o_o int NULL DEFAULT 0,
 CONSTRAINT PK_ddl_hist PRIMARY KEY CLUSTERED(id))
 
create trigger [tg_ddl_hist]
on database
--with encryption
for ddl_database_level_events
as
set nocount on;
declare @d xml;
set @d = EVENTDATA();
insert into ddl_hist(event_, tsql_, o_o) 
values ( @d.value('(EVENT_INSTANCE/EventType)[1]','varchar(100)'),
         @d.value('(EVENT_INSTANCE/TSQLCommand)[1]','varchar(max)'),
         @@procid)
         

create trigger dbo.ddl_hist_ins
on dbo.ddl_hist
instead of insert as
set nocount on;
begin try
	if (select object_id 
	   from sys.triggers
	   where name = 'tg_ddl_hist')  = (select o_o from inserted)
		insert into dbo.ddl_hist(event_, tsql_)
		select event_, tsql_ from inserted
end try
begin catch
	if @@trancount > 0
	rollback transaction
end catch


особого смысла не вижу, разве что если имеется туча триггеров и надо различить, какой из них вставлял в лог.
5 дек 13, 19:56    [15249064]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить