DDL-триггер и переименование объекта

1. Как известно, в SQL Server 2005 появилась возможность навешивать триггера не только на операции изменения данных (DML), но и на декларативные (DDL) операции - CREATE / ALTER / DROP всевозможных таблиц, представлений, процедур, пользователей и пр.объектов как уровня базы, так и уровня сервера. Полный список событий, для которых можно создать DDL-триггер, находится здесь: http://msdn.microsoft.com/ru-ru/library/bb522542.aspx. Аналогом служебных таблиц inserted/deleted для DDL-триггера является функция EVENTDATA(), выдающая служебную информацию о том, кто, где, когда, что сделал. Для удобства события объединяются в группы событий, чтобы муторно не перечислять при создании, что этот триггер должен вспыхивать, например, на create table, alter table, drop table. Группы событий перечислены здесь - http://msdn.microsoft.com/ru-ru/library/bb510452.aspx. Поэтому если вы не знаете, как в точности называется событие, на которое вы хотите повесить триггер, вешайте на общую группу и смотрите в результатах EVENTDATA(), что конкретно его торкнуло.

if exists(select 1 from sys.triggers where parent_class_desc = 'database' and name = 'tr1')

drop trigger tr1 on database

go

create trigger tr1 on database for DDL_DATABASE_LEVEL_EVENTS as

select EVENTDATA()

go

if OBJECT_ID('t', 'U') is not null drop table t

create table t(id int)

go

<EVENT_INSTANCE>

  <EventType>CREATE_TABLE</EventType>

  <PostTime>2009-12-05T01:33:09.550</PostTime>

  <SPID>52</SPID>

  <ServerName>W7X86SQL08R2</ServerName>

  <LoginName>W7X86SQL08R2\Administrator</LoginName>

  <UserName>dbo</UserName>

  <DatabaseName>tempdb</DatabaseName>

  <SchemaName>dbo</SchemaName>

  <ObjectName>t</ObjectName>

  <ObjectType>TABLE</ObjectType>

  <TSQLCommand>

    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />

    <CommandText>create table t(id int)

</CommandText>

  </TSQLCommand>

</EVENT_INSTANCE>

Скрипт 1

 

В данном случае видно, что триггер сработал в ответ на событие CREATE_TABLE несмотря на то, что был создан на более общую группу DDL_DATABASE_LEVEL_EVENTS.

В отличие от DML-триггеров DDL-триггеры бывают только after. Поддерживается более одного триггера на событие. Результат представляет суперпозицию результатов триггеров, в том числе созданных на более общие события, но зацепляющих произошедшее, в порядке их создания.

 

image

Рис.1

Из иллюстрации видно, что первые две строчки - результат срабатывания триггера tr1 на DDL_DATABASE_LEVEL_EVENTS (Скрипт 1), за которым последовательно идут tr2 на create_table, alter_table и tr3, tr4 на create_table.

Записывая всякий раз результат EVENTDATA() в таблицу, можно построить мониторинг и журналирование изменений над объектами SQL Server на основе DDL-триггеров, что особенно ценно в стандартной и младших редакциях, где нет оператора create audit.

 

2. Независимо от редакции в T-SQL нет команды ALTER TABLE ... RENAME TO ... Переименование таблиц и прочих объектов производится при помощи хранимой процедуры sp_rename. Новичков это, как правило, обескураживает. Процедура или оператор - дело десятое. Обескураживает то, что переименование, будучи по сути изменением объекта, не вызывало (до недавних пор) срабатывание триггера на изменение. Объяснялось это тем, что в SQL Server название объекта хранится отдельно. Определение объекта лежит в другом месте. DDL-триггеры настроены на то место, где хранится определение. Изменение названия их не задевало. Однако тем или другим местом это сильно задевало народ, который не желал слушать умных объяснений, а руководствовался здравым смыслом.

 

3. По многочисленным пожеланиям трудящихся в SQL Server 2008 R2 CTP3 ситуацию исправили - http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=283525. Появилось событие RENAME, триггер на которое ловит факт переименования объекта.

use tempdb

if exists(select 1 from sys.triggers where parent_class_desc = 'database' and name = 'tr1')

drop trigger tr1 on database

go

create trigger tr1 on database for RENAME as

select EVENTDATA()

go

if OBJECT_ID('t', 'U') is not null drop table t

create table t(id int)

go

if OBJECT_ID('t1', 'U') is not null drop table t1

exec sp_rename @objname = 't', @newname = 't1', @objtype = 'object'

Скрипт 2

 

image

Рис.2

 

Информация о событии содержит старое и новое имена.

<EVENT_INSTANCE>

  <EventType>RENAME</EventType>

  <PostTime>2009-12-05T02:10:19.673</PostTime>

  <SPID>52</SPID>

  <ServerName>W7X86SQL08R2</ServerName>

  <LoginName>W7X86SQL08R2\Administrator</LoginName>

  <UserName>dbo</UserName>

  <DatabaseName>tempdb</DatabaseName>

  <SchemaName>dbo</SchemaName>

  <ObjectName>t</ObjectName>

  <ObjectType>TABLE</ObjectType>

  <TargetObjectName />

  <TargetObjectType />

  <NewObjectName>t1</NewObjectName>

  <Parameters>

    <Param>t</Param>

    <Param>t1</Param>

    <Param>object</Param>

  </Parameters>

  <TSQLCommand>

    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />

    <CommandText>exec sp_rename @objname = 't', @newname = 't1', @objtype = 'object'</CommandText>

  </TSQLCommand>

</EVENT_INSTANCE>

Скрипт 3

 

4. sp_rename не была единственным исключением. Например, управление членством в ролях осуществляется при помощи хранимых процедур sp_addsrvrolemember/ sp_dropsrvrolemember для серверных ролей и sp_addrolemember/sp_droprolemember для датабазных. Как и sp_rename, они тоже пролетали мимо кассы. Всякие GRANT/DENY ловились, а добавление или удаление члена из роли не вызывало срабатывание DDL-триггера. Нынче с энтим безобразием тоже покончено.

use master

if exists(select 1 from sys.server_principals where name = 'alexejs') drop login alexejs

create login alexejs with password = 'Abra Cadabrae', check_policy = off, default_database = AdventureWorks2008R2

if exists(select 1 from sys.server_triggers where name = 'tr1')

drop trigger tr1 on all server

go

create trigger tr1 on all server for ADD_SERVER_ROLE_MEMBER, DROP_SERVER_ROLE_MEMBER as

select EVENTDATA()

go

 

use AdventureWorks2008R2

if exists(select 1 from sys.database_principals where name = 'alexejs') drop user alexejs

create user alexejs from login alexejs

 

if exists(select 1 from sys.triggers where parent_class_desc = 'database' and name = 'tr1')

drop trigger tr1 on database

go

create trigger tr1 on database for ADD_ROLE_MEMBER, DROP_ROLE_MEMBER as

select EVENTDATA()

go

 

exec sp_addrolemember @membername = 'alexejs', @rolename = 'db_datawriter'

exec sp_addsrvrolemember @loginame = 'alexejs', @rolename = 'sysadmin'

 

---

<EVENT_INSTANCE>

  <EventType>ADD_ROLE_MEMBER</EventType>

  <PostTime>2009-12-05T02:45:58.893</PostTime>

  <SPID>52</SPID>

  <ServerName>W7X86SQL08R2</ServerName>

  <LoginName>W7X86SQL08R2\Administrator</LoginName>

  <UserName>dbo</UserName>

  <DatabaseName>AdventureWorks2008R2</DatabaseName>

  <ObjectName>alexejs</ObjectName>

  <ObjectType>SQL USER</ObjectType>

  <DefaultSchema>dbo</DefaultSchema>

  <SID>NyocvZkMcEmSpfdMFvyHvQ==</SID>

  <RoleName>db_datawriter</RoleName>

  <TSQLCommand>

    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />

    <CommandText>exec sp_addrolemember @membername = 'alexejs', @rolename = 'db_datawriter'

</CommandText>

  </TSQLCommand>

</EVENT_INSTANCE>

 

<EVENT_INSTANCE>

  <EventType>ADD_SERVER_ROLE_MEMBER</EventType>

  <PostTime>2009-12-05T02:45:58.897</PostTime>

  <SPID>52</SPID>

  <ServerName>W7X86SQL08R2</ServerName>

  <LoginName>W7X86SQL08R2\Administrator</LoginName>

  <ObjectName>alexejs</ObjectName>

  <ObjectType>LOGIN</ObjectType>

  <DefaultLanguage>us_english</DefaultLanguage>

  <DefaultDatabase>AdventureWorks2008R2</DefaultDatabase>

  <LoginType>SQL Login</LoginType>

  <SID>NyocvZkMcEmSpfdMFvyHvQ==</SID>

  <RoleName>sysadmin</RoleName>

  <RoleSID>Aw==</RoleSID>

  <TSQLCommand>

    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />

    <CommandText>exec sp_addsrvrolemember @loginame = 'alexejs', @rolename = 'sysadmin'</CommandText>

  </TSQLCommand>

</EVENT_INSTANCE>

Скрипт 4

 

В примере создается логин, создается серверный DDL-триггер tr1 на добавление/удаление логина в серверную роль. Создаем пользователя в базе на основании этого логина. Создаем DDL-триггер tr1 уровня базы на добавление/удаление пользователя в роль БД. Тестируем, включая пользователя в датабазную роль и логина в серверную. Видим, что все поймалось.

 

image

Рис.3

 

5. Домашнее задание.

Задача.

Девушка Юля является администратором сервера isvrus. Желая пройти сертификацию Works with SQL Server 2008, она согласно Требованию 2 решает очистить серверную роль sysadmin от всяких сомнительных личностей.

image

Рис.4

Какой DDL-триггер на какое событие должен написать пользователь alexejs, чтобы сейчас, когда девушка Юля радостно нажмет кнопку Remove, из сисадминов вынесет не его, а кого-то другого?

 

Алексей Шуленин