SQL Server Audit – Trigger – Metadata – Day 1

Background

Evaluating appropriateness of using Server Triggers.  And, so created one and now trying to make sense of what was captured.

 

Metadata

Definition

In this first post we will look at only a couple of objects.

And, those are:

  1. sys.fn_get_audit_file
    • Link
      Link
    • Columns
      • class_id
      • action_id
  2. sys.dm_audit_class_type_map
    • Returns a table that maps the class_type field in the audit log to the class_desc field in sys.dm_audit_actions
      Link
    • Columns
      • class_type
      • class_type_desc
  3. sys.dm_audit_actions
    • Returns a row for 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.
      Link
    • Columns
      • action_id
      • class_desc

 

Sample Contents

sys.dm_audit_class_type_map

Query


select *

from   sys.dm_audit_class_type_map tblDACTM

order by

       tblDACTM.class_type

Output

sys.dm_audit_class_type_map

Query

Query- Object

select  *

from   sys.dm_audit_actions tblDAA

where  tblDAA.class_desc in 
	(
	  'OBJECT'
	)

order by
	  tblDAA.[action_id]
	, tblDAA.[name]

 

Output

Query – SERVER AUDIT

select  *

from   sys.dm_audit_actions tblDAA

where  tblDAA.class_desc in 
	(
	  'SERVER AUDIT'
	)

order by
	  tblDAA.[action_id]
	, tblDAA.[name]

Output

 

Sample Query

Query


declare @filename sysname

--please change
set @filename = 'E:\Audit\*.sqlaudit'

SELECT 

      [eventTimeInGMT]
        = event_time

    , [actionID]
		= itvfAF.action_id

	, [actionType]
		= tblDAA.[name]

    , [classType]
		= itvfAF.class_type

	, [classType]
		= tblDACTM.class_type_desc

	, [securableClass]
		= tblDACTM.securable_class_desc

    , [principal]
        = itvfAF.session_server_principal_name 

    , [database]
		= itvfAF.[database_name]

    , [schema]
		= itvfAF.[schema_name]

    , [object]
		= itvfAF.[object_name]

    , [statement]
		= itvfAF.[statement]

    , [additionalInformation]
		= itvfAF.additional_information


	, [bucket]
		= 
			case
				when (tblDAA.[class_desc] = tblDACTM.[securable_class_desc]) 
					then 'securable'

				when (tblDAA.[class_desc] = tblDACTM.[class_type_desc]) 
					then 'class'

				else 'Unknown'

			end

		--, tblDAA.*

FROM sys.fn_get_audit_file 
    (
          @filename
        , default
        , default
    ) itvfAF


INNER JOIN sys.dm_audit_class_type_map tblDACTM

    ON itvfAF.class_type = tblDACTM.class_type

INNER JOIN sys.dm_audit_actions tblDAA

    ON
		(

			-- class type
			(
		 
				(
						itvfAF.[action_id] = tblDAA.[action_id]
					AND tblDAA.[class_desc] = tblDACTM.[securable_class_desc]
				)
				and not exists
				(
					select * 
					
					from   sys.dm_audit_actions tblDAA_Inner 
					
					where  tblDAA_Inner.action_id =  itvfAF.action_id 
				
					and    tblDAA_Inner.class_desc = tblDACTM.class_type_desc
				) 

			)
		
			-- securable class
			or
			(

				(
						itvfAF.[action_id] = tblDAA.[action_id]
					AND tblDAA.[class_desc] = tblDACTM.class_type_desc
				)

			) -- or

		)

order by
	 itvfAF.event_time desc

GO
  

Output

 

 

References

  1. Microsoft
    • SQL Server Security Blog
      • Configuring SQL Audit using the Audit Dynamic Management Views