Oracle - Audit Trail for a specific user

Question

As stated on the topic , I am looking for a way for us to track on the activities of the specific user. May or may not have the SYSDBA or SYSOPER privilege.

For example , HR.

I would like to know what are the details of his login , what are the objects that are changed by him , what were their original values , SQL statements executed , what procedure/functions that were executed etc.

Could we set up such audit trail log in Oracle 11gR2 Standard/Enterprise?

Thanks

Answer 1

First of all you need to enable auditing in your database by setting audit_trail parameter as shown below-

SQL> alter system set audit_trail='OS|DB|DB,EXTENDED|XML|XML, EXTENDED';

Initialization Parameters Used for Auditing

Then, you can audit user as-

SQL>CONNECT sys/password AS SYSDBA

SQL> AUDIT ALL BY username BY ACCESS;
SQL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY username BY ACCESS;
SQL> AUDIT EXECUTE PROCEDURE BY username BY ACCESS;

AUDIT

Audit records can be found in DBA_AUDIT_TRAIL view.Following query list all audit related views.

SQL>SELECT view_name FROM dba_views WHERE view_name LIKE 'DBA%AUDIT%';

Fine-grained auditing is available in Enterprise Edition only.

Feature Availability by Edition