DB Best blog

Standard Oracle auditing feature allows capturing many pieces of information about database events. You can track the fact that an event occurred, the time when it occurred, the user who caused the audited event, and which client machine the user was on when the event happened. An audited event can be a user login, a change of system/object privileges, or an execution of specific SQL statement. Also, you can choose between storing audit records in the database and writing the records into operating system audit trail. Oracle allows to track SQL statements executed on a table and to watch user sessions.

Audit records generated by Oracle can be seen in the view DBA_AUDIT_TRAIL.

We cannot create a similar auditing process in SQL server 2005. But SQL Server 2008 has a more advanced audit feature, and it becomes possible to cover most of the Oracle auditing functionality. I will show how to do this by an example.

First step is to create Server Audit object. It will write audit records to operating system files. This is how you can create Server Audit with name “AuditName” and with file path “C:\Audit” in SQL Server 2008:

CREATE SERVER AUDIT [AuditName]
TO FILE
(
FILEPATH = N’C:\Audit\’
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
(
AUDIT_GUID = ‘5c185dce-8a23-4a6e-944c-e080209b7173′
)

The file name is generated based on the audit name and optional AUDIT_GUID parameter. If AUDIT_GUID is not supplied explicitly, it will be generated. Entering it manually is necessary in a few scenarios such as database mirroring, because an audit needs a specific GUID that matches the GUID located in the mirrored database. The GUID cannot be modified after the audit has been created. The generated GUID can be retrieved from sys.server_audits view. To choose actions to be recorded we should use a Server Audit Specification object, which links the actions to an existing Server Audit.

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecificationName]
FOR SERVER AUDIT [AuditName]ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGOUT_GROUP)
WITH (STATE = ON)
GO

Next step is to process the audit records after they have been recorded. SQL Server 2008 provides a special system function fn_get_audit_file, which allows inspecting audit records in Transact-SQL statements. We can retrieve the records from multiple files at once, for example:

select * from fn_get_audit_file (’C:\Audit\*’, NULL, NULL);

Of course, it is also possible to read auditing information from a single audit file:

select * from fn_get_audit_file (’ AuditName_5c185dce-8a23-4a6e-944c-e080209b7173 _0_128703816983800000.sqlaudit’, NULL, NULL);

To increase the performance of a complex query on audited data it is recommended to cache the result set in a database table, and then read the cached data only:

select * into tmptbl from fn_get_audit_file (’C:\Audit\*’, NULL, NULL);

SQL Server 2008 stores detailed information about each event in XML format, so you need to use XQuery syntax to retrieve these additional fields. For example, the following query shows all failed login attempts.

WITH XMLNAMESPACES(’http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data’ AS PI)
select server_principal_name as USERNAME,
          CAST(additional_information as xml).query(’(/PI:action_info/PI:address)[1]‘).value(’.', ‘nvarchar(max)’) as TERMINAL,
event_time,
          CAST(additional_information as xml).query(’(/PI:action_info/PI:error)[1]‘).value(’.', ‘nvarchar(max)’) as ERRORCODE,
          CAST(additional_information as xml).query(’(/PI:action_info/PI:state)[1]‘).value(’.', ‘nvarchar(max)’) as ERRORSTATE
from tmptbl
where action_id=’LGIF’;

If you need more information on this topic, please contact newsletter@dbbest.com

Written by Yuri Rusakov

Tagged as Audit, Migration, Oracle, SQL Server + Categorized as Newsletters, Newsletter #3, December 18, 2008