unauditable select

 

Source: Natalie Berger

 Oracle Unified Audit is a great and flexible tool. 
Unfortunately I found a little gap where a SELECT is not audited. 
It is an edge-case: a common use in a CDB selects the object via the CONTAINERS() syntax.

Please follow this example to see the details. 

As I'm lazy, I used a lot of code from Tims example. 

First the preparations:
SYSTEM@CDB

CREATE USER c##common_user IDENTIFIED BY Common1 QUOTA UNLIMITED ON users;

GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SYNONYM TO c##common_user CONTAINER=ALL;

SYSTEM@PDB

CREATE USER local_user IDENTIFIED BY Local1 QUOTA UNLIMITED ON users;

GRANT CREATE SESSION, CREATE TABLE TO local_user;

CREATE TABLE local_user.local_user_tab AS
    SELECT level AS ID
    FROM   dual
    CONNECT BY level <= 2; 

GRANT SELECT, INSERT, UPDATE, DELETE ON local_user.local_user_tab TO c##common_user;

Now let's enable & test an audit policy:
SYSTEM@PDB1

Create Audit Policy local_user_select
        ACTIONS
          ALL on local_user.local_user_tab
    ;

select * from local_user.local_user_tab;
  ID
   1
   2
   
select event_timestamp, action_name, object_name, sql_text,  system_privilege_used
    from unified_audit_trail u
    where UNIFIED_AUDIT_POLICIES='LOCAL_USER_SELECT'
    order by u.event_timestamp desc;   

EVENT_TIMESTAMP                   DBUSERNAME       ACTION_NAME   OBJECT_NAME      SQL_TEXT                                    SYSTEM_PRIVILEGE_USED
19-JUN-20 09.36.35.912206000 AM   SYSTEM           SELECT        LOCAL_USER_TAB   select * from LOCAL_USER.LOCAL_USER_TAB     SELECT ANY TABLE

Let's continue the setup:
C##COMMON_USER@CDB

CREATE TABLE c##common_user.local_user_tab_v (id NUMBER);

C##COMMON_USER@PDB1

CREATE VIEW c##common_user.local_user_tab_v AS
    SELECT * FROM local_user.local_user_tab;
    
select * from local_user_tab_v;
  ID
   1
   2    

SYSTEM@PDB1

/

EVENT_TIMESTAMP                   DBUSERNAME       ACTION_NAME   OBJECT_NAME      SQL_TEXT                                    SYSTEM_PRIVILEGE_USED
19-JUN-20 09.42.44.379274000 AM   C##COMMON_USER   SELECT        LOCAL_USER_TAB   select * from local_user_tab_v                                                   
19-JUN-20 09.42.33.786949000 AM   C##COMMON_USER   SELECT        LOCAL_USER_TAB   CREATE VIEW c##common_user.local_use  ...   CREATE VIEW
19-JUN-20 09.36.35.912206000 AM   SYSTEM           SELECT        LOCAL_USER_TAB   select * from LOCAL_USER.LOCAL_USER_TAB     SELECT ANY TABLE

Everything fine so far.
But the following statement is not audited:
C##COMMON_USER@CDB

SELECT con_id, id
    FROM   CONTAINERS(local_user_tab_v)
    ORDER BY con_id, id;

CON_ID   ID
       3    1
       3    2

But there is nothing in the audit trail on PDB1:
SYSTEM@PDB1

/

EVENT_TIMESTAMP                   DBUSERNAME       ACTION_NAME   OBJECT_NAME      SQL_TEXT                                    SYSTEM_PRIVILEGE_USED
19-JUN-20 09.42.44.379274000 AM   C##COMMON_USER   SELECT        LOCAL_USER_TAB   select * from local_user_tab_v                                                   
19-JUN-20 09.42.33.786949000 AM   C##COMMON_USER   SELECT        LOCAL_USER_TAB   CREATE VIEW c##common_user.local_use  ...   CREATE VIEW
19-JUN-20 09.36.35.912206000 AM   SYSTEM           SELECT        LOCAL_USER_TAB   select * from LOCAL_USER.LOCAL_USER_TAB     SELECT ANY TABLE

I can't enable a proper audit in the CDB, as the object does not exist in ALL containers:
SYSTEM@CDB

Create Audit Policy local_user_select
            ACTIONS
              ALL on local_user.local_user_tab
    Container=ALL;

          ALL on local_user.local_user_tab
                            *
ERROR at line 3:
ORA-00942: table or view does not exist

Here it's visible, there is a SELECT which is not audited properly.

---

But it's not totally invisible to Oracle. Performing privilege analysis in the PDB shows the activity:
After a quick sequence

 DBMS_PRIVILEGE_CAPTURE.enable_capture (on PDB) - SELECT (C##COMMON_USER@CDB) - DBMS_PRIVILEGE_CAPTURE.disable_capture & DBMS_PRIVILEGE_CAPTURE.generate_result (on PDB), 

at least these results are available:

select * from DBA_USED_OBJPRIVS       where capture not in ('ORA$DEPENDENCY') ;
CAPTURE   SEQUENCE   OS_USER   USERHOST   MODULE                      USERNAME         USED_ROLE        OBJ_PRIV   OBJECT_OWNER   OBJECT_NAME     OBJECT_TYPE  
db_pol          1    oracle    localhost  java@localhost (TNS V1-V3)  C##COMMON_USER   C##COMMON_USER   SELECT     LOCAL_USER     LOCAL_USER_TAB  TABLE            

If you have any idea how to audit this select properly, please leave a comment.