Database 12c: Common and local users and roles


Since 12c “nothing” is like before. Today we are talking about the user creation. The user beavior in a Non-CDB is like in 11g and before. In CDBs and PDBs the concept slightly changes. In a multitenant database users will be divided into two different types: Local and common users:

commonuser

Common users

A common user is user which is present in the CDB and all PDBs. Here from documentation:

“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.”

Special users are sys and system. Common users have the same characteristic in all instances (e.g. password, tablespace and so on)

Create a new common user:

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> create user C##TEST1 identified by test1 container = all;

User created.

Common users must start with c## or C##

SQL> create user TEST1 identified by bubu1 container=all;
create user TEST1 identified by bubu1 container=all
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

Changes to common users apply to all container:

SQL> create user C##TEST1 identified by test1 container = all default tablespace users;
create user C##TEST1 identified by test1 container = all default tablespace users
*
ERROR at line 1:
ORA-65048: error encountered when processing the current DDL statement in pluggable database PDB1
ORA-00959: tablespace 'USERS' does not exist

The password is the same in CDB and all PDBs, when trying to change you get an error:

QL> alter user C##TEST1 identified by bubu1;
alter user C##TEST1 identified by bubu1
*
ERROR at line 1:
ORA-65066: The specified changes must apply to all containers

Changes to common users are only allowed in CDB

SQL> alter user C##TEST1 identified by bubu1 container=all;
alter user C##TEST1 identified by bubu1 container=all
*
ERROR at line 1:
ORA-65050: Common DDLs only allowed in CDB$ROOT

Local users

Local users are users which are only exsits in a PDB but not in all containers.

Create local users:

SQL> create user TEST1 identified by bubu1;

User created.

SQL> create user TEST2 identified by bubu1 container=current;

User created.

SQL> 

In CDBs no local users can be created:

SQL> create user TEST1 identified by bubu1 container=current;
create user TEST1 identified by bubu1 container=current
                                *
ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT

The username for a local user must not start with c## or C##

SQL> create user C##TEST1 identified by bubu1;
create user C##TEST1 identified by bubu1
            *
ERROR at line 1:
ORA-65094: invalid local user or role name

SQL> create user C##TEST1 identified by bubu1 container=current;
create user C##TEST1 identified by bubu1 container=current
            *
ERROR at line 1:
ORA-65094: invalid local user or role name

Common roles

Common roles are like common users roles which are present in CDB and all PDBs. Important is that the privileges are granted at CDB or PDB level, but not over all containers, per default.

SQL> create role C##ROLE1 container=all;

Role created.

SQL> grant create session to C##ROLE1;

Grant succeeded.

SQL> select grantee,privilege,common from dba_sys_privs where grantee='C##ROLE1';
GRANTEE                        PRIVILEGE                      COMMON
------------------------------ ------------------------------ ------------------------------
C##ROLE1                       CREATE SESSION                 NO

SQL> alter session set container=PDB1;

Session altered.

SQL> select grantee,privilege,common from dba_sys_privs where grantee='C##ROLE1';

no rows selected

SQL> 

To grant privileges over all containers:

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> grant create table to C##ROLE1 container=all;

Grant succeeded.

SQL> select grantee,privilege,common from dba_sys_privs where grantee='C##ROLE1';

GRANTEE                        PRIVILEGE                      COMMON
------------------------------ ------------------------------ ------------------------------
C##ROLE1                       CREATE SESSION                 NO
C##ROLE1                       CREATE TABLE                   YES

SQL> alter session set container=PDB1;

Session altered.

SQL> select grantee,privilege,common from dba_sys_privs where grantee='C##ROLE1';

GRANTEE                        PRIVILEGE                      COMMON
------------------------------ ------------------------------ ------------------------------
C##ROLE1                       CREATE TABLE                   YES

SQL> 

Privileges granted in a pdb will only available in a pdb:

SQL> alter session set container=PDB1;

Session altered.

SQL> select grantee,privilege,common from dba_sys_privs where grantee='C##ROLE1';

GRANTEE                        PRIVILEGE                      COMMON
------------------------------ ------------------------------ ------------------------------
C##ROLE1                       CREATE TABLE                   YES

SQL> grant create view to C##ROLE1;

Grant succeeded.

SQL> select grantee,privilege,common from dba_sys_privs where grantee='C##ROLE1';

GRANTEE                        PRIVILEGE                      COMMON
------------------------------ ------------------------------ ------------------------------
C##ROLE1                       CREATE VIEW                    NO
C##ROLE1                       CREATE TABLE                   YES

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> select grantee,privilege,common from dba_sys_privs where grantee='C##ROLE1';

GRANTEE                        PRIVILEGE                      COMMON
------------------------------ ------------------------------ ------------------------------
C##ROLE1                       CREATE SESSION                 NO
C##ROLE1                       CREATE TABLE                   YES

SQL> 

3 thoughts on “Database 12c: Common and local users and roles

  1. I want to give access to COMMON USER “C##Test” on only PDB [PDBORCL] assume I have three PDBs.

    Is it possible

    • Hi,
      Common users are created in every cdb. Roles and Grant can be definied at cdb or pdb level. To achieve your case you have to create an common user at cdb level. This user is available in all pdbs and the cdb and has no priviledges. What is the first role/permission an oracle dba should grant? –> create session.

      So you can grant create session at pdb level and so deny access to other pdbs or the cdb. Here the example:

      Create common user:

      SQL> create user c##test identified by bubu;

      User created.

      SQL> select CON_ID,USERNAME from cdb_users where username='C##TEST' order by 1;

      CON_ID USERNAME
      ---------- ------------------------------
      1 C##TEST
      3 C##TEST
      4 C##TEST

      Grant privilege:

      SQL> alter session set container=PDB1;

      Session altered.

      SQL> grant create session to c##test;

      Grant succeeded.

      SQL>

      Try it now:
      @CDB1:

      [bash ~]$ echo "SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;" | sqlplus -S c##test/bubu@cdb1
      ERROR:
      ORA-01045: user C##TEST lacks CREATE SESSION privilege; logon denied
      SP2-0306: Invalid option.
      Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM}] [edition=value]]
      where ::= [/][@]
      ::= [][/][@]

      @PDB1:

      [bash ~]$ echo "SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;" | sqlplus -S c##test/bubu@pdb1

      SYS_CONTEXT('USERENV','CON_NAME')
      --------------------------------------------------------------------------------
      PDB1

      @PDB2:

      [bash ~]$ echo "SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;" | sqlplus -S c##test/bubu@pdb2
      ERROR:
      ORA-01045: user C##TEST lacks CREATE SESSION privilege; logon denied
      SP2-0306: Invalid option.
      Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM}] [edition=value]]
      where ::= [/][@]
      ::= [][/][@]
      [bash ~]$

  2. Database version is 12.1.0.1.0 on Windows 8.1 64 bit
    I’m trying to set up a user with a table space for Oracle golden gate on source db

    I created a table space SAMPLE and trying to create a common user with sample as default table space but it’s throwing up error.What I’m doing wrong?.

    Thanks

    connected as sysdba

    SQL>ALTER SESSION SET CONTAINER=CDB$ROOT;

    SQL>SHOW con_name;
    CON_NAME
    ——————————
    CDB$ROOT

    SQL>SELECT file_name,tablespace_name FROM dba_data_files
    WHERE tablespace_name=’SAMPLE’

    FILE_NAME TABLESPACE_NAME
    ——————————————————————————————
    D:\APP\USER\ORADATA\ORCL\SAMPLE01.DBF SAMPLE

    SQL>CREATE USER c##samadmin IDENTIFIED BY welcome1
    DEFAULT TABLESPACE SAMPLE;
    –CONTAINER=ALL;

    Error report –
    SQL Error: ORA-65048: error encountered when processing the current DDL statement in pluggable database PDB1
    ORA-00959: tablespace ‘SAMPLE’ does not exist
    65048. 00000 – “error encountered when processing the current DDL statement in pluggable database %s”
    *Cause: An error was encountered when executing a statement in one of the
    pluggable databases.
    *Action: Examine the cause of failure in the pluggable database.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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