Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Auditing

There is a newer version of this article here.

The auditing mechanism for Oracle is extremely flexible so I'll only discuss performing full auditing on a single user.

Related articles.

Server Setup

To allow auditing on the server you must:

Audit Options

Assuming that the "fireid" user is to be audited.

CONNECT sys/password AS SYSDBA

AUDIT ALL BY fireid BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY fireid BY ACCESS;
AUDIT EXECUTE PROCEDURE BY fireid BY ACCESS;

These options audit all DDL & DML issued by "fireid", along with some system events.

View Audit Trail

The audit trail is stored in the SYS.AUD$ table. It's contents can be viewed directly or via the following views.

The audit trail contains a lot of data, but the following are most likely to be of interest.

Maintenance

The audit trail must be deleted/archived on a regular basis to prevent the SYS.AUD$ table growing to an unnacceptable size.

Security

Only DBAs should have maintenance access to the audit trail. If SELECT access is required by any applications this can be granted to any users, or alternatively a specific user may be created for this.

Auditing modifications of the data in the audit trail itself can be achieved as follows.

AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;

For more information see:

Hope this helps. Regards Tim...

Back to the Top.