Auditing in Oracle

 

Auditing is the monitoring and recording of selected user database actions. Auditing is normally used to

 

NATIVE DATABASE AUDITING

The Oracle Database Server provides a fairly robust set of auditing capabilities “out of the box”. This is implemented as a

system, which writes activity to tables, log files, or even the Event Viewer on Windows. There are several ways you can record

activity in Oracle. But along with each method, there are shortcomings. Below we explore each option.

 

DATABASE AUDITING

Oracle’s first form of auditing is a subsystem you can use to record failed and successful attempts on the server. Recording

connection attempts is useful in being able to discover:

 

1) Who is attempting to connect to the database

2) When an attack is taking place

3) If an attack was successful.

 

To enable auditing in Oracle, start by configuring the proper settings in the init.ora file:

 

audit_trail=true

 

This is not enabled by default. You may need to execute the script ORACLE_HOME\rdbms\admin\cataudit.sql

using the SYS account if the auditing subsystem was not installed, or was uninstalled. This is not usually needed because all

auditing tables, views, and procedures are installed by default. You can then control the Oracle auditing subsystem using

system commands such as:

AUDIT ALL BY user1 BY ACCESS;

AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY user1 BY SESSION;

AUDIT EXECUTE PROCEDURE BY user1 BY ACCESS;

Types of Auditing

Oracle supports three general types of auditing:

statement auditing

The selective auditing of SQL statements with respect to only the type of statement, not the specific objects on which it operates. Statement auditing options are typically broad, auditing the use of several types of related actions per option; for example, AUDIT TABLE, which tracks several DDL statements regardless of the table on which they are issued. You can set statement auditing to audit selected users or every user in the database.

privilege auditing

The selective auditing of the use of powerful system privileges to perform corresponding actions, such as AUDIT CREATE TABLE. Privilege auditing is more focused than statement auditing, auditing only the use of the target privilege. You can set privilege auditing to audit a selected user or every user in the database.

object auditing

The selective auditing of specific statements on a particular schema object, such as AUDIT SELECT ON EMP. Object auditing is very focused, auditing only a specific statement on a specific object. Object auditing always applies to all users of the database.

The AUDIT command is fairly flexible. You can use it to set auditing on specific objects, commands, or actions. You can also

use it to set auditing based on the user taking an action. You can record events on every access. Or, you can record just the

first access for a session. To disable auditing, the corresponding NO AUDIT command takes identical parameters to disable

the auditing you configured with the AUDIT command. Records are typically stored in a table called SYS.AUD$. This can,

however, also be stored at the operating system level. To view the values from the table, use one of the following auditing

views:

• DBA_AUDIT_EXISTS

• DBA_AUDIT_OBJECT

• DBA_AUDIT_SESSION

• DBA_AUDIT_STATEMENT

• DBA_AUDIT_TRAIL

• DBA_OBJ_AUDIT_OPTS

• DBA_PRIV_AUDIT_OPTS

• DBA_STMT_AUDIT_OPTS

Focus of Auditing

Oracle allows audit options to be focused or broad in the following areas:

Audit Records and the Audit Trail

Audit records include such information as the operation that was audited, the user performing the operation, and the date/time of the operation. Audit records can be stored in either a data dictionary table, called the audit trail, or an operating system audit trail.

The database audit trail is a single table named AUD$ in the SYS schema of each Oracle database's data dictionary. Several predefined views are provided to help you use this information. Instructions for creating and using these views are included in the Oracle  Server Administrator's Guide.

Depending on the events audited and the auditing options set, the audit trail records can contain different types of information. The following information is always included in each audit trail record, provided that the information is meaningful to the particular audit action:

Audit trail records written to the OS audit trail contain some encodings that are not human readable. These can be decoded as follows:

Action Code

This describes the operation performed or attempted. The AUDIT_ACTIONS data dictionary table contains a list of these codes and their descriptions.

Privileges Used

This describes any system privileges used to perform the operation. The SYSTEM_PRIVILEGE_MAP table lists all of these codes and their descriptions.

Completion Code

This describes the result of the attempted operation. Successful operations return a value of zero, while unsuccessful operations return the Oracle error code describing why the operation was unsuccessful.

Considerations

Auditing at this level can have several shortcomings. First, since auditing is based in the database, it can detract from the

system’s performance. This is especially true when you attempt to record every access to certain data; the constant reading and

writing of auditing can result in substantial disk I/O on the database server, creating a bottleneck that significantly slows down

database performance. Another disadvantage: since auditing data is stored in the SYS.AUD$ table, it ends up sharing disk

space with user data, resulting in possible application downtime when log files fill up.

These two disadvantages merit consideration. The bigger issue is this: control of the database implies full control of the

auditing system. There is no way to:

• provide segregation of duties

• limit the DBA from disabling the auditing

• limit the DBA from deleting audit records

• limit the DBA from changing auditing configuration.

Segregation of duties is the key to meaningful security and regulatory compliance. The auditing subsystem must retain integrity,

and must not be manipulated by the users it is meant to monitor. The “observer” and the “observed” can not be the same

person.

This same shortcoming also applies to database intrusions. If I hack into your database, having an audit system to purge will

leave you with no forensic evidence. For the audit trail to maintain an acceptable level of integrity, it must be able to withstand

an attacker taking control of the database — and not lose the existing audit trail.

 

 

AUDIT_SYS_OPERATIONS

The AUDIT_SYS_OPERATIONS parameter logs SYS user operations to the operating system file that contains the audit

trail. This parameter was added to Oracle because, in earlier versions, these actions could not (and still can’t) be logged to the

SYS.AUD$ table. To configure the operating system file to which to log data, set the parameter AUDIT_FILE_DEST in the

init.ora file. This parameter does not affect the Microsoft Windows environments since, by default, all audit data is written to

the event log. Nor does the parameter affect other parameters, such as AUDIT_TRAIL. This parameter is new as of Oracle9i

release 2. By default this value is set to false. You can enable this setting by adding the following line to the init.ora file:

 

AUDIT_SYS_OPERATIONS=true

 

After changing this value, you must stop and restart the database.

 

The AUDIT_SYS_OPERATIONS also has shortcomings similar to those found in other Oracle native auditing methods.

Auditing directly impacts system performance since it runs in the Oracle software. In addition, audited data is not protected

against attackers who successfully break in and gain control of the database. Finally, the data is not protected against the DBA,

who is the individual AUDIT_SYS_OPERATIONS is designed to track and monitor.

When Do Audit Options Take Effect?

Statement and privilege audit options in effect at the time a database user connects to the database remain in effect for the duration of the session. A session does not see the effects of statement audit options being set or changed. A database user only adheres to modified statement or privilege audit options when the current session is ended and a new session is created. On the other hand, changes in object audit options become effective for current sessions immediately.

Auditing in a Distributed Database

Auditing is site autonomous; an instance audits only the statements issued by directly connected users. A local Oracle node cannot audit actions that take place in a remote database. Because remote connections are established via the user account of a database link, the remote Oracle node audits the statements issued via the database link's connection. See Chapter 21, "Distributed Databases", for more information about distributed databases and database links.

Auditing to the OS Audit Trail

Both Oracle7 and Trusted Oracle7 allow audit trail records to be directed to an operating system audit trail on platforms where the OS makes such an audit trail available to Oracle. On some other operating systems, these audit records are written to a file outside the database, with a format similar to other Oracle trace files.

Additional Information: See your platform-specific Oracle documentation to see if this feature has been implemented on your operating system.

Trusted Oracle and Oracle allow certain actions that are always audited to continue even when the operating system audit trail, or the operating system file containing audit records, is unable to record the audit record. The normal cause of this is that the operating system audit trail, or the file system, is full and unable to accept new records.

When configured with OS auditing, system administrators should ensure that the audit trail or the file system does not fill completely. Most operating systems provide extensive measures to provide administrators with sufficient information and warning to ensure this does not occur. Furthermore, configuring auditing to use the database audit trail removes this vulnerability, as the Oracle Server prevents audited events from occurring if the audit trail is unable to accept the audit record for the statement.

Statement Auditing

 

Statement auditing is the selective auditing of related groups of statements that fall into two categories:

Statement auditing can be broad and audit the activities of all database users, or focused and audit only the activities of a select list of database users.

Privilege Auditing

Privilege auditing is the selective auditing of the statements allowed using a system privilege. For example, auditing of the SELECT ANY TABLE system privilege audits users' statements that are executed using the SELECT ANY TABLE system privilege.

You can audit the use of any system privilege. In all cases of privilege auditing, owner privileges and object privileges are checked before the use of system privileges. If these other privileges suffice to permit the action, the action is not audited. If similar statement and privilege audit options are both set, only a single audit record is generated. For example, if the statement option TABLE and the system privilege CREATE TABLE are both audited, only a single audit record is generated each time a table is created.

Privilege auditing is more focused than statement auditing because each option audits only specific types of statements, not a related list of statements. For example, the statement auditing option TABLE audits CREATE TABLE, ALTER TABLE, and DROP TABLE statements, while the privilege auditing option CREATE TABLE audits only CREATE TABLE statements, since only the CREATE TABLE statement requires the CREATE TABLE privilege.

Privilege auditing can be broad, and audit the activities of all database users, or focused, and audit only the activities of a select list of database users.

Object Auditing

Object auditing is the selective auditing of specific DML statements (including queries), and GRANT and REVOKE statements for specific schema objects. Object auditing audits the operations permitted by object privileges, such as SELECT or DELETE statements on a given table, as well as the GRANT and REVOKE statements that control those privileges.

You can audit statements that reference tables, views, sequences, standalone stored procedures and functions, and packages (procedures in packages cannot be audited individually). Notice that statements that reference clusters, database links, indexes, or synonyms are not audited directly.

You can, however, audit access to these objects indirectly by auditing the operations that affect the base table. Object audit options are always set for all users of the database; these options cannot be set for a specific list of users. Oracle provides a mechanism for setting default object audit options for all auditable schema objects.

Object Audit Options for Views and Procedures

Because views and procedures (including stored functions, packages, and triggers) reference underlying objects in their definition, auditing with respect to views and procedures has several unique characteristics. Several audit records can potentially be generated as the result of using a view or a procedure. Not only is the use of the view or procedure subject to enabled audit options, but the SQL statements issued as a result of using the view or procedure are subject to the enabled audit options of the base objects (including default audit options).

As an illustration of this situation, consider the following series of SQL statements: 

AUDIT SELECT ON emp; 
 
CREATE VIEW emp_dept AS 
               SELECT empno, ename, dname 
                               FROM emp, dept 
                               WHERE emp.deptno = dept.deptno; 
 
AUDIT SELECT ON emp_dept; 
 
SELECT * FROM emp_dept;

As a result of the query on EMP_DEPT, two audit records are generated: one for the query on the EMP_DEPT view and one for the query on the base table EMP (indirectly via the EMP_DEPT view). The query on the base table DEPT does not generate an audit record because the SELECT audit option for this table is not enabled. All audit records pertain to the user that queried the EMP_DEPT view.

The audit options for a view or procedure are determined when the view or procedure is first used and placed in the shared pool. These audit options remain set until the view or procedure is flushed from, and subsequently replaced in, the shared pool. Auditing an object invalidates that object in the cache and causes it to be reloaded. Any changes to the audit options of base objects are not observed by views and procedures in the shared pool. Continuing with the above example, if auditing of SELECT statements is turned off for the EMP table, use of the EMP_DEPT view would no longer generate an audit record for the EMP table.

 

Focusing Statement, Privilege, and Object Auditing

Oracle allows statement, privilege, and object auditing to be focused in two areas:

In addition, you can enable statement and privilege auditing for specific users or for all users in the database.

Auditing Successful and Unsuccessful Statement Executions

For statement, privilege, and object auditing, Oracle allows the selective auditing of successful executions of statements, unsuccessful attempts to execute statements, or both. Therefore, you can monitor actions even if the audited statements do not complete successfully.

You can audit an unsuccessful statement execution only if a valid SQL statement is issued but fails because of lack of proper authorization or because it references a non-existent object. Statements that failed to execute because they simply were not valid cannot be audited. For example, an enabled privilege auditing option set to audit unsuccessful statement executions audits statements that use the target system privilege but have failed for other reasons (for example, CREATE TABLE is set, but a CREATE TABLE statement fails due to lack of quota for the specified tablespace).

Using either form of the AUDIT command, you can include

Auditing BY SESSION versus BY ACCESS

Most auditing options can be set to indicate how audit records should be generated if the audited statement is issued multiple times in a single user session. These sections describe the distinction between the BY SESSION and BY ACCESS options of the AUDIT command.

 

 

BY SESSION

BY SESSION inserts only one audit record in the audit trail, per user and object, per session that includes an audited action. This applies regardless of whether the audit is of an object, a statement, or a privilege.

To demonstrate how the BY SESSION option allows the generation of audit records, consider the following two examples.

Example 1 Assume the following:

In this case, the audit trail will contain two audit records for the eight SELECT statements (one for each session that issued a SELECT statement).

Example 2 Alternatively, assume the following:

In this case, the audit trail will contain two records (one for each object against which the user issued a SELECT statement in a session).

Although you can use the BY SESSION option when directing audit records to the operating system audit trail, this generates and stores an audit record each time an access is made. Therefore, in this auditing configuration, BY SESSION is equivalent to BY ACCESS.

Note: A session is the time between when a user connects to and disconnects from an Oracle database.

BY ACCESS

Setting audit BY ACCESS inserts one audit record into the audit trail for each execution of an auditable within a cursor. Events that cause cursors to be reused include the following:

Note that auditing is NOT affected by whether a cursor is shared; each user creates her or his own audit trail records on first execution of the cursor.

Example Assume the following:

The audit trail contains eight records for the eight SELECT statements.

 

WATCHING THE DBA

Presently, in most organizations, the DBA is the unrestricted owner of the database. An organization’s most critical

information is entirely exposed and controlled by this small handful of technologists. This leaves both the DBA, and the entire

organization, in a precarious position. The DBAs are afraid they will be blamed for any information leak. The organization is

forced to trust a small group of professionals in its technology group.

One way to mitigate risk is to audit and monitor DBA activities. Limit the amount of work a DBA does on a production

server. Auditing and monitoring this data should not add significant overhead to any system.

How do you properly audit database activity? Not through native auditing, which fails here because it is fully under the control

of the DBAs, who can turn off auditing, clear the audit logs, manipulate an audit record, or even reconfigure auditing to filter

their own malicious activity. Auditing should ultimately enable a separation of duty. An ideal audit system is intelligent enough

to distinguish database administration accounts, filter out “noise” and irrelevant events, and succinctly illustrate its activities.

As well, the system should write audited data to a secure location where even the DBA would not have direct control over the

recorded activity.

 

 

WATCHING TEMPORARY ACCOUNTS

Another type of activity that requires monitoring is the use of temporary and special accounts. Many companies have

procedures through which the database administrator can request a temporary account for others or for themselves to manage

databases as required. For instance, the DBA will request that the operations team create a temporary account for which to

logon and manage the database when the database goes down or when backups need to be recovered. This account will be set

to expire in several hours after which the account will be deleted.

This is an adequate system for reducing the exposure of a malicious database administrator. However it still leaves some

exposure in that it is difficult to track exactly what that administrator does during the period of time the temporary account

exists. An ideal monitoring and auditing system can provide real value in this situation. A system that can track the activity of

the temporary database administrator can help you to easily review the activities and ensure that nothing malicious occurred.

 

AUDITING ACCESS TO SENSITIVE DATA

Your auditing system should also monitor access to sensitive data in a subset of tables. A typical database contains massive amounts of data. Some of this data is not sensitive at all. However, if other data falls into the wrong hands, the consequences could be disastrous. Auditing every database action can lead to information overload. For instance, if you have a lookup table to map a product to a product ID, there is not much value in auditing access to that table. That table may be accessed thousands of times a day, and auditing all those accesses to the table would result in so much “noise” it could bury a realattack. Other tables may include credit card numbers, payroll information, or social security numbers. Access to these tables should, of course, be audited and monitored closely. This type of auditing requires that DBAs or application owners decide before-hand what data is sensitive, and define it as Duch in the auditing system. The auditing system should be able to accept and configure the list of databases, tables, objects, and columns to monitor, and should also be easily configured to monitor specified actions on the table. For instance, if you have static data that is public information, you may not want to audit who performs a SELECT from the table. However, you indeed want to record who modifies the data. In that case, you need the ability to audit any UPDATE, DELETE, or INSERT made by any user.

 

FLEXIBILITY TO FILTER RESULTS

There exists a real need to filter how data is audited based on who is accessing the data. For instance, HIPAA regulations require strong accountability to access of patient records. If a system administrator accesses patient Jane Smith’s medical records on June 15th , there must be a record of the action to ensure accountability for the data. On the other hand, if the patient’s doctor

accesses the data twenty times in a day, there’s little value in recording this activity multiple times. Auditing should record unauthorized users’ attempts to access data — yet should be flexible enough to minimize the “noise” level. Keeping “noise” level down can be accomplished by minimizing the recording of activity performed by authorized personnel. An ideal auditing solution allows you to filter auditing based on factors such as account name, source of activity, and the time of the activity.

 

AUDITING EXCEPTIONS

Audit systems should be able to “approve” traffic to prevent valid activity from continuing to trigger alerts. For instance, an application may legitimately access data in the database that is being monitored by the auditing system. This is good to know when you first install and set up the auditing system. However, it becomes “noise” after you see that data a few hundred times. By “noise”, in this case, I’m referring to scenarios where you gain no value from seeing the alert, and it only contributes to drowning out other more valuable audit data. If you get 10,000 audit records a day, it’s going to be hard to see the one record that really matters because it’s buried in information overload. This is why it is so important for an effective audit system to reduce the number of items audited, and only catch the things we care about. This goal is accomplished by allowing “exceptions”. For instance, an exception might say: “Do not record access to data when a specific SQL statement comes from user XYZ from machine ABC”. When any other access to the data occurs, the audit system should record the activity. As well, a proper auditing system should be able to record any activity that does not match specific criteria. For instance, the system should allow you to say: “Record all activity except for SQL statements from application XYZ.”

 

IDENTIFYING UNUSUAL ACTIVITY

Another important aspect of a monitoring system is its ability to identify atypical activity, i.e., activity that is unusual, and may be in violation of corporate policy. An ideal tool should classify activity into patterns, and based on those activity patterns, identify usage patterns. This is useful in determining if unauthorized activities are taking place, or if corporate policies are being broken.

Consider the mapping of typical administrator activity. If a monitoring system can detect when an administrator makes an uncharacteristic act, this can help ferret out wrongdoings. For example, an administrator breaks corporate policy by remotely administering the database from a home computer. Or perhaps the administrator logs into the network late at night from a remote office, raising a “red flag” for an attack from an internal employee. An auditing tool should be able to properly characterize your system, then monitor for attacks, breaches in security, valid users performing unauthorized activities, and violations of corporate policies.

 

 

 

 

KNOWN ATTACKS

It is imperative that your database monitoring system detect and recognize attacks. Attacks come in many forms. When an  attack occurs on your system, it should notify you that you’re under attack. Below is a sampling of the type of attacks a monitoring system should pick up on:

 

1. Buffer overflows being executed from PL\SQL

2. Web application attacks

3. Privilege escalations

4. Accessing OS resources

5. Password attacks

6. Pen Testing or hacker tools used against the database

7. Database starting and stopping

 

BUFFER OVERFLOWS

AppRadar monitors for attacks that take advantage of buffer overflow vulnerabilities. Oracle is susceptible to a number of buffer overflows which result in either a database crashing or the memory in the stack being overwritten. This can result in an exception being thrown, or worse yet, an attacker taking full control of the system. AppRadar Sensors can pick up on buffer overflow attack patterns such as the following:

 

BFILENAME buffer overflow

Database link buffer overflow

DROP_SITE_INSTANTIATION buffer overflow

FROM_TZ buffer overflow

INSTANTIATE_OFFLINE buffer overflow

INSTANTIATE_ONLINE buffer overflow

NUMTODSINTERVAL buffer overflow

NUMTOYMINTERVAL buffer overflow

SERVICE_NAME buffer overflow

TIME_ZONE buffer overflow

TO_CHAR buffer overflow

TO_TIMESTAMP_TZ buffer overflow

TZ_OFFSET buffer overflow

 

WEB APPLICATION ATTACKS

Rules within this category can be enabled to monitor against possible access-related attacks. Attacks may include attempts to elevate privileges and gain access to powerful resources within an Oracle database

 

PRIVILEGE ESCALATION

It is possible for a low-privileged user to exploit Oracle vulnerabilities to effectively bypass access controls. This category of rules alerts on the exploitation of these kinds of vulnerabilities.

 

ACCESSING OPERATING SYSTEM RESOURCES

This category focuses on monitoring database features that allow operating system access. For example, by changing the UTL_FILE_DIR parameter, Oracle can be fooled into allowing SYS.UTL_FILE to overwrite important files on the operating system thus giving access to the OS through a database attack.

 

PASSWORD ATTACKS

Attempts to guess passwords by trying likely combinations of characters or exploiting certain Oracle vulnerabilities are simplistic attacks that can be used against a database.

 

SYSTEM EVENTS

These rules uncover system level events such as the starting and stopping of the database being monitored and the starting and stopping of the AppRadar Sensor.

 

CONCLUSION

Monitoring your database applications is a critical component of achieving a strong defense-in-depth around your sensitive data. However, to be efficient and effective you must use the right combination of tools. Monitoring should never replace other layers in the security stack, instead it should complement the existing pieces. Database intrusion detection and security auditing continues to grow in importance because of the rising volume of successful database attacks, and the resulting security legislation and regulations, including:

• Payment Card Industry Data Security Standard (PCI-DSS)

• Sarbanes-Oxley Act

• HIPAA (Health Insurance Portability and Accountability Act)

• European Union Data Protection Directive

• California’s Database Security Breach Notification Act (California Senate Bill 1386)

• Gramm-Leach-Bliley Act

• Federal Information Security Management Act

 

Clearly, database intrusion detection and security auditing comes with its complexities. Monitoring your databases is a useful tactic, but only if used in conjunction with a well-conceived and balanced security plan. Database monitoring should be a layer of defense augmenting your overall database security strategy. When used in conjunction with vulnerability assessment,

encryption, and database integrity solutions, an extremely solid security solution can be implemented. When considering the use of database monitoring be sure to select a tool that will work well with other database security products. This will ensure an effective and holistic approach to security by incorporating and integrating all the different layers. By doing so, you will more effectively fortify your castle (database) and crown jewels (sensitive data) from the barbarians of these modern times.

 

 

 

 

 

1