SQL Server 2008 Auditing - Database Audit

To create an audit you need to do the following

1. Audit: Define where the audit data goes
2. Audit Specification: Define what goes in to the audit
3. Analyse: Find out what has been audited and view this in a meaningful way

Audit

First we need to define where the audit data goes and how much space it should take up. We also define what to do if we cannot write the audit (e.g. due to lack of space)

Audit data cannot be written to the database so we need to specify either the windows event logs or just a file on the local machine. Personally I like the files this can then be viewed, grouped and filtered from within sql server management studio using standard select statements.  See “Analyse”.

Open SSMS and connect to your server. Open the “Security” tab and right click on “Audits” and choose “New Audit…”.

The settings in here are quite self-explanatory and the online help is good enough for me not to have to explain too much but basically choose a folder and decide how much delay before the audit is written. Longer delay is better for the server overhead. (I use 5000ms). Other options basically prevent you from filling up the disk space. The default name is plain silly so change it so something more helpful.

If you script it you get something like this.

CREATE SERVER AUDIT [Audit-DATE-TIME]
TO FILE
(     FILEPATH = N'XXXXXXXXX'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
(     QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)

Audit Specification

What are we going to put in to the audit?

We can add an audit specification either at the server level or at the database level.

Server level

We can set the audit to capture events on the

• SERVER
• DATABASE
• SCHEMA

The server level is useful for capturing events on all databases and also for capturing server level events not specific to a database such as login events.

e.g. Capturing SCHEMA events will capture all events on that schema regardless of which database they occur in. Have a play about and you’ll get the gist.

To get to this dialog box go to the general Security folder in SSMS and right click “Server Audit Specification”

Database level

The database level allows more granular auditing of a specific database, allowing you to capture fine grain events such as just INSERT’s.

(The database level audit cannot capture CREATE (OR DROP) on a database as the database must exist in order to do this).

To get to this dialog box open the database in SSMS and in the Security folder and right click “Database Audit Specification”.

 

Specifying events

Within a specification we define which audit action types we want to audit. Regardless of where we specify the Audit, the dialog box looks the same (but the available action types to audit will be different depending on where we access it from).

Click “New xxx Audit Specification”. The following Dialog pops up…

Start by choosing the Audit that you created earlier in the “Audit:” dropdown. This will bind this specification to the Audit file that you have just defined, therefore pushing the captured events in to that file.

Now select the audit action types that you want to include. You must select at least 1.

There are many Audit Action Types so how do we know which we need to capture?

There are a couple of dynamic management views that will help here to understand what gets audited in which category but I would urge you to look at these later. For the moment set up a few random audits and then carry on just so that we can see some data and then come back to this bit later.

To start with, in any database where you are going to get change, create a database audit specification and add the SCHEMA_OBJECT_ACCESS_GROUP audit action type. This will capture all insert, update, delete and select statements (plus other) so should give you something to look at.

At Server level  you could add the SCHEMA_OBJECT_CHANGE_GROUP audit action type. This will provide information based on all changes atschema level but not include Data Manipulation language queries (SELECT, INSERT etc).

Analysing the audit data

Here are the views that show us the structure of how the data is stored…

sys.dm_audit_class_type_map
and
sys.dm_audit_actions

Select * from each of these and see whats in them.

Now try this
SELECT  DISTINCT containing_group_name
FROM sys.dm_audit_actions

You’ll see a familiarity with whats in the drop down list on the specification page right? So this is where the groups are but what do they contain?

Try the following query and this should make more sense.
SELECT
CM.securable_class_desc
,AA.containing_group_name
,AA.parent_class_desc
,AA.name
,action_id
,CM.class_type
,CM.class_type_desc
FROM sys.dm_audit_class_type_map CM
INNER JOIN sys.dm_audit_actions AA ON AA.class_desc = CM.securable_class_desc
ORDER BY CM.securable_class_desc

Optionally add in a where clause like this…
WHERE AA.containing_group_name = 'SCHEMA_OBJECT_CHANGE_GROUP'

This shows you all the items that will be added if you add this action type at Server level.

e.g. One line shows the following

OBJECT      DATABASE    CREATE      CR    P     STORED PROCEDURE

Which indicates that stored procedure creation in any database will be audited by this Audit Action Type at Server level

Another way to look at these would be to ask what Action Type do we need to add to capture CREATE DATABASE, ALTER DATABASE and DROP DATABASE statements?

Clearly the CREATE DATABASE statement cannot be captured at DATABASE level (it doesn’t yet exist) so it must be a server wide audit, but which one?

SELECT
CM.securable_class_desc
,AA.containing_group_name
,AA.parent_class_desc
,AA.name
,action_id
,CM.class_type
,CM.class_type_desc
FROM        sys.dm_audit_class_type_map CM
INNER JOIN  sys.dm_audit_actions AA ON AA.class_desc = CM.securable_class_desc
WHERE       CM.class_type_desc = 'DATABASE'
AND               AA.name = 'CREATE'

This shows us that at the parent class is the SERVER and to secure the DATABASE CREATE we need to include the DATABASE_CHANGE_GROUP, so this is what we need to add to the audit specification in order to capture this event.

Analyse

Brilliant! Now we have the server auditing the data and / or schema changes so now all we need is a method to see what’s changed.

Auditing can produce huge quantities of data. Add a C2 audit to a busy production server and you can see phenomenal files sizes in no time at all. C2 audit may be simple to implement but it basically audits every single statement provided to the server. This means all selects, executes, DBCC commands etc. (even those produced by the system for replication etc). Hopefully your new audit is just storing the data you actually need to know at a much more granular level.

The audit data will be held in the location you specified in the Server level “Audit” mentioned in step 1. If this was file based then to see the audit data you simply select it using the sys.fn_get_audit_file system table values function in the master database.

e.g.
SELECT top 100 * FROM sys.fn_get_audit_file('LOCALPATH\*',null,null)

Where LOCALPATH is the local path to the folder where your audit files are placed e.g. c:\Audit\

If we now join and constrain this data using the same dynamic management views mentioned earlier we get a much more meaningful picture.

DECLARE @CutOffDate Datetime = Dateadd(day,-1,GETUTCDATE())
;WITH cte AS
(
SELECT
AA.name as Operation
, database_name
FROM sys.fn_get_audit_file('LOCALPATH\*',null,null) A
INNER JOIN sys.dm_audit_class_type_map CM ON A.class_type = CM.class_type
INNER JOIN sys.dm_audit_actions AA ON A.action_id = AA.action_id and AA.class_desc = CM.securable_class_desc
where event_time > @CutOffDate
)
SELECT
database_name
,Operation
,COUNT(*) as Actions
FROM cte
GROUP BY
database_name
,Operation
ORDER BY
database_name
,Operation


On major point to note in the above query is that I am looking for the data in the last day and to do this I have obtained the current UTC datetime and taken a day off it. The reason we use UTC date is that this is how audit data is captured. The local system time is not relavent in auditing as this would be confusing in an internationally distributed system so for consistency auditing has a single time zone. (imagine how daylight saving would affect the audit if local time was used!)

Reporting

I created three linked reports in reporting services for this (drill down reports) .
SP1: At the top level I just want to see the databases and how many operations have been performed on them.
SP2: I look at a single database broken down by object
SP3: I look at the detail of each audit item on the individual object I nthe database.

The following three stored procedures were used.

-- =============================================
-- Author:        David Bridge
-- Create date: June 2011
-- Description:   interrogate the audit – level 1
-- =============================================
ALTER PROCEDURE [dbo].[Audit_report_Level1]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CutOffDate Datetime = Dateadd(day,-1,GETUTCDATE())
;WITH cte AS
(
SELECT
CM.class_type_desc
,AA.class_desc
,AA.name as Operation
,database_name
FROM        sys.fn_get_audit_file('LOCALPATH\*',null,null) A
INNER JOIN  sys.dm_audit_class_type_map CM      ON A.class_type = CM.class_type
INNER JOIN  sys.dm_audit_actions          AA    ON A.action_id = AA.action_id
AND AA.class_desc = CM.securable_class_desc
where event_time > @CutOffDate
)
SELECT
database_name
,Operation
,COUNT(*) as Actions
FROM cte
GROUP BY
database_name
,Operation
ORDER BY
database_name
,Operation
END

-- =============================================
-- Author:        David Bridge
-- Create date: June 2011
-- Description:   interrogate the audit – level 2
-- =============================================
ALTER PROCEDURE [dbo].[Audit_report_Level2]
(
@database_name varchar(max)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CutOffDate Datetime = Dateadd(day,-1,GETUTCDATE())
;WITH cte AS
(
SELECT
AA.name         as Operation
, database_name
, object_name
FROM        sys.fn_get_audit_file('LOCALPATH\*',null,null) A
INNER JOIN  sys.dm_audit_class_type_map CM      ON    A.class_type = CM.class_type
INNER JOIN  sys.dm_audit_actions          AA    ON    A.action_id  = AA.action_id
AND AA.class_desc = CM.securable_class_desc
where event_time > GETUTCDATE() -1
and database_name = @database_name
)
SELECT
database_name
,object_name
,Operation
,COUNT(*)         as Actions
FROM cte
GROUP BY
database_name
,object_name
,Operation
ORDER BY
database_name
,object_name
,Operation
END

-- =============================================
-- Author:        David Bridge
-- Create date: June 2011
-- Description:   interrogate the audit – detail
-- =============================================
ALTER PROCEDURE [dbo].[Audit_report_Level3]
(
@database_name varchar(max)
,@object_name varchar(max)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CutOffDate Datetime = Dateadd(day,-1,GETUTCDATE())
SELECT
AA.class_desc
,AA.name as Operation
,database_name
,object_name
,statement
,succeeded
,server_principal_name
,event_time
,AA.containing_group_name
FROM        sys.fn_get_audit_file('LOCALPATH\*',null,null) A
INNER JOIN  sys.dm_audit_class_type_map CM      ON A.class_type = CM.class_type
INNER JOIN  sys.dm_audit_actions          AA    ON A.action_id = AA.action_id
AND AA.class_desc = CM.securable_class_desc
where event_time        > @CutOffDate
and         database_name     = @database_name
and         object_name       = @object_name
ORDER BY
event_time
,succeeded
END


About these ads