Practice: SYSLOG Destination for Common Unified Audit Policies

Overview

This practice shows how to enable all audit records from common unified audit policies to be consolidated into a single destination. The new initialization parameter used for the configuration is supported only on UNIX platforms and NOT available on Windows.

Before starting any new practice, refer to the Practices Environment recommendations.

Step 1 : Create a common user

  • Before configuring the SYSLOG destination for common unified audit policies to be consolidated into a single destination, execute the /home/oracle/labs/M104781GC10/setup_SYSLOG_audit.sh shell script against CDB21. The shell script creates a common user named C##TEST and commonly grants the common user the CREATE SESSION and CREATE TABLE privileges.

    
    $ cd /home/oracle/labs/M104781GC10
    $ /home/oracle/labs/M104781GC10/setup_SYSLOG_audit.sh
    Connected to:
    
    SQL> shutdown abort
    ORACLE instance shut down.
    SQL> exit
    ...
    /usr/bin/ar cr /u01/app/oracle/product/21.0.0/dbhome_1/rdbms/lib/libknlopt.a /u01/app/oracle/product/21.0.0/dbhome_1/rdbms/lib/kzaiang.o
    chmod 755 /u01/app/oracle/product/21.1.0/dbhome_1/bin
    
     - Linking Oracle
    rm -f /u01/app/oracle/product/21.0.0/dbhome_1/rdbms/lib/oracle
    ...
    SQL> STARTUP
    ...
    SQL> CREATE USER c##test IDENTIFIED BY password CONTAINER=ALL;
    
    User created.
    
    SQL> GRANT CREATE SESSION, CREATE TABLE, UNLIMITED TABLESPACE TO c##test CONTAINER=ALL;
    
    Grant succeeded.
    
    SQL> EXIT
    $

Step 2 : Create a common and local audit policy

  • Create the common audit policy at the CDB root in CDB21.

    
    $ sqlplus / AS SYSDBA
    
    Connected.
    
    SQL> CREATE AUDIT POLICY pol_common ACTIONS create table CONTAINER=ALL;
    
    Audit policy created.
    
    SQL> AUDIT POLICY pol_common;
    
    Audit succeeded.
    
    SQL> CREATE AUDIT POLICY pol_root ACTIONS insert;
    
    Audit policy created.
    
    SQL> AUDIT POLICY pol_root;
    
    Audit succeeded.
    
    SQL> COL policy_name FORMAT A18
    SQL> COL audit_option FORMAT A18
    SQL> SELECT policy_name, audit_option, common
          FROM AUDIT_UNIFIED_POLICIES  
          WHERE policy_name like 'POL%'; 
    
    POLICY_NAME        AUDIT_OPTION       COM
    ------------------ ------------------ ---
    POL_COMMON         CREATE TABLE       YES
    POL_ROOT           INSERT             NO
    
    SQL>
  • Create the local audit policy at the PDB level in PDB21.

    
    SQL> CONNECT system@PDB21
    Enter password:
    Connected.
    SQL> CREATE AUDIT POLICY pol_pdb21 ACTIONS select;
    
    Audit policy created.
    
    SQL> AUDIT POLICY pol_pdb21;
    
    Audit succeeded.
    
    SQL>
  • Display the policy names, their actions, and commonality.

    
    SQL> COL policy_name FORMAT A18
    SQL> COL audit_option FORMAT A18
    SQL> SELECT policy_name, audit_option, common
          FROM AUDIT_UNIFIED_POLICIES  
          WHERE policy_name like 'POL%'; 
    
    POLICY_NAME        AUDIT_OPTION       COM
    ------------------ ------------------ ---
    POL_COMMON         CREATE TABLE       YES
    POL_PDB21          SELECT             NO
    
    SQL>

Step 3 : Configure the SYSLOG destination for common and local audit policies

  • Configure the SYSLOG destination for common unified audit policies to be consolidated into a single destination. The facility_clause refers to the facility to which you will write the audit trail records. Valid choices are USER and LOCAL. If you enter LOCAL, then optionally append 0–7 to designate a local custom facility for the SYSLOG records. priority_clause refers to the type of warning in which to categorize the record. Valid choices are NOTICE, INFO, DEBUG, WARNING, ERR, CRIT , ALERT, and EMERG.

    
    SQL> CONNECT / AS SYSDBA
    Connected.
    SQL> ALTER SYSTEM SET UNIFIED_AUDIT_COMMON_SYSTEMLOG='local0.info' SCOPE=SPFILE;
    
    System altered.
    
    SQL>
  • Configure the SYSLOG destination for local unified audit policies to be consolidated into a single destination.

    
    SQL> CONNECT sys@PDB21 AS SYSDBA
    Enter password:
    Connected.
    SQL> ALTER SYSTEM SET UNIFIED_AUDIT_COMMON_SYSTEMLOG='local1.warning' SCOPE=SPFILE;
    ALTER SYSTEM SET UNIFIED_AUDIT_COMMON_SYSTEMLOG='local1.warning'  SCOPE=SPFILE
    *
    ERROR at line 1:
    ORA-65040: operation not allowed from within a pluggable database
    
    SQL> CONNECT / AS SYSDBA
    Connected.
    SQL> ALTER SYSTEM SET UNIFIED_AUDIT_SYSTEMLOG='local1.warning' SCOPE=SPFILE;
    
    System altered.
    
    SQL> 

    Observe that UNIFIED_AUDIT_COMMON_SYSTEMLOG is a CDB-level initialization parameter.

  • Restart the database instance because UNIFIED_AUDIT_COMMON_SYSTEMLOG has been set at the SPFILE scope.

    
    SQL> SHUTDOWN IMMEDIATE
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> STARTUP
    ORACLE instance started.
    
    Total System Global Area  851440288 bytes
    Fixed Size                  9691808 bytes
    Variable Size             599785472 bytes
    Database Buffers          222298112 bytes
    Redo Buffers               19664896 bytes
    Database mounted.
    Database opened.
    SQL>
    
    SQL> ALTER PLUGGABLE DATABASE pdb21 OPEN;
    
    Pluggable database altered.
    
    SQL> 

Step 4 : Define the OS directories for the SYSLOG files

  • Before audited actions are recorded by the SYSLOG system, define the OS directories for the SYSLOG files to store the audited records. Open another terminal session as root.

    
    $ exit
    logout
    $ sudo su -
    Last login: Tue Dec 15 00:00:07 UTC 2020
    #
  • Edit the /etc/rsyslog.conf configuration file and under the RULES section, add as many lines as different values defined in the CDB for SYSTEMLOG to specify related OS directories.

    
    # vi /etc/rsyslog.conf
    ...
    #### RULES ####
    ...
    # Save boot messages also to boot.log
    local7.*                                                /var/log/boot.log
    
    # Unified Audit Rules
    local0.info            /var/log/root_common_audit_records.log
    local1.warning         /var/log/root_audit_records.log
    ...
    # 
  • Restart the SYSLOG daemon.

    
    # cd /etc/init.d
    # service rsyslog restart
    Redirecting to /bin/systemctl restart  rsyslog.service
    ...
    # 

Step 5 : Test

  • In the oracle UNIX session, log on as C##TEST to the CDB root and perform a CREATE TABLE operation followed by an INSERT operation on the table.

    
    SQL> CONNECT c##test
    Enter password:
    SQL> ALTER SESSION SET default_sharing = 'EXTENDED DATA';
    
    Session altered.
    
    SQL> CREATE TABLE test (id NUMBER, label VARCHAR2(10));
    
    Table created.
    
    SQL> INSERT INTO test VALUES (1,'A');
    
    1 row created.
    
    SQL> INSERT INTO test VALUES (2,'B');
    
    1 row created.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL>
  • Back in the root UNIX session, check that a syslog entry is created in the /var/log/root_common_audit_records.log file because an audit record for CREATE TABLE was generated because of the POL_COMMON common audit policy.

    
    # grep -i  'Oracle Unified Audit'  /var/log/root_common_audit_records.log
    Nov 13 15:52:56 db21si journal: Oracle Unified Audit[23128]: LENGTH: '215' TYPE:"4" DBID:"2809789491" SESID:"2759083216" CLIENTID:"" ENTRYID:"1" STMTID:"9" DBUSER:"C##TEST" CURUSER:"C##TEST" ACTION:"1" RETCODE:"0" SCHEMA:"C##TEST" OBJNAME:"TEST" PDB_GUID:"B3316DF8AB281563E053E704F40AD8A9"
    #

    If you query AUDIT_ACTIONS, you observe that the value in ACTION number 1 is CREATE TABLE.

    If you query V$CONTAINERS, you observe that the GUID value corresponds to the CDB root.

    The single entry corresponds to the CREATE TABLE action audited commonly because the POL_COMMON audit policy audits all CREATE TABLE statements in all containers. The INSERT action (ACTION number 2 in AUDIT_ACTIONS view) is not recorded in this log file because the audit policy that audits INSERT statements, POL_ROOT, is enabled only locally in the CDB root.

  • Check that syslog entries are created in /var/log/root_audit_records.log file because audit records for INSERT were generated because of the POL_ROOT local root audit policy.

    
    # grep -i  'Oracle Unified Audit' /var/log/root_audit_records.log | grep TEST
    Nov 13 15:52:56 db21si journal: Oracle Unified Audit[23128]: LENGTH: '215' TYPE:"4" DBID:"2809789491" SESID:"2759083216" CLIENTID:"" ENTRYD:"1" STMTID:"9" DBUSER:"C##TEST" CURUSER:"C##TEST" ACTION:"1" RETCODE:"0" SCHEMA:"C##TEST" OBJNAME:"TEST" PDB_GUID:"B3316DF8AB281563E053E04F40AD8A9"
    Nov 13 15:53:02 db21si journal: Oracle Unified Audit[23128]: LENGTH: '216' TYPE:"4" DBID:"2809789491" SESID:"2759083216" CLIENTID:"" ENTRYD:"2" STMTID:"10" DBUSER:"C##TEST" CURUSER:"C##TEST" ACTION:"2" RETCODE:"0" SCHEMA:"C##TEST" OBJNAME:"TEST" PDB_GUID:"B3316DF8AB281563E053704F40AD8A9"
    Nov 13 15:53:05 db21si journal: Oracle Unified Audit[23128]: LENGTH: '216' TYPE:"4" DBID:"2809789491" SESID:"2759083216" CLIENTID:"" ENTRYD:"3" STMTID:"11" DBUSER:"C##TEST" CURUSER:"C##TEST" ACTION:"2" RETCODE:"0" SCHEMA:"C##TEST" OBJNAME:"TEST" PDB_GUID:"B3316DF8AB281563E053704F40AD8A9"
    #

    The first entry corresponds to the CREATE TABLE action audited commonly and also locally in the CDB root. The second and third entries correspond to the two INSERT actions recorded in this log file because the POL_ROOT audit policy that audits INSERT statements is enabled locally in the CDB root.

    If you query V$CONTAINERS, you observe that the GUID value corresponds to PDB21.

  • Back in the oracle UNIX session, log on as C##TEST to PDB21 and perform a CREATE TABLE operation, followed by an INSERT operation on the table.

    
    SQL> CONNECT c##test@PDB21
    Enter password:
    Connected.
    SQL> CREATE TABLE testpdb21 (id NUMBER, label VARCHAR2(10));
    
    Table created.
    
    SQL> INSERT INTO testpdb21 VALUES (1,'A');
    
    1 row created.
    
    SQL> INSERT INTO testpdb21 VALUES (2,'B');
    
    1 row created.
    
    SQL> COMMIT;
    
    Commit complete. 
    
    SQL> EXIT
    
    $
  • Back in the root UNIX session, check whether a syslog entry is created in the /var/log/root_common_audit_records.log file.

    
    # grep -i  'Oracle Unified Audit'  /var/log/root_common_audit_records.log
    Nov 13 15:52:56 db21si journal: Oracle Unified Audit[23128]: LENGTH: '215' TYPE:"4" DBID:"2809789491" SESID:"2759083216" CLIENTID:"" ENTRYID:"1" STMTID:"9" DBUSER:"C##TEST" CURUSER:"C##TEST" ACTION:"1" RETCODE:"0" SCHEMA:"C##TEST" OBJNAME:"TEST" PDB_GUID:"B3316DF8AB281563E053E704F40AD8A9"
    Nov 13 16:01:47 db21si journal: Oracle Unified Audit[51696]: LENGTH: '219' TYPE:"4" DBID:"3207694222" SESID:"502695322" CLIENTID:"" ENTRYID:"6" STMTID:"7" DBUSER:"C##TEST" CURUSER:"C##TEST" ACTION:"1" RETCODE:"0" SCHEMA:"C##TEST" OBJNAME:"TESTPDB21" PDB_GUID:"B3C43A538FDE55BEE0537167606449A3"
    #

    The second entry corresponds to the CREATE TABLE action audited commonly because the POL_COMMON common audit policy audits all CREATE TABLE statements in all containers and also in PDB21. No INSERT action is recorded in this log file because the POL_ROOT audit policy that audits INSERT statements, is created only locally in the CDB root and not commonly in all containers.

  • Check whether syslog entries are created in the /var/log/root_audit_records.log file.

    
    # grep -i  'Oracle Unified Audit'  /var/log/root_audit_records.log | grep TEST
    Nov 13 15:52:56 db21si journal: Oracle Unified Audit[23128]: LENGTH: '215' TYPE:"4" DBID:"2809789491" SESID:"2759083216" CLIENTID:"" ENTRYID:"1" STMTID:"9" DBUSER:"C##TEST" CURUSER:"C##TEST" ACTION:"1" RETCODE:"0" SCHEMA:"C##TEST" OBJNAME:"TEST" PDB_GUID:"B3316DF8AB281563E053E704F40AD8A9"
    Nov 13 15:53:02 db21si journal: Oracle Unified Audit[23128]: LENGTH: '216' TYPE:"4" DBID:"2809789491" SESID:"2759083216" CLIENTID:"" ENTRYID:"2" STMTID:"10" DBUSER:"C##TEST" CURUSER:"C##TEST" ACTION:"2" RETCODE:"0" SCHEMA:"C##TEST" OBJNAME:"TEST" PDB_GUID:"B3316DF8AB281563E053E704F40AD8A9"
    Nov 13 15:53:05 db21si journal: Oracle Unified Audit[23128]: LENGTH: '216' TYPE:"4" DBID:"2809789491" SESID:"2759083216" CLIENTID:"" ENTRYID:"3" STMTID:"11" DBUSER:"C##TEST" CURUSER:"C##TEST" ACTION:"2" RETCODE:"0" SCHEMA:"C##TEST" OBJNAME:"TEST" PDB_GUID:"B3316DF8AB281563E053E704F40AD8A9"
    # exit
    logout
    $ 

    Although a local audit policy, POL_PDB21 in PDB21, audits INSERT actions, no audit record is written in the SYSLOG file because SYSLOG records only actions executed at the CDB level.

Step 6: Cleanup

  • Back in the oracle UNIX session, execute the /home/oracle/labs/M104781GC10/cleanup.sh shell script to reset the SYSLOG destinations for both common and local unified audit policies and drop the policies in the CDB root and PDB21.

    
    $ /home/oracle/labs/M104781GC10/cleanup.sh
    ...
    SQL> ALTER SYSTEM SET UNIFIED_AUDIT_COMMON_SYSTEMLOG='' SCOPE=SPFILE;
    
    System altered.
    
    SQL> ALTER SYSTEM SET UNIFIED_AUDIT_SYSTEMLOG='' SCOPE=SPFILE;
    
    System altered.
    
    SQL> noaudit POLICY pol_common;
    
    Noaudit succeeded.
    
    SQL> drop AUDIT POLICY pol_common;
    
    Audit Policy dropped.
    
    SQL> exit
    ...
    SQL> shutdown abort
    ORACLE instance shut down.
    SQL> exit
    ...
    Connected to an idle instance.
    
    SQL> STARTUP
    ORACLE instance started.
    
    Total System Global Area  851440088 bytes
    Fixed Size                  9691608 bytes
    Variable Size             570425344 bytes
    Database Buffers          134217728 bytes
    Redo Buffers               19664896 bytes
    In-Memory Area            117440512 bytes
    Database mounted.
    Database opened.
    SQL> ALTER PLUGGABLE DATABASE all OPEN;
    
    Pluggable database altered.
    
    SQL> exit
    ...
    SQL> noAUDIT POLICY pol_pdb21;
    
    Noaudit succeeded.
    
    SQL> drop AUDIT POLICY pol_pdb21;
    
    Audit Policy dropped.
    
    SQL> EXIT
    $