SQL Server DDL安全審計

應用情境：

        作為SQL Server安全審計的一部分，DBA可能需要這樣的一份報吿：知道哪些資料庫物件(儲如表、預存程序、視圖、使用者、函數、使用者權限等等)在什麼時候被誰修改過，以及修改的內容等等。也還可能需要一份預存程序、函數以及視圖的代碼修改曆史清單，知道這些代碼在去過某段時間裡被修改。那麼，下面這份代碼正好是你所需要的。

        此代碼在SQL Server 2005/2008運行良好。

1.      建立一個審計資料庫，專門用於存放審計記錄；

此步驟略。審計資料庫名為：AuditDB;

 

2.      建立一個審計使用者，用於實施審計時所需要的許可權；

CREATE LOGIN[AuditUser]WITH PASSWORD=N'123',DEFAULT_DATABASE=[AuditDB]GOuse AuditDBGOCREATE USERAudituserFOR LOGIN Audituser;GOALTER LOGIN[AuditUser]DISABLEGO

註：該使用者無需登入，所以可以禁用此使用者的登入。

3.      在審計資料庫AuditDB上建立下如下審計表：

表1：DBA_ChangedObjectLog(審計主表)

表2：DBA_ChangedObjectText(審計從表) 

註： 

審計主表與審計從表之間的關聯欄位是LogID;

只有視圖、預存程序、函數、觸發器才有從表資料，因為只有這些對象才有代碼定義；

 

4.      為審計使用者AuditUser授權：

use mastergogrant viewserverstate to Audituseruse AuditDBgogrant insertonDBA_ChangedObjectLogtoAuditusergrant insertonDBA_ChangedObjectTexttoAudituser

5.      為所有可以修改資料庫物件的使用者，授予類比AuditUser的許可權:

如果你不這麼做，那你就必須為所有這些使用者分別授予第四步中AuditUser的許可權；

grant impersonateonlogin::user1toauditusergrant impersonateonlogin::auditusertouser1

如果你的資料庫中已經有幾十上百或上千的使用者，那你可以用如下語句產生的程式碼大量授權：

select 'grant impersonate on login::['+[name]+'] to audituser'fromsys.server_principalswhere type='S'andname<>'audituser'union allselect 'grant impersonate onlogin::audituser to ['+[name]+']'fromsys.server_principalswhere type='S'andname<>'audituser'

註：以對後建立的每個使用者，也必須這樣授權。

 

6.      在審計主表上建立觸發器tr_Dba_ObjectChangeLog_insert。

此觸發器的目的是獲得當前修改的資料庫物件(視圖、預存程序、函數、觸發器)代碼定義並儲存入從表中。

USE [AuditDB]GOSET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGO --在審計主表上建立觸發器：ALTER trigger[dbo].[tr_Dba_ObjectChangeLog_insert]on [dbo].[DBA_ChangedObjectLog]for insertasbegin    declare       @Logid uniqueidentifier,       @DatabaseNamenvarchar(256),       @UserNamenvarchar(256),       @SchemaNamenvarchar(256),       @ObjectNamenvarchar(256),       @ObjectTypenvarchar(256),       --@original_usernvarchar(256),       @EventTypenvarchar(256),       @sql nvarchar(256),       @IsaDDLTriggerint        execute as login =original_login()        set @IsaDDLTrigger= -1       select @Logid=logid,@DatabaseName=DatabaseName,@SchemaName=SchemaName,@ObjectName=ObjectName,@ObjectType=ObjectType,@UserName=UserName,@EventType=EventType       from inserted;        if  @ObjectType not in('PROCEDURE','VIEW','FUNCTION','TRIGGER') return;       if @EventType like 'DROP%'return;        set @sql = N'select @IsaDDLTriggerOut=count(*) from ['+@DatabaseName+'].sys.triggers where name='''+@ObjectName+''' and parent_class=0';       execute sp_executesql@sql,N'@IsaDDLTriggerOut int OUTPUT',@IsaDDLTriggerOut=@IsaDDLTriggerOUTPUT;        --DDL觸發器不能使用sp_helptext獲得它的文本       if @IsaDDLTrigger>0 return;            if object_id('tempdb..#temp')isnot null           drop table #temp        create table #temp(           [Line][int]IDENTITY(1,1)NOTNULL,           [LineText]varchar(max)       )        set @sql='insert into #temp exec ['+@DatabaseName+'].dbo.sp_helptext '''+@SchemaName+'.'+@ObjectName+'''';       exec (@sql)          execute as caller       insert into dbo.Dba_ChangedObjectText       select @Logid,@DatabaseName,@SchemaName,@ObjectName,[Line],[LineText]from#temp        drop table #temp end

7.      在要實施審計的資料庫上建立觸發器tr_dba_Trace_ObjectChangeLog：

建立之前，需要對要實施審計的資料庫如下配置。例如你需要對資料庫TEST進行DDL審計，則：

alter database[TEST]set trustworthy on

然後再建立觸發器tr_dba_Trace_ObjectChangeLog

USE [TEST]GOSET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGOCREATE trigger[tr_dba_Trace_ObjectChangeLog]on database--withexecute as 'AuditUser'for ddl_table_events,ddl_view_events,ddl_index_events,    ddl_synonym_events,ddl_function_events,ddl_procedure_events,ddl_trigger_events,     ddl_database_security_events--,ddl_rule_events  asbegin    set nocount on     declare       @newid UNIQUEIDENTIFIER,        @data xml,       @spid smallint,       @LoginNamevarchar(256),       @ProgramNamevarchar(256),       @IP varchar(15),       @mac varchar(12),       @EventTypevarchar(50),       @ObjectTypevarchar(25),       @ObjectNamevarchar(256),       @IsaDDLTriggerint,       @Line smallint     set @newid = newid()    set @data = EVENTDATA()    set @spid = @data.value('(/EVENT_INSTANCE/SPID)[1]','smallint')    set @EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]','varchar(50)')    set @ObjectType = @data.value('(/EVENT_INSTANCE/ObjectType)[1]','varchar(25)')    set @ObjectName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(256)')    set @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(256)')    --set @ObjectName= @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')    set @mac =(SELECTNET_ADDRESSFROM master.dbo.sysprocessesWHEREspid = @spid)    set @Line = 0     --過濾掉由維護計劃產生的重建索引記錄(因此類記錄太龐大)    if @LoginName='NT AUTHORITY\SYSTEM'and@ObjectType= 'INDEX'       return;     -- if the objectis a ddl trigger    if (selectcount(*)from sys.triggerswherename=@ObjectNameandparent_class=0)= 0       set @IsaDDLTrigger = 0    else set @IsaDDLTrigger = 1     execute as login ='AuditUser'     select @ProgramName=[Program_Name]fromsys.dm_exec_sessionswheresession_id=@spid    select @IP=client_net_addressfromsys.dm_exec_connectionswheresession_id=@spid     insert into DBAdminPlat.dbo.DBA_ChangedObjectLog(       [LogId],       [EventType],[PostTime],[SPID],[ServerName],[Host_IP_Address],[Host_MAC_Address],       [ProgramName],[LoginName],[UserName],[DatabaseName],[SchemaName],       [ObjectName],[ObjectType],[TSQLCommand]--,[EventDate]    )    values(       @newid,       @EventType,       @data.value('(/EVENT_INSTANCE/PostTime)[1]','datetime'),       @spid,       @data.value('(/EVENT_INSTANCE/ServerName)[1]','varchar(256)'),       @IP,       @mac,       @ProgramName,       @LoginName,--@data.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(256)'),       @data.value('(/EVENT_INSTANCE/UserName)[1]','varchar(256)'),       @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(256)'),       @data.value('(/EVENT_INSTANCE/SchemaName)[1]','varchar(256)'),       @ObjectName,       @ObjectType,       --For objectslike procs, views, triggers and functions ,text of which will be stored inmaster.[dbo].[tb_dba_ChangedObjectText] table.       case when @ObjectType in('PROCEDURE','VIEW','FUNCTION','TRIGGER')and(@EventType<>'GRANT_DATABASE'or@EventType like 'DROP%')and@IsaDDLTrigger=0           then null else @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','varchar(max)')end    );     REVERT; endGO SET ANSI_NULLSOFFGOSET QUOTED_IDENTIFIEROFFGOENABLE TRIGGER[tr_dba_Trace_ObjectChangeLog]ON DATABASEGO