8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Auditing Enhancements in Oracle Database 19c
This article gives an overview of the auditing enhancements in Oracle database 19c.
- Audit Top-Level Statements
- EVENT_TIMESTAMP_UTC Column Added to UNIFIED_AUDIT_TRAIL View
- Trace File Analyzer (TFA) and Audit
- Enabling SYSLOG and Windows Event Viewer Captures for the Unified Audit Trail
- PDB_GUID Field Added For SYSLOG and Windows Event Viewer
- Oracle Database Vault Command Rules For Unified Audit Policies
Related articles.
Audit Top-Level Statements
When we are using PL/SQL APIs, a single call to a procedure may result in a large number of auditable actions. To reduce the number of entries in the audit trail, we can audit only top-level statements. In the case of a PL/SQL API, we would only audit the call to the procedure, rather than all the actions taken by the procedure when it runs.
We connect to a privileged user and empty the audit trail. Don't do this if you care about the current contents of the audit trail.
conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba exec dbms_audit_mgmt.clean_audit_trail(dbms_audit_mgmt.audit_trail_unified,false);
We create a test user.
-- drop user testuser1 cascade; create user testuser1 identified by testuser1 quota unlimited on users; grant connect, resource to testuser1; grant select_catalog_role to testuser1;
We create a test table called T1
, and create a procedure called INSERT_T1
to add rows to the T1
table. Both objects are owned by the TESTUSER1
user.
create table testuser1.t1 ( id number ); create or replace procedure testuser1.insert_t1 (p_id in number) as begin insert into t1 values (p_id); end; /
We create two unified audit policies. They both audit all actions for the TESTUSER1
user, but second policy has the ONLY TOPLEVEL
clause added.
create audit policy testuser1_pol actions all when q'~ sys_context('userenv', 'session_user') = 'TESTUSER1' ~' evaluate per session; create audit policy testuser1_toplevel_pol actions all when q'~ sys_context('userenv', 'session_user') = 'TESTUSER1' ~' evaluate per session only toplevel;
We enable the normal unified audit policy.
audit policy testuser1_pol;
We connect to the test user and execute the stored procedure to add a row into the T1
table.
conn testuser1/testuser1@//localhost:1521/pdb1 exec insert_t1(1); commit;
We check the unified audit trail for rows related to the TESTUSER1
user. We have 10 rows of auditing, including the top-level call to the stored procedure, as well as the insert into the T1
table.
conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba set linesize 200 column event_timestamp format a30 column action_name format a13 column object_schema format a15 column object_name format a25 select event_timestamp, action_name, object_schema, object_name from unified_audit_trail where dbusername = 'TESTUSER1' order by event_timestamp; EVENT_TIMESTAMP ACTION_NAME OBJECT_SCHEMA OBJECT_NAME ------------------------------ ------------- --------------- ------------------------- 09-JAN-22 02.52.41.856739 PM LOGON 09-JAN-22 02.52.41.864478 PM ALTER SESSION 09-JAN-22 02.52.41.870481 PM SELECT SYS DUAL 09-JAN-22 02.52.41.876372 PM EXECUTE SYS DBMS_APPLICATION_INFO 09-JAN-22 02.52.41.881929 PM COMMIT 09-JAN-22 02.52.41.886135 PM COMMIT 09-JAN-22 02.52.41.889243 PM INSERT TESTUSER1 T1 09-JAN-22 02.52.41.890034 PM EXECUTE TESTUSER1 INSERT_T1 09-JAN-22 02.52.41.890959 PM COMMIT 09-JAN-22 02.52.53.693288 PM LOGOFF 10 rows selected. SQL>
We stop auditing the normal policy and audit the top-level policy. We also clear down the audit trail.
noaudit policy testuser1_pol; audit policy testuser1_toplevel_pol; exec dbms_audit_mgmt.clean_audit_trail(dbms_audit_mgmt.audit_trail_unified,false);
We connect to the test user and execute the stored procedure to add a row into the T1
table.
conn testuser1/testuser1@//localhost:1521/pdb1 exec insert_t1(1); commit;
We check the unified audit trail for rows related to the TESTUSER1
user. We now have only 8 rows of auditing. We have audited the top-level call to the stored procedure, but not the insert into the T1
table.
conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba set linesize 200 column event_timestamp format a30 column action_name format a13 column object_schema format a15 column object_name format a25 select event_timestamp, action_name, object_schema, object_name from unified_audit_trail where dbusername = 'TESTUSER1' order by event_timestamp; EVENT_TIMESTAMP ACTION_NAME OBJECT_SCHEMA OBJECT_NAME ------------------------------ ------------- --------------- ------------------------- 09-JAN-22 02.56.18.545875 PM LOGON 09-JAN-22 02.56.18.556143 PM SELECT SYS DUAL 09-JAN-22 02.56.18.561399 PM EXECUTE SYS DBMS_APPLICATION_INFO 09-JAN-22 02.56.18.566822 PM COMMIT 09-JAN-22 02.56.18.572013 PM COMMIT 09-JAN-22 02.56.18.575647 PM EXECUTE TESTUSER1 INSERT_T1 09-JAN-22 02.56.18.576603 PM COMMIT 09-JAN-22 02.56.23.354887 PM LOGOFF 8 rows selected. SQL>
When you are finished, disable and drop the audit policy.
noaudit policy testuser1_pol noaudit policy testuser1_toplevel_pol; drop audit policy testuser1_pol; drop audit policy testuser1_toplevel_pol;
EVENT_TIMESTAMP_UTC Column Added to UNIFIED_AUDIT_TRAIL View
The EVENT_TIMESTAMP_UTC
column has been added to the UNIFIED_AUDIT_TRAIL
view. The EVENT_TIMESTAMP_UTC
column should be included in the WHERE clause of queries against the UNIFIED_AUDIT_TRAIL
view where possible, as it will allow partition pruning, resulting in better performance.
Other best practices for querying the UNIFIED_AUDIT_TRAIL
view are listed here.
Trace File Analyzer (TFA) and Audit
In Oracle 19c the Trace File Analyzer (TFA) supports several new Service Request Data Collections (SRDCs) including "dbaudit". You can find the full list here.
cd $ORACLE_HOME/ahf/oracle.ahf/bin ./tfactl diagcollect -srdc dbaudit
Enabling SYSLOG and Windows Event Viewer Captures for the Unified Audit Trail
In Oracle 19c we can write a subset of unified audit trail records to the operating system logging system. Two initialization parameters have been added to support this.
UNIFIED_AUDIT_SYSTEMLOG
: Can be applied to individual containers (CDB or PDBs).UNIFIED_AUDIT_COMMON_SYSTEMLOG
: (UNIX only) Can only be applied to the root container, allowing a common audit log for all containers.
The allowable values vary depending on the operating system.
Windows UNIFIED_AUDIT_SYSTEMLOG = { TRUE | FALSE } UNIX UNIFIED_AUDIT_SYSTEMLOG = 'facility_clause.priority_clause' UNIFIED_AUDIT_COMMON_SYSTEMLOG = 'facility_clause.priority_clause' facility_clause ::= { USER | LOCAL[ 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 ] } priority_clause::= { NOTICE | INFO | DEBUG | WARNING | ERR | CRIT | ALERT | EMERG }
The documentation on this feature is unclear, with some sections of the documentation contradicting others regarding allowable values and what needs to be set to make it work. I have only got it to work using the following process.
Create an entry in the "/etc/rsyslog.conf" file for the logging you are planning to do, then restart SYSLOG.
cat >> /etc/rsyslog.conf <<EOF local0.notice /var/log/local0.notice.audit.log EOF systemctl restart syslog
Set the UNIFIED_AUDIT_COMMON_SYSTEMLOG
parameter in the CDB and the UNIFIED_AUDIT_SYSTEMLOG
in any container (CDB or PDB) that you want to be included in the SYSLOG auditing.
conn / as sysdba alter system set unified_audit_common_systemlog='local0.notice' scope=spfile; shutdown immediate; startup; conn / as sysdba alter session set container=pdb1; alter system set unified_audit_systemlog='local0.notice' scope=spfile; shutdown immediate; startup;
Some audit messages will now be included in the "/var/log/messages" and the "/var/log/local0.notice.audit.log" files. At the time of writing, there doesn't seem to be a way to avoid getting the auditing in the "/var/log/messages" file. This is true up to and including Oracle 19.13 and 21.4.
On Windows systems, simply set the UNIFIED_AUDIT_SYSTEMLOG
initialization parameter in any container and restart the database.
PDB_GUID Field Added For SYSLOG and Windows Event Viewer
The PDB_GUID
field has been added into the audit records written to SYSLOG and the Windows Event Viewer. This indicates which container performed the audited action.
The full list of fields written to SYSLOG and the Windows Event Viewer are listed here here.
Oracle Database Vault Command Rules For Unified Audit Policies
Oracle 19c allows us to create Oracle Database Value command rules for unified audit policies. Specifically the AUDIT POLICY
and NOAUDIT POLICY
statement. You can read about command rules here.
For more information see:
- TFA : New SRDCs - Oracle 19c
- Enabling SYSLOG and Windows Event Viewer Captures for the Unified Audit Trail
- Auditing Only Top-Level Statements
- About Writing the Unified Audit Trail Records to SYSLOG or the Windows Event Viewer
- Best Practices for Querying the UNIFIED_AUDIT_TRAIL Data Dictionary View
- Configuring Command Rules
- Auditing : All Articles
- Autonomous Health Framework (AHF)
- Oracle Trace File Analyzer (TFA)
Hope this helps. Regards Tim...