The main new feature of Oracle 12cR1 has to be the multitennant architecture that allows tennant databases to be added or plugged into a container database. I am interested in the security of this of course and one element that permeates the whole container database architecture is the use of local and common user accounts and local and common privileges. We can create common accounts and these are identified by columns on various views including DBA_USERS:
SQL> @print 'select username,common,oracle_maintained from dba_users where username=''''C##ORASCAN'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select username,common,oracle_maintained from dba_users where username=''C##ORASCAN''','''','''''');
Executing Query [select username,common,oracle_maintained from dba_users where
username='C##ORASCAN']
USERNAME : C##ORASCAN
COMMON : YES
ORACLE_MAINTAINED : N
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
|
As you can see my container database has a common user called C##ORASCAN; the C## part of the name is demanded by Oracle in its documentation and is enforced in the database. All common users that you create must start C## and also any local user or indeed any user (as you cannot create common users in a plug database from the plug database) cannot start C##. But, you may ask then how come Oracle can create its users with names it chooses such as SYS, SYSTEM, DBSNMP etc. Are Oracles created users special, is there a maintained list somewhere that says its an Oracle user and not one we created? There is a column on DBA_USERS and other views that is called ORACLE_MAINTAINED that shows Oracle installed or created accounts. But what if we want to control the names of accounts.
OK, so the rules say we cannot create a common user without C##, lets try:
SQL> create user mycommon identified by mycommon container=all;
create user mycommon identified by mycommon container=all
*
ERROR at line 1:
ORA-65096: invalid common user or role name
SQL>
|
So it fails, the rules are met. So lets try again:
SQL> alter session set "_ORACLE_SCRIPT"=true;
Session altered.
SQL> create user mycommon identified by mycommon container=all;
User created.
SQL> alter session set "_ORACLE_SCRIPT"=false;
Session altered.
SQL>
|
So it seems to have worked; I was able to create a common user with a name i defined; i.e. not defined by Oracle. Check the user exists and its settings:
SQL> @print 'select username,common,oracle_maintained from dba_users where username=''''MYCOMMON'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select username,common,oracle_maintained from dba_users where username=''MYCOMMON''','''','''''');
Executing Query [select username,common,oracle_maintained from dba_users where
username='MYCOMMON']
USERNAME : MYCOMMON
COMMON : YES
ORACLE_MAINTAINED : Y
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
|
It is there and it is a COMMON account and also it states it is ORACLE_MAINTAINED because we tricked the database to think its an Oracle account
Is it visible in the plug database (first show its the plug by the connect string):
SQL> connect sys/oracle1@//192.168.1.86:1521/pdborcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> select * from dba_users where username like '%MY%';
no rows selected
SQL>
|
The user is not there in the plug. Hmmmm, it says its a common user in the root container, see the output above but it isn't as it does not exist in the plug. Lets create a second account MYSECOND without the container clause:
SQL> connect sys/oracle1@//192.168.1.86:1521/orcl.localdomain as sysdba
Connected.
SQL> alter session set "_ORACLE_SCRIPT"=true;
Session altered.
SQL> create user mysecond identified by mysecond;
User created.
SQL> alter session set "_ORACLE_SCRIPT"=false;
Session altered.
SQL> @print 'select * from dba_users where username=''''MYSECOND'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from dba_users where username=''MYSECOND''','''','''''');
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> @print 'select * from dba_users where username=''''MYSECOND'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from dba_users where username=''MYSECOND''','''','''''');
Executing Query [select * from dba_users where username='MYSECOND']
USERNAME : MYSECOND
USER_ID : 110
PASSWORD :
ACCOUNT_STATUS : OPEN
LOCK_DATE :
EXPIRY_DATE : 01-JAN-14
DEFAULT_TABLESPACE : USERS
TEMPORARY_TABLESPACE : TEMP
CREATED : 05-JUL-13
PROFILE : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP : DEFAULT_CONSUMER_GROUP
EXTERNAL_NAME :
PASSWORD_VERSIONS : 10G 11G
EDITIONS_ENABLED : N
AUTHENTICATION_TYPE : PASSWORD
PROXY_ONLY_CONNECT : N
COMMON : YES
LAST_LOGIN :
ORACLE_MAINTAINED : Y
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
|
So the user is created and its again a COMMON user and also ORACLE_MAINTAINED. Has this created the COMMON user in the plug:
SQL> connect sys/oracle1@//192.168.1.86:1521/pdborcl.localdomain as sysdba
Connected.
SQL>
SQL> set serveroutput on
SQL> @print 'select * from dba_users where username=''''MYSECOND'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from dba_users where username=''MYSECOND''','''','''''');
Executing Query [select * from dba_users where username='MYSECOND']
PL/SQL procedure successfully completed.
SQL>
|
Nope, it still has not worked, it is not a real common account. So we have a common user in the root container and its name doesnt start with C##. What about if we do it properly. We can create a common user starting c## and it should appear in the plug database also.
SQL> connect sys/oracle1@//192.168.1.86:1521/orcl.localdomain as sysdba
Connected.
SQL> create user c##mycommon identified by c##mycommon;
User created.
SQL> set serveroutput on
SQL> @print 'select * from dba_users where username=''''C##MYCOMMON'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from dba_users where username=''C##MYCOMMON''','''','''''');
Executing Query [select * from dba_users where username='C##MYCOMMON']
USERNAME : C##MYCOMMON
USER_ID : 111
PASSWORD :
ACCOUNT_STATUS : OPEN
LOCK_DATE :
EXPIRY_DATE : 01-JAN-14
DEFAULT_TABLESPACE : USERS
TEMPORARY_TABLESPACE : TEMP
CREATED : 05-JUL-13
PROFILE : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP : DEFAULT_CONSUMER_GROUP
EXTERNAL_NAME :
PASSWORD_VERSIONS : 10G 11G
EDITIONS_ENABLED : N
AUTHENTICATION_TYPE : PASSWORD
PROXY_ONLY_CONNECT : N
COMMON : YES
LAST_LOGIN :
ORACLE_MAINTAINED : N
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
|
So we have a COMMON user, not ORACLE_MAINTAINED and does it exist in the plug?
SQL> connect sys/oracle1@//192.168.1.86:1521/pdborcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @print 'select * from dba_users where username=''''C##MYCOMMON'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from dba_users where username=''C##MYCOMMON''','''','''''');
Executing Query [select * from dba_users where username='C##MYCOMMON']
USERNAME : C##MYCOMMON
USER_ID : 114
PASSWORD :
ACCOUNT_STATUS : OPEN
LOCK_DATE :
EXPIRY_DATE : 01-JAN-14
DEFAULT_TABLESPACE : USERS
TEMPORARY_TABLESPACE : TEMP
CREATED : 05-JUL-13
PROFILE : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP : DEFAULT_CONSUMER_GROUP
EXTERNAL_NAME :
PASSWORD_VERSIONS : 10G 11G
EDITIONS_ENABLED : N
AUTHENTICATION_TYPE : PASSWORD
PROXY_ONLY_CONNECT : N
COMMON : YES
LAST_LOGIN :
ORACLE_MAINTAINED : N
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
|
Yes, so it works correctly when we use the correct name for a common user. What about if we change the common users password for the account that didn't have C## in its name:
SQL> alter user mycommon identified by newpwd;
alter user mycommon identified by newpwd
*
ERROR at line 1:
ORA-65048: error encountered when processing the current DDL statement in
pluggable database PDBORCL
ORA-01918: user 'MYCOMMON' does not exist
SQL>
|
Hmmm, this does not look good but is obvious really as the common user does not exist in the pluggable database therefore we cannot change its password. The error also gives a clue to how this all works; the ORA-01918 is from the plug and is a normal error, the DDL executed in the root is obviously grabbed by a trigger/DV/OLS/VPD policy and run against the plug from the container.
Interesting; what if we now create a local user in the plug database with the same name as the common user in the root container:
SQL> connect sys/oracle1@//192.168.1.86:1521/pdborcl.localdomain as sysdba
Connected.
SQL> create user mycommon identified by mycommon;
User created.
SQL> set serveroutput on
SQL> @print 'select * from dba_users where username=''''MYCOMMON'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from dba_users where username=''MYCOMMON''','''','''''');
Executing Query [select * from dba_users where username='MYCOMMON']
USERNAME : MYCOMMON
USER_ID : 115
PASSWORD :
ACCOUNT_STATUS : OPEN
LOCK_DATE :
EXPIRY_DATE : 01-JAN-14
DEFAULT_TABLESPACE : USERS
TEMPORARY_TABLESPACE : TEMP
CREATED : 05-JUL-13
PROFILE : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP : DEFAULT_CONSUMER_GROUP
EXTERNAL_NAME :
PASSWORD_VERSIONS : 10G 11G
EDITIONS_ENABLED : N
AUTHENTICATION_TYPE : PASSWORD
PROXY_ONLY_CONNECT : N
COMMON : NO
LAST_LOGIN :
ORACLE_MAINTAINED : N
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
|
So it works; we now have a user MYCOMMON in the root, marked as COMMON but it doesnt exist in the plug (as a common user, it exists as a local user) and we obviously have the same user in the plug but its local not common. What happens when we now change the password for MYCOMMON in the root container.
SQL> connect sys/oracle1@//192.168.1.86:1521/orcl.localdomain as sysdba
Connected.
SQL> alter user mycommon identified by newpwd;
User altered.
SQL>
|
I am in the root container and have changed the common user in root:
SQL> set serveroutput on
SQL> @print 'select password,spare4 from sys.user$ where name=''''MYCOMMON'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select password,spare4 from sys.user$ where name=''MYCOMMON''','''','''''');
Executing Query [select password,spare4 from sys.user$ where name='MYCOMMON']
PASSWORD : ADD0BB1368A68A28
SPARE4 :
S:9019FACA912F1FBB19CD0456BD65BE9D744F7A208B1BAE6CF7F8CEDEEC9C;H:1D7C0286938CB26
4DCB54C660FD94564
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
|
In the plug container:
SQL> connect sys/oracle1@//192.168.1.86:1521/pdborcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @print 'select password,spare4 from sys.user$ where name=''''MYCOMMON'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select password,spare4 from sys.user$ where name=''MYCOMMON''','''','''''');
Executing Query [select password,spare4 from sys.user$ where name='MYCOMMON']
PASSWORD : ADD0BB1368A68A28
SPARE4 :
S:68FBFE1C81A6D6EAF9E0B56F1A371CF53B8B44511E90EED661902D0339CE;H:5F847339D2FEA92
6BC1F172E25CCF351
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
|
It has the same 10g password in both the root and the plug container. The MYCOMMON user is still obviously not a common user in the plug but i changed its password from the root container; so I was able to change a local users password in a pluggable container from the root container when i should not be able to do that:
SQL> @print 'select * from dba_users where username=''''MYCOMMON'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from dba_users where username=''MYCOMMON''','''','''''');
Executing Query [select * from dba_users where username='MYCOMMON']
USERNAME : MYCOMMON
USER_ID : 115
PASSWORD :
ACCOUNT_STATUS : OPEN
LOCK_DATE :
EXPIRY_DATE : 01-JAN-14
DEFAULT_TABLESPACE : USERS
TEMPORARY_TABLESPACE : TEMP
CREATED : 05-JUL-13
PROFILE : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP : DEFAULT_CONSUMER_GROUP
EXTERNAL_NAME :
PASSWORD_VERSIONS : 10G 11G
EDITIONS_ENABLED : N
AUTHENTICATION_TYPE : PASSWORD
PROXY_ONLY_CONNECT : N
COMMON : NO
LAST_LOGIN :
ORACLE_MAINTAINED : N
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
|
If we are logged into the plug and try and change a common users password we get this:
SQL> @conn
ID
--------------------------------------------------------------------------------
NAME
------------------------------
3
PDBORCL
SQL> alter user c##orascan identified by newpwd;
alter user c##orascan identified by newpwd
*
ERROR at line 1:
ORA-65066: The specified changes must apply to all containers
SQL> alter user c##orascan identified by newpwd container=all;
alter user c##orascan identified by newpwd container=all
*
ERROR at line 1:
ORA-65050: Common DDLs only allowed in CDB$ROOT
SQL>
|
But what happens with MYCOMMON:
SQL> alter user mycommon identified by x;
User altered.
SQL>
|
If we compare the hashes:
SQL> @print 'select name,password,spare4 from sys.user$ where name=''''MYCOMMON'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select name,password,spare4 from sys.user$ where name=''MYCOMMON''','''','''''');
Executing Query [select name,password,spare4 from sys.user$ where
name='MYCOMMON']
NAME : MYCOMMON
PASSWORD : 7A9DD10633C8ED6B
SPARE4 :
S:B447EF343C518C6060F15BF8E5CCA4071DD3AED27AEAA7A85395784E6627;H:62597AA76948E25
907B5B99D8CF13206
-------------------------------------------
PL/SQL procedure successfully completed.
SQL> @conn
ID
--------------------------------------------------------------------------------
NAME
------------------------------
3
PDBORCL
SQL>
|
And in the root container:
SQL> connect sys/oracle1@//192.168.1.86:1521/orcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> @print 'select name,password,spare4 from sys.user$ where name=''''MYCOMMON'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select name,password,spare4 from sys.user$ where name=''MYCOMMON''','''','''''');
Executing Query [select name,password,spare4 from sys.user$ where
name='MYCOMMON']
NAME : MYCOMMON
PASSWORD : ADD0BB1368A68A28
SPARE4 :
S:9019FACA912F1FBB19CD0456BD65BE9D744F7A208B1BAE6CF7F8CEDEEC9C;H:1D7C0286938CB26
4DCB54C660FD94564
-------------------------------------------
PL/SQL procedure successfully completed.
SQL> @conn
ID
--------------------------------------------------------------------------------
NAME
------------------------------
1
CDB$ROOT
SQL>
|
Obviously the passwords are different. If we have a common account the passwords should be sync'd across all databases but the issue is that for the plug the account is local so we can change it, for the root its common so we can also change it in the plug. What happens if we use the same ideas to create an account in the plug database that starts with c## which according to the documentation we are not supposed to be able to do:
SQL> connect sys/oracle1@//192.168.1.86:1521/pdborcl.localdomain as sysdba
Connected.
SQL> set serveroutput on
SQL> create user c##c identified by c;
create user c##c identified by c
*
ERROR at line 1:
ORA-65094: invalid local user or role name
SQL>
|
This fails as expected as thats the rules that are documented, we are not supposed to create accounts called C## or c## in the plug databases, only accounts called C## or c## are allowed in the root container. OK, lets use the same hack as see what happens:
SQL> alter session set "_ORACLE_SCRIPT"=true;
Session altered.
SQL> create user c##c identified by c;
create user c##c identified by c
*
ERROR at line 1:
ORA-65050: Common DDLs only allowed in CDB$ROOT
SQL> alter session set "_ORACLE_SCRIPT"=false;
Session altered.
SQL>
|
OK, this is good as we cannot bypass the name prefix in this way so there is an unbalanced issue.
There is another hidden parameter related to this area that we can play with "_common_user_prefix" - this is what enforces the 'C##' name for a common user. I have changed this in my database to a NULL string:
SQL> sho parameter _comm
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_common_user_prefix string
SQL>
|
Now create a common user with a name i decide and check it:
SQL> @conn
ID
--------------------------------------------------------------------------------
NAME
------------------------------
1
CDB$ROOT
SQL> create user mycomm identified by mycomm container=all;
User created.
SQL> set serveroutput on
SQL> @print 'select * from dba_users where username=''''MYCOMM'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from dba_users where username=''MYCOMM''','''','''''');
Executing Query [select * from dba_users where username='MYCOMM']
USERNAME : MYCOMM
USER_ID : 112
PASSWORD :
ACCOUNT_STATUS : OPEN
LOCK_DATE :
EXPIRY_DATE : 01-JAN-14
DEFAULT_TABLESPACE : USERS
TEMPORARY_TABLESPACE : TEMP
CREATED : 05-JUL-13
PROFILE : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP : DEFAULT_CONSUMER_GROUP
EXTERNAL_NAME :
PASSWORD_VERSIONS : 10G 11G
EDITIONS_ENABLED : N
AUTHENTICATION_TYPE : PASSWORD
PROXY_ONLY_CONNECT : N
COMMON : YES
LAST_LOGIN :
ORACLE_MAINTAINED : N
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
|
This works. Has it been created in the plug database?
SQL> connect system/oracle1@//192.168.1.86:1521/pdborcl.localdomain
Connected.
SQL> set serveroutput on
SQL> @print 'select * from dba_users where username=''''MYCOMM'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from dba_users where username=''MYCOMM''','''','''''');
Executing Query [select * from dba_users where username='MYCOMM']
USERNAME : MYCOMM
USER_ID : 117
PASSWORD :
ACCOUNT_STATUS : OPEN
LOCK_DATE :
EXPIRY_DATE : 01-JAN-14
DEFAULT_TABLESPACE : USERS
TEMPORARY_TABLESPACE : TEMP
CREATED : 05-JUL-13
PROFILE : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP : DEFAULT_CONSUMER_GROUP
EXTERNAL_NAME :
PASSWORD_VERSIONS : 10G 11G
EDITIONS_ENABLED : N
AUTHENTICATION_TYPE : PASSWORD
PROXY_ONLY_CONNECT : N
COMMON : YES
LAST_LOGIN :
ORACLE_MAINTAINED : N
-------------------------------------------
PL/SQL procedure successfully completed.
|
So, yes this is much cleaner if you need to name common accounts without C##. The first method with the _oracle_script hidden parameter creates a complex scenario where we are able to create an account with the same name in the root and also the pluggable database but they can have different passwords and one is a common account and the other a local account. Finally if we need to drop an account created under the regime of the _oracle_script variable then this happens:
SQL> drop user mysecond;
drop user mysecond
*
ERROR at line 1:
ORA-28014: cannot drop administrative users
SQL>
|
The solution is to drop under the _oracle_script parameter:
SQL> alter session set "_ORACLE_SCRIPT"=true;
Session altered.
SQL> drop user mysecond;
User dropped.
SQL> alter session set "_ORACLE_SCRIPT"=false;
Session altered.
SQL>
|
And of course it works. If you need to create accounts with your own name convention then use the hidden parameter _common_user_prefix but its hidden so you would need to check with Oracle whether you are still supported when doing this, my gut feeling would be yes because this parameter must exist for a reason and its most likely to ensure legacy applications (including Oracle default schemas) where the name cannot be changed will still work.
We should not rely on _oracle_script as its clearly coded to work under certain circumstances when installing Oracle defined accounts and appears to be inconsistant.
What else does all of this tell us? well we cannot rely on the COMMON column in views such as DBA_USERS as i created a COMMON user that is not COMMON and did not exist in each pluggable database until i created manually. Then I could sync passwords BUT i could still create seperate passwords if I needed. We cannot assume a COMMON account in the root container has the same password in all pluggable databases. Also the account in the plug cannot be considered common as i had to create it manually and it was listed as not common. We also cannot rely on a COMMON user being called C## something. We also cannot rely on the ORACLE_MAINTAINED column as I created a user that was not ORACLE_MAINTAINED.
Obviously do not use these parameters in production without discussing it with support; the first parameter _oracle_script seems buggy and unreliable but the second _common_user_prefix seems sensible.
