Know more about Unified Auditing in Oracle 19c
Today, a customer encountered a database issue in an Oracle 19c 4-node RAC environment on an Oracle Exadata machine. The database is experiencing a high number of active sessions—thousands in total—indicating waits for ‘enq: hw contention’ and ‘enq: tx contention.’ The blocked business session is executing the SQL statement “insert into AUD$UNIFIED…,” related to unified auditing of the database. However, it’s puzzling that audit records, including FGA, show complete delete statements among the DML records. If the SQL execution volume is large, this leads to an increase in records. Currently, the most recent partition has 200 million records over the last two days, with a segment size exceeding 450GB. Attached is a record of the 19c audit.
What is Unified Auditing?
When you create a new database, by default the database uses mixed mode auditing, which enables both traditional (that is, the audit facility from releases earlier than Release 12c) and the new audit facilities (unified auditing). This chapter describes how to use pure unified auditing only.
You can enable the database in either of these two modes, The mixed mode auditing or pure unified auditing mode. In mixed mode, you can use the new unified audit facility alongside the traditional auditing facility. In pure unified auditing, you only use the unified audit facility.
Once unified auditing is enabled, it does not depend on the initialization parameters that were used in previous releases.
By default, audit trail records are written to the AUDSYS schema in the SYSAUX tablespace.
starting with Oracle Database 12c release 2 (12.2) the queued-write mode is deprecated and the unified audit records are written immediately to disk to a range-partitioned table in the AUDSYS schema called AUD$UNIFIED.
Access to the AUDSYS. AUD$UNIFIED table is provided via the view SYS.UNIFIED_AUDIT_TRAIL.
Unified auditing can be implemented in one of the following ways:
1) Mixed mode auditing: allows both the new audit engine and the traditional pre-Oracle 12c audit engine to work simultaneously. This is the default on upgrade to 12c.
2) Pure unified auditing: allows only the unified auditing
How to enable Unified Auditing?
You need to check the Unified Auditing value is FALSE it means Unified Auditing status is disabled and the audit_trail parameter value is must be set to NONE.
SQL> set lines 400 pages 400 SQL> col value for a30 SQL> col parameter for a50 SQL> select parameter, value from v$option where parameter='Unified Auditing'; PARAMETER VALUE -------------------------------------------------- ------------------------------ Unified Auditing FALSE SQL> alter system set audit_trail=none scope=spfile; System altered. SQL> shut immediate;
Shutdown the Database and Stop all services related to Oracle Binary.
Enable the Unified Auditing
[oracle@anbob ~]$ cd $ORACLE_HOME/rdbms/lib
oracle@anbob lib]$ pwd
/u01/app/oracle/product/19c/dbhome_1/rdbms/lib
[oracle@anbob lib]$ ls -ltr ins_rdbms.mk*
-rw-r--r--. 1 oracle oinstall 43434 Apr 17 2019 ins_rdbms.mk
-rw-r--r--. 1 oracle oinstall 43434 Feb 17 23:45 ins_rdbms.mk_backup
[oracle@anbob lib]$ make -f ins_rdbms.mk uniaud_on ioracle
Check the Unified Auditing status:
SQL> select parameter, value from v$option where parameter='Unified Auditing'; PARAMETER VALUE -------------------------------------------------- ------------------------------ Unified Auditing TRUE SQL>
Change the default tablespace for Unified Auditing:
SQL> create tablespace audit_data datafile '/u01/app/oracle/oradata/ORCL/audit_data_01.dbf' size 100m autoextend on; Tablespace created. SQL> SQL> begin 2 dbms_audit_mgmt.set_audit_trail_location( 3 audit_trail_type => dbms_audit_mgmt.audit_trail_unified, 4 audit_trail_location_value => 'AUDIT_DATA'); 5 end; 6 / PL/SQL procedure successfully completed. SQL> SQL> select owner,table_name,interval,partitioning_type,partition_count,def_tablespace_name from dba_part_Tables where owner='AUDSYS'; OWNER TABLE_NAME INTERVAL PARTITION PARTITION_COUNT DEF_TABLESPACE_NAME ---------- --------------- -------------------- --------- --------------- ------------------------------ AUDSYS AUD$UNIFIED INTERVAL '1' MONTH RANGE 1048575 AUDIT_DATA
How to disable Unified Auditing?
SQL> select parameter, value from v$option where parameter='Unified Auditing'; PARAMETER VALUE -------------------------------------------------- ------------------------------ Unified Auditing TRUE -- List all policy but not enable all SQL> select distinct policy_name,AUDIT_OPTION_TYPE from audit_unified_policies order by 1; POLICY_NAME AUDIT_OPTION_TYPE ------------------------------------------------------- ------------------ ORA$DICTIONARY_SENS_COL_ACCESS NONE ORA_ACCOUNT_MGMT STANDARD ACTION ORA_ALL_TOPLEVEL_ACTIONS STANDARD ACTION ORA_CIS_RECOMMENDATIONS STANDARD ACTION ORA_CIS_RECOMMENDATIONS SYSTEM PRIVILEGE ORA_DATABASE_PARAMETER STANDARD ACTION ORA_DV_AUDPOL OBJECT ACTION ORA_DV_AUDPOL2 DV ACTION ORA_LOGON_FAILURES STANDARD ACTION ORA_LOGON_LOGOFF STANDARD ACTION ORA_RAS_POLICY_MGMT XS ACTION ORA_RAS_SESSION_MGMT XS ACTION ORA_SECURECONFIG OBJECT ACTION ORA_SECURECONFIG STANDARD ACTION ORA_SECURECONFIG SYSTEM PRIVILEGE ORA_STIG_RECOMMENDATIONS OBJECT ACTION ORA_STIG_RECOMMENDATIONS OLS ACTION ORA_STIG_RECOMMENDATIONS STANDARD ACTION ORA_STIG_RECOMMENDATIONS SYSTEM PRIVILEGE
Check and Disable all enabled Policies:
SQL> select * from audit_unified_enabled_policies;
POLICY_NAME ENABLED_OPTION ENTITY_NAME ENTITY_TYP SUCCESS FAILURE
------------------------------ --------------- ------------------------------ ---------- ---------- ----------
ORA_LOGON_FAILURES BY USER ALL USERS USER YES YES
POLICY_1 BY USER ALL USERS USER YES YES
ORA_SECURECONFIG BY USER ALL USERS USER YES YES
FOR_CREDENTIALS BY USER ALL USERS USER YES YES
SQL> select POLICY_NAME,AUDIT_OPTION,AUDIT_OPTION_TYPE,OBJECT_SCHEMA from audit_unified_policies where POLICY_NAME='ORA_SECURECONFIG'
POLICY_NAME AUDIT_OPTION AUDIT_OPTION_TYPE OBJECT_SCHEMA
-------------------- -------------------------------------------------- ------------------ --------------------
ORA_SECURECONFIG LOGMINING SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG TRANSLATE ANY SQL SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG EXEMPT REDACTION POLICY SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG PURGE DBA_RECYCLEBIN SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG ADMINISTER KEY MANAGEMENT SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG DROP ANY SQL TRANSLATION PROFILE SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG ALTER ANY SQL TRANSLATION PROFILE SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG CREATE ANY SQL TRANSLATION PROFILE SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG CREATE SQL TRANSLATION PROFILE SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG CREATE EXTERNAL JOB SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG CREATE ANY JOB SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG GRANT ANY OBJECT PRIVILEGE SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG EXEMPT ACCESS POLICY SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG CREATE ANY LIBRARY SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG GRANT ANY PRIVILEGE SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG DROP ANY PROCEDURE SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG ALTER ANY PROCEDURE SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG CREATE ANY PROCEDURE SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG ALTER DATABASE SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG GRANT ANY ROLE SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG DROP PUBLIC SYNONYM SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG CREATE PUBLIC SYNONYM SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG DROP ANY TABLE SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG ALTER ANY TABLE SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG CREATE ANY TABLE SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG DROP USER SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG BECOME USER SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG CREATE USER SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG AUDIT SYSTEM SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG ALTER SYSTEM SYSTEM PRIVILEGE NONE
ORA_SECURECONFIG CREATE DATABASE LINK STANDARD ACTION NONE
ORA_SECURECONFIG DROP DATABASE LINK STANDARD ACTION NONE
ORA_SECURECONFIG ALTER USER STANDARD ACTION NONE
ORA_SECURECONFIG CREATE ROLE STANDARD ACTION NONE
ORA_SECURECONFIG DROP ROLE STANDARD ACTION NONE
ORA_SECURECONFIG SET ROLE STANDARD ACTION NONE
ORA_SECURECONFIG CREATE PROFILE STANDARD ACTION NONE
ORA_SECURECONFIG DROP PROFILE STANDARD ACTION NONE
ORA_SECURECONFIG ALTER PROFILE STANDARD ACTION NONE
ORA_SECURECONFIG ALTER ROLE STANDARD ACTION NONE
ORA_SECURECONFIG CREATE DIRECTORY STANDARD ACTION NONE
ORA_SECURECONFIG DROP DIRECTORY STANDARD ACTION NONE
ORA_SECURECONFIG ALTER DATABASE LINK STANDARD ACTION NONE
ORA_SECURECONFIG CREATE PLUGGABLE DATABASE STANDARD ACTION NONE
ORA_SECURECONFIG ALTER PLUGGABLE DATABASE STANDARD ACTION NONE
ORA_SECURECONFIG DROP PLUGGABLE DATABASE STANDARD ACTION NONE
ORA_SECURECONFIG ALTER DATABASE DICTIONARY STANDARD ACTION NONE
ORA_SECURECONFIG EXECUTE OBJECT ACTION REMOTE_SCHEDULER_AGE
NT
disable all enabled Ploicies in this database.
SQL> select 'noaudit policy '||policy_name||';' from audit_unified_enabled_policies; 'NOAUDITPOLICY'||POLICY_NAME||';' -------------------------------------------------- noaudit policy ORA_LOGON_FAILURES; noaudit policy POLICY_1; noaudit policy ORA_SECURECONFIG; noaudit policy FOR_CREDENTIALS;
Demo –23C
SQL> create table anbob.test1(id int); Table created. SQL> create audit policy ANBOB_P1 actions select on anbob.test1; SQL> select * from audit_unified_enabled_policies; POLICY_NAME ENABLED_OPTION ENTITY_NAME ENTITY_ SUC FAI -------------------- --------------- ------------------------------------------------------- ------- --- --- ORA_SECURECONFIG BY USER ALL USERS USER YES YES ORA_LOGON_FAILURES BY USER ALL USERS USER NO YES ORA$DICTIONARY_SENS_ BY USER ALL USERS USER YES YES COL_ACCESS SQL> col POLICY_NAME for a50 SQL>select unique policy_name from audit_unified_policies POLICY_NAME -------------------------------------------------- ORA$DICTIONARY_SENS_COL_ACCESS ORA_STIG_RECOMMENDATIONS ORA_ACCOUNT_MGMT ORA_DATABASE_PARAMETER ORA_LOGON_FAILURES ORA_RAS_SESSION_MGMT ORA_RAS_POLICY_MGMT ANBOB_P1 ORA_DV_AUDPOL2 ORA_CIS_RECOMMENDATIONS ORA_ALL_TOPLEVEL_ACTIONS ORA_DV_AUDPOL ORA_SECURECONFIG ORA_LOGON_LOGOFF SQL> audit policy ANBOB_P1; Audit succeeded. SQL> col ENTITY_NAME for a30 SQL> select * from audit_unified_enabled_policies POLICY_NAME ENABLED_OPTION ENTITY_NAME ENTITY_ SUC FAI -------------------------------------------------- --------------- ------------------------------ ------- --- --- ORA_SECURECONFIG BY USER ALL USERS USER YES YES ORA_LOGON_FAILURES BY USER ALL USERS USER NO YES ORA$DICTIONARY_SENS_COL_ACCESS BY USER ALL USERS USER YES YES ANBOB_P1 BY USER ALL USERS USER YES YES SQL> select * from anbob.test1; no rows selected SQL> insert into anbob.test1 values(1); SQL> commit; col OS_USERNAME format a15 col SQL_TEXT format a30 col ACTION_NAME format a10 col UNIFIED_AUDIT_POLICIES format a10 col ACTION_NAME format a30 SQL> select AUDIT_TYPE,OS_USERNAME,TERMINAL,ACTION_NAME,SQL_TEXT,UNIFIED_AUDIT_POLICIES from UNIFIED_AUDIT_TRAIL where OBJECT_NAME='TEST1' AUDIT_TYPE OS_USERNAME TERMINAL ACTION_NAME SQL_TEXT UNIFIED_AU ---------- --------------- ------------------------------ ------------------------------ ------------------------------ ---------- Standard oracle pts/3 CREATE TABLE create table anbob.test1(id i ORA_SECURE nt) CONFIG Standard oracle pts/3 SELECT select * from anbob.test1 ANBOB_P1
The cause for the action not capture by audit in a procedure is due to the policy ONLY TOPLEVEL.
UNIFIED_AUDIT_TRAIL
SQL> @o %.unified_audit_trail
owner object_name object_type status OID D_OID CREATED LAST_DDL_TIM
------------------------- ------------------------------ -------------------- --------- ---------- ---------- ------------ ------------
AUDSYS UNIFIED_AUDIT_TRAIL VIEW VALID 21690 07-OCT-22 07-OCT-22
PUBLIC UNIFIED_AUDIT_TRAIL SYNONYM VALID 21691 07-OCT-22 07-OCT-22
SQL> @v audsys.UNIFIED_AUDIT_TRAIL
Show SQL text of views matching "%audsys.UNIFIED_AUDIT_TRAIL%"...
V_OWNER VIEW_NAME TEXT
------------------------- ------------------------------ ----------------------------------------------------------------------------------------------------
AUDSYS UNIFIED_AUDIT_TRAIL
catuat.sql:create or replace public synonym CDB_UNIFIED_AUDIT_TRAIL for AUDSYS.CDB_UNIFIED_AUDIT_TRAIL
Rem Project 46892
Rem UNIFIED_AUDIT_TRAIL is now UNION ALL on gv$unified_audit_trail and
Rem new relational table AUDSYS.AUD$UNIFIED
select ...
from sys.gv_$unified_audit_trail uview, sys.all_unified_audit_actions act,
sys.system_privilege_map spx, sys.stmt_audit_option_map aom
where uview.action = act.action (+)
and - uview.system_privilege = spx.privilege (+)
and uview.audit_option = aom.option# (+)
and uview.audit_type = act.type
UNION ALL
select ...
from audsys.aud$unified auduni, sys.all_unified_audit_actions act1,
sys.system_privilege_map spx1, sys.stmt_audit_option_map aom1
where auduni.action = act1.action (+)
and - auduni.system_privilege = spx1.privilege (+)
and auduni.audit_option = aom1.option# (+)
and auduni.audit_type = act1.type)
/
comment on table AUDSYS.UNIFIED_AUDIT_TRAIL is
'All audit trail entries'
/
create or replace public synonym UNIFIED_AUDIT_TRAIL for AUDSYS.UNIFIED_AUDIT_TRAIL
/
Purging Unified Audit Trail
SQL> COLUMN parameter_value FORMAT A20
SQL> COLUMN audit_trail FORMAT A20
SQL> 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
14 rows selected.
SQL> select unified_audit_policies,action_name,count(*) from unified_audit_trail group by unified_audit_policies,action_name;
UNIFIED_AUDIT_POLICIES ACTION_NAME COUNT(*)
---------------------------------------- -------------------- ----------
EXECUTE 3
ORA_LOGON_FAILURES LOGON 408275
SQL>select audit_type,unified_audit_policies,action_name,return_code,count(*)
from unified_audit_trail where event_timestamp>sysdate-1
group by audit_type,unified_audit_policies,action_name,return_code
order by 5 desc;
SQL> alter table AUDSYS.AUD$UNIFIED truncate partition SYS_P3087;
alter table AUDSYS.AUD$UNIFIED truncate partition SYS_P3087
*
ERROR at line 1:
ORA-46385: DML and DDL operations are not allowed on table "AUDSYS"."AUD$UNIFIED".
[oracle@db1 ~]$ oerr ora 46385
46385, 00000, "DML and DDL operations are not allowed on table \"%s\".\"%s\"."
// *Cause: A DML or DDL operation was attempted on a unified auditing
// internal table.
// *Action: No action required. Only Oracle is allowed to perform such
// operations on a unified auditing internal table.
to PURGE RECORD using dbms_audit_mgmt package, I set the timestamp to 6 hours before now
SQL> exec dbms_audit_mgmt.set_last_archive_timestamp(audit_trail_type=>dbms_audit_mgmt.audit_trail_unified,last_archive_time=>sysdate-6/24);
PL/SQL procedure successfully completed.
And call the clean procedure:
SQL> exec dbms_audit_mgmt.clean_audit_trail(audit_trail_type=>dbms_audit_mgmt.audit_trail_unified,use_last_arch_timestamp=>TRUE);
PL/SQL procedure successfully completed.
if you trace the purge opration using 10046 event will be find
select sys.dbms_audit_mgmt.is_droppable_partition(:1, :2) from dual
The Unified Audit Trail is partitioned on timestamp and the purge procedure checks it the partition can be dropped instead of running a long delete statement.
Purging the Unified Audit Trail via a job
SQL> BEGIN DBMS_AUDIT_MGMT.CREATE_PURGE_JOB ( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, AUDIT_TRAIL_PURGE_INTERVAL => 24, AUDIT_TRAIL_PURGE_NAME => ‘Unified_Audit_Trail_Purge_Job’, USE_LAST_ARCH_TIMESTAMP => TRUE, CONTAINER => DBMS_AUDIT_MGMT.CONTAINER_CURRENT); END; /
When purge a large number of Unified Auditing log using last_arch_timestamp, and the purge scope is not include the whole partition, therefore, it has to use DELETE instead of DROP PARTITION, and DELETE is much more expensive than DROP PARTITON to delete AUDSYS.AUD$UNIFIED records one by one, and lots of time spent on access and maintenance related LOB segments and index segments.
Alter AUDSYS.AUD$UNIFIED to daily time interval partition, and specified the last_arch_timestamp to cover the whole partitions, that will use DROP PARTITION instead of DELETE internally.
BEGIN DBMS_AUDIT_MGMT.ALTER_PARTITION_INTERVAL( interval_number => 1, interval_frequency => 'DAY'); END; /
Purge all
SQL> exec dbms_audit_mgmt.clean_audit_trail(audit_trail_type=>dbms_audit_mgmt.audit_trail_unified ,use_last_arch_timestamp=>FALSE);
PL/SQL procedure successfully completed.
You can see directly in the trace a truncate of the whole table 。
- If you want to manage only the old audit, then you should disable ORA_LOGON_FAILURES and ORA_SECURECONFIG.
- If you want to manage both, then add a job to purge the unified audit trail (audit_trail_type=>dbms_audit_mgmt.audit_trail_unified).
- If you don’t use the old auditing, then enable the ‘pure mode’. But then, AUDIT_TRAIL=NONE is ignored.
Differenct DBMS_FGA
--FGA
sys@ORA19C> BEGIN
DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => 'TEST',
OBJECT_NAME => 'TEST_INDEX',
POLICY_NAME => 'TEST_INDEX_POL',
ENABLE => TRUE,
STATEMENT_TYPES => 'INSERT,UPDATE,DELETE');
END;
/
PL/SQL procedure successfully completed.
Fine-grained audit FGA does not query audit_unified_enabled_policies, but queries dba_audit_policies. FGA audit DML records(non sys user ) are not in UNIFIED_AUDIT_TRAIL but in dba_fga_audit_trail and DBA_COMMON_AUDIT_TRAIL.
Known Issue
This is likely caused by a known but UNPUBLISHED defect:
Bug 28571239 – UNIFIED_AUDIT_POLICIES COLUMN EMPTY FOR SOME ENTRIES
Note that this is superseded by UNPUBLISHED defect:
Bug 32969863 – UNIFIED_AUDIT_POLICIES COLUMN IS EMPTY FOR PRIVILEGE AUDITING
References
Unified Auditing Purge Takes Too Long Time By DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL Using last_arch_timestamp(Doc ID 2573372.1)
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。