T-SQL SSRS: SQL Theory: Native Auditing Support in Modern Relational Database Management System

Native Auditing Support in Modern Relational Database Management System
Traditionally, Relational Database Management Systems (RDBMS) ship with an auditing tool that allows database administrators to monitor the database from a security perspective. Information provided includes what events occur (logon/logoff), what database objects are being accessed, what data is queried, etc.

The most common reason for using auditing is to determine when a database user executes some sort of SQL. The SQL statement is logged by the auditing subsystem in the form of a clear text log file, xml file, binary file, audit table(s) or remote system log. Auditing occurs separately from any transaction – even when a transaction is rolled back, the auditing log will contain records about statements executed if the auditing is configured to do so.

It is extremely important to properly configure auditing by only auditing the events that require monitoring. Auditing has an impact on performance so you shouldn’t audit what isn’t necessary. In addition, the audit trail (audit data) location should be protected to avoid manipulation by any user. This necessary step ensures accuracy of the logs.

Most modern databases have similar auditing architecture. Auditing should be installed, enabled, and configured to allow the audit data to be examined via SQL query. Let’s examine how this is done in three of the most popular databases – Microsoft SQL Server 2008 R2, Oracle Database 11g R2, and Sybase ASE 15.5.

Microsoft SQL Server 2008 R2

By default, the server is not configured to log many security-related events. In the server log, one may see entries like these that may give a glimpse on what was going on:

Using 'xpstar.dll' version '2009.100.1600' to execute extended stored procedure 'xp_readerrorlog'. This is an informational message only; no user action is required.

In addition, by default the server keeps a small (~5 MB) trace file that might be used to recover some events like failed logins. However, these are not true auditing features. Audit is a powerful facility that can be configured on the server and database levels and allows event monitoring on a very granular level. To configure audit on the server level for failed login events we can run:

-- Specify where to keep the audit data

CREATE SERVER AUDIT FailedLoginsAudit TO FILE
FILEPATH = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\’)

– Specify events to audit

CREATE SERVER AUDIT SPECIFICATION FailedLoginsAudit_Specification
FOR SERVER AUDIT FailedLoginsAudit
ADD (FAILED_LOGIN_GROUP) WITH (STATE = ON)

– Enable entire thing

ALTER SERVER AUDIT FailedLoginsAudit
WITH (STATE = ON)

After these steps a file named like the one below appears under the path specified inFILEPATH:

FailedLoginsAudit_67E198CB-8552-4197-BFAD-D0446F2D6BB9_0_129552391845230000.sqlaudit

The unique identifier part of the name (67E198CB-8552-4197-BFAD-D0446F2D6BB9) is configurable via the AUDIT_GUID option of the CREATE SERVER AUDIT statement.

Now all failed logins are audited. Next, try to login with an invalid login. Then read the audit data via:

SELECT * FROM sys.fn_get_audit_file (N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\*', default, default)
GO

The same concept applies to database level auditing: see the CREATE DATABASE AUDIT SPECIFICATION statement description in SQL Server Books Online.

Microsoft SQL Server 2008 R2 supports the c2 audit mode. The c2 audit mode logs both failed and successful attempts to access statements and objects. To enable this mode, run the following:

exec sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
exec sp_configure 'c2 audit mode', 1
GO
RECONFIGURE
GO

The results are saved in files under the instance DATA directory and have names likeaudittrace20110715170611.trc. The files are regular trace files and thus can be viewed with the Profiler tool usually used to troubleshoot performance.

Oracle Database 11g R2

In Oracle Databases auditing can be configured via a parameter named audit_trail:

ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

This will record auditing information inside the database in the special table SYS.AUD$, except for mandatory and SYS audit records that are stored in the operating system log. Note that the database has to be restarted for the changes to take effect.

In addition, auditing for particular events (e.g. logon and logoff) can be enabled:

AUDIT SESSION BY ACCESS;

Now all logins will be audited. If user james attempts to log in with a wrong password the following record will appear in the SYS.AUD$ table:

SELECT USERID, action#, returncode FROM sys.aud$

USERID ACTION# RETURNCODE
—————————— ———————- —————-
JAMES 100 1017

The above result corresponds to failed logins: ORA-01017: invalid username/password; logon denied. There are many more columns in the SYS.AUD$ table to examine – the above query is just to illustrate how this data can be used.

There are many more options that allow DBA’s to monitor database activity at various levels. These options include the ability to monitor access to specified objects and specified columns and even rows (fine-grained auditing). Audit data can be saved in different locations including the database, operating system log and external files. In any case, the audit trail destination should be protected to prevent auditing data from being manipulated.

Sybase ASE 15.5

The first step to enable auditing on Sybase ASE 15.5 is to install the auditing subsystem. By default, the auditing subsystem isn’t installed. To install the auditing subsystem, the special program auditinit or a SQL script are used. The Sybase ASE documentation has more details on this. Ultimately, this creates a special databasesybsyssecurity and populates it with objects. In the Sybase ASE auditing architecture, several tables (by default sysaudits_01 through sysaudits_08) are used to keep the audit trail. The current table can be specified by the administrator via sp_configure "current audit table". When the number of records in the current audit table has reached its threshold, the contents may be archived to a different database via a special threshold procedure.

Then auditing should be enabled on the instance:

exec sp_configure "auditing", 1

The next step is to configure what to audit. For example, to configure failed logins auditing:

exec sp_audit "login", "all", "all", "fail"

To query auditing data:

SELECT * FROM sybsecurity.dbo.sysaudits_01
event,eventmod,spid,eventtime,sequence,suid,dbid,objid,xactid,loginname,dbname,objname,objowner,extrainfo,nodeid
45,2,0,2011-07-18 23:37:57.42,1,0,1,,,'james','master',,,'; ; ; ; SYBASE15, 192.168.0.1, network password no encryption , 4067.14.1; ; james/ase; ',

Above is a record for a failed login attempt by user james.

Sybase ASE auditing subsystem allows for detailed events configuration, however, this shows just a simple example.

Conclusion

Modern databases provide powerful built-in auditing capabilities that are often underestimated. There are downsides of native auditing like performance degradation and the ability of a malicious user to manipulate or erase the audit trail. Overall, this feature allows customers to monitor database activity at a very granular level provided that they invest in setting up the appropriate auditing policies. To provide a compensating control where native auditing has a weakness, third-party database activity monitoring (DAM) solutions should be implemented.