Learn how to use the SQL Server 2008 catalog view and dynamic
management view to access the audit details of existing server and database
level audits.
Previous articles have illustrated the Auditing feature
of SQL Server, namely creating a server level audit, database level audit and
granular level database level audit. This article illustrates how to use the
SQL Server 2008 catalog view and dynamic management view to access the audit
details of existing server and database level audits.
The first and foremost information that any database administrator
needs to know is what audits are defined on the server. You can find that by
using the following catalog view. [Refer Fig 1.0]
select Name, is_state_enabled, type_desc from sys.server_audits
Fig 1.0
The column Type_Desc gives you the target information
of the audit defined. Here we see that Compliance_Server_audit goes to the Windows
Application Log and the other two audit’s targets are pointing to a file.
You can also view this information from SQL Server
Management Studio under the Security\Audit folder as shown below. [Refer Fig
1.1]
Fig 1.1
The next level of detail that a database administrator
should know is the location of the target, size and status of the audit itself.
You
can find that by using the following Dynamic management view. [Refer Fig 1.3]
select name, status_desc, audit_file_path, audit_file_size from
sys.dm_server_audit_status
Click for larger image
Fig 1.3
From the result, you can see that the target for both
compliance_database_audit and MyDB_Database_Granular_Audit points to
C:\SQLAudit and the file size are shown as 5MB.
We can verify the location and size using windows
explorer as shown below. [Refer Fig 1.4]
Click for larger image
Fig 1.4
This information can also be viewed from the SQL Server Management studio using Audit properties. [Refer Fig 1.5]
Click for larger image
Fig 1.5
More detailed information about the auditing with FILE
as target can be found using the following catalog view. [Refer Fig 1.6]
select name, on_failure ,max_file_size ,max_rollover_files ,log_file_path
,log_file_name from sys.server_file_audits
Fig 1.6
Details
of a database audit specification of an individual database audit can be
retrieved using the following two catalog views. [Refer Fig 1.7]
select * from sys.database_audit_specification_details where database_specification_id in
(select database_specification_id from sys.database_audit_specifications where name ='MyDB_Database_Granular_Audit_SPEC')
Fig 1.7
The
information can be retrieved using SQL Server Management studio under database
MyDB\Security\Database Audit Specification. [Fig 1.8]
Fig 1.8
You
can check the extended events
that are created by SQL server related to this audit using the following dynamic
management view command. [Refer Fig 1.9]
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.9
There
are other metadata information about the audit that can be retrieved using dynamic
management views. They are;
-
sys.dm_audit_actions: This view contains information about every audit
action that can be reported in the audit log and every audit action group that
can be configured as part of SQL Server Audit.
-
sys.dm_audit_class_type_map: This dynamic management view maps the
class_type field in the audit log to the class_desc field in
sys.dm_audit_actions view.
Example:
Select top 10 action_id,name,class_desc,parent_class_desc from sys.dm_audit_actions
Result:
action_id, name, class_desc, parent_class_desc
R , REVOKE, DATABASE, SERVER
D , DENY, DATABASE, SERVER
G , GRANT, DATABASE, SERVER
GWG , GRANT WITH GRANT, DATABASE, SERVER
RWG , REVOKE WITH GRANT, DATABASE, SERVER
RWC , REVOKE WITH CASCADE, DATABASE, SERVER
DWC , DENY WITH CASCADE, DATABASE, SERVER
R , REVOKE, OBJECT, DATABASE
D , DENY, OBJECT, DATABASE
G , GRANT, OBJECT, DATABASE
Example:
select top 20 * from sys.dm_audit_class_type_map
Result:
class_type, class_type_desc, securable_class_desc
DB, DATABASE, DATABASE
OB, OBJECT, OBJECT
TY, TYPE, TYPE
SC, SCHEMA, SCHEMA
SX, XML SCHEMA COLLECTION, XML SCHEMA COLLECTION
AS, ASSEMBLY, ASSEMBLY
US, USER, USER
RL, ROLE, ROLE
AR, APPLICATION ROLE, APPLICATION ROLE
MT, MESSAGE TYPE, MESSAGE TYPE
CT, CONTRACT, CONTRACT
SV, SERVICE, SERVICE
BN, REMOTE SERVICE BINDING, REMOTE SERVICE BINDING
RT, ROUTE, ROUTE
FC, FULLTEXT CATALOG, FULLTEXT CATALOG
FL, FULLTEXT STOPLIST, FULLTEXT STOPLIST
SK, SYMMETRIC KEY, SYMMETRIC KEY
CR, CERTIFICATE, CERTIFICATE
AK, ASYMMETRIC KEY, ASYMMETRIC KEY
SR, SERVER, SERVER
Last
but not least is getting the actual data from the target files. This can be
retrieved using the following function.
SELECT session_server_principal_name, database_name,schema_name,Object_name, statement
FROM sys.fn_get_audit_file ('C:\SQLAudit\MyDB_Database_*.sqlaudit',default,default);
Result:
session_server_principal_name, database_name, schema_name, Object_name,
statement
WIN2K8-R2\Administrator, , , ,
MyLogin, MyDB, dbo, mytable, INSERT INTO [mytable] SELECT @1
MyLogin, MyDB, dbo, mytable, INSERT INTO [mytable] SELECT @1
MyLogin, MyDB, dbo, mytable, INSERT INTO [mytable] SELECT @1
MyLogin, MyDB, dbo, mytable, INSERT INTO [mytable] SELECT @1
MyLogin, MyDB, dbo, mytable, INSERT INTO [mytable] SELECT @1
MyLogin, MyDB, dbo, mytable, UPDATE [mytable] set [id] = @1 WHERE [id]=@2
Conclusion
Part 1 through part 4 of this series illustrated the
various components involved in SQL Server auditing as well as demonstrates how
to create server level, database level and audit level audit using action and
action group specification provided by Microsoft SQL Server 2008. This final part
of the series illustrated how to use SQL Server 2008 catalog view and dynamic management
view to access the audit details of existing server and database level audits.
Additional Resources
Catalog Views
(Transact-SQL)
Dynamic Management
Views and Functions
»
See All Articles by Columnist
MAK
