SQL Server DDL安全审计

应用场景：

        作为SQL Server安全审计的一部分，DBA可能需要这样的一份报吿：知道哪些数据库对象(储如表、存储过程、视图、用户、函数、用户权限等等)在什么时候被谁修改过，以及修改的内容等等。也还可能需要一份存储过程、函数以及视图的代码修改历史清单，知道这些代码在去过某段时间里被修改。那么，下面这份代码正好是你所需要的。

        此代码在SQL Server 2005/2008运行良好。

1.      建立一个审计数据库，专门用于存放审计记录；

此步骤略。审计数据库名为：AuditDB;

 

2.      建立一个审计用户，用于实施审计时所需要的权限；

CREATE LOGIN[AuditUser]WITH PASSWORD=N'123',DEFAULT_DATABASE=[AuditDB]
GO
use AuditDB
GO
CREATE USERAudituserFOR LOGIN Audituser;
GO
ALTER LOGIN[AuditUser]DISABLE
GO

注：该用户无需登录，所以可以禁用此用户的登录。

3.      在审计数据库AuditDB上建立下如下审计表：

表1：DBA_ChangedObjectLog(审计主表)

表2：DBA_ChangedObjectText(审计从表) 

注： 

审计主表与审计从表之间的关联字段是LogID;

只有视图、存储过程、函数、触发器才有从表数据，因为只有这些对象才有代码定义；

 

4.      为审计用户AuditUser授权：

use master
go
grant viewserverstate to Audituser

use AuditDB
go
grant insertonDBA_ChangedObjectLogtoAudituser
grant insertonDBA_ChangedObjectTexttoAudituser

5.      为所有可以修改数据库对象的用户，授予模拟AuditUser的权限:

如果你不这么做，那你就必须为所有这些用户分别授予第四步中AuditUser的权限；

grant impersonateonlogin::user1toaudituser
grant impersonateonlogin::auditusertouser1

如果你的数据库中已经有几十上百或上千的用户，那你可以用如下语句生成的代码批量授权：

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

注：以对后新建的每个用户，也必须这样授权。

 

6.      在审计主表上建立触发器tr_Dba_ObjectChangeLog_insert。

此触发器的目的是获得当前修改的数据库对象(视图、存储过程、函数、触发器)代码定义并保存入从表中。

USE [AuditDB]
GO
SET ANSI_NULLSON
GO
SET QUOTED_IDENTIFIERON
GO
 
--在审计主表上建立触发器：
ALTER trigger[dbo].[tr_Dba_ObjectChangeLog_insert]
on [dbo].[DBA_ChangedObjectLog]
for insert
as
begin
    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]
GO
SET ANSI_NULLSON
GO
SET QUOTED_IDENTIFIERON
GO

CREATE 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  
as
begin
    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;
 
end

GO
 
SET ANSI_NULLSOFF
GO
SET QUOTED_IDENTIFIEROFF
GO
ENABLE TRIGGER[tr_dba_Trace_ObjectChangeLog]ON DATABASE
GO