SQL Server DDL Security Audit

Application scenarios:

As part of SQL Server Security Audit, DBAs may need a report like this: Know Which database objects (such as tables, stored procedures, views, users, functions, user permissions, etc) who modified the content and so on. You may also need a copy of the code modification history list of stored procedures, functions, and views to know that these codes have been modified for a certain period of time. The following code is exactly what you need.

This code runs well on SQL Server 2005/2008.

1. Create an audit database for storing audit records;

This step is omitted. Audit Database Name: auditdb;

 

2. Create an audit user for the permissions required for implementing the audit;

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

Note: This user does not need to log on, so you can disable this user's logon.

3. Create the following audit table in the audit database auditdb:

Table 1: dba_changedobjectlog (audit master table)

Table 2: dba_changedobjecttext (audit slave table)

Note:

The Association field between the audit master table and the audit slave table is logid;

Only views, stored procedures, functions, and triggers have slave table data, because only these objects have code definitions;

 

4. Authorization for Audit User:

use mastergogrant viewserverstate to Audituseruse AuditDBgogrant insertonDBA_ChangedObjectLogtoAuditusergrant insertonDBA_ChangedObjectTexttoAudituser

5. Grant the simulated audituser permission to all users who can modify database objects:

If you do not do this, you must grant the audituser permission in Step 4 to all these users;

grant impersonateonlogin::user1toauditusergrant impersonateonlogin::auditusertouser1

If your database already has dozens or hundreds of thousands of users, you can use the following statements to generate code for batch authorization:

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'

Note: To authorize each new user.

 

6. Create the trigger tr_dba_objectchangelog_insert on the audit master table.

The purpose of this trigger is to get the code definition of the currently modified database object (view, stored procedure, function, trigger) and save it to the slave table.

Use [auditdb] goset ansi_nullsongoset quoted_identifierongo -- create a trigger on the audit master table: Alter trigger [DBO]. [tr_dba_objectchangelog_insert] on [DBO]. [UNKNOWN] For insertasbegin declare @ logid uniqueidentifier, @ resolve (256), @ usernamenvarchar (256), @ resolve (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 triggers cannot use sp_helptext to obtain its text. 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. Create the trigger tr_dba_trace_objectchangelog on the database to be audited:

Before creating a database, you need to configure the database to be audited as follows. For example, if you need to perform DDL audit on the database test, then:

alter database[TEST]set trustworthy on

Create the trigger tr_dba_trace_objectchangelog.

Use [test] goset events triggers trigger [events] on database -- withexecute as 'audituser' for ddl_table_events, ddl_view_events, ddl_index_events, events, functions, functions --, 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 -- filter out the rebuilt index records generated by the Maintenance Plan (so the Class records are too large) 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 ('processed', '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 [Break] On databasego