Auditing using Database audit specification and Server audit specification

Audit specifications can have three categories of actions:

• Server level actions
• Database level actions or
• Audit level actions

This article discusses how server level actions and database level actions are enabled for auditing on an SQL server using audit specification.

Enabling server audit specification

These are the audit specifications for actions carried out at the server level. It is available in all the editions of SQL server. There can be only one server audit specification per audit.

To create server audit specification, the user needs :

• Permission to connect to the database.
• ALTER ANY SERVER AUDIT permission to edit.
• CONTROL SERVER permission to allow the audit to be viewed by the user.

Steps to create Server Audit Specification:

1. Create a server audit
   • Navigate to Security folder, in Object Explorer.
   • Right-click the Audits folder and select New Audit.
   • When you finish selecting options, select OK.
2. Navigate to Security folder >> Server Audit Specifications.
3. Right-click Server Audit specifications and select New Server Audit Specification.
4. Specify the following:
   • Name field with name. If left empty, a default name is assigned.
   • Server Audit field with the target machine to which these events should be logged.
5. From the Audit list, select the audit object created in step 1.
6. In the Actions pane, add actions and objects to the server audit.
7. Right-click your server audit specification and select Enable Server Audit Specification.

Database audit specification

These are the audit specifications for actions carried out at the database level. Database audit specification is available only in the Enterprise edition of SQL server and can be carried out at the object or user level.

To create server audit specification, you need the below permissions:

• Permission to connect to the database
• ALTER ANY DATABASE AUDIT SPECIFICATION or ALTER
• CONTROL permission for the database to which they would like to add the audit.

Steps to create database audit specification:

The following needs to be configured to create a Database Audit Specification:

1. Navigate to Security node >> Database Audit specifications.
2. Right-click Database Audit Specifications and select New Database Audit Specification.
3. Specify the following fields
   • Name field with name. If left empty, a name is assigned by default.
   • Audit field with the name of the existing Server Audit object.
   • Audit Action Type field, which can be selected from both Audit Actions and Audit Action Groups.
   • Object Name of the object to be audited when an Audit Action has been selected.
   • Schema of the selected object.
   • Principal Name. To audit all users, use the keyword public in this field.
4. Click OK.
5. Right-click your database audit specification and select Enable Database Audit specification.

To audit server scope objects, create a database audit specification in the master database.

It can also be created with Transact SQL.

Creating a database audit specification using Transact SQL

Here's a scenario in which we want to audit the changes made to an employee pay history table. Assume that the employee details are stored in HumanResource.EmployeePayHistory table. First, we define a server audit called Payrole_Security_Auditc and enable it. We then proceed to create a database audit specification called Audit_Pay_Tables. This specification audits SELECT and INSERT statements by the dbo user for the table, as required.

Steps to enable auditing for the above scenario

• Connect to an instance of Database Engine, in the Object Explorer.
• Select New Query on the standard bar.
• Paste the following example code into the query window and select Execute.
  USE master ;
GO
-- Create the server audit.
CREATE SERVER AUDIT Payrole_Security_Audit
TO FILE ( FILEPATH =
'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA' ) ;
GO
-- Enable the server audit.
ALTER SERVER AUDIT Payrole_Security_Audit
WITH (STATE = ON) ;
• Repeat the first three steps for the example code below.
  USE AdventureWorks2012 ;
GO
-- Create the database audit specification.
CREATE DATABASE AUDIT SPECIFICATION Audit_Pay_Tables
FOR SERVER AUDIT Payrole_Security_Audit
ADD (SELECT , INSERT
ON HumanResources.EmployeePayHistory BY dbo )
WITH (STATE = ON) ;
GO

Auditing procedures such as these involve a lot of time and human effort. This is as far as the auditing goes—you still don't get a holistic picture on the security of your SQL database.

ManageEngine's EventLog Analyzer, a comprehensive log management tool, audits both SQL server and SQL database. It provides out-of-box reports, real-time alerts and even an intuitive dashboard You can drill down to the logs, filter reports, customize alerts, perform log searches, and archive logs for powerful and effective management of SQL Servers. Click here to know to more