Common and Local Users in Oracle 12c

 

Technorati Tags: Oracle12c,Common User prefix,ORA-65096

In oracle 12c Database there are mainly two type users

1) common users
2) local users
The difference between them is as below -

Common Users in a CDB
======================
A common user is a database user that has the same identity in the root and in every existing and future PDB. Every common user can connect to and perform operations within the root, and within any PDB in which it has privileges.
Every common user is either Oracle-supplied or user-created. Examples of Oracle-supplied common users are SYS and SYSTEM.
Common users have the following characteristics:
    A common user can log in to any container (including CDB$ROOT) in which it has the CREATE SESSION privilege.
    The name of every user-created common user must begin with the characters c## or C##. (Oracle-supplied common user names do not have this restriction.)
    The names of common users must contain only ASCII or EBCDIC characters.
    Every common user is uniquely named across all containers.
    The schemas for a common user can differ in each container.

Local Users in PDB
============================
A local user is a database user that is not common and can operate only within a single PDB. Local users have the following characteristics:
    A local user is specific to a particular PDB and owns a schema in this PDB.
    A local user on one PDB cannot log in to another PDB or to the root
    The name of a local user must not begin with the characters c## or C##.
    The name of a local user must only be unique within its PDB.
    Whether local users can access objects in a common schema depends on their user privileges.

The new rule in 12c is you must create "common user" under CDB$ROOT with "c##" or "C##" prefix for the user name.

When you try to create users in 12c by default it is in CDB container, so you will need to use the C## prefix to create a common user.

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

ORA-65096: invalid common user or role name or ORA-65049: creation in oracle 12c
65049, 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.
65049, 00000, "creation of local user or role is not allowed in CDB$ROOT"
*Cause: An attempt was made to create a local user or role in CDB$ROOT.
*Action: If trying to create a common user or role, specify CONTAINER=ALL.

But If you add the prefix, this works

SQL> create user c##xxx identified by yyyy ;
User created.

If you want to avoid the prefix then you can change the container to PDB and create a local user which does not have this restriction.

SQL> SELECT PDB FROM V$SERVICES
PDB
------------------------------ ------------------------------
  PDB_ora12c
SQL>
SQL> alter session set container=pdb_ora12c ;
Session altered.
SQL>
SQL> show con_name

--------------------------------------------------------
PDB_ORA12C

SQL>
SQL> create user xxx identified by yyyy ;
User created.

Changing Common User prefix in Oracle 12c

There is a hidden parameter in Oracle 12c called  “_COMMON_USER_PREFIX” which stores the C## prefix.  This parameter accepts a string 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> select value from v$parameter where name like '_common%';

C##

SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT.

Now the default root is CDB. we need to make a backup of the spfile. The location is mainly as mentioned below.

C:/app/oracle/product/12.1.0.1/dbhome_1/database/spfileora12c.ora

now make a backup of the file as a PFILE and then shutdown the CDB container.

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

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

Once the CDB is down, edit the PFILE  created and add the _COMMON_USER_PREFIX to specify whatever prefix you want to use.
Add this line to the file as below

ora12c._common_user_prefix=’C_’

Now we need to restart the CDB using the PFILE.

SQL> startup mount pfile='pfile_ora12c.ora'
ORACLE instance started.
Database mounted.
SQL> alter database open;
Database altered.

Check the parameter now

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

VALUE
---------------
C_

Now we can use the COMMON_USER architecture to create users in CDB.