Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 6, 2010

Auditing made easy by Microsoft SQL Server 2008

By Muthusamy Anantha Kumar aka The MAK

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM