how to record all oracle select statement executed on table oracle

Question

I want to record all oracle select statement executed on specific table oracle and pc ip address, windows username, PC name

in other words I need to know who and when a table was read..

I searched and found the this query will return pc ip address, windows username, PC name

SELECT SYS_CONTEXT ('USERENV', 'IP_ADDRESS'),
       SYS_CONTEXT ('USERENV', 'HOST'),
       SYS_CONTEXT ('userenv', 'OS_USER')
  FROM DUAL;

But I am wondering does this will return correct information when there is no database on that pc ?

since trigger cannot be launched on select then How to deal with that case ?

I am using oracle forms 6i application if there is possible solution too

atokpas atokpas · Accepted Answer · 2017-04-04T04:07:52

You can use database auditing for individual SQL DML/DDL statements.

Example:

SQL> conn sys as sysdba
SQL> alter system set audit_trail=DB,EXTENDED scope=spfile;

Reboot the database.

I have a table called T1 and I want audit SELECT statements fired against it.

SQL> audit select on t1 by access;

Audit succeeded.

SQL> select * from t1;

no rows selected

The audit information can be obtained from USER_AUDIT_TRAIL view.

SQL> select OS_USERNAME,USERNAME, USERHOST, SQL_TEXT, ACTION_NAME from dba_audit_trail where obj_name='T1';

OS_USERNAME     USERNAME    USERHOST        SQL_TEXT            ACTION_NAME
------------    ---------   ------------    -----------------   ------------
oracle           JAY        myserver.js     select * from t1    SELECT

Individually Auditing SQL Statements

how to record all oracle select statement executed on table oracle

1 Answers