Infrastructure at your Service

Lazhar Felahi

From Oracle Standard Auditing to Oracle Unified Auditing

In this post, I will not show you all advantages related to Oracle Unified Auditing. For that, read the oracle documentation.

You can also read the blogs written by my colleagues :

I just want to share with you the steps needed to move from Standard Auditing to Unified Auditing in a mutlitenant environment.

Steps 1 : Check if Unified Auditing is disabled

[email protected]*****:/u01/app/oracle/product/rdbms19_1/rdbms/lib/ [MYCDB] sqh
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 4 10:45:35 2021
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0

SQL> SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

VALUE
----------------------------------------------------------------
FALSE

 

FALSE means unified audit trail and audit policy functionality is enabled in mixed mode

Step 2 : As we want to disabled Standard Auditing and enabled Unifed Auditing, let’s check the audit_file_dest parameter

SQL> sho parameter audit_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/xxxx/adump

Step 4 : Let’s check the folder /u01/app/oracle/admin/xxxx/adump

ls -ltr /u01/app/oracle/admin/CSYR01I/adump
...
-rw-r-----. 1 oracle dba     1090 Oct  4 10:55 MYCDB_m001_19948_202105041945FHDFJDHF888912.aud
-rw-r-----. 1 oracle dba     1090 Oct  4 10:55 MYCDB_m001_19948_202FàDéFàDéDFéàD0527767763.aud
-rw-r-----. 1 oracle dba     1090 oct  4 10:55 MYCDB_m001_19948_20210504FHDJFHDJHFD9851253.aud
-rw-r-----. 1 oracle dba     1090 Oct  4 10:55 MYCDB_m001_19948_202105041FLDKFLDKDL4960612.aud
-rw-r-----. 1 oracle dba    40985 Oct  4 10:56 MYCDB_m003_20296_2021050FDJFDKJD54384117793.aud
-rw-r-----. 1 oracle dba     2504 Oct  4 10:59 MYCDB_ora_32211_202105041FKDJFDDKJFD9350382.aud

 

Step 5 : Stop all listeners and databases sharing the ORACLE_HOME

[email protected]:/u01/app/oracle/product/rdbms19_1/rdbms/lib/ [MYCDB] lsnrctl stop LISTENER
[email protected]:/u01/app/oracle/product/rdbms19_1/rdbms/lib/ [MYCDB] sqh
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 04 14:11:37 2021
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

 

Step 6 : To enable the Unified Auditng, we have to relink the file $ORACLE_HOME/rdbms/lib/ins_rdbms.mk

[email protected]:/u01/app/oracle/product/rdbms19_1/rdbms/lib/ [MYCDB] make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME
/usr/bin/ar d /u01/app/oracle/product/rdbms19_1/rdbms/lib/libknlopt.a kzanang.o
/usr/bin/ar cr /u01/app/oracle/product/rdbms19_1/rdbms/lib/libknlopt.a /u01/app/oracle/product/rdbms19_1/rdbms/lib/kzaiang.o
chmod 755 /u01/app/oracle/product/rdbms19_1/bin
- Linking Oracle
rm -f /u01/app/oracle/product/rdbms19_1/rdbms/lib/oracle
/u01/app/oracle/product/rdbms19_1/bin/orald -o /u01/app/oracle/product/rdbms19_1/rdbms/lib/oracle -m64 -z noexecstack -Wl,--disable-new-dtags -L/u01/app/oracle/product/rdbms19_1/rdbms/lib/ -L/u01/app/oracle/product/rdbms19_1/lib/ -L/u01/app/oracle/product/rdbms19_1/lib/stubs/ -Wl,-E /u01/app/oracle/product/rdbms19_1/rdbms/lib/opimai.o /u01/app/oracle/product/rdbms19_1/rdbms/lib/ssoraed.o /u01/app/oracle/product/rdbms19_1/rdbms/lib/ttcsoi.o -Wl,--whole-archive -lperfsrv19 -Wl,--no-whole-archive /u01/app/oracle/product/rdbms19_1/lib/nautab.o /u01/app/oracle/product/rdbms19_1/lib/naeet.o /u01/app/oracle/product/rdbms19_1/lib/naect.o /u01/app/oracle/product/rdbms19_1/lib/naedhs.o /u01/app/oracle/product/rdbms19_1/rdbms/lib/config.o -ldmext -lserver19 -lodm19 -lofs -lcell19 -lnnet19 -lskgxp19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lclient19 -lvsnst19 -lcommon19 -lgeneric19 -lknlopt -loraolap19 -lskjcx19 -lslax19 -lpls19 -lrt -lplp19 -ldmext -lserver19 -lclient19 -lvsnst19 -lcommon19 -lgeneric19 `if [ -f /u01/app/oracle/product/rdbms19_1/lib/libavserver19.a ] ; then echo "-lavserver19" ; else echo "-lavstub19"; fi` `if [ -f /u01/app/oracle/product/rdbms19_1/lib/libavclient19.a ] ; then echo "-lavclient19" ; fi` -lknlopt -lslax19 -lpls19 -lrt -lplp19 -ljavavm19 -lserver19 -lwwg `cat /u01/app/oracle/product/rdbms19_1/lib/ldflags` -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnro19 `cat /u01/app/oracle/product/rdbms19_1/lib/ldflags` -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnnzst19 -lzt19 -lztkg19 -lmm -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lztkg19 `cat /u01/app/oracle/product/rdbms19_1/lib/ldflags` -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnro19 `cat /u01/app/oracle/product/rdbms19_1/lib/ldflags` -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnnzst19 -lzt19 -lztkg19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 `if /usr/bin/ar tv /u01/app/oracle/product/rdbms19_1/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo19 -lserver19"; fi` -L/u01/app/oracle/product/rdbms19_1/ctx/lib/ -lctxc19 -lctx19 -lzx19 -lgx19 -lctx19 -lzx19 -lgx19 -lclscest19 -loevm -lclsra19 -ldbcfg19 -lhasgen19 -lskgxn2 -lnnzst19 -lzt19 -lxml19 -lgeneric19 -locr19 -locrb19 -locrutl19 -lhasgen19 -lskgxn2 -lnnzst19 -lzt19 -lxml19 -lgeneric19 -lgeneric19 -lorazip -loraz -llzopro5 -lorabz2 -lorazstd -loralz4 -lipp_z -lipp_bz2 -lippdc -lipps -lippcore -lippcp -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lsnls19 -lunls19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lasmclnt19 -lcommon19 -lcore19 -ledtn19 -laio -lons -lmql1 -lipc1 -lfthread19 `cat /u01/app/oracle/product/rdbms19_1/lib/sysliblist` -Wl,-rpath,/u01/app/oracle/product/rdbms19_1/lib -lm `cat /u01/app/oracle/product/rdbms19_1/lib/sysliblist` -ldl -lm -L/u01/app/oracle/product/rdbms19_1/lib `test -x /usr/bin/hugeedit -a -r /usr/lib64/libhugetlbfs.so && test -r /u01/app/oracle/product/rdbms19_1/rdbms/lib/shugetlbfs.o && echo -Wl,-zcommon-page-size=2097152 -Wl,-zmax-page-size=2097152 -lhugetlbfs`
rm -f /u01/app/oracle/product/rdbms19_1/bin/oracle
mv /u01/app/oracle/product/rdbms19_1/rdbms/lib/oracle /u01/app/oracle/product/rdbms19_1/bin/oracle
chmod 6751 /u01/app/oracle/product/rdbms19_1/bin/oracle
(if [ ! -f /u01/app/oracle/product/rdbms19_1/bin/crsd.bin ]; then \
getcrshome="/u01/app/oracle/product/rdbms19_1/srvm/admin/getcrshome" ; \
if [ -f "$getcrshome" ]; then \
crshome="`$getcrshome`"; \
if [ -n "$crshome" ]; then \
if [ $crshome != /u01/app/oracle/product/rdbms19_1 ]; then \
oracle="/u01/app/oracle/product/rdbms19_1/bin/oracle"; \
$crshome/bin/setasmgidwrap oracle_binary_path=$oracle; \
fi \
fi \
fi \
fi\
);

 

Step 7 : Restart the listener and the databases and check if Unified Auditing is enabled from the container database (CDB) and from the pluggable databases (PDB)

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
[email protected]:/u01/app/oracle/product/rdbms19_1/rdbms/lib/ [MYCDB] sqh
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 04 14:26:04 2021
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0

SQL> select value from v$option where lower(parameter)='unified auditing';

VALUE
----------------------------------------------------------------
TRUE

SQL> alter session set container=MYPDB;

Session altered.

SQL> select value from v$option where lower(parameter)='unified auditing';

VALUE
----------------------------------------------------------------
TRUE

Now we have Unified Pure Auditing without traditional auditing, than means that AUDIT* spfile parameters are no more taking account and the *.aud files are no more generated.

 

Step 8 : Set the last archive timestamp for audit records stored into the table unified_audit_trail (below we remove all rows with timestamp > 5 days)

BEGIN
     DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type     => DBMS_AUDIT_MGMT.audit_trail_unified,
    last_archive_time    => SYSTIMESTAMP-5,
    --rac_instance_number  =>  1,
    container            => DBMS_AUDIT_MGMT.container_current
  );
 END;
/

PL/SQL procedure successfully completed.

COLUMN audit_trail FORMAT A20
COLUMN last_archive_ts FORMAT A40
SELECT audit_trail,
       last_archive_ts
  FROM   dba_audit_mgmt_last_arch_ts;

AUDIT_TRAIL          LAST_ARCHIVE_TS
-------------------- ----------------------------------------
UNIFIED AUDIT TRAIL  04-OCT-21 15.24.32.000000 PM +00:00

Step 9 : Create and schedule the purge jobs PURGE_ALL_AUDIT_TRAILS

BEGIN
  DBMS_AUDIT_MGMT.create_purge_job(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    audit_trail_purge_interval => 24 /* hours */,
    audit_trail_purge_name     => 'PURGE_ALL_AUDIT_TRAILS',
    use_last_arch_timestamp    => TRUE );
END;
/

PL/SQL procedure successfully completed.

SELECT job_action
FROM   dba_scheduler_jobs
WHERE  job_name = 'PURGE_ALL_AUDIT_TRAILS';

JOB_ACTION
--------------------------------------------------------------------------------
BEGIN AUDSYS.DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(15, TRUE, 1);  END;

SQL>

My customer wanted to audit all LOGON (even FAILS LOGIN) and LOGOFF for all users including proxy users.

Create the audit policy LOG_ON_OFF for actions LOGON and LOGOFF and enable it :

conn /as sysdba
CREATE AUDIT POLICY LOG_ON_OFF ACTIONS LOGON,LOGOFF;
AUDIT POLICY LOG_ON_OFF;
SQL> alter session set container=MYPDB;

Session altered.

col user_name format A10
col policy_name format A18
select POLICY_NAME, ENABLED_OPTION, ENTITY_NAME, SUCCESS, FAILURE
from AUDIT_UNIFIED_ENABLED_POLICIES
  3  where POLICY_NAME ='LOG_ON_OFF';

POLICY_NAME		ENABLED_OPTION		ENTITY_NAME		SUCCESS FAILURE
--- ------ ------ ------ ------ ------ ------ ------ ------ ---
LOG_ON_OFF		BY USER				ALL USERS		YES 	YES

 

Let’s simulate a fails connection through a proxy users :

[email protected]:/home/oracle/ [MYCDB (CDB$ROOT)] sqlplus xfelahi[xxxxx]/"******"@server.domain.name/pdb.domain.name

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 5 16:07:37 2021
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Let’s check the Unified Auditing records into the table unified_audit_trail

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Lazhar Felahi
Lazhar Felahi

Consultant