SQL Server Audit

Auditing (Audit) is used to track and record events that occur in a SQL Server instance or database, including auditing objects (Audit) and audit Specifications (Audit specification), and creating an audit first requires creating an audit object at the instance level of SQL Server , and then create a "server audit specification" or "Database Audit specification" that belongs to it. Audit data can be exported to the audit file, security log, and application log (application log).

Auditing is enabled for the purpose of monitoring operations performed by SQL Server, such as recording when someone is querying data, modifying data, logging into an instance of SQL Server, and so on, because audit records may be rich in data, so enabling auditing can generate a lot of log data, Occupies a large amount of space on the disk. The audit uses a word to summarize: record who did what when, Audit object (Audit) Definition: Record where, and audit specification (Audit specification) Definition: Record what matter, once the specific event triggers, SQL The server engine uses audit-Logged events for field information.

The general steps for creating and using audits are:

• Step1: Create a server-level audit object and enable audit objects;
• Step2: Create audit specification, map to audit object Audit, enable audit specification, Audit object to start tracking and record data;
• Step3: Viewing audit data, you can view log data for a record by using the "Log Files Viewer" or the function sys.fn_get_audit_file in SSMs.

One, create an Audit object

First create a server-level audit object, expand "Security", right-click Audits, through "New Audit", open the "Create Audit" form to start creating an Audit object, audit output data saved to "Audit destination", This article selects file, stores the data in the audit file, and other types are: Security log and Application log. Queue delay refers to the delay in writing data to an audit file, which is 1s by default. The Audit object is to specify the path of the audit data preservation, as well as the delay of writing data and the size of the data file, the audit object is mainly to store audit specification data.

Depending on the limitations of the hard disk space, set the properties of the Audit object Audit file maximum limit, maximum file size, and reserve disk space, control the size of the audit files, manage the use of hard disk space, and avoid hard disk explosion.

The Audit object created is disabled by default (Disable), must be enabled before using the Audit object, select the newly created Audit object, right-click, and select Enable Audit.

Second, create a server-level audit specification

Expand Security, select "Server Audit Specifications", right-click the popup shortcut menu, select "New Server Audit Specifications", open the "Create server Audit Specifications "form, create an audit specification through the UI:

The audit specification specifies the type of event logged by the Audit object, the type of event specified in the audit specification, and once SQL Server detects an event, the information associated with the event is written to the file specified by the Audit object and saved for subsequent checks (review).

1, add Audit Action Group

Create an audit specification, select an Audit object, select an audit action group from the Audit action type list, create an audit specification that is disabled by default, select the newly created audit specification, right-click the popup shortcut menu, select "Enable Server Audit Specifications" Enable:

Select the audit Action group from the list of audit action types, the Audit Action Group is the event action type of audit record, and the Common Audit Action Group is:

• Database_object_access_group: This event is raised when a database object is accessed;
• Database_object_change_group: This event is raised when a CREATE, ALTER, or DROP statement is executed against a database object, such as a schema. This event is raised when any database object is created, changed, or deleted.
• Database_operation_group: This event is raised when an action occurs in the database, such as a checkpoint or subscription query notification. This event is raised for any operation on any database.
• Failed_database_authentication_group: indicates that a principal attempted to log on to the database and failed.
• Failed_login_group: Indicates that the principal attempted to log on to SQL Server but failed.
• Successful_login_group: Indicates that the principal has successfully logged on to SQL Server.

2. View Audit data

Click on the Audit object, right click on the popup shortcut menu, click "View Audit Logs" To view the audit object recorded data:

or view the audit data through the TSQL function,

sys.fn_get_audit_file (File_pattern,       default| | NULL },       default| | NULL } )  

The audit file name consists of four parts: Auditname_guid_n_m.sqlaudit, the first parameter is File_pattern, including the path and file name, and for the file name, you can specify by a special match:

• *: denotes all characters;
• {}: Specifies the GUID;
• If the file name is extended with an extension (. Sqlaudit) end, indicating the viewing of a specific file;

For example, view the data for all audit files:

Select *  from Sys.fn_get_audit_file ('e:\auditfiles\*',default,default )

Third, create a database-level audit specification

In database security, right-clicking on "Database Audit specifications", DB-level audit action group, mostly similar to server-level audit action groups, except for database-level audit actions (Database-level Audit Actions), the event information is logged when the following action occurs on the database object:

• SELECT
• UPDATE
• INSERT
• DELETE
• EXECUTE
• REFERENCES

Database-level Audit operations track and record events that occur on database objects (schema,objects), so you must configure the object Class, object Schema,object name, and Principal name fields:

Review the audit data, select the Audit object at the server level, and view the recorded log data through View Audit Logs.

IV, maintenance audits

Creating an audit is simple, and DBAs need to pay more attention to the maintenance of audit data

The following supplement

Reference Documentation:

SQLSERVER2008 new Audit/Audit features

SQL Server Audit (Database Engine)

SQL Server Audit Action Groups and Actions

SYS.FN_GET_AUDIT_FILE (Transact-SQL)

SQL Server Audit