Audit on object by user in Oracle

auditoracle

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

Best Answer

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;
/
Related Question