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 !!!