SQL Server DDL安全审计

雨点打透心脏的1/2处 2022-08-10 11:48 223阅读 0赞

应用场景：

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

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

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

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

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

**\[html\]**  [view plain][] [copy][view plain]

1.  CREATE LOGIN\[AuditUser\]WITH PASSWORD=N'123',DEFAULT\_DATABASE=\[AuditDB\]  
2.  GO  
3.  use AuditDB  
4.  GO  
5.  CREATE USERAudituserFOR LOGIN Audituser;  
6.  GO  
7.  ALTER LOGIN\[AuditUser\]DISABLE  
8.  GO

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

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

表1：DBA\_ChangedObjectLog(审计主表)

![20130517160847413][]

表2：DBA\_ChangedObjectText(审计从表)

![20130517160912360][]

注：

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

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

**4.      为审计用户AuditUser授权：**

**\[sql\]**  [view plain][] [copy][view plain]

1.  use master  
2.  go  
3.  **grant** viewserverstate **to** Audituser  
4.    
5.  use AuditDB  
6.  go  
7.  **grant** insertonDBA\_ChangedObjectLogtoAudituser  
8.  **grant** insertonDBA\_ChangedObjectTexttoAudituser

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

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

**\[sql\]**  [view plain][] [copy][view plain]

1.  **grant** impersonateonlogin::user1toaudituser  
2.  **grant** impersonateonlogin::auditusertouser1

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

**\[sql\]**  [view plain][] [copy][view plain]

1.  **select** 'grant impersonate on login::\['\+\[**name**\]+'\] to audituser'fromsys.server\_principals  
2.  **where** type='S'andname<>'audituser'  
3.  **union** all  
4.  **select** 'grant impersonate onlogin::audituser to \['\+\[**name**\]+'\]'fromsys.server\_principals  
5.  **where** type='S'andname<>'audituser'

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

**6.      在审计主表上建立触发器tr\_Dba\_ObjectChangeLog\_insert。**

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

**\[sql\]**  [view plain][] [copy][view plain]

1.  USE \[AuditDB\]  
2.  GO  
3.  **SET** ANSI\_NULLSON  
4.  GO  
5.  **SET** QUOTED\_IDENTIFIERON  
6.  GO  
7.     
8.  \--在审计主表上建立触发器：  
9.  **ALTER** **trigger**\[dbo\].\[tr\_Dba\_ObjectChangeLog\_insert\]  
10. **on** \[dbo\].\[DBA\_ChangedObjectLog\]  
11. **for** **insert**  
12. **as**  
13. **begin**  
14.     **declare**  
15.        @Logid uniqueidentifier,  
16.        @DatabaseNamenvarchar(256),  
17.        @UserNamenvarchar(256),  
18.        @SchemaNamenvarchar(256),  
19.        @ObjectNamenvarchar(256),  
20.        @ObjectTypenvarchar(256),  
21.        \--@original\_usernvarchar(256),  
22.        @EventTypenvarchar(256),  
23.        @sql nvarchar(256),  
24.        @IsaDDLTriggerint  
25.    
26.        **execute** **as** login =original\_login()  
27.    
28.        **set** @IsaDDLTrigger= -1  
29.        **select** @Logid=logid,@DatabaseName=DatabaseName,@SchemaName=SchemaName,@ObjectName=ObjectName,@ObjectType=ObjectType,@UserName=UserName,@EventType=EventType  
30.        **from** inserted;  
31.    
32.        if  @ObjectType not in('PROCEDURE','VIEW','FUNCTION','TRIGGER') **return**;  
33.        if @EventType like 'DROP%'**return**;  
34.    
35.        **set** @sql = N'select @IsaDDLTriggerOut=count(\*) from \['\+@DatabaseName+'\].sys.triggers where name='''\+@ObjectName+''' and parent\_class=0';  
36.        **execute** sp\_executesql@sql,N'@IsaDDLTriggerOut int OUTPUT',@IsaDDLTriggerOut=@IsaDDLTriggerOUTPUT;  
37.    
38.        \--DDL触发器不能使用sp\_helptext获得它的文本  
39.        if @IsaDDLTrigger>0 **return**;      
40.    
41.        if object\_id('tempdb..\#temp')isnot null  
42.            **drop** **table** \#**temp**  
43.    
44.        **create** **table** \#**temp**(  
45.            \[Line\]\[**int**\]IDENTITY(1,1)NOTNULL,  
46.            \[LineText\]**varchar**(**max**)  
47.        )  
48.    
49.        **set** @sql='insert into \#temp exec \['\+@DatabaseName+'\].dbo.sp\_helptext '''\+@SchemaName+'.'\+@ObjectName+'''';  
50.        **exec** (@sql)  
51.      
52.        **execute** **as** caller  
53.        **insert** **into** dbo.Dba\_ChangedObjectText  
54.        **select** @Logid,@DatabaseName,@SchemaName,@ObjectName,\[Line\],\[LineText\]**from**\#**temp**  
55.    
56.        **drop** **table** \#**temp**   
57. **end**

**7.      在要实施审计的数据库上建立触发器tr\_dba\_Trace\_ObjectChangeLog：**

建立之前，需要对要实施审计的数据库如下配置。例如你需要对数据库TEST进行DDL审计，则：

**\[sql\]**  [view plain][] [copy][view plain]

1.  **alter** **database**\[TEST\]**set** trustworthy **on**

然后再建立触发器tr\_dba\_Trace\_ObjectChangeLog

**\[sql\]**  [view plain][] [copy][view plain]

1.  USE \[TEST\]  
2.  GO  
3.  **SET** ANSI\_NULLSON  
4.  GO  
5.  **SET** QUOTED\_IDENTIFIERON  
6.  GO  
7.    
8.  **CREATE** **trigger**\[tr\_dba\_Trace\_ObjectChangeLog\]  
9.  **on** **database**  
10. \--withexecute as 'AuditUser'  
11. **for** ddl\_table\_events,ddl\_view\_events,ddl\_index\_events,  
12.     ddl\_synonym\_events,ddl\_function\_events,ddl\_procedure\_events,ddl\_trigger\_events,   
13.     ddl\_database\_security\_events\--,ddl\_rule\_events    
14. **as**  
15. **begin**  
16.     **set** nocount **on**  
17.    
18.     **declare**  
19.        @newid UNIQUEIDENTIFIER,   
20.        @data xml,  
21.        @spid **smallint**,  
22.        @LoginNamevarchar(256),  
23.        @ProgramNamevarchar(256),  
24.        @IP **varchar**(15),  
25.        @mac **varchar**(12),  
26.        @EventTypevarchar(50),  
27.        @ObjectTypevarchar(25),  
28.        @ObjectNamevarchar(256),  
29.        @IsaDDLTriggerint,  
30.        @Line **smallint**  
31.    
32.     **set** @newid = newid()  
33.     **set** @data = EVENTDATA()  
34.     **set** @spid = @data.value('(/EVENT\_INSTANCE/SPID)\[1\]','smallint')  
35.     **set** @EventType = @data.value('(/EVENT\_INSTANCE/EventType)\[1\]','varchar(50)')  
36.     **set** @ObjectType = @data.value('(/EVENT\_INSTANCE/ObjectType)\[1\]','varchar(25)')  
37.     **set** @ObjectName = @data.value('(/EVENT\_INSTANCE/ObjectName)\[1\]','varchar(256)')  
38.     **set** @LoginName = @data.value('(/EVENT\_INSTANCE/LoginName)\[1\]','varchar(256)')  
39.     \--set @ObjectName= @data.value('(/EVENT\_INSTANCE/ObjectName)\[1\]', 'varchar(256)')  
40.     **set** @mac =(SELECTNET\_ADDRESSFROM master.dbo.sysprocessesWHEREspid = @spid)  
41.     **set** @Line = 0  
42.    
43.     \--过滤掉由维护计划生成的重建索引记录(因此类记录太庞大)  
44.     if @LoginName='NT AUTHORITY\\SYSTEM'and@ObjectType= 'INDEX'  
45.        **return**;  
46.    
47.     \-- if the objectis a ddl trigger  
48.     if (selectcount(\*)**from** sys.triggerswherename=@ObjectNameandparent\_class=0)= 0  
49.        **set** @IsaDDLTrigger = 0  
50.     **else** **set** @IsaDDLTrigger = 1  
51.    
52.     **execute** **as** login ='AuditUser'  
53.    
54.     **select** @ProgramName=\[Program\_Name\]fromsys.dm\_exec\_sessionswheresession\_id=@spid  
55.     **select** @IP=client\_net\_addressfromsys.dm\_exec\_connectionswheresession\_id=@spid  
56.    
57.     **insert** **into** DBAdminPlat.dbo.DBA\_ChangedObjectLog(  
58.        \[LogId\],  
59.        \[EventType\],\[PostTime\],\[SPID\],\[ServerName\],\[Host\_IP\_Address\],\[Host\_MAC\_Address\],  
60.        \[ProgramName\],\[LoginName\],\[UserName\],\[DatabaseName\],\[SchemaName\],  
61.        \[ObjectName\],\[ObjectType\],\[TSQLCommand\]\--,\[EventDate\]  
62.     )  
63.     **values**(  
64.        @newid,  
65.        @EventType,  
66.        @data.value('(/EVENT\_INSTANCE/PostTime)\[1\]','datetime'),  
67.        @spid,  
68.        @data.value('(/EVENT\_INSTANCE/ServerName)\[1\]','varchar(256)'),  
69.        @IP,  
70.        @mac,  
71.        @ProgramName,  
72.        @LoginName,\--@data.value('(/EVENT\_INSTANCE/LoginName)\[1\]','varchar(256)'),  
73.        @data.value('(/EVENT\_INSTANCE/UserName)\[1\]','varchar(256)'),  
74.        @data.value('(/EVENT\_INSTANCE/DatabaseName)\[1\]','varchar(256)'),  
75.        @data.value('(/EVENT\_INSTANCE/SchemaName)\[1\]','varchar(256)'),  
76.        @ObjectName,  
77.        @ObjectType,  
78.        \--For objectslike procs, views, triggers and functions ,text of which will be stored inmaster.\[dbo\].\[tb\_dba\_ChangedObjectText\] table.  
79.        case **when** @ObjectType in('PROCEDURE','VIEW','FUNCTION','TRIGGER')and(@EventType<>'GRANT\_DATABASE'or@EventType like 'DROP%')and@IsaDDLTrigger=0  
80.            **then** null **else** @data.value('(/EVENT\_INSTANCE/TSQLCommand)\[1\]','varchar(max)')**end**  
81.     );  
82.    
83.     REVERT;  
84.    
85. **end**  
86.   
87. GO  
88.    
89. **SET** ANSI\_NULLSOFF  
90. GO  
91. **SET** QUOTED\_IDENTIFIEROFF  
92. GO  
93. ENABLE **TRIGGER**\[tr\_dba\_Trace\_ObjectChangeLog\]**ON** **DATABASE**  
94. GO  
95.

[view plain]: http://blog.csdn.net/xiao_hn/article/details/8940159#
[20130517160847413]: /images/20220810/c50eab8613f24232929b25363b919bd2.png
[20130517160912360]: /images/20220810/63e59dc307c7499d9c93da470ffe7c39.png

发表评论取消回复

表情：

评论列表（有 0 条评论，223人围观）

还没有评论，来说两句吧...

相关阅读

相关 mysql数据库安全审计_MySQL数据库安全日志审计工具

说明由于MySQL社区版没有自带的审计功能或插件，对于等级保护当中对数据库管理的要求的就存在一定的不满足情况的，抛开条条框框不说数据库的日志是值得研究的，通过收集数据库的日

逃离我推掉我的手/ 2023年01月11日 13:15/ 0 赞/ 111 阅读

相关 [SQL Server]系统存储过程,视图的ddl语句

\[SQL Server\]系统存储过程,视图的ddl语句 [http://www.g-productions.nl/index.php?name=sysdatabases]

Dear 丶/ 2022年09月28日 11:49/ 0 赞/ 123 阅读

相关 SQL Server 数据库安全

http://my.oschina.net/cuilili/blog/162561 今天偶然打开windows事件查看器，发现几千条登录失败的日志，心中一惊，马上明白过来肯

╰半橙微兮°/ 2022年08月27日 08:58/ 0 赞/ 166 阅读

相关 SQL Server DDL安全审计

应用场景：作为SQL Server安全审计的一部分，DBA可能需要这样的一份报吿：知道哪些数据库对象(储如表、存储过程、视图、用户、函数、用户权限等等)在什

雨点打透心脏的1/2处/ 2022年08月10日 11:48/ 0 赞/ 224 阅读

相关 SQL SERVER 审计(核)案例

---创建审计及审计规范 USE master CREATE SERVER AUDIT my_first_audit TO FILE(FILEPAT

╰半夏微凉°/ 2022年06月18日 01:16/ 0 赞/ 106 阅读

相关 SQL DDL触发器

--同一个数据库服务器上不同数据库表结构同步 CREATE PROCEDURE PRO_DB_DDL (@sql nvarchar(max))

快来打我*/ 2022年06月15日 00:37/ 0 赞/ 128 阅读

相关 SQL Server 安全设置

光盘\\TM\\lx\\3\\SQL Server 2008数据库的安全设置.exe SQL Server 2008 数据库的安全设置要对SQL Server 2008

你的名字/ 2022年06月09日 14:52/ 0 赞/ 144 阅读

相关 SQL Server 2008中的代码安全（二）：DDL触发器与登录触发器

本文主要涉及DDL触发器和登录触发器的应用实例。 MicrosoftSQL Server 提供两种主要机制来强制使用业务规则和数据完整性：约束和触发器。触发器为特殊类型的存

港控/mmm°/ 2022年05月15日 05:07/ 0 赞/ 192 阅读

相关 MySql开启安全审计（audit审计插件）

> 本文已在本人博客[https://www.nsxsg.com/archives/44][https_www.nsxsg.com_archives_44]首发文章目录

清疚/ 2022年04月04日 09:18/ 0 赞/ 1138 阅读

相关 golang 代码安全审计

前言 Go语言主要用作服务器端开发语言，适合于很多程序员一起开发大型软件，并且开发周期长，支持云计算的网络服务。Go语言能够让程序员快速开发，并且在软件不断的增长过程中，

向右看齐/ 2021年07月28日 14:55/ 0 赞/ 701 阅读