Native Auditing In Modern Relational Database Management

Wednesday, August 03, 2011

Alexander Rothacker

B451da363bb08b9a81ceadbadb5133ef

Native Auditing Support In Modern Relational Database Management Systems

By Martin Rakhmanov, TeamSHATTER

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\’)

GO

– Specify events to audit

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

GO

– Enable entire thing

ALTER SERVER AUDIT FailedLoginsAudit
WITH (STATE = ON)

GO

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

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 like audittrace20110715170611.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 database sybsyssecurity 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.

References

Auditing (Database Engine) in SQL Server 2008 R2
http://msdn.microsoft.com/en-us/library/cc280526.aspx

Verifying Security Access with Auditing
http://download.oracle.com/docs/cd/E11882_01/network.112/e16543/auditing.htm

Introduction to auditing in Adaptive Server
http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc31654.1550/html/sag1/X85522.htm

Cross-Posted from www.TeamSHATTER.com

Possibly Related Articles:
7464
General
Information Security
Databases Oracle Security Audits Monitoring TeamSHATTER RDBMS SQL Server
Post Rating I Like this!
Default-avatar
Chris Rich Native SQL auditing can get you the basics, however it was not designed to support many compliance needs. If you have multiple systems spread across a large area, prefer not having to scour audit logs and would like some form of reporting, a 3rd-party solution such as NetWrix SQL Server Change Reporter should be considered. This tool automates collection of audit data including who changed what, when and where without any programming and presents useful reports showing both before and after values for databases, permissions, security, and server settings to manage security and sustain compliance. It’s cost-effective, and easy-to-use and install. Free trial can be downloaded from this link. http://www.netwrix.com/sql_server_audit_change_reporter_freeware.html

Chris Rich
Product Manager, NetWrix Corporation
NetWrix is #1 for Change Auditing: Simple, Lightweight, Affordable
www.netwrix.com
1312918398
Default-avatar
John Langston Working in a SQL 2005/SQL 2008 R2 environment, I like Microsoft's Log Parser for zeroing in on Failed login attempts. The price is right (free).

Regardless of the approach, either a set of eyes or something akin to a signature-based tool that calls attention to events meeting a specific criteria have to scan the reports for significant events, like failed login attempts.
1312919482
B451da363bb08b9a81ceadbadb5133ef
Alexander Rothacker Free tools are great for very specific purposes. However, consider what you store in your database. For mission critical databases, or databases containing sensitive information, like PII, credit card data, etc. I would recommend to invest in a commercial 3rd party database activity monitoring solution.
1312925559
The views expressed in this post are the opinions of the Infosec Island member that posted this content. Infosec Island is not responsible for the content or messaging of this post.

Unauthorized reproduction of this article (in part or in whole) is prohibited without the express written permission of Infosec Island and the Infosec Island member that posted this content--this includes using our RSS feed for any purpose other than personal use.