8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | 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:
- Set "audit_trail = true" in the init.ora file.
- Run the $ORACLE_HOME/rdbms/admin/cataudit.sql script while connected as SYS.
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.
- DDL (CREATE, ALTER & DROP of objects)
- DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).
- SYSTEM EVENTS (LOGON, LOGOFF etc.)
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.
- 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
The audit trail contains a lot of data, but the following are most likely to be of interest.
- USERNAME : Oracle Username.
- TERMINAL : Machine that the user performed the action from.
- TIMESTAMP : When the action occured.
- OBJECT_OWNER : The owner of the object that was interacted with.
- OBJECT_NAME : The name of the object that was interacted with.
- ACTION_NAME : The action that occured against the object. (INSERT, UPDATE, DELETE, SELECT, EXECUTE)
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...