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;

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';

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%';

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).

QUOTA unlimited on USERS
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.

QUOTA unlimited on USERS
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.



twitter: @curtisbl294



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Mrs. Jaye's Class Blog

Coordinate Algebra & Advanced Algebra

Pardy DBA

ORA-00001: unique constraint (ORA.BLOG_TAGLINE_PK) violated

Martin Widlake's Yet Another Oracle Blog

Oracle performance, Oracle statistics and VLDBs


Heli's thoughts on Database Designing, Oracle SQL Developer Data Modeler, User Groups etc.

Julian Dontcheff's Database Blog

The good DBA is one who learns from his mistakes, the best DBA is one who learns from other DBA's mistakes

Martins Blog

Trying to explain complex things in simple terms

The Data Warrior

Changing the world, one data model at a time. How can I help you?

Maaz Anjum's Blog

A life yet to be lived...

Stuff that interests me, if not you!

%d bloggers like this: