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)
