Audit pluggable database

In the old now-deprecated maybe-soon-desupported non-cdb infrastructure, AUDIT’ing was done right after connect / as sysdba.

In single-tenant (or multi-tenant), things get complicated.

Once again, the doc must be read at least twice 😉

If you issue an audit statement in the root, then the database performs auditing across the entire CDB, that is, in the root and all PDBs […] all common users are audited

This is very tricky, because you don’t want to audit common users only

Let’s try

SQL> conn / as sysdba
Connected.
SQL> create user c##u identified by ***;
User created.
SQL> grant create session to c##u container=all;
Grant succeeded.
SQL> alter session set container=pdb01;
Session altered.
SQL> create user u identified by ***;
User created.
SQL> grant create session to u;
Grant succeeded.
SQL> alter session set container=cdb$root;
Session altered.
SQL> audit connect container=all;
Audit succeeded.
SQL> select AUDIT_OPTION, CON_ID from cdb_STMT_AUDIT_OPTS;
AUDIT_OPTION   CON_ID
-------------- ------
CREATE SESSION      1
SQL> sho parameter audit_trail
NAME         VALUE
------------ -------------
audit_trail  DB, EXTENDED
SQL> 

Audit is now logging all connections of all common users on all databases…

SQL> conn c##u/***
Connected.
SQL> sho user
USER is "C##U"
SQL> sho con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> conn c##u/***@pdb01
Connected.
SQL> sho user
USER is "C##U"
SQL> sho con_name
CON_NAME
------------------------------
pdb01

Let’s verify :

SQL> conn / as sysdba
Connected.
SQL> select * from cdb_audit_trail order by timestamp;

CON_ID USERNAME ACTION TIMESTAM
------ -------- ------ --------
     1 C##U     LOGON  18:01:05
     1 C##U     LOGOFF 18:01:06
     3 C##U     LOGON  18:01:07
     3 C##U     LOGOFF 18:01:08

So far so good. What about local users?

SQL> conn u/***@pdb01
Connected.
SQL> sho user
USER is "U"
SQL> sho con_name
CON_NAME
------------------------------
pdb01

And???

SQL> conn / as sysdba
Connected.
SQL> select * from cdb_audit_trail where USERNAME='U';
no rows selected

Nope! DBA like me and you don’t care about those C## users, we want ALL users, not all common users.

For this purpose, we need to activate audit on every pluggable.

SQL> conn / as sysdba
Connected.
SQL> alter session set container=pdb01;
Session altered.
SQL> audit connect;
Audit succeeded.
SQL> alter session set container=cdb$root;
Session altered.
SQL> select AUDIT_OPTION, CON_ID from cdb_STMT_AUDIT_OPTS;
AUDIT_OPTION   CON_ID
-------------- ------
CREATE SESSION      1
CREATE SESSION      3

Now it should work

SQL> conn u/***@pdb01
Connected.
SQL> sho user
USER is "U"
SQL> sho con_name
CON_NAME
------------------------------
pdb01
SQL> host sleep 1

… and …

SQL> select * from cdb_audit_trail where username='U';
CON_ID USERNAME ACTION TIMESTAM
------ -------- ------ --------
     3 U        LOGON  18:01:12
     3 U        LOGOFF 18:01:13

If you already switched from non-cdb to single-tenant, please check your audit strategy NOW !!!