Enable Oracle 12c Unified Auditing - Pure Mode

Enable Oracle 12c Unified Auditing - Pure Mode

Why we want Pure Mode?
The first is the audit trails are no longer written to their traditional pre-12c audit locations. Auditing is consolidated into the Unified Audit views and stored using Oracle SecureFiles. Oracle Secured Files use a proprietary format which means that Unified Audit logs cannot be viewed using editors such vi and may preclude or affect the use of third party logging solutions such as Splunk or HP ArcSight.

Operations done by SYS are also recorded.

Unified Auditing comes standard with Oracle Enterprise Edition; no additional license is required. It is installed by default, but not fully enabled by default.

Enable Unified Auditing(Pure Mode):

- Stop Application

- Stop Listener

$ lsnrctl stop

- Relink Oracle Home binaries:

SQL> select * from v$option where PARAMETER = 'Unified Auditing';

SQL> shutdown immediate

cd $ORACLE_HOME/rdbms/lib

$ make -f ins_rdbms.mk uniaud_on ioracle

$ lsnrctl start

SQL> startup 

AUDIT POLICY ORA_CIS_RECOMMENDATIONS;

AUDIT POLICY ORA_LOGON_FAILURES;

AUDIT POLICY ORA_SECURECONFIG;

SQL>

CREATE AUDIT POLICY ORA_DML_APP_OBJ

  ACTIONS DELETE, INSERT, UPDATE

WHEN 'sys_context(''userenv'',''SESSION_USER'') not in ( ''APPUSR1'',''APPUSR2'')'

  EVALUATE PER STATEMENT;

AUDIT POLICY ORA_DML_APP_OBJ BY DBA_PITOU;

COL USER_NAME FOR A30

COL POLICY_NAME FOR A30  

SELECT USER_NAME,POLICY_NAME FROM AUDIT_UNIFIED_ENABLED_POLICIES;

USER_NAME                      POLICY_NAME

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

ALL USERS                      ORA_SECURECONFIG

ALL USERS                      ORA_LOGON_FAILURES

ALL USERS                      ORA_CIS_RECOMMENDATIONS

ALL USERS                      ORA_DML_APP_OBJ

select SEGMENT_NAME,TABLESPACE_NAME,BLOCKS,BYTES/1024/1024 "Size Mb" 

from dba_segments where  SEGMENT_NAME = 'AUD$';

select SEGMENT_NAME,SEGMENT_SUBTYPE,SEGMENT_TYPE,BYTES/1024/1024 "Size Mb",TABLESPACE_NAME  

from dba_segments where OWNER = 'AUDSYS';

CREATE TABLESPACE AUDIT_TRAIL

  DATAFILE '/u01/app/oracle/oradata/pcrd/audit_trail01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED

  ENCRYPTION USING 'AES256'

  DEFAULT STORAGE (ENCRYPT);

 

BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(

       audit_trail_type            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,

       audit_trail_location_value  =>  'AUDIT_TRAIL');

END;

/

BEGIN 

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(

AUDIT_TRAIL_TYPE =>DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_TRAIL');

END;

/

select SEGMENT_NAME,TABLESPACE_NAME,BLOCKS,BYTES/1024/1024 "Size Mb" 

from dba_segments where  SEGMENT_NAME = 'AUD$';

select SEGMENT_NAME,SEGMENT_SUBTYPE,SEGMENT_TYPE,BYTES/1024/1024 "Size Mb",TABLESPACE_NAME  

from dba_segments where OWNER = 'AUDSYS';

CREATE AUDIT POLICY PL_AUD_DDL

 ACTIONS

 ALTER CLUSTER,CREATE CLUSTER,DROP CLUSTER,TRUNCATE CLUSTER,

 ALTER FUNCTION,CREATE FUNCTION,DROP FUNCTION,

 ALTER INDEX,CREATE INDEX,DROP INDEX,

 ALTER OUTLINE,CREATE OUTLINE,DROP OUTLINE,

 ALTER PACKAGE,CREATE PACKAGE,DROP PACKAGE,

 ALTER PACKAGE BODY,CREATE PACKAGE BODY,DROP PACKAGE BODY,

 ALTER SEQUENCE,CREATE SEQUENCE,DROP SEQUENCE,

 ALTER TABLE,CREATE TABLE,DROP TABLE,TRUNCATE TABLE,

 ALTER TRIGGER,CREATE TRIGGER,DROP TRIGGER,

 ALTER TYPE,CREATE TYPE,DROP TYPE,

 ALTER TYPE BODY,CREATE TYPE BODY,DROP TYPE BODY,

 ALTER VIEW,CREATE VIEW,DROP VIEW;

 AUDIT POLICY PL_AUD_DDL;

--NOAUDIT POLICY PL_AUD_DDL;

--DROP AUDIT POLICY PL_AUD_DDL;

Default Policy:

ORA_ACCOUNT_MGMT

ORA_CIS_RECOMMENDATIONS

ORA_DATABASE_PARAMETER

ORA_DV_AUDPOL

ORA_DV_AUDPOL2

ORA_LOGON_FAILURES

ORA_RAS_POLICY_MGMT

ORA_RAS_SESSION_MGMT

ORA_SECURECONFIG

SET LINESIZE 200

COL USER_NAME FOR A15

COL POLICY_NAME FOR A20

COL ENABLED_OPTION FOR A15

COL SUCCESS FOR A5

COL FAILURE FOR A5

SELECT USER_NAME,POLICY_NAME,ENABLED_OPTION,SUCCESS,FAILURE FROM AUDIT_UNIFIED_ENABLED_POLICIES;

USER_NAME       POLICY_NAME          ENABLED_OPTION  SUCCE FAILU

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

ALL USERS       ORA_SECURECONFIG     BY USER         YES   YES

ALL USERS       ORA_LOGON_FAILURES   BY USER         NO    YES

COL USER_NAME FOR A30

COL POLICY_NAME FOR A30  

SELECT USER_NAME,POLICY_NAME FROM AUDIT_UNIFIED_ENABLED_POLICIES;

DBA_PITOU ORA_DML_APP_OBJ

ALL USERS ORA_SECURECONFIG

ALL USERS ORA_LOGON_FAILURES

ALL USERS ORA_CIS_RECOMMENDATIONS

SELECT * FROM audit_unified_policies

ORDER BY policy_name,

         audit_option;  

==========================================

SELECT * FROM audit_unified_policies

where policy_name in ('ORA_SECURECONFIG','ORA_CIS_RECOMMENDATIONS')

ORDER BY policy_name,

         audit_option;

 

SELECT * FROM audit_unified_policies

where policy_name in (SELECT POLICY_NAME FROM AUDIT_UNIFIED_ENABLED_POLICIES)

ORDER BY policy_name,

         audit_option;  

SELECT * FROM audit_unified_policies

where policy_name in (SELECT POLICY_NAME FROM AUDIT_UNIFIED_ENABLED_POLICIES) and audit_option like '%TABLE%'

ORDER BY policy_name,

         audit_option;

SET LINESIZE 200

COL OS_USERNAME FOR A11

COL USERHOST FOR A20

COL DBUSERNAME FOR A15

COL ACTION_NAME FOR A15

COL OBJECT_NAME FOR A25

COL CLIENT_PROGRAM_NAME FOR A15

COL SQL_TEXT FOR A50

select DBUSERNAME,EVENT_TIMESTAMP,SQL_TEXT,ACTION_NAME,SYSTEM_PRIVILEGE_USED,UNIFIED_AUDIT_POLICIES,OBJECT_SCHEMA,OBJECT_NAME,CLIENT_PROGRAM_NAME,OS_USERNAME,USERHOST

from unified_audit_trail

order by event_timestamp desc ;

select DBUSERNAME,ACTION_NAME,COUNT(*)

from unified_audit_trail

group by DBUSERNAME,ACTION_NAME

order by DBUSERNAME,ACTION_NAME;

select DBUSERNAME,EVENT_TIMESTAMP,SQL_TEXT,ACTION_NAME,SYSTEM_PRIVILEGE_USED,UNIFIED_AUDIT_POLICIES,OBJECT_SCHEMA,OBJECT_NAME,CLIENT_PROGRAM_NAME,OS_USERNAME,USERHOST

from unified_audit_trail

Where event_timestamp >= TO_DATE('15/FEB/2019','dd/mon/yyyy')

order by event_timestamp desc ;

select DBUSERNAME,EVENT_TIMESTAMP,SQL_TEXT,ACTION_NAME,SYSTEM_PRIVILEGE_USED,UNIFIED_AUDIT_POLICIES,OBJECT_SCHEMA,OBJECT_NAME,CLIENT_PROGRAM_NAME,OS_USERNAME,USERHOST

from unified_audit_trail

Where event_timestamp >= TO_DATE('20/FEB/2019','dd/mon/yyyy') and action_name not in('LOGON','LOGOFF BY CLEANUP')

order by event_timestamp desc ;

select DBUSERNAME,EVENT_TIMESTAMP,SQL_TEXT,ACTION_NAME,SYSTEM_PRIVILEGE_USED,UNIFIED_AUDIT_POLICIES,OBJECT_SCHEMA,OBJECT_NAME,CLIENT_PROGRAM_NAME,OS_USERNAME,USERHOST

from unified_audit_trail

Where TRUNC(event_timestamp) = '16-DEC-2019'

order by event_timestamp desc ;

select DBUSERNAME,TO_CHAR(EVENT_TIMESTAMP,'DD-MON-YYYY HH24:MI') EVENT_TIMESTAMP,Count(*)

from unified_audit_trail

where DBUSERNAME='SYS'

group by DBUSERNAME,TO_CHAR(EVENT_TIMESTAMP,'DD-MON-YYYY HH24:MI')

order by EVENT_TIMESTAMP desc ;

select DBUSERNAME,EVENT_TIMESTAMP,SQL_TEXT,ACTION_NAME,SYSTEM_PRIVILEGE_USED,UNIFIED_AUDIT_POLICIES,OBJECT_NAME,CLIENT_PROGRAM_NAME,OS_USERNAME,USERHOST

from unified_audit_trail

WHERE DBUSERNAME IN ('DBA_PITOU')

      AND TRUNC(event_timestamp)='21-SEP-2019'

order by event_timestamp desc ;