8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 19c » Here

Auditing Enhancements in Oracle Database 19c

This article gives an overview of the auditing enhancements in Oracle database 19c.

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.

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:

Hope this helps. Regards Tim...

Back to the Top.