Configuring SQL Audit using the Audit Dynamic Management Views
In SQL Audit we added 2 Dynamic Management Views (DMVs) for use with reporting and configuration that I thought could use some more explanation and examples of how we intended they be used.
The first is sys.dm_audit_class_type_map. Unfortunately the terms "class" and "type" are very overloaded in SQL Server metadata. In our permission model, we refer to the different types of objects as "securable classes". One of the most common of these securable classes is "object", or more descriptive "schema object". This can consist of the metadata object types of U - User Defined Table or P - Stored Procedure to give just two examples. Sys.dm_audit_class_type_map is a comprehensive mapping between metadata object types and securable classes.
This is needed because when we fire events we record the object type, not the securable class. We decided to record the object type as it is more granular and descriptive than the securable class. And although audit reports may want to roll-up and lose some of these details we thought it best to be as descriptive as possible in the actual log and provide DMVs to facilitate rolling up for reporting purposes.
The securable class is needed configuration. When defining a granular audit or trying to find the correct action group you need to know the securable class. This is because audit events are fired during security access checks and so it is the security class of the object that defines the object's type and actions. For defining granular audit actions in database audit specifications the securable class is always needed except for SCHEMA_OBJECT - which we just assume as a convenience as it's the most common case. This is just syntactic sugar; the securable class is always needed internal, as can be seen in the sys.database_audit_specification_details metadata table.
For action groups, the securable class is always a part of the name and picking the wrong action group will mean you don't see the events expect. Some exceptions to the securable class being part of the name are SERVER_OBJECT_*_GROUPs and DATABASE OBJECT_*_GROUPs which cover all securable classes that are immediate children of SERVER and DATABASE respectively. Except for Logins in the case of SERVER_OBJECT. And the Login and User securable classes are referred to as server principal and database principal in the action group names. Got all that?
Moving on to the second DMV. Sys.dm_audit_actions documents for every audit action or action group which may appear in an audit log or be configured the securable class or metadata object type related to the action. It also maps for the action/securable class pair the covering action, covering parent action, and action group. The reason for sometimes recording the metadata object type is again to provide as much detailed information as possible.
By joining these two tables together with the output of fn_get_audit_file you can get a very detailed audit report that would give you the exhaustive list of not only what happened but what audit configurations may have been enabled to cause the event to fire. Sometimes the sys.dm_audit_actions table reports as class_type_desc the metadata object type and sometimes the securable class. Because of this, to get this exhaustive list the query needs to be two exclusive queries unioned together to capture all cases.
Most people don't need this exhaustive list - the usual reporting case probably just wants to expand the action_id and class_type to more descriptive words, which is a straight forward join. But below I want enumerate all possible ways to configure an audit to get an event into the audit log. This is useful for finding out why events are in logs (or why they are not).
-- Case where the sys.dm_audit_actions reports securable class and the
-- securable class is not the same as the metadata object type.
SELECT event_time, sequence_number, a.action_id, a.class_type, b.securable_class_desc, b.class_type_desc, c.name FROM
fn_get_audit_file( N'c:\temp\*.sqlaudit', null, null ) AS a
inner join sys.dm_audit_class_type_map AS b
on (a.class_type = b.class_type)
inner join sys.dm_audit_actions c on (c.action_id = a.action_id and
c.class_desc = b.securable_class_desc)
where not exists(select * from sys.dm_audit_actions d where d.action_id =
a.action_id and d.class_desc = b.class_type_desc)
union
-- Case where the sys.dm_audit_actions reports class_type_desc, or
-- metadata object type.
SELECT event_time, sequence_number, a.action_id, a.class_type, b.securable_class_desc, b.class_type_desc, c.name FROM
fn_get_audit_file( N'c:\temp\*.sqlaudit', null, null ) AS a
inner join sys.dm_audit_class_type_map AS b
on (a.class_type = b.class_type)
inner join sys.dm_audit_actions c on (c.action_id = a.action_id and
c.class_desc = b.class_type_desc)
Let's look at some output:
event_time sequence_number action_id class_type securable_class_desc class_type_desc name
---------------------- --------------- --------- ---------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
2008-08-05 19:38:19.85 0 AUSC A SERVER SERVER AUDIT AUDIT SESSION CHANGED
2008-08-05 19:38:36.76 1 CR SL LOGIN SQL LOGIN CREATE
Here we see 2 events - the audit starting and a SQL Login being created. If we had only looked at just the output of sys.fn_get_audit_file we'd only we the first 4 columns. But by joining with the other tables we see this is a server audit sessions that's being changed and a sql login being created.
Further, by selecting the containing_group_name you can see which action group will cause an event to be audited.
Granular audits are a bit more complicated. Let's take a look at SELECT:
SELECT action_id,
class_desc,
covering_action_name,
parent_class_desc,
covering_parent_action_name,
containing_group_name
FROM sys.dm_audit_actions where name = 'SELECT'
action_id class_desc covering_action_name parent_class_desc covering_parent_action_name containing_group_name
--------- ----------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
SL DATABASE SCHEMA_OBJECT_ACCESS_GROUP SERVER NULL SCHEMA_OBJECT_ACCESS_GROUP
SL OBJECT NULL SCHEMA SELECT SCHEMA_OBJECT_ACCESS_GROUP
SL SCHEMA NULL DATABASE SELECT SCHEMA_OBJECT_ACCESS_GROUP
(3 row(s) affected)
Here there are 3 rows returned for SELECT. These 3 rows tell use that a select event could be the result of 5 different audit specifications:
1. ADD(SELECT ON OBJECT::<objectname> BY public) (row 2)
2. ADD(SELECT ON SCHEMA::<schemaname> BY public) (see row 3)
3. ADD(SELECT ON DATABASE::<databasename> BY public) (see row 1)
4. ADD(SCHEMA_OBJECT_ACCESS_GROUP) in a database audit specification (see containing_group_name)
5. ADD(SCHEMA_OBJECT_ACCESS_GROUP) in a server audit specification (see containing_group_name)
All events with a parent_class_desc of DATABASE or lower in our hierarchy can be defined in a Database Audit Specification. Server class events (logins, endpoints, and actions on the server itself) can only be defined in Server Audit Specifications.
Hope this helps spark some ideas of how to use these DMVs for reporting and configuration. Happy Auditing!
Comments
- Anonymous
January 20, 2013
Hiya. Complicated, to say the least. What's even worse is that there is practically no information in the official documentation on MSDN on how to join the tables to get what one wants! Confusion, confusion, confusion... I'd like to know if there is any official document that would detail all the interdependencies (foreign key relations) among all the system views, management dynamic views and management dynamic functions. I've been looking for a comprehensive guide and all I could find is a pdf file on MSDN that details a pathetic handful of the (probably most important only) views. Or am I missing something here? - Anonymous
January 20, 2013
Hi there. Nice post... but could I ask you to correct the mistakes in the text? Thanks.