[2k5] DDL 트리거 생성, 확인, 삭제, 활성화 및 비활성화

1. /**********************************************************************************************
   -- Title : [2k5] DDL 트리거 생성, 확인, 삭제, 활성화 및 비활성화
   -- Reference : mcpworld.com
   -- Key word : server_triggers create trigger disable enable
   **********************************************************************************************/
   use master
   go
2. drop database ddltrigger;
   go
3. create database ddltrigger;
   go
4. /*
   -- 활용예1: 감사 - 서버 차원에서 로그인과 관련되는 이벤트 추적
   */
   use ddltrigger;
   go
5. create table auditddl_logins (loginlog xml);
   go
6. -- 로그인 감사용 ddl 트리거 생성
   create trigger tr_auditlogins
   on all server
   for create_login, alter_login, drop_login
   as
   insert ddltrigger..auditddl_logins values (eventdata());
   go
7. -- 트리거 확인
   select * from sys.server_triggers;
   go
8. -- 트리거 테스트
   create login login1 with password = '123';
   alter login login1 with password = 'xyz';
   drop login login1;
   go
9. -- 로그 확인
   select * from ddltrigger..auditddl_logins;
   go
10. --ddl 로긴 감사용
    create trigger ddl_trig_login
    on all server
    for ddl_login_events
    as
    print 'login event issued.'
    select eventdata().value
    ('(/event_instance/tsqlcommand/commandtext)[1]','nvarchar(max)');
    go
11. -- 트리거 테스트
    drop login login1
    go
12. -- 트리거 삭제
    drop trigger ddl_trig_login on all server;
    drop trigger tr_auditlogins on all server;
13. /*
    -- 활용예2: ddl 실행 시 별도의 테이블에 ddl 문에 관한 내용을 기록
    */
    use ddltrigger;
    go
14. create table auditddl_createtable (ddllog xml);
    go
     
    -- ddl 트리거 생성
    create trigger tr_createtable1
    on database for create_table
    as
    insert auditddl_createtable values (eventdata());
    go
15. -- ddl문을 실행하고 ddl 트리거가 테이블에 로깅을 제대로 하는지 확인
    create table t1 (c1 int);
    go
16. select * from auditddl_createtable;
    go
17. drop table t1;
    go
    select
     tab.col.value('./eventtype[1]','nvarchar(50)') as 'eventtype',
     tab.col.value('./posttime[1]','datetime') as  'posttime',
     tab.col.value('./spid[1]','nvarchar(50)') as  'spid',
     tab.col.value('./servername[1]','nvarchar(50)') as  'servername',
     tab.col.value('./loginname[1]','nvarchar(50)') as 'loginname',
     tab.col.value('./username[1]','nvarchar(50)') as 'username',
     tab.col.value('./databasename[1]','nvarchar(128)') as 'databasename',
     tab.col.value('./schemaname[1]','nvarchar(128)') as 'schemaname',
     tab.col.value('./objectname[1]','nvarchar(128)') as 'objectname',
     tab.col.value('./objecttype[1]','nvarchar(50)') as 'objecttype',
     tab.col.value('./tsqlcommand[1]/commandtext[1]','nvarchar(4000)') as 'commandtext',
     tab.col.value('./tsqlcommand[1]/setoptions[1]/@ansi_nulls','nvarchar(3)') as 'ansi_nulls_option',
     tab.col.value('./tsqlcommand[1]/setoptions[1]/@ansi_null_default','nvarchar(3)') as 'ansi_null_default_option',
     tab.col.value('./tsqlcommand[1]/setoptions[1]/@ansi_padding','nvarchar(3)') as 'ansi_padding_option',
     tab.col.value('./tsqlcommand[1]/setoptions[1]/@quoted_identifier','nvarchar(3)') as 'quoted_identifier_option',
     tab.col.value('./tsqlcommand[1]/setoptions[1]/@encrypted_option','nvarchar(4)') as 'encrypted_option'
    from auditddl_createtable
    cross apply
    ddllog.nodes('/event_instance') as tab(col);
    go
18. -- 트리거 삭제
    drop trigger tr_createtable1 on database;
    go
19. /*
    -- 활용예3: ddl 실행 시 ddl 문에 관한 내용을 디스플레이
    */
    create trigger tr_createtable2
    on database for create_table
    as
    declare @x xml
    set @x = eventdata()
    select
     tab.col.value('./eventtype[1]','nvarchar(50)') as 'eventtype',
     tab.col.value('./posttime[1]','datetime') as  'posttime',
     tab.col.value('./spid[1]','nvarchar(50)') as  'spid',
     tab.col.value('./servername[1]','nvarchar(50)') as  'servername',
     tab.col.value('./loginname[1]','nvarchar(50)') as 'loginname',
     tab.col.value('./username[1]','nvarchar(50)') as 'username',
     tab.col.value('./databasename[1]','nvarchar(128)') as 'databasename',
     tab.col.value('./schemaname[1]','nvarchar(128)') as 'schemaname',
     tab.col.value('./objectname[1]','nvarchar(128)') as 'objectname',
     tab.col.value('./objecttype[1]','nvarchar(50)') as 'objecttype',
     tab.col.value('./tsqlcommand[1]/commandtext[1]','nvarchar(4000)') as 'commandtext',
     tab.col.value('./tsqlcommand[1]/setoptions[1]/@ansi_nulls','nvarchar(3)') as 'ansi_nulls_option',
     tab.col.value('./tsqlcommand[1]/setoptions[1]/@ansi_null_default','nvarchar(3)') as 'ansi_null_default_option',
     tab.col.value('./tsqlcommand[1]/setoptions[1]/@ansi_padding','nvarchar(3)') as 'ansi_padding_option',
     tab.col.value('./tsqlcommand[1]/setoptions[1]/@quoted_identifier','nvarchar(3)') as 'quoted_identifier_option',
     tab.col.value('./tsqlcommand[1]/setoptions[1]/@encrypted_option','nvarchar(4)') as 'encrypted_option'
    from @x.nodes('/event_instance') as tab(col);
    go
20. -- ddl 트리거 테스트
    create table t4 (c1 int);
    go
21. select * from auditddl_createtable;
    go
22. drop table t1;
    go
23. -- 트리거 삭제
    drop trigger tr_createtable2 on database;
    go
     
    /*
    -- 활용예4: 모든 오브젝트들에 대한 ddl 실행 방지
    */
    use ddltrigger;
    go
24. create table auditddl_all
    (
     stmtid  int    not null identity
            constraint pk_auditddl_all
             primary key clustered,
     loginname sysname   not null,
     username sysname   not null,
     posttime datetime  not null,
     eventtype nvarchar(100) not null,
     ddlstmt  nvarchar(2000) not null
    );
    go
25. create trigger tr_preventallddl on database
    for ddl_database_level_events
    as
    declare @data xml
    set @data = eventdata()
    raiserror ('이 데이터베이스에서는 ddl문의 실행이 금지되어 있습니다 !', 16, -1)
    rollback
    insert auditddl_all (loginname, username, posttime, eventtype, ddlstmt)
    values (system_user, current_user, getdate(),
       @data.value('(/event_instance/eventtype)[1]', 'nvarchar(100)'),
       @data.value('(/event_instance/tsqlcommand)[1]', 'nvarchar(2000)') )
    return;
    go
26. -- ddl 트리거 목록 확인
    select * from sys.triggers
    where parent_class = 0
    and is_disabled = 0;
    go
27. -- ddl 트리거 테스트
    drop trigger tr_createtable2 on database;
    go --> 실패 !!!
    create table t3 (c1 int);
    go --> 실패 !!!
    create index nx_stmtid on auditddl_all(stmtid);
    go --> 실패 !!!
28. select * from auditddl_all;
    go
29. -- 트리거 비활성화
    disable trigger tr_preventallddl on database;
    go
    create table t33 (c1 int);
    go --> 성공 !!!
    enable trigger tr_preventallddl on database;
    go
30. drop trigger tr_createtable2, tr_preventallddl on database;
    go --> 실패 !!!
    drop trigger tr_preventallddl on database;
    go --> 성공 !!!
    drop trigger tr_createtable2 on database;
    go --> 성공 !!!
31. drop table auditddl_all;
    go
     
    -- 카탈로그 뷰에서 확인
    select * from sys.triggers;
    select * from sys.server_triggers;
    go
32. /*테스트가 잘 되지 않았당...ㅡㅡ;; */