Auditing made easy by Microsoft SQL Server 2008

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

Latest Articles