Change COMMON_USER prefix – 12c Edition

Earlier I wrote a post on the difference between COMMON_USERS and LOCAL_USERS within the Oracle Database 12c architecture.  I mentioned in that post that the COMMON_USER had to be configured with a C## or c## to identify the user as a common user.  This got me to thinking, what if I wanted to use a different prefix for common users?  How do I change the prefix?  How hard would it be to change? Oracle documentation doesn’t go into these questions because they want everyone to stick with the c## rule.  

It wasn’t until today that I found a simple solution to my questions (Thanks to Jonathan Lewis).  If you want to change the prefix for common users, there is a hidden parameter that needs to be set.  This parameter is “_COMMON_USER_PREFIX”.  This parameter accepts a string to change the prefix.  Lets walk through the process to change the prefix.

As with any parameter change, the first thing we need to do is backup the SPFILE for the instance that is running.  In this case, since we are using a CDB, we need to make sure to connect to the CDB.  The “show con_name” will give us the name of the container we are in.  We should be in the CDB$ROOT container.  

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT

Now that we are confident that we are in the root of the CDB, we need to make a backup of the spfile.  I always check to make sure I know where the SPFILE is at before doing anything.

SQL> select value from v$parameter where name = 'spfile';

VALUE
----------------------------------------
/opt/oracle/product/12.1.0.1/dbhome_1/db
s/spfileora12c.ora

Knowing the location of the spfile, we can now make a backup of the file as a PFILE and then shutdown the CDB.

SQL> create pfile='pfile_ora12c.ora' from spfile;

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Now comes the fun part.  We need to edit the PFILE we just created and add the _COMMON_USER_PREFIX to specify what prefix we want to use.  Just for testing purposes, lets use “B##”.

$cd $ORACLE_HOME/dbs

$vi pfile_ora12c.ora

Add this to the file -> ora12c._common_user_prefix=’B##’

Now we need to restart the CDB using the PFILE.

SQL> startup mount pfile='pfile_ora12c.ora'
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2293880 bytes
Variable Size 562040712 bytes
Database Buffers 264241152 bytes
Redo Buffers 6529024 bytes
Database mounted.
SQL> alter database open;

Database altered.

Lets check to see if the parameter took effect.

SQL> select value from v$parameter where name like '_common%';

VALUE
---------------
B##

Everything is in place to create a COMMON_USER; however, lets test if we can still create a COMMON_USER with the default C## prefix (as Oracle recommends).

SQL> CREATE USER c##admin
IDENTIFIED BY welcome1
DEFAULT TABLESPACE USERS
QUOTA unlimited on USERS
TEMPORARY TABLESPACE temp
CONTAINER = ALL; 2 3 4 5 6 
CREATE USER c##admin
 *
ERROR at line 1:
ORA-65096: invalid common user or role name

Notice that the C## as part of the user name no longer works.  Oracle thinks it is an invalid common user or role name.  If we change the user name to be prefix with B## the account is created.

SQL> CREATE USER b##admin
IDENTIFIED BY welcome1
DEFAULT TABLESPACE USERS
QUOTA unlimited on USERS
TEMPORARY TABLESPACE temp
CONTAINER = ALL; 2 3 4 5 6

User created.

Now that you know how to override the default COMMON_USER_PREFIX, this should help with security protocols within organizations.  We can now change the prefix to whatever is desired and still use the COMMON_USER architecture without worry.

Enjoy!

twitter: @curtisbl294

blog: http://dbasolved.com

Please follow and like: