Making the CDB Common User Without Using C## Prefix….

If you have done any play with the latest release of Oracle database 12c, you must have come across the concept of Common and Local users. I won’t rewrite what’s already there in the docs but for setting the context of the post, a Common user must be created with a prefix added to the username-C##. Miss that and you won’t be able to create one while being connected to the Container database. On the other hand, if you are working in a Pluggable database, this restriction is not there and you would be allowed to create the user just like we have been creating since we have started working with Oracle database. Like a picture is worth more than 10000 words, a demo is better than many definitions so have a look.

SQL> select * from V$version;

BANNER
——————————————————————————–
    CON_ID
———-
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
     0

PL/SQL Release 12.1.0.2.0 – Production
     0

CORE    12.1.0.2.0    Production
     0

TNS for Linux: Version 12.1.0.2.0 – Production
     0

NLSRTL Version 12.1.0.2.0 – Production
     0

Now, let’s confirm where we are right now!

SQL> show con_name

CON_NAME
——————————
CDB$ROOT
SQL> show con_id

CON_ID
——————————
1

Since it’s confirmed that we are in the Container database Root, let’s try to create a user without using the prefix C##.

SQL> create user aman identified by aman;
create user aman identified by aman
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

As expected, it didn’t work. So let’s see the explanation that we have for the given error code.

SQL> !oerr ora 65096
65096, 00000, "invalid common user or role name"
// *Cause:  An attempt was made to create a common user or role with a name
//          that was not valid for common users or roles.  In addition to
//          the usual rules for user and role names, common user and role
//          names must start with C## or c## and consist only of ASCII
//          characters.
// *Action: Specify a valid common user or role name.
//

If you would read the doc link mentioned somewhere above, you would get the confirmation that to make a common user, prefixing the name with C## is a must. So let’s make a second attempt and this time, use the prefix along with the username.

SQL> create user c##aman identified by aman;

User created.

Now, I wasn’t really happy doing this and was wanted to change this behaviour. So the obvious place to look was at some internal parameter that was behind it. Say hello to _common_user_prefix-an internal parameter that is behind this prefix C##.

SQL> select a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from x$ksppi a,
x$ksppcv b,
x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and substr(ksppinm,1,1)=’_’ and lower(c.ksppstvl) like ‘%c##%’
order by a.ksppinm;  2    3    4    5    6    7    8    9 

Parameter               Session Value              Instance Value
—————————— —————————— ——————————
_common_user_prefix           C##                  C##

Well, now it’s just about modifying this parameter to whatever value you want! I just wanted to make the Common user creation the way I have been creating users all the time so I decided to make it simply null as shown below.

SQL> select a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from x$ksppi a,
x$ksppcv b,
x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and substr(ksppinm,1,1)=’_’ and lower(c.ksppstvl) like ‘%c##%’
order by a.ksppinm;  2    3    4    5    6    7    8    9 

Parameter               Session Value              Instance Value
—————————— —————————— ——————————
_common_user_prefix           C##                  C##

A database bounce is required to make the change come in effect. and this is what I got after the database was restarted.

SQL> select a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from x$ksppi a,
x$ksppcv b,
x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and substr(ksppinm,1,1)=’_’ and lower(a.ksppinm) like ‘%common_user_prefix%’
order by a.ksppinm;  2    3    4    5    6    7    8    9 

Parameter               Session Value              Instance Value
—————————— —————————— ——————————
_common_user_prefix

Great! So let’s create a Common user once again but this time, we won’t need the prefix C## to be added with it.

SQL> show con_id

CON_ID
——————————
1
SQL> show con_name

CON_NAME
——————————
CDB$ROOT
SQL> drop user c##aman cascade;

User dropped.

SQL> create user aman identified by aman;

User created.

SQL> grant dba to aman;

Grant succeeded.

SQL> column username format a40
SQL> select username, common from dba_users where username=’AMAN’;

USERNAME                 COM
—————————————- —
AMAN                     YES

I haven’t got any ora-600 or anything else that has broken down my database but let’s see if something shows up. Also, it goes without saying that using any underscore/internal parameters like the one shown, without the consent of Oracle Support Services must be only done in a test database which is used for nothing but learning/testing. So if you are going to modify this parameter and will witness database being crashed or any other functionality being broken, don’t say that you haven’t been warned!