We just finished up the Oracle Database 12c Managing Multitenant Architecture course at ACC last night and while doing the labs we were not getting the same results that Oracle got when it created the material. One of my better students, Robert, did some googling and found some blog posts that seemed to indicate that you needed to be logged in as a user other than SYS if you wanted audit policies to be applied. This didn’t seem to make a lot of sense and I did see some SYS actions that had been audited, so I ended up creating a test case and it looked like I had found a bug! Audit policies were not being enforced even though you had turned them on. Obviously this would sort of defeat the whole purpose of auditing!
The test:
I first shut down all my oracle databases and relinked the oracle binary to enable the unified auditing option:
[oracle@multitenant ~]$ . oraenv
ORACLE_SID = [oracle] ? cdb1
The Oracle base has been set to /u01/app/oracle
[oracle@multitenant ~]$ cd $ORACLE_HOME/rdbms/lib
[oracle@multitenant lib]$ make -f ins_rdbms.mk uniaud_on ioracle
The above generates a whole bunch of output and when finished, you have enabled unified auditing. After restarting my multitenant container database and my listener, I connected to the root container and created a brand new pluggable database to verify my suspicions. I wanted to make sure that I had a completely clean slate when I did the test. Of course I used netca to create a local tns entry for my new pluggable database which I decided to call pdb_uniaud_bug and created the /u01/app/oracle/oradata/cdb1/pdb_uniaud_bug directory to hold my datafiles before I ran the below.
[oracle@multitenant ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 1 10:32:54 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
sys@CDB1> create pluggable database pdb_uniaud_bug admin user uniaud_bug_admin identified by x file_name_convert=('/u01/app/oracle/oradata/cdb1/pdbseed','/u01/app/oracle/oradata/cdb1/pdb_uniaud_bug');
Pluggable database created.
sys@CDB1> alter pluggable database pdb_uniaud_bug open read write;
Pluggable database altered.
sys@CDB1> conn sys/oracle_4U@pdb_uniaud_bug as sysdba
Connected.
sys@PDB_UNIAUD_BUG>
I’ve got my database created, let’s make the testing a bit easier by setting the db_create_file_dest parameter so I can use Oracle Managed Files when I create datafiles. (I’ve decided to create a policy to audit tablespace creation).
sys@PDB_UNIAUD_BUG> alter system set db_create_file_dest = '/u01/app/oracle/oradata/cdb1/pdb_uniaud_bug' scope=both;
System altered.
Let’s create a test user to verify that the bug happens to everybody.
sys@PDB_UNIAUD_BUG> create user test_user identified by x;
User created.
sys@PDB_UNIAUD_BUG> grant connect, create tablespace to test_user;
Grant succeeded.
In a different SQL Plus session, I’ll connect into the database as this test user.
test_user@PDB_UNIAUD_BUG>
With everything set up, let’s begin the test!
sys@PDB_UNIAUD_BUG> create audit policy audi_tablespace actions create tablespace;
Audit policy created.
sys@PDB_UNIAUD_BUG> audit policy audit_tablespace;
Audit succeeded.
sys@PDB_UNIAUD_BUG> select * from audit_unified_enabled_policies where policy_name like '%TABLESP%';
USER_NAME POLICY_NAME ENABLED_ SUCCESS FAILURE
---------- --------------- -------- ------- -------
ALL USERS AUDIT_TABLESPACE BY YES YES
OK, we’ve created the audit policy and then enabled it, and we can see that it is in effect. Let’s audit!
sys@PDB_UNIAUD_BUG> create tablespace existing_connection_sys;
Tablespace created.
sys@PDB_UNIAUD_BUG> select dbusername,action_name, object_name from unified_audit_trail where action_name like '%TABLESP%';
no rows selected
Where are my audit records? Can it be that SYS actions are not logged? Let’s try with our test user.
test_user@PDB_UNIAUD_BUG> create tablespace existing_connection_test_user;
Tablespace created.
Running the same query gave me exactly the same results! No audit records. It appears that there is a bug here! I can’t believe that Oracle let this code out of development. (Is what I was thinking…)
What if I established a new connection to the database? I’m only showing the SYS connection below, I also reconnected as test_user and created another new tablespace.
sys@PDB_UNIAUD_BUG> conn sys/oracle_4U@pdb_uniaud_bug as sysdba
Connected.
sys@PDB_UNIAUD_BUG> create tablespace new_connection_sys;
Tablespace created.
sys@PDB_UNIAUD_BUG> col object_name for a25
sys@PDB_UNIAUD_BUG> select dbusername,action_name, object_name from unified_audit_trail where action_name like '%TABLESP%';
DBUSERNAME ACTION_NAME OBJECT_NAME
------------ -------------------- -------------------------
TEST_USER CREATE TABLESPACE NEW_CONNECTION_TEST_USER
SYS CREATE TABLESPACE NEW_CONNECTION_SYS
There’s the auditing we were expecting!
It turns out that Oracle Unified Auditing policies are NOT applied to users that already have connections to a database. That seems like a bug to me!
Of course I log into Oracle Support and create a service request to get a bugged logged…
Ken from Oracle Support responded with the following:
http://docs.oracle.com/database/121/DBSEG/audit_admin.htm#DBSEG353
Statement and privilege audit options from unified audit policies that are in effect at the time a database user connects to the database remain in effect for the duration of the session. When the session is already active, setting or changing statement or privilege unified audit options does not take effect in that session. The modified statement or privilege audit options take effect only when the current session ends and a new session is created.
In contrast, changes to schema object audit options become immediately effective for current sessions.
In other words, I’d just verified what the documentation already said. To be fair, I’d read Tom Kyte’s Expert Oracle Database Architecture, and I’d read the Oracle 12c Database Concepts Guide, and I’d read and taught Oracle Education’s Oracle Database 12c Managing Multitenant Architecture, AND I’d read through some (but apparently not enough) of the Oracle 12c Security Guide and I hadn’t run across that before.
Cars and Code 
Leave a Reply