Free Oracle Tips

HTML Text

Home
E-mail Us
Oracle Articles

Oracle Training
Oracle Tips
Oracle Forum
Class Catalog

Remote DBA
Oracle Tuning
Emergency 911
RAC Support
Apps Support
Analysis
Design
Implementation
Oracle Support

SQL Tuning
Security
Oracle UNIX
Oracle Linux
Monitoring
Remote support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
Development
Implementation

Consulting Staff
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB

Oracle Database Resident Audit Trail Security

Oracle Security Tips by Burleson Consulting

This is an excerpt from the bestselling book "Oracle Privacy Security Auditing", a complete Oracle security reference with working Oracle security scripts.

Database Resident Audit Trail

The special table named aud$ holds the audit trail generated. This table is automatically generated during the database creation process. All other dependent views and action code tables are created by running the file $ORACLE_HOME/rdbms/admin/cataudit.sql.

The raw table aud$ is not very user friendly – the columns are not very intuitive and the table is not logically divided by the different types of information it contains. Therefore, there are several views that have been created on this table to present a more user-friendly view into the table. These views are present with the word AUDIT in them and are available for the user and the DBA views with prefixes – USER and DBA, respectively. Since there is no context where an equivalent one will be useful for ALL views, there is none with that prefix. These views will be better explained as we go through this chapter.

dba_audit_trail – This is a view based on the aud$ table which decodes most of the values inside the table to present it in a user understandable manner. For instance, the column SPARE1 in aud$ actually stores the Operating System User Name. The column OS_USERNAME in this view gets its value from that column in the aud$ table. Similarly, the column ACTION# column holds the user's action in a numeral form. For example, 108 indicates granting system privilege. The view decodes the numerical representation for the actions and presents the information in the column ACTION_NAME. Therefore, this view is actually more useful for analysis than the aud$ table itself.

The following views are based on the dba_audit_trail view.

user_audit_trail – This view is based on all the columns of the dba_audit_trail view, but presents information on the current logged in user only. Unlike other data dictionary view, there is no equivalent all_audit_trail view, as it is not relevant in this case.

dba_audit_exists – This is an audit trail of the entries where the operation failed due to non-existing objects. This is actually filtered from the view dba_audit_trail where the return code is a value other than zero.

dba_audit_session – This records all database connect and disconnect activity. It also records if the action failed, for example, failure due to a security violation.

dba_audit_statement – This view contains information about users entering statements that do not actually access the data inside an object, e.g. ALTER SYSTEM, GRANT, REVOKE on objects, etc.

dba_audit_object – Finally, this view shows all the audit records that were based on database object accesses – e.g. a user selected from a table, or altered another table, etc.

Types of Auditing

The general Oracle auditing can be broadly divided into three categories based on the event on which they are fired. Before discussing that, another important concept related to auditing, which is present in all these three categories, must be discussed.

By Session/Access

When an action is made on an object, the action can cause the audit trail to be updated for each time it is issued, or just once per session. For instance, the user issues the following statements:

select first_name||’ ‘||last_name from members where member_id = 102;
select plan_code from members where SSN = ‘123456789’;
select SSN from members where last_name = ‘DOE’;

Note the table MEMBER was accessed three times by the user in this single session. The choice can be made to write three records into the audit trail, or just one. Sometimes it may not make sense to write audit trails three times. Although the information accessed and the records retrieved may be different, the audit facility does not capture that, so writing three records is not going to be useful for determining accountability any more than capturing only one record.

Therefore, the frequency of capturing the auditing information becomes important in some cases. The frequency that the audit trail is updated can be set by using one of the options shown below:

BY ACCESS – This specifies that audit records be written every time the object is accessed.

BY SESSION – This specifies the audit record be written only once per session.

In subsequent parts of this chapter we will see this clause in action.

On Success

Most of the information captured in auditing is to satisfy the requirements of accountability, i.e. the audit trail captures that fact that Judy updated the CLAIMS table. However, another important objective of auditing is to act as a surveillance tool to capture attempted break-ins that must be investigated. Capturing the audit trail when an action is not successful can identify attempted break-ins.

For instance, Judy does not have update privileges on the CLAIMS table and any attempt to update it may be construed as an attempt to break-in. Some attempted access may happen inadvertently, but a sequence of repeated attempts may be a sign of desperate attempts and must be investigated.

To facilitate this effort, Oracle also provides an option to generate audit trails when the audited action is: 1) only successful, 2) only unsuccessful, or 3) both. By default the audit is triggered on both successful and unsuccessful attempts, but they can also be limited to only one option. A clause added to the end of the statement determines this. For instance, if the auditing is to be enabled for successful attempts only, the clause WHENEVER SUCCESSFUL is used. When only the unsuccessful ones are to be audited, the clause WHENEVER NOT SUCCESSFUL is used. An example is given below.

audit update on claim_schema,claims by access
whenever not successful;

In this statement we want to capture audit records only if they are not successful. Subsequence paragraphs will illustrate this clause in its proper context.

Statement Auditing

Statement auditing enabled recording of any action due to the execution of an SQL statement such as "audit create procedure." Whenever a user executes the statement, the audit record is generated. Let's see an example. We want to audit when user CLAIM_SCHEMA creates a procedure; so we will issue:

audit create procedure by claim_schema;

Any time the user CLAIM_SCHEMA tries to create a procedure, the action is logged, regardless of whether it was successful.

In general the syntax for statement level auditing is:

statement_list is a list of statements that trigger auditing. If more than one auditable statement is provided, they are separated by commas:

audit create table, create sequence by judy;

The allowable statements vary from version to version. The valid list of statements can be found in a sys owned table called stmt_audit_option_map. The keyword ALL can be used as well to enable auditing for all allowed statements.

username is the name of the user whose actions are to be audited. It is optional and if omitted the action is audited for the statement by any user.

proxyuser is provided when proxy usernames are used to authenticate users. The optional clause ON BEHALF OF is used.

Finally, the clause WHENEVER SUCCESSFUL or NOT SUCCESSFUL can be used.

Table 8.1 shows all options available for statement auditing that can be used in the statement_list as of Oracle 9i Release 2.

ALTER ANY CLUSTER

ALTER ANY DIMENSION

ALTER ANY INDEX

ALTER ANY LIBRARY

ALTER ANY OUTLINE

ALTER ANY PROCEDURE

ALTER ANY ROLE

ALTER ANY SEQUENCE

ALTER ANY SNAPSHOT

ALTER ANY TABLE

ALTER ANY TRIGGER

ALTER ANY TYPE

ALTER DATABASE

ALTER PROFILE

ALTER RESOURCE COST

ALTER ROLLBACK SEGMENT

ALTER SEQUENCE

ALTER SESSION

ALTER SYSTEM

ALTER TABLE

ALTER TABLESPACE

ALTER USER

ANALYZE ANY

AUDIT ANY

BACKUP ANY TABLE

BECOME USER

CLUSTER

COMMENT ANY TABLE

COMMENT TABLE

CONTEXT

CREATE ANY CLUSTER

CREATE ANY CONTEXT

CREATE ANY DIMENSION

CREATE ANY DIRECTORY

CREATE ANY INDEX

CREATE ANY LIBRARY

CREATE ANY OUTLINE

CREATE ANY PROCEDURE

CREATE ANY SEQUENCE

CREATE ANY SNAPSHOT

CREATE ANY SYNONYM

CREATE ANY TABLE

CREATE ANY TRIGGER

CREATE ANY TYPE

CREATE ANY VIEW

CREATE CLUSTER

CREATE DATABASE LINK

CREATE DIMENSION

CREATE DIRECTORY

CREATE LIBRARY

CREATE PROCEDURE

CREATE PROFILE

CREATE PUBLIC DATABASE LINK

CREATE PUBLIC SYNONYM

CREATE ROLE

CREATE ROLLBACK SEGMENT

CREATE SEQUENCE

CREATE SESSION

CREATE SNAPSHOT

CREATE SYNONYM

CREATE TABLE

CREATE TABLESPACE

CREATE TRIGGER

CREATE TYPE

CREATE USER

CREATE VIEW

DATABASE LINK

DEBUG ANY PROCEDURE

DEBUG CONNECT ANY

DEBUG CONNECT SESSION

DEBUG CONNECT USER

DEBUG PROCEDURE

DELETE ANY TABLE

DELETE TABLE

DEQUEUE ANY QUEUE

DIMENSION