OVERVIEW:
With oracle 12c, unified auditing has been introduced. It consolidates all audit trails into a single audit trail table.
It will capture audit records from below sources.
- SYS audit records ( which was written to os trail in traditional method, will now be written to db table)
- Unified audit policies for different action/privilege/statement/role etc.
- EXPDP/IMPDP events
- RMAN events
- Sql loader
TRADITIONAL VS UNIFIED
| TRADITIONAL AUDITING | UNIFIED_AUDITING |
| Depends on db init parameter like audit_trail,audit_sys_log | Independent of db parameter,bydefault enabled |
| writes audit records to different trails depending upon audit type | all audit trails are writeen to single trail |
| sys records are written to os .aud files | sys records are written to unified_audit_trail |
| auditing not possible for rman/expdp/sqllder | auditing can be enabled for db compoenents like rman/datapump/sqlldr |
| each audit record was written to disk immediately, which causes i/o issue | If querywrite method is enabled(default), then all audit records will queued in sga and later will be flushed to disk , which improves performance |
| auditing need to enabled for each action/statement individually. | One policy can contain mulitiple actions/privilge/role audit option and which can enabled or disables easily |
There are two types of unified auditing mode in oracle 12c.
MODE OF UNIFIED_AUDITING:
1. Mixed auditing – By default it is enable in 12c. It enables to use both traditional auditing and unified auditing methods. I.e. apart from traditional auditing we can use all the features of unified auditing. Once we are comfortable with the unified concept, we can migrate exiting audit setup to unified policy , we can enable pure auditing.
This serves as a good mediator for an easy and hassle free switch to the preferred Unified auditing.
2. Pure auditing – Once pure auditing is enabled. We cannot use the traditional auditing methods.
Which unified auditing mode enabled for my database
SELECT value FROM v$option WHERE parameter = 'Unified Auditing'; VALUE ----------------- FALSE
FALSE – > MIXED AUDTING
TRUE –> PURE AUDITING:
How to change from MIXED to PURE auditing:(relink library)
SELECT value FROM v$option WHERE parameter = 'Unified Auditing'; VALUE ----------------- FALSE sqlplus / as sysdba shutdown immediate; exit cd $ORACLE_HOME/rdbms/lib make -f ins_rdbms.mk unaiaud_on ioracle sqlplus / as sysdba startup
SELECT value FROM v$option WHERE parameter = 'Unified Auditing'; VALUE ----------------- TRUE
NOTE – FOR RAC, linking need to be done all the nodes
WHAT IS UNIFIED AUDIT POLICY AND HOW IT WORKS:
Unified audit policy is like a group of audit options with different conditions. It is like a ROLE which is a group of privileges.
For enabling auditing , first need to create a policy with different audit options and then need to enable or disable for all or few users depending upon the requirement.
All the audit records will be stored in unified_audit_trail table. By default 7 audit policies will be present in a 12c database.
DEFAULT POLICIES IN 12C DATABASE
SQL> select distinct POLICY_NAME from AUDIT_UNIFIED_POLICIES; POLICY_NAME ------------------------------------------------------------------------------- ORA_CIS_RECOMMENDATIONS ORA_RAS_POLICY_MGMT ORA_RAS_SESSION_MGMT ORA_LOGON_FAILURES ORA_SECURECONFIG ORA_DATABASE_PARAMETER ORA_ACCOUNT_MGMT
But not all are enabled. Query AUDIT_UNIFIED_ENABLED_POLICIES to find, which policies are enabled.
Query to find which policies are enabled
SQL> select distinct policy_name from AUDIT_UNIFIED_ENABLED_POLICIES; POLICY_NAME ----------------------------------------- ORA_LOGON_FAILURES ORA_SECURECONFIG
Query to check the audit options included in a policy:
SQL> select AUDIT_OPTION from AUDIT_UNIFIED_POLICIES where POLICY_NAME='ORA_SECURECONFIG'; LOGMINING TRANSLATE ANY SQL EXEMPT REDACTION POLICY PURGE DBA_RECYCLEBIN ADMINISTER KEY MANAGEMENT DROP ANY SQL TRANSLATION PROFILE ALTER ANY SQL TRANSLATION PROFILE CREATE ANY SQL TRANSLATION PROFILE CREATE SQL TRANSLATION PROFILE CREATE EXTERNAL JOB CREATE ANY JOB GRANT ANY OBJECT PRIVILEGE EXEMPT ACCESS POLICY CREATE ANY LIBRARY GRANT ANY PRIVILEGE DROP ANY PROCEDURE ALTER ANY PROCEDURE CREATE ANY PROCEDURE ALTER DATABASE GRANT ANY ROLE DROP PUBLIC SYNONYM CREATE PUBLIC SYNONYM DROP ANY TABLE ALTER ANY TABLE CREATE ANY TABLE DROP USER CREATE USER AUDIT SYSTEM ALTER SYSTEM CREATE DATABASE LINK DROP DATABASE LINK ALTER USER CREATE ROLE DROP ROLE SET ROLE CREATE PROFILE DROP PROFILE ALTER PROFILE ALTER ROLE CREATE DIRECTORY DROP DIRECTORY ALTER DATABASE LINK CREATE PLUGGABLE DATABASE ALTER PLUGGABLE DATABASE DROP PLUGGABLE DATABASE EXECUTE
Even if no new policy is created in database, Audit action of the above audit options will be recorded in unified_audit_trail.
Below are few test cases on unified audit policy :
TEST CASE 1 : ( default audit option):
DROP DIRECTORY , which is one of the audit option of the default policy ORA_SECURECONFIG.
Connect to bsstdba and drop a directory
SQL> show user USER is "BSSTDBA" SQL> drop directory TEST; Directory dropped.
Check the audit report
set lines 299 col SQL_TEXT for a23 col action_name for a18 col UNIFIED_AUDIT_POLICIES for a23 SQL> select action_name,SQL_TEXT,UNIFIED_AUDIT_POLICIES ,EVENT_TIMESTAMP from unified_AUDIT_trail where DBUSERNAME='BSSTDBA' and EVENT_TIMESTAMP > sysdate -1/24; ACTION_NAME SQL_TEXT UNIFIED_AUDIT_POLICIES EVENT_TIMESTAMP ------------------ ----------------------- ----------------------- ------------------------------ LOGON ORA_LOGON_FAILURES 16-FEB-17 11.29.03.981436 PM DROP DIRECTORY drop directory TEST2 ORA_SECURECONFIG 16-FEB-17 11.29.59.924533 PM
TEST CASE 2 : CREATE AUDIT POLICY WITH MULTIPLE AUDIT OPTIONS:
create audit policy test_case2 ACTIONS CREATE TABLE, INSERT ON classdba.EMP_TAB, TRUNCATE TABLE, select on classdba.PROD_TAB;
set lines 299 col POLICY_NAME for a23 col AUDIT_OPTION for a12 col AUDIT_CONDITION for a12 col OBJECT_SCHEMA for a23 col OBJECT_NAME for a14 select POLICY_NAME,audit_option,AUDIT_CONDITION,OBJECT_SCHEMA,OBJECT_NAME FROM AUDIT_UNIFIED_POLICIES where POLICY_NAME='TEST_CASE2'; POLICY_NAME AUDIT_OPTION AUDIT_CONDITION OBJECT_SCHEMA OBJECT_NAME ------------ --------------------- ------------------ --------------- ------------------ TEST_CASE2 CREATE TABLE NONE NONE NONE TEST_CASE2 TRUNCATE TABLE NONE NONE NONE TEST_CASE2 INSERT NONE BSSTDBA EMP_TAB TEST_CASE2 SELECT NONE BSSTDBA PROD_TAB SQL> select distinct policy_name from AUDIT_UNIFIED_ENABLED_POLICIES where policy_name='TEST_CASE2'; no rows selected
Unless we enable the policy, auditing conditions wont be evaluated
SQL> audit policy TEST_CASE2; Audit succeeded. SQL> select distinct policy_name from AUDIT_UNIFIED_ENABLED_POLICIES where policy_name='TEST_CASE2'; POLICY_NAME ------------ TEST_CASE2
Do some changes and generate audit report:
SQL> select action_name,SQL_TEXT,UNIFIED_AUDIT_POLICIES ,EVENT_TIMESTAMP from unified_AUDIT_trail where DBUSERNAME='STCDBA' and EVENT_TIMESTAMP > sysdate -1/24;
ACTION_NAME SQL_TEXT UNIFIED_AUDIT_POLICIES EVENT_TIMESTAMP
------------------ ----------------------- ----------------------- --------------------------------
CREATE TABLE create table EMP_NUM as TEST_CASE2 17-FEB-17 09.19.16.054209 AM
select * from classdba.
emp_tab
EXCLUDE ONE USER FROM THE POLICY:
SQL> select USER_NAME,POLICY_NAME,ENABLED_OPT from AUDIT_UNIFIED_ENABLED_POLICIES where POLICY_NAME='TEST_CASE2'; USER_NAME POLICY_NAME ENABLED_OPT ------------- ------------ ----------------------- ALL USERS TEST_CASE2 BY SQL> audit policy TEST_CASE2 except proddba; audit policy TEST_CASE2 except proddba * ERROR at line 1: ORA-46350: Audit policy TEST_CASE2 already applied with the BY clause.
Once audit policy is enabled, if we try to enable again, it will throw error. So to change the audit condition, disable and enable with new condition.
SQL> noaudit policy TEST_CASE2; Noaudit succeeded. SQL> audit policy TEST_CASE2 except stcdba; Audit succeeded. SQL> select USER_NAME,POLICY_NAME,ENABLED_OPT from AUDIT_UNIFIED_ENABLED_POLICIES where POLICY_NAME='TEST_CASE2'; USER_NAME POLICY_NAME ENABLED_OPT ------------- ------------ ----------------------- PRODDBA TEST_CASE2 EXCEPT
Now create a table from stcdba.
SQL> SQL> connect stcdba
Enter password:
Connected.
SQL> create table TEST4 ( empnum number);
Table created.
SQL> conn / as sysdba
Connected.
SQL> select action_name,SQL_TEXT,UNIFIED_AUDIT_POLICIES ,EVENT_TIMESTAMP from unified_AUDIT_trail where DBUSERNAME='STCDBA' and EVENT_TIMESTAMP > sysdate -1/24;
ACTION_NAME SQL_TEXT UNIFIED_AUDIT_POLICIES EVENT_TIMESTAMP
------------------ ----------------------- ----------------------- ---------------------------------------------------------------------------
CREATE TABLE create table EMP_NUM as TEST_CASE2 17-FEB-17 09.19.16.054209 AM
select * from classdba.
emp_tab
We can see the new audit action ( CREATE TABLE TEST4 is not recorded in audit trail table) as expected.
We can mention success/failure condition similar to traditional auditing:
audit policy TEST_CASE2 whenever successful;
audit policy TEST_CASE2 Whenever not successful;
3. TEST_CASE 3 :
Create an audit policy, to audit delete on table bsstdba.EMP_TAB,insert on bsstdba.PROD_TAB and update on bsstdba.SAL_TAB TABLE BY user STCDBA.
This can be achieved by using the same method of test_case2, But here we will define the condition in the audit policy itself, instead of mentioning it while enabling audit.
SQL> create AUDIT POLICY test_case3
ACTIONS DELETE ON classdba.EMP_TAB,
INSERT ON classdba.PROD_TAB,
UPDATE ON classdba.SAL_TAB
WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''STCDBA'''
EVALUATE PER SESSION; 2 3 4 5 6
Audit policy created.
SQL> audit policy TEST_CASE3;
Audit succeeded.
EVALUATE PER refers to the following options:
STATEMENT evaluates the condition for each relevant auditable statement that
occurs.
SESSION evaluates the condition only once during the session, and then caches
and re-uses the result during the remainder of the session. Oracle Database
evaluates the condition the first time the policy is used, and then stores the result
in UGA memory afterward.
INSTANCE evaluates the condition only once during the database instance
lifetime. After Oracle Database evaluates the condition, it caches and re-uses the
result for the remainder of the instance lifetime. As with the SESSION evaluation,
the evaluation takes place the first time it is needed, and then the results are stored
in UGA memory afterward
SQL> select POLICY_NAME,audit_option,AUDIT_CONDITION,OBJECT_SCHEMA,OBJECT_NAME,CONDITION_EVAL_OPT FROM AUDIT_UNIFIED_POLICIES where POLICY_NAME='TEST_CASE3';
POLICY_NAME AUDIT_OPTION AUDIT_CONDITION OBJECT_SCHEMA OBJECT_NAME CONDITION
------------ --------------------- ------------------ --------------- ------------------ ---------
TEST_CASE3 UPDATE SYS_CONTEXT('USERE CLASSDBA SAL_TAB SESSION
NV', 'SESSION_USER
') = 'PRODDBA'
TEST_CASE3 DELETE SYS_CONTEXT('USERE CLASSDBA EMP_TAB SESSION
NV', 'SESSION_USER
') = 'PRODDBA'
TEST_CASE3 INSERT SYS_CONTEXT('USERE CLASSDBA PROD_TAB SESSION
NV', 'SESSION_USER
') = 'PRODDBA'
SQL> select USER_NAME,POLICY_NAME,ENABLED_OPT from AUDIT_UNIFIED_ENABLED_POLICIES where POLICY_NAME='TEST_CASE3';
USER_NAME POLICY_NAME ENABLED_OPT
------------- ------------ -----------------------
ALL USERS TEST_CASE3 BY
TESTCASE_4:
Create an audit policy, to audit insert on bsstdba.PROD_TAB and update on bsstdba.SAL_TAB TABLE WHEN USER_NAME NOT IN (‘STCDBA’,’TCSDBA’)
SQL>
CREATE AUDIT POLICY test_case4
ACTIONS insert on classdba.PROD_TAB,
update on classdba.SAL_TAB
WHEN 'sys_context(''userenv'',''SESSION_USER'') not in ( ''STCDBA'',''TCSDBA'')'
EVALUATE PER STATEMENT;SQL> 2 3 4 5
Audit policy created.
SQL> AUDIT POLICY test_case4;
Audit succeeded.
SQL> SQL> select POLICY_NAME,audit_option,AUDIT_CONDITION,OBJECT_SCHEMA,OBJECT_NAME,CONDITION_EVAL_OPT FROM AUDIT_UNIFIED_POLICIES where POLICY_NAME='TEST_CASE4';
POLICY_NAME AUDIT_OPTION AUDIT_CONDITION OBJECT_SCHEMA OBJECT_NAME CONDITION
------------ --------------------- ------------------ --------------- ------------------ ---------
TEST_CASE4 UPDATE sys_context('usere BSSTDBA SAL_TAB STATEMENT
nv','SESSION_USER'
) not in ( 'PRODDBA
','DEVDBA')
TEST_CASE4 INSERT sys_context('usere BSSTDBA PROD_TAB STATEMENT
nv','SESSION_USER'
) not in ( 'PRODDBA
','DEVDBA')
SQL> connect stcdba
Enter password:
Connected.
SQL> insert into BSSTDBA.PROD_TAB select * from BSSTDBA.PROD_TAB;
4 rows created.
SQL> SQL> commit;
Commit complete.
SQL> select action_name,SQL_TEXT,UNIFIED_AUDIT_POLICIES ,EVENT_TIMESTAMP from unified_AUDIT_trail where UNIFIED_AUDIT_POLICIES='TEST_CASE4';
no rows selected
No audit record found for stcdba as expected.
TEST_CASE 5 ( AUDITING ROLE)
It will audit all users using a particular ROLE
Create an user with dba privs
CREATE USER TCSDBA IDENTIFIED BY TCSDBA; GRANT DBA TO TCSDBA;
Enable audit for the role DBA
CREATE AUDIT POLICY ROLE_AUDIT roles dba; audit policy ROLE_AUDIT;
Do any dba activity and check report
SQL> show user
USER is "DEVDBA"
SQL>
SQL>
SQL> truncate table bsstdba.emp_tab';
SQL> select action_name,SQL_TEXT,UNIFIED_AUDIT_POLICIES ,EVENT_TIMESTAMP from unified_AUDIT_trail where DBUSERNAME='DEVDBA' and action_name like 'TRUNCATE%';
ACTION_NAME SQL_TEXT UNIFIED_AUDIT_POLICIES EVENT_TIMESTAMP
------------------ ---------------------------------- ----------------------- --------------------------------
TRUNCATE TABLE truncate table bsstdba.emp_tab ORA_SECURECONFIG, ROLE_ 17-FEB-17 01.31.58.862039 PM
AUDIT
TEST CASE 6 ( FILTER BY HOSTNAME)
Now we can define to exclude auditing for few hosts
CREATE AUDIT POLICY test_case6
ACTIONS UPDATE ON OE.ORDERS, DELETE ON SALES.ORDERS
WHEN 'SYS_CONTEXT (''USERENV'', ''HOST'') NOT IN
(''sedbhost1'',''sedbhost2'')'
EVALUATE PER STATEMENT;
DATAPUMP AUDITING:
SQL> create audit policy expdp_aduit actions component=datapump export;
Audit policy created.
SQL> audit policy expdp_aduit;
Audit succeeded.
SQL> set lines 299
col POLICY_NAME for a23
col AUDIT_OPTION for a12
col AUDIT_CONDITION for a12
col OBJECT_SCHEMA for a23
col OBJECT_NAME for a14
SQL> select POLICY_NAME,audit_option,AUDIT_CONDITION,OBJECT_SCHEMA,OBJECT_NAME FROM AUDIT_UNIFIED_POLICIES where POLICY_NAME='EXPDP_ADUIT';
POLICY_NAME AUDIT_OPTION AUDIT_CONDIT OBJECT_SCHEMA OBJECT_NAME
----------------------- ------------ ------------ ----------------------- --------------
EXPDP_ADUIT EXPORT NONE NONE NONE
expdp dumpfile=test.dmp logfile=test.log directory=T tables=catalog.TRANSACTIONS
Export: Release 12.1.0.2.0 - Production on Fri Feb 17 11:38:41 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA dumpfile=test.dmp logfile=test.log directory=T tables=catalog.TRANSACTIONS
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "CATALOG"."TRANSACTIONS" 63.94 KB 689 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/export/home/oracle/test.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Fri Feb 17 11:39:19 2017 elapsed 0 00:00:34
SQL> select DBUSERNAME,DP_TEXT_PARAMETERS1 from UNIFIED_AUDIT_TRAIL where DP_TEXT_PARAMETERS1 is not null;
DBUSERNAME DP_TEXT_PARAMETERS1
------------------------------ ------------------------------------------------------------------------------
SYS MASTER TABLE: "SYS"."SYS_EXPORT_TABLE_01" , JOB_TYPE: EXPORT, METADATA_JOB_MO
DE: TABLE_EXPORT, JOB VERSION: 12.1.0.2.0, ACCESS METHOD: AUTOMATIC, DATA OPTI
ONS: 0, DUMPER DIRECTORY: NULL REMOTE LINK: NULL, TABLE EXISTS: NULL, PARTITI
ON OPTIONS: NONE
TEST_CASE7 ( AUDITING FOR SYSTEM PRIVILEGES):
We can enable auditing for system privileges as below.
create audit policy aud_syspriv_pol PRIVILEGES alter any table;
NOTE- Instead of privilege keyword , I have created policy with action keyword in test_case2 , and it is showing similar result.
SYS AUDITING:
With mix auditing, sys audit records will be written to both os level and unified_trail also if audit action policy is enable.
As per Oracle doc:
FOR MIXED AUDITING
Administrative user sessions generate SYS audit records. These records are written if the AUDIT_SYS_OPERATIONS initialization parameter is set to TRUE.This process writes the records only to the traditional audit trails. However, when unified audit policies are enabled for administrative users,
these unified audit records are also written to unified audit trail.
FOR PURE UNIFIED AUDITING:
All sys audit records will be written to UNIFIED_AUDIT_TRAIL TABLE ONLY
set lines 299
col SQL_TEXT for a23
col action_name for a18
col UNIFIED_AUDIT_POLICIES for a23
select action_name,SQL_TEXT,UNIFIED_AUDIT_POLICIES ,EVENT_TIMESTAMP from unified_AUDIT_trail where DBUSERNAME='SYS' and EVENT_TIMESTAMP > sysdate -1/24;
DROPPING AUDIT POLICY:
SQL> DROP AUDIT POLICY TEST_CASE5; DROP AUDIT POLICY TEST_CASE5 * ERROR at line 1: ORA-46361: Audit policy cannot be dropped as it is currently enabled.
We can’t drop a policy when it is enabled.
SQL> NOAUDIT POLICY TEST_CASE5; Noaudit succeeded. SQL> DROP AUDIT POLICY TEST_CASE5; Audit Policy dropped.
PURGE AUDIT TRAIL:
Purging mechanism is similar to that of previous dbms_audit_mgmt(11g) , Only we need to set the audit_trail_type to unified
exec DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP ( - AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, - LAST_ARCHIVE_TIME => sysdate-30) exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( - AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, - USE_LAST_ARCH_TIMESTAMP => TRUE)
Refer – Purge audit table using dbms_mgmt
AUDIT WRITE MODE:
Oracle 12c writes audit records first to SGA. And then periodically flush the queue to AUDSYS schema audit table in sysaux table.
MODES:
1. QUEUED-WRITE MODE (DEFAULT) – PERIODICALLY FLUSHED TO DISK
2. IMMEDIATE-WRITE MODE – IMMEDIATELY FLUSHED ( PRE 12C MODE, NOT RECOMMENDED)
set lines 2999 col PARAMETER_NAME for a31 col PARAMETER_VALUE for a34 select * from dba_audit_mgmt_config_params; PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL ------------------------------- ---------------------------------- ------------------------ DB AUDIT TABLESPACE SYSAUX STANDARD AUDIT TRAIL DB AUDIT TABLESPACE SYSAUX FGA AUDIT TRAIL DB AUDIT TABLESPACE SYSAUX UNIFIED 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 DB AUDIT CLEAN BATCH SIZE 10000 STANDARD AUDIT TRAIL DB AUDIT CLEAN BATCH SIZE 10000 FGA AUDIT TRAIL OS FILE CLEAN BATCH SIZE 1000 OS AUDIT TRAIL OS FILE CLEAN BATCH SIZE 1000 XML AUDIT TRAIL AUDIT WRITE MODE QUEUED WRITE MODE UNIFIED AUDIT TRAIL AUDIT FILE MAX SIZE 10000 UNIFIED AUDIT TRAIL AUDIT FILE MAX AGE 5 UNIFIED AUDIT TRAIL
Role segregation:
AUDYS is the schema, which keeps audit trail info. No user can connect to this user including SYS.
SQL> show user USER is "SYS" SQL> connect AUDSYS/AUDSYS ERROR: ORA-46370: cannot connect as AUDSYS user Warning: You are no longer connected to ORACLE.
Below two new roles are present in 12c
AUDIT_ADMIN – For creating and managing audit policies
AUDIT_VIEWER – For viewing audit reports ( Mostly for auditors)
Nice and detailed
Thanks Srikanth.
Great article but there is typo error
make -f ins_rdbms.mk unaiaud_on ioracle
should be
make -f ins_rdbms.mk uniaud_on ioracle
so what is the purpouse of AUDSYS table when we have UNIFIED_AUDIT_TRAIL view?
Thanks for this post. Very informative !!
we used to select from this DBA_AUDIT_STATEMENT view to find info about grants of privileges on an object. currently we are now in 19c and using the unified auditing — UNIFIED_AUDIT_TRAIL.
every time we issue the command for example:
grant select on table1 to user1;
grant select on table1 to role1;
this didn’t appears recording into the UNIFIED_AUDIT_TRAIL view. we don’t see any rows that are related to the granting of an objects.
checking the v$option returns true for the parameter ‘Unified Auditing’:
select value from v$option where parameter=’Unified Auditing’;
we also tried to enable the audits by this:
audit grant procedure;
audit grant sequence;
audit grant table;
still not working.
how do we set or turn on the audits to grants of privileges on an object? please help.
thank you