How to Enable Audit for Grantor User in Oracle Database

Scenario: Here we want to enable audit for those Database users who are giving Database Privileges to other Database users. 

Step 1: Create two users (said and mihir) and grant DBA privileges to one user

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>create user said identified by said ;

SQL> grant dba to said;

SQL> create user mihir identified by mihir;

Step 2: Now connect to Database with DBA privilege user and grant some privileges to other user

[oracle@said ~]$ sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 25 09:39:28 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Enter user-name: said/said

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter audit

NAME      TYPE  VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest       string  /u01/app/oracle/admin/orcl/adu
 mp
audit_sys_operations      boolean  TRUE
audit_syslog_level      string
audit_trail      string  DB, EXTENDED

SQL> grant create table to mihir; 

Grant succeeded.

SQL> grant create session to mihir;

Grant succeeded.

SQL> select count(*) from dba_audit_trail;

  COUNT(*)
----------
0


Step 3: Now grant privilege to user SAID for auditing 

[oracle@said ~]$ sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 25 09:38:35 2017

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

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> AUDIT system grant by "SAID";

Audit succeeded.


SQL> show parameter audit

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest      string /u01/app/oracle/admin/orcl/adu
mp
audit_sys_operations      boolean TRUE
audit_syslog_level      string
audit_trail      string DB, EXTENDED


Step 4: Now provide privilege to user mihir, connect database as said

[oracle@said ~]$ sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 25 09:38:35 2017

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

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn said/said
Connected.
SQL>
SQL> show user
USER is "SAID"

SQL> grant create session to mihir;

Grant succeeded.

SQL> grant create table to mihir;

Grant succeeded.

SQL> select count(*) from dba_audit_trail;

  COUNT(*)
----------
2

SQL> column username format a10
SQL> column action_name format a20
SQL> column grantee format a10
SQL> column sql_text format a40
SQL> set lin 100
SQL> select username,action_name,grantee, sql_text from dba_audit_trail;

USERNAME   ACTION_NAME GRANTEE    SQL_TEXT
---------- -------------------- ---------- ----------------------------------------
SAID    SYSTEM GRANT MIHIR    grant create table to mihir
SAID    SYSTEM GRANT MIHIR    grant create session to mihir

SQL>

SQL> show parameter audit

NAME      TYPE  VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest       string  /u01/app/oracle/admin/orcl/adu
 mp
audit_sys_operations      boolean  TRUE
audit_syslog_level      string
audit_trail      string  DB, EXTENDED

Thats it....!!!!!!!!!