Oracle 12c Common User & Local User

      Common user must be created in CDB only.
      When we create a common user must give C## as prefix
      The user is present in all containers(CDB$ROOT and all PDB)

Local Users

     Local user can only created at the PDB.
     The same username can be created in multiple PDB and they are unrelated.

     Use Container Clause to set the current container

     SQL> show con_name

     CON_NAME
     ——————————
     CDB$ROOT

     When we try to create a normal user in CDB it raise the error

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

     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 consist only of ASCII characters, and must contain the prefix
     //          specified in common_user_prefix parameter.
     // *Action: Specify a valid common user or role name.
     //

     It agains the rule because only common user allowed to create in CDB. If any reason we need to create the local user in CDB we use undocumented parameter _oracle_script=true at system level.

Create common users
     We connected to common user with the create user privilege.
     The current container must be the root container.
     The username for the common user must be prefixed with “C##” or “c##” and contain only ASCII or EBCDIC characters.
     The username must be unique across all containers.
     The Default table space, Temporary Table space, Quota and Profile, must all reference objects that exist in all containers.
     You can either specify the container=all clause, or omit it, as this is the default setting when the current container is the root.

Common user with container clause.

     SQL> create user C##cdbuser identified by oracle container=all;

     User created.

     SQL> grant create session to C##cdbuser container=all;

     Grant Succeeded.

Common User with default setting

     SQL> create user c##cuser identified by oracle;

     User created.

Create Local User

    You must be connected to a user with the create user privilege
    The username for the local user must not be prefixed with “c##”.
    The username must be unique within the PDB.
     You can either specify the container=current clause, or omit it, as this is the default setting when the current container is a PDB.

Switching container for session
     SQL> alter session set container=pram;

     Session altered.

     SQL> show con_name;

     CON_NAME
     ——————————
     PRAM

     or

Connect as a User

     ]$ export ORACLE_SID=pram
     ]$ sqlplus essvee@pram

     SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 15 10:46:08 2018

     Copyright (c) 1982, 2016, Oracle.  All rights reserved.

     Enter password:
     Last Successful login time: Tue Jun 12 2018 12:24:14 +05:30

     Connected to:
     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

     SQL> show user
     USER is “ESSVEE”

     SQL> show con_name;

     CON_NAME
     ——————————
     PRAM

Local user with container clause.

     User created by  a user who have  create user privilege.

     SQL>  create user locuser1 identified by oracle container=current;

     User created.   

Local User with default setting

     SQL> create user locuser2 identified by oracle;

     User created.