audit on object by user in Oracle

Question

Is there some way to audit, for example, a select in a specific table just by one specified user? something like this:

audit select on foo.table by soe;

this command return:

ERROR at line 1:
ORA-01708: ACCESS or SESSION expected

also, it's not possible using the suggestion:

audit select on foo.table by session by soe;
ERROR at line 1:
ORA-00933: SQL command not properly ended

Answer 1

We can use oracle fine grained audit (dbms_fga package). But as always - any audit costs some additional work for database )) Here is an example:

SYS@mydb> BEGIN
 2 DBMS_FGA.add_policy (object_schema => 'scott',
 3 object_name => 'EMP',
 4 policy_name => 'EMP_SEL',
 5 audit_column => null,
 6 audit_condition => 'USER=''HR'''
 7 );
 8 END;
 9 /
SYS@mydb> conn oe/oe
Connected.
OE@mydb> select * from scott.emp;
 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
 7369 SMITH CLERK 7902 17.12.1980 00:00:00 800 20
 7499 ALLEN SALESMAN 7698 20.02.1981 00:00:00 1600 300 30
....
 7902 FORD ANALYST 7566 03.12.1981 00:00:00 3000 20
 7934 MILLER CLERK 7782 23.01.1982 00:00:00 1300 10
14 rows selected.
OE@mydb> conn hr/hr
Connected.
HR@mydb> select * from scott.emp;
 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
 7369 SMITH CLERK 7902 17.12.1980 00:00:00 800 20
...
 7934 MILLER CLERK 7782 23.01.1982 00:00:00 1300 10
14 rows selected.
HR@mydb> select count(*) from dba_fga_audit_trail;
 COUNT(*)
----------
 1
SYS@mydb> select *
 2 from
 3 xmltable( '/ROWSET/ROW/*'
 4 passing xmltype( cursor (select * from dba_fga_audit_trail) )
 5 columns
 6 col varchar2(35) path 'name()'
 7 ,val varchar2(100) path '.'
 8 )
 9 /
COL VAL
----------------------------------- ----------------------------------------------
SESSION_ID 120780131
TIMESTAMP 25.11.2020 15:29:04
DB_USER HR
OS_USER ed
USERHOST WORKGROUP\ED
OBJECT_SCHEMA SCOTT
OBJECT_NAME EMP
POLICY_NAME EMP_SEL
SCN 22979107
SQL_TEXT select * from scott.emp
STATEMENT_TYPE SELECT
EXTENDED_TIMESTAMP 25-NOV-20 03.29.04.646000 PM +08:00
INSTANCE_NUMBER 0
OS_PROCESS 5380:16020
STATEMENTID 18
ENTRYID 2
DBID 2904675769
17 rows selected.

As you see the audit log contains only 1 row now from user HR, but not from OE.

Cleanout:

begin 
 dbms_fga.drop_policy (object_schema => 'scott',
 object_name => 'EMP',
 policy_name => 'EMP_SEL' );
end;
/