SQL Server DDL安全审计

最新推荐文章于 2024-07-28 18:10:51 发布

isoleo

最新推荐文章于 2024-07-28 18:10:51 发布

阅读量1k

点赞数

分类专栏： sql server

sql server 专栏收录该内容

41 篇文章 0 订阅

订阅专栏

应用场景：

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

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

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

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

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

[html]view plaincopy 
   
 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授权：

[sql]view plaincopy 
   
 use master  
 go  
 grant viewserverstate to Audituser  
   
 use AuditDB  
 go  
 grant insertonDBA_ChangedObjectLogtoAudituser  
 grant insertonDBA_ChangedObjectTexttoAudituser  

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

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

[sql]view plaincopy 
   
 grant impersonateonlogin::user1toaudituser  
 grant impersonateonlogin::auditusertouser1  

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

[sql]view plaincopy 
   
 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。

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

[sql]view plaincopy 
   
 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审计，则：

[sql]view plaincopy 
   
 alter database[TEST]set trustworthy on  

然后再建立触发器tr_dba_Trace_ObjectChangeLog

[sql]view plaincopy 
   
 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  