DATABASE

SQL Server 2008 : Creating Database Audit Specifications

2/27/2011 10:49:53 AM
If you've determined that you need to audit database-level information, then you need to create a database audit specification to capture that information. Database audit specifications consist of database-level audit action groups and/or database-level audit actions. We will discuss database-level audit action groups and database-level actions in the next couple of sections. For right now, just think of database-level actions and audit action groups as the actions that are defined in order to audit database-level activity.

The syntax for creating a database audit specification is shown in Listing 1.

Example 1. Syntax to Create a Database Audit Specification
CREATE DATABASE AUDIT SPECIFICATION audit_specification_name
FOR SERVER AUDIT audit_name
ADD ( <audit_action_specification> | audit_action_group_name ) , ...n

WITH ( STATE= ON|OFF)
audit_action_specification =
action [ ,...n ]ON [class ::]securable BY principal [ ,...n ]

Similar to creating a server audit specification, the database audit specification must be associated with a server audit. Once you specify the server audit, then add the database-level audit action group or an audit_action_specification. Adding databaselevel audit action groups is similar to adding server-level audit action groups. Adding the audit_action_specification requires a different syntax. We will discuss adding the databaselevel audit action groups and database-level audit actions further in the next couple of sections.

1. Database-Level Audit Action Groups

Database-level audit action groups consist of actions against a specific database. Database-level audit actions enable you to monitor actions on database, schemas, and schema objects within the database. The database-level audit action groups are predefined actions that don't allow customization.

Because there are so many database-level audit action groups, we will not cover them all here. However, following are the database-level audit action groups that we frequently use to audit our systems.

  • Database_Role_Member_Change_Group: Tracks the addition and removal of logins to database roles.

  • Database_Object_Change_Group: Identifies when CREATE, ALTER, or DROP statements occur against database objects.

  • Database_Principal_Change_Group: Tracks creation, alteration, and deletion of database principals.

  • Database_Permission_Change_Group: Tracks when permissions change for a database user.

  • Database_Object_Permission_Change_Group: Identifies the issuing of grant, deny, or revoke permissions to database objects.

  • Schema_Object_Change_Group: Tracks the CREATE, ALTER, and DELETE statements performed on schema objects.

We encourage you to go to SQL Server Books Online and search for "SQL Server Audit Action Groups and Actions" to get a complete list of all the database-level audit action groups. The more aware you are of the available database-level audit action groups, the better you can plan audits on your system.

The following code provides an example of adding database-level audit action groups to a database audit specification. This code sample also creates a SQL Server audit to associate the database-level audit action groups to.

USE master;
GO

CREATE SERVER AUDIT exampleAudit2
TO FILE ( FILEPATH = 'C:\',MAXSIZE = 1 GB)
WITH( ON_FAILURE = CONTINUE)

GO

USE AdventureWorks2008
GO

CREATE DATABASE AUDIT SPECIFICATION databaseActionGroup
FOR SERVER AUDIT exampleAudit2
ADD (DATABASE_PERMISSION_CHANGE_GROUP)
GO

2. Database-Level Audit Actions

Database-level audit actions allow you to audit specific, customizable actions within the database. In other words, database-level audit actions against a database, schema, or schema object of your choosing will trigger an audit event. The following list contains the databaselevel audit actions with brief descriptions:

  • Select: Tracks execution of SELECT statements.

  • Insert: Determines when the execution of INSERT statements occur.

  • Delete: Identifies when DELETE statements execute.

  • Update: Tracks UPDATE statements executed against the database.

  • Execute: Determines when EXECUTE statements run against the database.

  • Receive: Tracks when RECEIVE statements are issued.

  • References: Identifies when the references of permissions are checked.

Referring to the syntax in Listing 15-6, you can see that adding the database-level audit action consists of an action on a securable by a principal. What does all that mean? An action is a database-level auditable action like the items listed in the preceding list—statements like SELECT, INSERT, UPDATE, DELETE. Securables, are the objects within a database that you want to audit. Examples of securables are tables, views, stored procedures, and other database objects. The principal is the SQL Server principal that you want to apply the audit to. In other words, when you define a database-level audit action, you are defining the type of statement you want audited against a table or view, or done by a user.

The following code provides an example of adding database-level audit actions to a database audit specification.

USE AdventureWorks2008
GO

CREATE DATABASE AUDIT SPECIFICATION databaseActionGroup
FOR SERVER AUDIT exampleAudit2
ADD (INSERT,UPDATE,DELEETE ON Person.Person by dbo),

3. Testing Your Database Audit Specification

Since you understand the syntax of the CREATE database audit specification, let's construct one. Listing 2 creates a database audit specification along with some statements that will force the events to fire causing writes to your audit. For this example, we want to audit who is querying a specific production table. Since we cannot prevent non-application users from having access to the database, we can track if they are viewing tables unrelated to their jobs, such as the salary table for all the employees. Sometimes the best way to prove to management that not everyone needs access to production is to show them examples of abusing the privileges. Capturing users running bad queries or looking at information that they shouldn't helps with that argument. We also monitor the DATABASE_PRINCIPAL_CHANGE_GROUP just in case the user is smart and creates another user to query the table.

Example 2. SQL Script to Create a Database Audit Specification
USE master;
GO

-- Create the server audit
CREATE SERVER AUDIT salaryViewing
TO FILE ( FILEPATH = 'C:\'',MAXSIZE = 1 GB)

GO

-- Create the database audit specification in the database you want audited
USE AdventureWorks2008
GO

CREATE DATABASE AUDIT SPECIFICATION salaryQueries
FOR SERVER AUDIT salaryViewing
ADD (SELECT,UPDATE ON humanresources.EmployeePayHistory by dbo),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP)

GO

USE master;
GO

ALTER SERVER AUDIT salaryViewing
WITH (STATE = ON)
GO


USE AdventureWorks2008
GO

ALTER DATABASE AUDIT SPECIFICATION salaryQueries
WITH (STATE = ON)
GO

SELECT TOP 10 *
FROM AdventureWorks2008.HumanResources.EmployeePayHistory

SELECT JobTitle, Rate,RateChangeDate
FROM AdventureWorks2008.HumanResources.Employee e
JOIN AdventureWorks2008.HumanResources.EmployeePayHistory eh
ON e.BusinessEntityID = eh.BusinessEntityId
ORDER BY jobTitle, RateChangeDate DESC


SELECT JobTitle, Rate,RateChangeDate
FROM AdventureWorks2008.HumanResources.Employee e
JOIN AdventureWorks2008.HumanResources.EmployeePayHistory eh
ON e.BusinessEntityID = eh.BusinessEntityId
WHERE rate > 50.
ORDER BY jobTitle, RateChangeDate DESC

USE Adventureworks2008
GO

CREATE USER sneakyUser FOR LOGIN auditTest
GO

To view the results of the database specification audit, use the following query. The results of the query are shown in Figure 1.

SELECT event_time,server_principal_name,database_principal_name,
object_name, statement,*
FROM fn_get_audit_file ('C:\sal*',NULL, NULL)

Figure 1. Results of the audit from Listing 2

Other  
  •  Programming Microsoft SQL Server 2005 : The XML Data Type (part 3) - XML Indexes
  •  Programming Microsoft SQL Server 2005 : The XML Data Type (part 2) - XML Schemas
  •  Programming Microsoft SQL Server 2005 : The XML Data Type (part 1) - Working with the XML Data Type as a Variable & Working with XML in Tables
  •  SQL Server 2008 : Auditing SQL Server - Creating Server Audit Specifications
  •  SQL Server 2008 : Auditing SQL Server - Creating SQL Server Audits with T-SQL
  •  Programming Microsoft SQL Serve 2005 : An Overview of SQL CLR - Security
  •  Programming Microsoft SQL Serve 2005 : An Overview of SQL CLR - CLR Aggregates
  •  SQL Server 2008: Monitoring Resource Governor
  •  SQL Server 2008: Managing Resources with the Resource Governor (part 3) - Classifier Function
  •  SQL Server 2008: Managing Resources with the Resource Governor (part 2) - Workload Groups
  •  
    Most View
    SSD Shakedown: Corsair Neutron GTX 480GB vs. Intel 335 Series SSD 240GB
    Microsoft Windows Home Server 2011 : Modifying User Accounts
    Use Layer Mask For Safe Photo Editing (Part 2)
    Sharepoint 2013 : Farm Management - Remove a Managed Path, Merge Log Files, End the Current Log File
    Canon PowerShot S200 - Back To Basics (Part 2)
    Kyocera Torque - An Ultra-Durable Phone Providing Prominent Sound (Part 1)
    Magnetic Tape Storage - Data Storage For 30 Years
    The Assemblage Of GeForce GTX 650 Ti Graphics Cards (Part 7)
    Tokina AT-X 116 Pro DX II 11-16mm F2.8 Lens
    Windows 7 : Computer Management (part 1) - Task Scheduler, Event Viewer
    Top 10
    Return Of The Mac McIntosh MXA70 Review (Part 2)
    Return Of The Mac McIntosh MXA70 Review (Part 1)
    SMC Pentax-DA 18–135mm f/3.5-5.6ED AL (IF) DC WR All-Weather Friend
    Sony Cyber-Shot DSC-RX100 III Perfecting The Threequel
    Porsche 911 GT3 (996) Review
    Porsche 911T (1967 SWB) Review
    The Volkswagen Polo 1.5 TDI – Offer Effortless Performance In All Situations (Part 2)
    The Volkswagen Polo 1.5 TDI – Offer Effortless Performance In All Situations (Part 1)
    The Rolls-Royce Wraith – A Car Of Considerable Allure And Significance
    Twin Test – New BMW M3 vs Porsche Macan – Brawn To Be Wild (Part 3)