Audit by Table Level

                Table Level Auditing

SQL> show user

USER is "SYS"

SQL> show parameter audit_trail;

 NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

audit_trail                          string      NONE

SQL> show parameter audit_file_dest

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

audit_file_dest                      string      /u03/oravis/VIS/12.1.0/rdbms/audit

SQL> alter system set AUDIT_TRAIL=db, extended scope=spfile;

System altered.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size                  3712904 bytes

Variable Size             905971832 bytes

Database Buffers         1224736768 bytes

Redo Buffers               13062144 bytes

Database mounted.

Database opened.

SQL> show parameter audit_trail;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

audit_trail                          string      DB, EXTENDED

SQL> audit table;

 Audit succeeded.

SQL> audit select,update,delete on scott.emp by access;

 Audit succeeded.

SQL> desc dba_audit_trail

SQL> conn shyam/shyam

Connected.

SQL> select *from scott.emp;

    EMPNO ENAME      JOB              MGR HIREDATE                  SAL

---------- ---------- --------- ---------- ------------------ ----------

      COMM     DEPTNO

      7839 KING       PRESIDENT            17-NOV-81                5000

                   10

      7698 BLAKE      MANAGER         7839 01-MAY-81                2850

                   30

      7782 CLARK      MANAGER         7839 09-JUN-81                2450

                   10

  

     EMPNO ENAME      JOB              MGR HIREDATE                  SAL

---------- ---------- --------- ---------- ------------------ ----------

      COMM     DEPTNO

---------- ----------

      7566 JONES      MANAGER         7839 02-APR-81                2975

                   20

      7788 SCOTT      ANALYST         7566 19-APR-87                3000

                   20

      7902 FORD       ANALYST         7566 03-DEC-81                3000

                   20

     EMPNO ENAME      JOB              MGR HIREDATE                  SAL

---------- ---------- --------- ---------- ------------------ ----------

      COMM     DEPTNO

---------- ----------

      7369 SMITH      CLERK           7902 17-DEC-80                 800

                   20

      7499 ALLEN      SALESMAN        7698 20-FEB-81                1600

       300         30

      7521 WARD       SALESMAN        7698 22-FEB-81                1250

       500         30

     EMPNO ENAME      JOB              MGR HIREDATE                  SAL

---------- ---------- --------- ---------- ------------------ ----------

      COMM     DEPTNO

---------- ----------

      7654 MARTIN     SALESMAN        7698 28-SEP-81                1250

      1400         30

      7844 TURNER     SALESMAN        7698 08-SEP-81                1500

         0         30

      7876 ADAMS      CLERK           7788 23-MAY-87                1100

                   20

     EMPNO ENAME      JOB              MGR HIREDATE                  SAL

---------- ---------- --------- ---------- ------------------ ----------

      COMM     DEPTNO

---------- ----------

      7900 JAMES      CLERK           7698 03-DEC-81                 950

                   30

      7934 MILLER     CLERK           7782 23-JAN-82                1300

                   10

14 rows selected.

sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 9 16:31:05 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved. 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select os_username,username,owner,timestamp,action_name,sql_text from dba_audit_trail;

OS_USERNAME

--------------------------------------------------------------------------------

USERNAME

--------------------------------------------------------------------------------

OWNER

--------------------------------------------------------------------------------

TIMESTAMP          ACTION_NAME

------------------ ----------------------------

SQL_TEXT

--------------------------------------------------------------------------------

oravis

SHYAM

  

OS_USERNAME

--------------------------------------------------------------------------------

USERNAME

--------------------------------------------------------------------------------

OWNER

--------------------------------------------------------------------------------

TIMESTAMP          ACTION_NAME

------------------ ----------------------------

SQL_TEXT

--------------------------------------------------------------------------------

09-AUG-18          LOGON

OS_USERNAME

--------------------------------------------------------------------------------

USERNAME

--------------------------------------------------------------------------------

OWNER

--------------------------------------------------------------------------------

TIMESTAMP          ACTION_NAME

------------------ ----------------------------

SQL_TEXT

--------------------------------------------------------------------------------

oravis

SHYAM

SCOTT

OS_USERNAME

--------------------------------------------------------------------------------

USERNAME

--------------------------------------------------------------------------------

OWNER

--------------------------------------------------------------------------------

TIMESTAMP          ACTION_NAME

------------------ ----------------------------

SQL_TEXT

--------------------------------------------------------------------------------

09-AUG-18          SELECT

select *from scott.emp

SQL> audit table by shyam whenever not successful;

 Audit succeeded.