AUDIT Statement of Traditional Auditing in CDB$ROOT Container Cannot Audit Local User of PDBs

(Doc ID 2392448.1)

Last updated on MAY 08, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Information in this document applies to any platform.

Symptoms

Customer executes AUDIT statement of traditional auditing in CDB$ROOT container without auditing_by_clause, and proposes to audit all alter system actions both common user and local user in PDBs, but it does not work for local user in PDBs, while it works well for common user.

For example:

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBBT1                         READ WRITE NO
         4 PDBBT2                         READ WRITE NO
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show user
USER is "SYS"
SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB
SQL> audit alter system by access container=all;

Audit succeeded.

SQL> conn aa/aa@pdbbt1
Connected.
SQL> select USERNAME,TIMESTAMP,ACTION_NAME from DBA_AUDIT_TRAIL;

no rows selected

SQL> alter system set OPTIMIZER_DYNAMIC_SAMPLING=2;

System altered.

SQL> select USERNAME,TIMESTAMP,ACTION_NAME from DBA_AUDIT_TRAIL;

no rows selected             *<<<<<<---------- ALTER SYSTEM action of local user cannot be audited.

SQL> conn c##tt/tt@pdbbt1
Connected.
SQL> select USERNAME,TIMESTAMP,ACTION_NAME from DBA_AUDIT_TRAIL;

no rows selected

SQL> alter system set OPTIMIZER_DYNAMIC_SAMPLING=2;

System altered.

SQL> select USERNAME,TIMESTAMP,ACTION_NAME from DBA_AUDIT_TRAIL;

USERNAME   TIMESTAMP ACTION_NAME
---------- --------- ----------------------------
C##TT      28-APR-18 ALTER SYSTEM     *<<<<<<---------- ALTER SYSTEM action of common user is audited.

SQL>

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms