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....!!!!!!!!!

step by step enable audit in xml,extended files in Oracle 11gR2

step by step enable audit in xml,extended files in Oracle 11gR2

Step1: Check the audit parameter setting

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /oracledb/admin/stlbas/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      NONE
SQL>

Step2: Enable audit as xml,extended 

ALTER SYSTEM SET audit_trail=xml SCOPE=SPFILE;

Note: xml- Auditing is enabled, with all audit records stored as XML format OS files.

ALTER SYSTEM SET audit_trail=xml,extended SCOPE=SPFILE;

Note: xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.

Step3: First check the current location of the audit trail tables

SELECT table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

Step4: create a new tablespace to hold the audit trail.

CREATE TABLESPACE TBL_SBL_AUDIT_DAY30 DATAFILE 
  '/oracledb/oradata/stlbas/TBl_SBL_AUDIT_DAY30_01.dbf' SIZE 1500M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;


Step5:  move the standard audit trail to the new tablespace.

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    audit_trail_location_value => 'TBL_SBL_AUDIT_DAY30');
END;
/

Step6: Check locations.
SELECT table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

Step7: move the fine-grained audit trail.

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    audit_trail_location_value => 'TBL_SBL_AUDIT_DAY30');
END;
/

Step8: Check locations of audit tables.

SELECT table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

Step9: Finally, we move them both back to their original location in a single step.

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
    audit_trail_location_value => 'SYSTEM');
END;
/

---Controlling the Size and Age of the OS Audit Trail

SQL> SELECT *
FROM   dba_audit_mgmt_config_params
WHERE  parameter_name LIKE 'AUDIT FILE MAX%';  2    3

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL
AUDIT FILE MAX AGE             5                    XML AUDIT TRAIL

SQL>


These defaults mean that OS and XML audit trail files will grow to a maximum of 10,000Kb, at which point a new file will be created. In addition, files older than 5 days will not be written to any more, even if they are below the maximum file size. Instead, a new file will be created and written to. Here are some examples of changing the settings.

-- Set the Maximum size of OS audit files to 15,000Kb.
BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_property(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
    audit_trail_property       => DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,
    audit_trail_property_value => 15000);
END;
/

-- Set the Maximum age of XML audit files to 10 days.
BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_property(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,
    audit_trail_property       => DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,
    audit_trail_property_value => 10);
END;
/

The CLEAR_AUDIT_TRAIL_PROPERTY procedure can be used to remove the size and age restrictions, or reset them to the default values. Setting the USE_DEFAULT_VALUES parameter value to FALSE removes the restrictions, while setting it to TRUE returns the restriction to the default value.

-- Reset the max size default values for both OS and XML audit file.
BEGIN
  DBMS_AUDIT_MGMT.clear_audit_trail_property(
   audit_trail_type     => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES,
   audit_trail_property => DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,
   use_default_values   => TRUE );
END;
/

-- Remove the max age restriction for both OS and XML audit file.
BEGIN
  DBMS_AUDIT_MGMT.clear_audit_trail_property(
   audit_trail_type     => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES,
   audit_trail_property => DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,
   use_default_values   => FALSE );
END;
/

Step10: Purging Audit Trail Records

In this specification:

AUDIT_TRAIL_TYPE: Enter one of the following values:

DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail table, AUD$.
DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail table, FGA_LOG$.
DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trail tables.
DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS: Operating system audit trail files with the .aud extension. (This setting does not apply to Windows Event Log entries.)
DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML: XML Operating system audit trail files.
DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES: Both operating system and XML audit trail files.
DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL: All audit trail records, that is, both database audit trail and operating system audit trail types.

--Manul Purging--

BEGIN
  DBMS_AUDIT_MGMT.clean_audit_trail(
   audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES,
   use_last_arch_timestamp => FALSE);
END;
/


--Automated Purging
The following code schedules a purge of all audit trails every 24 hours. The resulting job is visible in the DBA_SCHEDULER_JOBS view.

BEGIN
  DBMS_AUDIT_MGMT.create_purge_job(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    audit_trail_purge_interval => 24 /* hours */,  
    audit_trail_purge_name     => 'PURGE_ALL_AUDIT_TRAILS',
    use_last_arch_timestamp    => TRUE);
END;
/

--The job can be disabled and enabled using the SET_PURGE_JOB_STATUS procedure.

BEGIN
  DBMS_AUDIT_MGMT.set_purge_job_status(
    audit_trail_purge_name   => 'PURGE_ALL_AUDIT_TRAILS',
    audit_trail_status_value => DBMS_AUDIT_MGMT.PURGE_JOB_DISABLE);

  DBMS_AUDIT_MGMT.set_purge_job_status(
    audit_trail_purge_name   => 'PURGE_ALL_AUDIT_TRAILS',
    audit_trail_status_value => DBMS_AUDIT_MGMT.PURGE_JOB_ENABLE);
END;
/

--The interval of the purge job can be altered using the SET_PURGE_JOB_INTERVAL procedure.

BEGIN
  DBMS_AUDIT_MGMT.SET_PURGE_JOB_INTERVAL(
    audit_trail_purge_name     => 'PURGE_ALL_AUDIT_TRAILS',
    audit_trail_interval_value => 48);
END;
/

If you want the purge job to maintain an audit trail of a specific number of days, the easiest way to accomplish this is to define a job to set the last archive time automatically. The following job resets the last archive time on a daily basis, keeping the last archive time 90 days in the past.

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'audit_last_archive_time',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN 
                          DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-90);
                          DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, TRUNC(SYSTIMESTAMP)-90);
                          DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, TRUNC(SYSTIMESTAMP)-90);
                          DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML, TRUNC(SYSTIMESTAMP)-90);
                        END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Automatically set audit last archive time.');
END;
/

SELECT job_name, next_run_date, state, enabled FROM dba_scheduler_jobs WHERE job_name LIKE '%AUDIT%';


Step11: AUDIT Enable for specific user

AUDIT DELETE TABLE, UPDATE TABLE,INSERT TABLE      BY TEST1;

NOAUDIT DELETE TABLE, UPDATE TABLE,INSERT TABLE      BY TEST1;

--******** Note : AUDIT will be effected after logout the seesion by audited user---**********

Step12: AUDIT Enable on specific table

AUDIT INSERT, DELETE, UPDATE ON TEST1.test BY ACCESS  WHENEVER SUCCESSFUL;

NOAUDIT INSERT, DELETE, UPDATE ON TEST1.test   WHENEVER SUCCESSFUL;



--View ALL Audit which are enabled
column USER_NAME format a10
column PROXY_NAME format a10
column AUDIT_OPTION format a20



select * from dba_stmt_audit_opts;

NOAUDIT ALL; - remove all auditing.
noaudit delete table;
noaudit insert table;
noaudit update table;

NOAUDIT INSERT TABLE BY TEST2;
NOAUDIT UPDATE TABLE BY TEST2;
NOAUDIT DELETE TABLE BY TEST2;
NOAUDIT SELECT TABLE BY TEST2;
NOAUDIT EXECUTE PROCEDURE BY TEST2;


NOAUDIT INSERT TABLE BY TEST1;
NOAUDIT UPDATE TABLE BY TEST1;
NOAUDIT DELETE TABLE BY TEST1;
NOAUDIT SESSION by TEST1;

TRUNCATE TABLE SYS.AUD$;  --truncate all audit record

---Miscellaneous command for audit views---

--shows all audited privileges
select * from DBA_PRIV_AUDIT_OPTS

--shows all audited statements
select * from DBA_STMT_AUDIT_OPTS

--shows all audited objects
select * from DBA_OBJ_AUDIT_OPTS 

SELECT * FROM  V$XML_AUDIT_TRAIL order by extended_timestamp desc;

--Ref--

http://www.oracle-base.com/articles/11g/auditing-enhancements-11gr2.php