SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 28 17:07:52 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
New Auditing came with oracle 12c called Unified Auditing which is ” enables selective and effective auditing inside the Oracle database using policies and conditions. The new policy based syntax simplifies management of auditing within the database and provides the ability to accelerate auditing based on conditions. For example, audit policies can be configured to audit based on specific IP addresses, programs, time periods, or connection types such as proxy authentication. In addition, specific schemas can be easily exempted from auditing when the audit policy is enabled.”
You can check here.
regarding to oracle Documentation with this kind of new audit you can capture :
- Audit records (including SYS audit records) from unified audit policies and AUDIT settings
- Fine-grained audit records from the DBMS_FGA PL/SQL package
- Oracle Database Real Application Security audit records
- Oracle Recovery Manager audit records
- Oracle Database Vault audit records
- Oracle Label Security audit records
- Oracle Data Mining records
- Oracle Data Pump
- Oracle SQL*Loader Direct Load
For More Information Read Oracle Documentation Here
With Show parameter auditing you will not see value for unified auditing
SQL> show parameter audit ;
NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /u01/app/oracle/admin/db12c/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
unified_audit_sga_queue_siz integer 1048576
To check Value you need to see v$option table
select value from v$option where PARAMETER = ‘Unified Auditing’;
PARAMETER VALUE
—————————————————————-
Unified Auditing FALSE
if you set this parameter to OS level, check $ORACLE_BASE/audit which the new folder to save all the information , when you create database , oracle uses new feature for auditing called Mixed mode from the name you can see there’s more than one auditing in this mode , One : old mode that we already know in oracle 10g,11g and new mode that called unified auditing.
Auditing Traditional way can be control using AUDIT_TRAIL parameter , the new Way can be determine using Audit Policy called ORA_SECURECONFIG ( Policy create already in database ) and to enable Unified audit you have to enable at least one policy. Only sysdba can do that or someone have two role AUDIT_ADMIN,AUDIT_VIEWER. i mention before on my blog for 12c new feature how to use audit policy and enable,disable and drop them.
This is only small section for what ORA_SECURECONFIG contain and what can audit :
select POLICY_NAME, AUDIT_OPTION from AUDIT_UNIFIED_POLICIES where policy_name = ‘ORA_SECURECONFIG’ order by 2 ;
POLICY_NAME AUDIT_OPTION
——————– —————————————-
ORA_SECURECONFIG ADMINISTER KEY MANAGEMENT
ORA_SECURECONFIG ALTER ANY PROCEDURE
ORA_SECURECONFIG ALTER ANY SQL TRANSLATION PROFILE
ORA_SECURECONFIG ALTER ANY TABLE
ORA_SECURECONFIG ALTER DATABASE
ORA_SECURECONFIG ALTER DATABASE LINK
ORA_SECURECONFIG ALTER PLUGGABLE DATABASE
ORA_SECURECONFIG ALTER PROFILE
ORA_SECURECONFIG ALTER ROLE
ORA_SECURECONFIG ALTER SYSTEM
ORA_SECURECONFIG ALTER USER
ORA_SECURECONFIG AUDIT SYSTEM
ORA_SECURECONFIG CREATE ANY JOB
ORA_SECURECONFIG CREATE ANY LIBRARY
ORA_SECURECONFIG CREATE ANY PROCEDURE
ORA_SECURECONFIG CREATE ANY SQL TRANSLATION PROFILE
ORA_SECURECONFIG CREATE ANY TABLE
ORA_SECURECONFIG CREATE DATABASE LINK
ORA_SECURECONFIG CREATE DIRECTORY
ORA_SECURECONFIG CREATE EXTERNAL JOB
ORA_SECURECONFIG CREATE PLUGGABLE DATABASE
ORA_SECURECONFIG CREATE PROFILE
ORA_SECURECONFIG CREATE PUBLIC SYNONYM
ORA_SECURECONFIG CREATE ROLE
ORA_SECURECONFIG CREATE SQL TRANSLATION PROFIL
Check if this Audit Policy set as default for Database :
SQL> select POLICY_NAME from AUDIT_UNIFIED_ENABLED_POLICIES where policy_name = ‘ORA_SECURECONFIG’;
POLICY_NAME
——————–
ORA_SECURECONFIG
Check this to see by default oracle 12c enable audit which is very useful :
select action_name, dbusername from unified_audit_trail where dbusername=’OSAMA’
ACTION_NAME DBUSERNAME
——————– ——————–
SELECT OSAMA
SELECT OSAMA
SELECT OSAMA
SELECT OSAMA
SELECT OSAMA
SELECT OSAMA
SELECT OSAMA
SELECT OSAMA
UPDATE OSAMA
LOGON OSAMA
LOGON OSAMA
LOGON OSAMA
LOGON OSAMA
LOGOFF OSAMA
LOGOFF OSAMA
LOGOFF OSAMA
LOGOFF OSAMA
LOGOFF OSAMA
LOGOFF OSAMA
Unified Contain new features to monitor RMAN command , Expdp,impdp and other new features but to enable it ( set value to true ) you have to relink oracle lib after shutdown all services using the below command , cd $ORACLE_HOME/rdbms/lib and fire :
make -f ins_rdbms.mk uniaud_on ioracle
SQL> select value from v$option where PARAMETER = ‘Unified Auditing’;
VALUE
———-
TRUE
Let’s Test Some New Features and Audit RMAN :
SQL> create audit policy expdp_aduit actions component=datapump export;
Audit policy created.
SQL> audit policy expdp_aduit;
Audit succeeded.
Check if our audit policy is enabled :
select * from AUDIT_UNIFIED_ENABLED_POLICIES where POLICY_NAME like ‘%EXPDP%’;
USER_NAME POLICY_NAM ENABLED_ SUC FAI
———- ———- ——– — —
ALL USERS EXPDP_AUDIT BY YES YES
Make sure you create directory , Grant privileges on this directory and run expdp command ( system user )
[oracle@test12c dump]$ expdp directory=dump logfile=audit.log dumpfile=osama_schema schemas=osama
this transaction will exists on memory until background process flash it to disks , so before check the tables i will execute package that ensure to do this immediately ( no waiting )
SQL> EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
PL/SQL procedure successfully completed.
check the user who make expdp
SQL> select DBUSERNAME, DP_TEXT_PARAMETERS1, DP_BOOLEAN_PARAMETERS1
from UNIFIED_AUDIT_TRAIL
where DP_TEXT_PARAMETERS1 is not null;
SYSTEM
MASTER TABLE: “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ , JOB_TYPE: EXPORT, METADATA_JOB_
MODE: SCHEMA_EXPORT, JOB VERSION: 12.1.0.0.0, ACCESS METHOD: AUTOMATIC, DATA OPT
IONS: 0, DUMPER DIRECTORY: NULL REMOTE LINK: NULL, TABLE EXISTS: NULL, PARTITIO
N OPTIONS: NONE
MASTER_ONLY: FALSE, DATA_ONLY: FALSE, METADATA_ONLY: FALSE, DUMPFILE_PRESENT: TR
UE, JOB_RESTARTED: FALSE
For test case , i remove users tablespace from os after take backup using rman ( database should be in archive mode ) , by default Audit For RMAN is enable no need to do anything ,like the following :
RMAN > backup tablespace users ;
On OS Level :
rm /u01/app/oracle/oradata/db12c/users01.dbf
Back to RMAN :
RMAN> alter tablespace users offline immediate;
using target database control file instead of recovery catalog
Statement processed
RMAN> restore tablespace USERS;
RMAN> alter tablespace users online;
Now Check Audit :
SQL> select DBUSERNAME, RMAN_OPERATION from UNIFIED_AUDIT_TRAIL where RMAN_OPERATION is not null;
DBUSERNAME RMAN_OPERATION
—————————— ——————–
SYS Recover
SYS Restore
SYS Backup
SYS Backup
Note : if nothing appear at audit table level re run
exec SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL
After finished test :
SQL> noaudit POLICY EXPDP_AUDIT;
Noaudit succeeded.
SQL> select count(*) from unified_audit_trail;
COUNT(*)
———-
3334
You can clean your Audit Now Using Package Called : DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL
Thank you
Osama mustafa
Technology Geek 