Learn how to create a database
level audit at a granular level and retrieve the audit records in this 9-step
tutorial.
"Auditing
made easy by Microsoft SQL Server 2008" illustrated how to create, and
test a database level
audit, as well as how to retrieve the audit records. Now we move on to creating a
database level audit at a granular level and then retrieve the audit records.
Step1: Create Server Audit
First, let’s create a Server Audit Object
using the following command.
CREATE SERVER AUDIT MyDB_Database_Granular_Audit
TO FILE (FILEPATH=’C:\SQLAudit\’, MAXSIZE = 5MB,
MAX_ROLLOVER_FILES = 100, RESERVE_DISK_SPACE = ON)
Note:
Compliance_Database_Audit is the name of the Server Audit. This command
allocates 5MB of disk space for this audit. When the 5MB file is filled up, it
automatically rolls over to another file to the maximum number of 100 Files.
When the audit restarts the next time, it evaluates the number of files and
deletes any older files.
Step 2: Enable Server Audit
By default, the server audit is disabled.
Enable the server audit using the following command.
ALTER SERVER AUDIT MyDB_Database_Granular_Audit with (STATE = ON)
Note: Once the audit starts, the OS
allocates 5MB disk space for the audit file. Refer to Fig 1.1.
Click for larger image
Fig 1.1
Step 3: Add the Database Audit specification
Database audit specification is the list of action
groups that Server Audit will audit.
Let’s add a couple of actions when creating the
Audit specification.
use MyDB
go
CREATE DATABASE AUDIT SPECIFICATION MyDB_Database_Granular_Audit_SPEC
FOR SERVER AUDIT MyDB_Database_Granular_Audit
ADD (INSERT ON MyDB.dbo.MyTable BY MyLogin),
ADD (DELETE ON MyDB.dbo.MyTable BY MyLogin2)
Note:
Compliance_Database_Audit_Spec is the name of the Server Audit specification.
Step 4: Update Server Audit specification
If you want to add action groups to the
existing database audit specification, you could use the following statement.
ALTER DATABASE AUDIT SPECIFICATION MyDB_Database_Granular_Audit_SPEC
ADD (UPDATE ON MyDB.dbo.MyTable BY MyLogin,MyLogin2)
Note: You could skip step 4 if you already listed
all of the action groups in Step 4
Step 5: Enable Server Audit specification
Enable the Audit specification by using the
following command.
use MyDB
go
ALTER DATABASE AUDIT SPECIFICATIOn MyDB_Database_Granular_Audit_SPEC WITH (STATE = ON)
Step 6: Read Audit data
Execute the following command to read the AUDIT
data from the binary file. Refer to Figure 1.2
SELECT event_time, action_id, succeeded, session_id, session_server_principal_name, target_server_principal_name,
target_database_principal_name, database_name, database_principal_name, schema_name, object_name,
statement FROM sys.fn_get_audit_file (‘C:\SQLAudit\MyDB_Database_*.sqlaudit’,default,default);
Fig 1.2
Step 7: Test Audit
We have the following action added to the Audit
specification.
INSERT
UPDATE
DELETE
Now let’s test the action group by executing the
following TSQL Command.
Use MyDB
go
GRANT INSERT,DELETE, UPDATE on MyTable to MyLogin,MyLogin2
go
Login as MyLogin and MyLogin2 and insert, update
and delete some rows. Refer to Fig 1.3.
Fig 1.3
use MyDB
go
Insert into mytable select 1
Insert into mytable select 2
Insert into mytable select 3
Insert into mytable select 4
Insert into mytable select 5
Update mytable set id =25 where id =2
delete mytable where id = 5
Now let us see if these actions have been logged to
the binary file under c:\SQLAudit, by executing the command. Refer to Fig 1.4.
SELECT event_time,statement, action_id, succeeded, session_id, session_server_principal_name,
target_server_principal_name, target_database_principal_name, database_name, database_principal_name,
schema_name, object_name from sys.fn_get_audit_file (‘C:\SQLAudit\MyDB_Database_*.sqlaudit’,default,default);
Fig 1.4
Step 8: Check the Audit metadata
Metadata information on the Server Audit and Audit specification can be viewed using the following commands.
SELECT * FROM sys.server_audits
SELECT * FROM sys.Database_audit_specifications
\
Fig 1.5
You also check the extended events that are created by SQL server related to this audit by using the following Dynamic management
view command. Refer to Fig 1.6.
select s.name as [session name], se.event_name as [event name], p.name as [package name], t.target_name as [target name]
from sys.dm_xe_sessions s
join sys.dm_xe_session_events se on s.address = se.event_session_address
join sys.dm_xe_packages p on se.event_package_guid = p.guid
join sys.dm_xe_session_targets t on s.address=t.event_session_address
Fig 1.7
Step 9: Stop Server AUDIT
Server audit can be stopped using the ALTER SERVER AUDIT command a shown below.
use master
go
ALTER SERVER AUDIT MyDB_Database_Granular_Audit with (STATE = OFF)
Note: When the audit is stopped, SQL
Server releases the free space on the audit files to the Operating System.
Refer to Fig 1.7
Fig 1.7
Conclusion
This article has illustrated how to create database
level audit at a granular level and retrieve the audit records.