Refer Me for Oracle: Auditing

Unified Auditing

a) The unified audit facility serves to consolidate all audit trails in to single

Unified audit trail table.

è UNIFIED_AUDIT_TRAIL

b) Improved performance.

è SGA queues for accumulating audit records, enabling auditing to be turned on with Negligible overhead.

c) Secured Audit data for all, rman / data pump activities.

è Read only audit trail tables.

è Auditing sys users actions, auditing any operation related to Audit configuration.

è Separation of duties AUDIT_ADMIN / AUDIT_VIEWER roles.

d) Simple configuration

è Audit options are group into simple audit policies.

è Privilege / Roles / Action based auditing.

è Condition based auditing.

è Users exemptions from auditing.

How it works:-

a) Audit statements are captured.

b) Audit records are generated in SGA queues, each client has 2 SGA queues so that client can client

continue to write to the 2^nd queue when the other queue is persisted to table.

c) BG process GEN0 flushes the audit records to AUDSYS owned read only tables every 3 seconds.

Manual flush can be done using dbms_audit_mgmt.flush_unified_audit_trail

d) Audit records are viewed using UNIFIED_AUDIT_TRAIL views.

Components

Audit records for all the below components are viewed using single view UNIFIED_AUDIT_TRAIL.

FGA event - FGA_xxx

Data pump export / import - DP_xxx

RMAn bakup & Recovery - RMAN_xxx Auditied by default

OLS operation - OLS_xxx

Database vault - DV_xxx

Real appliation secuity - XS_XXX

XX_xxx are the column name in unified_audit_trail views.

To Audit Component

SQL> create audit policy datapump_audit actions component=datapump export;

Audit policy created.

SQL> alter audit policy datapump_audit add actions component=datapump import;

Audit policy altered.

SQL> audit policy datapump_audit;

Audit succeeded.

expdp manzoor/ahamed directory=testdir dumpfile=emp.dmp logfile=emp.log

SQL> select DP_TEXT_PARAMETERS1, DP_BOOLEAN_PARAMETERS1, DBUSERNAME from unified_audit_trail where DBUSERNAME = 'MANZOOR';

MASTER TABLE: "MANZOOR"."SYS_EXPORT_SCHEMA_01" , JOB_TYPE: EXPORT, METADATA_JOB_MODE: SCHEMA_EXPORT, JOB VERSION: 12.1.0.0.0, ACCESS METHOD: AUTOMATIC, DATA OPTIONS: 0, DUMPER DIRECTORY: NULL REMOTE

LINK: NULL, TABLE EXISTS: NULL, PARTITION OPTIONS: NONE

MASTER_ONLY: FALSE, DATA_ONLY: FALSE, METADATA_ONLY: FALSE, DUMPFILE_PRESENT: TRUE, JOB_RESTARTED: FALSE

MANZOOR

MASTER TABLE: "MANZOOR"."SYS_SQL_FILE_FULL_01" , JOB_TYPE: IMPORT, METADATA_JOB_MODE: DATABASE_EXPORT, JOB VERSION: 12.1.0.0.0, ACCESS METHOD: NULL, DATA OPTIONS: 0, DUMPER DIRECTORY: NULL REMOTE LI

NK: NULL, TABLE EXISTS: NULL, PARTITION OPTIONS: NONE

MASTER_ONLY: FALSE, DATA_ONLY: FALSE, METADATA_ONLY: FALSE, DUMPFILE_PRESENT: TRUE, JOB_RESTARTED: FALSE

MANZOOR

Mixed Auditing Mode

When upgrading datbase from 11g to 12c the audit settings are not affected and continue to support the traditional auditing facilities. it is recommended to migrate to the unified auditing to take advance of the new features and the performance.

Before decide to switch you can use the create audit policy command and enable it with audit command, this will enable the mixed audit configuration.

If you do not want to create new policy we can enable either one of these policies

ORA_SECURECONFIG - Oracle 11g default audit settings. (This policy is enabled by default).

ORA_ACCOUNT_MGMT - Audits user account and privilege management.

ORA_DATABASE_PARAMETER – Audits the changes made to database parameters.

when a database is created, mixed auditing mode will be enabled by default usng the ORA_SECURECONFIG policy. But unified auditing mode is not yet enabled.

SQL> select parameter, value from V$option where PARAMETER = 'Unified Auditing';

PARAMETER VALUE

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

Unified Auditing FALSE

Enabling Unified Auditing Mode

To enable it relink oracle with uniaud_on , after enabling none of the old

parameters are honoured (audit_trail/audit_file_dest/audit_sys_operation/audit_syslog_level).

There is no possibility to write audit records to os / syslog

a) Shutdown all process and database instance which are running from the oracle home.

b) Enable uniaud_on and relink oracle

[oracle@rhel11gr2rac1 ~]$ cd /oracle/product/12.1.0.2/db_1/rdbms/lib/

[oracle@rhel11gr2rac1 lib]$ make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME

c) Startup all the process from the oracle home.

d) Unified auditing is enabled and traditional auditing is disabled.

SQL> select parameter, value from V$option where PARAMETER = 'Unified Auditing';

PARAMETER VALUE

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

Unified Auditing TRUE

Security

--------

SYS user activity (startup / shutdown / alter database / alter system) are automatically audited.

Mandatory sys auditing logs will be written to $ORACLE_BASE/audit/oracle_sid when db is not open.

[oracle@rhel11gr2rac1 purchase]$ pwd

/u01/app/oraInventory/audit/purchase

[oracle@rhel11gr2rac1 purchase]$ ls -lrt

total 20

-rw-r----- 1 oracle oinstall 1024 Sep 5 08:57 ora_audit_10.bin

-rw-r----- 1 oracle oinstall 1024 Sep 5 08:58 ora_audit_00.bin

-rw-r----- 1 oracle oinstall 1536 Sep 5 08:58 ora_audit_11.bin

-rw-r----- 1 oracle oinstall 2560 Sep 5 08:58 ora_audit_17.bin

-rw-r----- 1 oracle oinstall 2560 Sep 5 08:58 ora_audit_131.bin

You can load the files in to the database once the db is open using the below.

dbms_audit_mgmt.unified_audit_files, once the files are loaded this will be automatically delted from the OS.

SQL> exec dbms_audit_mgmt.load_unified_audit_files;

PL/SQL procedure successfully completed.

SQL> exit

[oracle@rhel11gr2rac1 purchase]$ ls -lrt

total 0

audit_admin role is required to configure audit.

audit_viewer roles allows to view and analyze audit data.

Audit Policy

a) Create audit policies.

SYNTAX

Create audit policy [policy_name]

[Audit-option :- system wide / object based / role based ]

optional [ When condition EVALUATE PER STATEMENT] /* Condition are evaluated per statement or once during instance / session level */

optional [ event handler alert ]

in CDB [container = CURRENT / ALL ]

b) Enable audit policy using

SYNTAX

Audit policy [policy_name] [by all (default) / user / except user (username)]

c) Disable audit policy using

SYNTAX

Noaudit policy policy_name] [by all (default) / user / except user (username)]

d) View audit records from unified_audit_trail view.

Refer - https://docs.oracle.com/database/121/SQLRF/statements_4007.htm#SQLRF01107 for SYNTAX.

Creating Audit policies

Privilege based

SQL> SELECT view_name FROM DBA_VIEWS where view_name like '%AUDITABLE%';

VIEW_NAME

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

AUDITABLE_SYSTEM_ACTIONS

AUDITABLE_OBJECT_ACTIONS

SQL> create audit policy audit_sys_wide privileges create table ;

Audit policy created.

SQL> alter audit policy audit_sys_wide add privileges select any table, create procedure;

Audit policy altered.

Refer below views for list of auditable actions.

SQL> conn manzoor/ahamed

Connected.

SQL> create table emp_1 as select * from emp where 1=2;

Table created.

SQL> create procedure test_pro as

2 a number;

3 b number;

4 begin

5 a := 10;

6 b := 20;

7 dbms_output.put_line(a+b);

8 end;

9 /

Procedure created.

SQL> select * from scott.emp where empno = 20;

no rows selected

Lets verify.

SQL> select SESSIONID, userhost, DBUSERNAME, EVENT_TIMESTAMP, ACTION_NAME, SQL_TEXT from unified_audit_trail where UNIFIED_AUDIT_POLICIES = 'AUDIT_SYS_WIDE'

SESSIONID USERHOST DBUSERNAME

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

EVENT_TIMESTAMP ACTION_NAME

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

SQL_TEXT

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

1677519438 rhel11gr2rac1.manzoor.com MANZOOR

05-SEP-15 10.09.52.022055 AM CREATE TABLE

create table emp_1 as select * from emp where 1=2

1677519438 rhel11gr2rac1.manzoor.com MANZOOR

05-SEP-15 10.11.00.280418 AM CREATE PROCEDURE

create procedure test_pro as

a number;

b number;

begin

a := 10;

b := 20;

1677519438 rhel11gr2rac1.manzoor.com MANZOOR

05-SEP-15 10.14.39.587038 AM SELECT

select * from scott.emp where empno = 20

Create audit policies based on object specific action.

SQL> create audit policy aud_update_ename actions update on manzoor.emp;

Audit policy created.

SQL> audit policy aud_update_ename by sys whenever successful;

Audit succeeded.

SQL> audit policy aud_update_ename by scott;

Audit succeeded.

SQL> create audit policy aud_delete_emp actions delete on manzoor.emp;

Audit policy created.

SQL> audit policy aud_delete_emp;

Audit succeeded.

SQL> col policy_name for a18;

SQL> select * from audit_unified_enabled_policies where policy_NAME IN ('AUD_UPDATE_ENAME','AUD_DELETE_EMP');

USER_NAME POLICY_NAME ENABLED_ SUC FAI

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

SYS AUD_UPDATE_ENAME BY YES NO

SCOTT AUD_UPDATE_ENAME BY YES YES

ALL USERS AUD_DELETE_EMP BY YES YES

[oracle@rhel11gr2rac1 purchase]$ sqlplus scott/tiger

SQL*Plus: Release 12.1.0.2.0 Production on Sat Sep 5 11:31:18 2015

Last Successful login time: Sat Sep 05 2015 10:31:42 +08:00

Connected to:

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

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

and Unified Auditing options

SQL> update manzoor.emp set ename = 'Ahamed';

997 rows updated.

SQL> commit;

Commit complete.

SQL> delete from manzoor.emp where ename = 'Ahamed';

996 rows deleted.

SQL> commit;

and Unified Auditing options

[oracle@rhel11gr2rac1 purchase]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Sep 5 11:37:46 2015

Connected to:

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

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

and Unified Auditing options

SQL> update manzoor.emp set ename = 'Manzoor';

1000 rows updated.

SQL> commit;

Commit complete.

SQL> select DBUSERNAME, ACTION_NAME, SQL_TEXT from unified_audit_trail where UNIFIED_AUDIT_POLICIES in ('AUD_UPDATE_ENAME','AUD_DELETE_EMP');

DBUSERNAME ACTION_NAME SQL_TEXT

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

SCOTT UPDATE update manzoor.emp set ename = 'Ahamed'

SCOTT DELETE delete from manzoor.emp where ename = 'Ahamed'

SYS UPDATE update manzoor.emp set ename = 'Manzoor'

SQL> noaudit policy aud_update_ename by sys whenever successful;

Noaudit succeeded.

SQL> noaudit policy aud_update_ename by scott;

Noaudit succeeded.

SQL> drop audit policy aud_update_ename;

Audit Policy dropped.

SQL> noaudit policy aud_delete_emp;

Noaudit succeeded.

SQL> drop audit policy aud_delete_emp;

drop audit policy aud_delete_emp

ERROR at line 1:

ORA-46361: Audit policy cannot be dropped as it is currently enabled.

SQL> col user_name for a20;

SQL> col policy_name for a20;

SQL> select * from AUDIT_UNIFIED_ENABLED_POLICIES;

USER_NAME POLICY_NAME ENABLED_ SUC FAI

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

MANZOOR IMP_TABLE BY YES YES

MANZOOR AUD_DELETE_EMP BY YES YES

ALL USERS ORA_SECURECONFIG BY YES YES

ALL USERS DATAPUMP_AUDIT BY YES YES

ALL USERS AUDIT_SYS_WIDE BY YES YES

SQL> noaudit policy AUD_DELETE_EMP by MANZOOR;

Noaudit succeeded.

SQL> drop audit policy AUD_DELETE_EMP;

Audit Policy dropped.

SQL> create audit policy aud_truncate actions truncate table;

Audit policy created.

SQL> audit policy aud_truncate by manzoor;

Audit succeeded.

SQL> select * from AUDIT_UNIFIED_ENABLED_POLICIES;

USER_NAME POLICY_NAME ENABLED_ SUC FAI

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

MANZOOR AUD_TRUNCATE BY YES YES

ALL USERS ORA_SECURECONFIG BY YES YES

ALL USERS DATAPUMP_AUDIT BY YES YES

SQL> select POLICY_NAME, AUDIT_CONDITION, AUDIT_OPTION, AUDIT_OPTION_TYPE from AUDIT_UNIFIED_POLICIES where policy_name = 'AUD_TRUNCATE';

POLICY_NAME AUDIT_CONDITION AUDIT_OPTION AUDIT_OPTION_TYPE

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

AUD_TRUNCATE NONE TRUNCATE TABLE STANDARD ACTION

[oracle@rhel11gr2rac1 ~]$ sqlplus manzoor/ahamed@purchase

SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 15 21:51:53 2015

Last Successful login time: Thu Oct 15 2015 21:45:20 +08:00

Connected to:

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

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

and Unified Auditing options

SQL> create table emp as select * from scott.emp;

Table created.

SQL> truncate table emp;

Table truncated.

SQL> select dbusername, userhost, sql_text from unified_audit_trail where dbusername = 'MANZOOR' and sql_text like '%trunc%';

DBUSERNAME USERHOST

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

SQL_TEXT

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

MANZOOR rhel11gr2rac1.manzoor.com

truncate table emp

Refer Me for Oracle

Thursday, 15 October 2015

Auditing

No comments:

Post a Comment

About Me