Auditing made easy by Microsoft SQL Server 2008

January 6, 2010

It is one of the major tasks of the Microsoft SQL Server database administrator to enable auditing on all of the servers, secure the data collected and extract the data from those audits for the internal and external auditors.

Auditing is very important to track and log events. It provides data for many legal compliance requirements, such as Sarbanes Oxley, SAS 70, etc.

In Microsoft SQL Server 2008, Microsoft made the life of a DBA easy by providing the enhanced auditing feature "SQL Server Audit". In this article series, I am going to illustrate the various components involved in SQL Server auditing as well as demonstrate how to create server level, database level and audit level audit using action and action group specification provided by Microsoft SQL Server 2008.

This part of the article series illustrates the various components for auditing and the action groups provided by Microsoft SQL Server 2008.

Components of Auditing

  • SQL Server Audit
  • Server Level Audit Specification
  • Database Level Audit Specification
  • Target

SQL Server Audit object is the audit component that collects Server or Database level actions or group of actions that we want to monitor.

Server Level Audit specification object is the audit component that collects server level actions or group of actions that we want to monitor.

Database Level Audit specification object is the audit component that collects database level actions or group of actions that we want to monitor.

Target component is the destination where you want to store the audit records. It could be Windows Application or Security event log or it could be a file.

Steps involved in creating and using Audit

  • Create Audit and define target
  • Create Server level audit specification, Database level audit specification or Audit level specification that maps to the Audit defined.
  • Enable the Audit specification
  • Enable the Audit
  • Read the audit event records from Windows Application or Security event log using Event Viewer or read it from a file using the SQL Server function fn_get_audit_file function

Audits Action Categories

Audit can have the following categories of Actions

  • Server Level: Includes Server operations
  • Database Level: Include DDL and DML.
  • Audit Level: Include the auditing process

Microsoft SQL Server 2008 has grouped actions for Server Level, Database Level and Audit Level actions that we could use when creating the Server Level or Database Level Audit specification.

The following are the Server Level Audit action groups that could be defined in the Audit specification

  • SUCCESSFUL_LOGIN_GROUP
  • LOGOUT_GROUP
  • FAILED_LOGIN_GROUP
  • LOGIN_CHANGE_PASSWORD_GROUP
  • APPLICATION_ROLE_CHANGE_PASSWORD_GROUP
  • SERVER_ROLE_MEMBER_CHANGE_GROUP
  • DATABASE_ROLE_MEMBER_CHANGE_GROUP
  • BACKUP_RESTORE_GROUP
  • DBCC_GROUP
  • SERVER_OPERATION_GROUP
  • DATABASE_OPERATION_GROUP
  • AUDIT_ CHANGE_GROUP
  • SERVER_STATE_CHANGE_GROUP
  • SERVER_OBJECT_CHANGE_GROUP
  • SERVER_PRINCIPAL_CHANGE_GROUP
  • DATABASE_CHANGE_GROUP
  • DATABASE_OBJECT_CHANGE_GROUP
  • DATABASE_PRINCIPAL_CHANGE_GROUP
  • SCHEMA_OBJECT_CHANGE_GROUP
  • SERVER_PRINCIPAL_IMPERSONATION_GROUP
  • DATABASE_PRINCIPAL_IMPERSONATION_GROUP
  • SERVER_PRINCIPAL_IMPERSONATION_GROUP
  • DATABASE_PRINCIPAL_IMPERSONATION_GROUP
  • SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP
  • DATABASE_OWNERSHIP_CHANGE_GROUP
  • DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP
  • SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP
  • SERVER_PERMISSION_CHANGE_GROUP
  • SERVER_OBJECT_PERMISSION_CHANGE_GROUP
  • DATABASE_PERMISSION_CHANGE_GROUP
  • DATABASE_OBJECT_PERMISSION_CHANGE_GROUP
  • SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP
  • DATABASE_OBJECT_ACCESS_GROUP
  • SCHEMA_OBJECT_ACCESS_GROUP
  • BROKER_LOGIN_GROUP
  • DATABASE_MIRRORING_LOGIN_GROUP
  • TRACE_CHANGE_GROUP

The following are the Database Level Audit action groups that could be defined in the Audit specification

  • DATABASE_ROLE_MEMBER_CHANGE_GROUP
  • DATABASE_OPERATION_GROUP
  • DATABASE_CHANGE_GROUP
  • DATABASE_OBJECT_CHANGE_GROUP
  • DATABASE_PRINCIPAL_CHANGE_GROUP
  • SCHEMA_OBJECT_CHANGE_GROUP
  • DATABASE_PRINCIPAL_IMPERSONATION_GROUP
  • DATABASE_OWNERSHIP_CHANGE_GROUP
  • DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP
  • SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP
  • DATABASE_PERMISSION_CHANGE_GROUP
  • DATABASE_OBJECT_PERMISSION_CHANGE_GROUP
  • SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP
  • DATABASE_OBJECT_ACCESS_GROUP
  • SCHEMA_OBJECT_ACCESS_GROUP

The following are the Audit Level Audit action groups that could be defined in the Audit specification

  • AUDIT_ CHANGE_GROUP

The following are the Database Level Audit action that could be defined in the Audit specification

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

The target audit file consists of zero or more audit action items, which are recorded. As mentioned previously, the target component is the destination where you want to store the audit records. It could be Windows Application or Security event log or it could be a file. The following are the elements in the records sent to the target.

  • event_time
  • sequence_no
  • action_id
  • succeeded
  • permission_bitmask
  • is_column_permission
  • session_id
  • server_principal_id
  • database_principal_id
  • object_ id
  • target_server_principal_id
  • target_database_principal_id
  • class_type
  • session_server_principal_name
  • server_principal_name
  • server_principal_sid
  • database_principal_name
  • target_server_principal_name
  • target_server_principal_sid
  • target_database_principal_name
  • server_instance_name
  • database_name
  • schema_name
  • object_name
  • TSQL statement
  • additional_information

Conclusion

Part 1 of this series illustrated the various components for auditing, the action groups provided by Microsoft SQL Server 2008 and the various elements in the target records. Part 2 will illustrate how to define and enable Server Level Audit and read the target audit records.

» See All Articles by Columnist MAK








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers