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 6User 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
Current Oracle Certs
Bobby Curtis
I’m Bobby Curtis and I’m just your normal average guy who has been working in the technology field for awhile (started when I was 18 with the US Army). The goal of this blog has changed a bit over the years. Initially, it was a general blog where I wrote thoughts down. Then it changed to focus on the Oracle Database, Oracle Enterprise Manager, and eventually Oracle GoldenGate.
If you want to follow me on a more timely manner, I can be followed on twitter at @dbasolved or on LinkedIn under “Bobby Curtis MBA”.
how to buy priligy in usa Aras S, Zaidi MR
Muchas gracias. ?Como puedo iniciar sesion?
priligy dapoxetine amazon Elevated risks of borderline tumors in women treated with fertility hormones have been suggested to reflect surveillance bias, and not a biological explanation, which may explain the absence of risk with increasing number of cycles of ART
cytotec pills over the counter PubMed 16390772