Administration Database Users including Roles and Privileges on Multitenant

This topic describes how to create and manage a user in Multitenant database.

There are two types of users in Multitenant Database:

• Common User: User exists in all containers (root container and all PDBS).
• Local User: User exists on particular PDB only.

Similarly, Two types of role are available in Multitenant Database:

• Common Role: Role exists in all containers (root container and all PDBS).
• Local Role: Role exists on particular PDB only.

Note::: Common or Local User and Role are same way to create to manager.

Create Common User:

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 SALESPDB                       MOUNTED
         5 PDBTEST                        READ WRITE NO
         7 PDBNODATA                      MOUNTED

SQL> show parameter common

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
common_user_prefix                   string      C##

Note::: Grant privileges to common user using container=all for root as well as PDBs. If don’t use container=all then it will applicable for container root only.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

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

User created.

SQL> grant create session, connect, resource to c##usercon;

Grant succeeded.

SQL> conn c##usercon/oracle;
Connected.

SQL> show user
USER is "C##USERCON"

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> conn / as sysdba
Connected.

SQL> conn c##usercon/oracle@pdbtest
ERROR:
ORA-01045: user C##USERCON lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.

SQL> grant connect, resource to c##usercon container=all;

Grant succeeded.

SQL> conn c##usercon/oracle@pdbtest
Connected.

SQL> show user
USER is "C##USERCON"

SQL> show con_name

CON_NAME
------------------------------
PDBTEST

Common user will be created in all PDBs automatically:

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 SALESPDB                       MOUNTED
         5 PDBTEST                        READ WRITE NO
         7 PDBNODATA                      MOUNTED

SQL> col USERNAME for A20
SQL> set linesize 100
SQL> SELECT username, b.name, user_id, a.con_id
FROM cdb_users a, v$containers b
WHERE a.con_id = b.con_id AND username = 'C##USERCON';  

USERNAME             NAME                              USER_ID     CON_ID
-------------------- ------------------------------ ---------- ----------
C##USERCON           PDBTEST                               105          5
C##USERCON           CDB$ROOT                              102          1

SQL> alter pluggable database pdbnodata open;

Pluggable database altered.

SQL> SELECT username, b.name, user_id, a.con_id
FROM cdb_users a, v$containers b
WHERE a.con_id = b.con_id AND username = 'C##USERCON';  

USERNAME             NAME                              USER_ID     CON_ID
-------------------- ------------------------------ ---------- ----------
C##USERCON           PDBTEST                               105          5
C##USERCON           CDB$ROOT                              102          1
C##USERCON           PDBNODATA                             105          7

Common user can be created only from root container:

SQL> alter session set container=PDBTEST;

Session altered.

SQL> show user
USER is "SYS"

SQL> create user c##usercon01 identified by oracle;
create user c##usercon01 identified by oracle
            *
ERROR at line 1:
ORA-65094: invalid local user or role name

Create Local User:

SQL> alter session set container=PDBTEST;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDBTEST

SQL> create user user_pdb identified by oracle;

User created.

SQL> grant create session, connect, resource to user_pdb;

Grant succeeded.

SQL> conn user_pdb/oracle@pdbtest
Connected.

SQL> show con_name

CON_NAME
------------------------------
PDBTEST

SQL> show user
USER is "USER_PDB"

Local user can be created from PDB only but not from root container:

SQL> show user
USER is "SYS"
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> create user user_pdb identified by oracle;
create user user_pdb identified by oracle
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

Common Role:

Common role has to be created same way like Common user.

• Role Name should be starting with C##
• Creates from root container.
• Can be assigned to common user as well as local users any PDBs.
• Common Role name is unique for entire database (CDB + PDBs).

SQL> create role c##role_hr;

Role created.

SQL> grant create session to c##role_hr;

Grant succeeded.

SQL> grant c##role_hr to c##usercon;

Grant succeeded.

SQL> grant c##role_hr to user_pdb;
grant c##role_hr to user_pdb
                    *
ERROR at line 1:
ORA-01917: user or role 'USER_PDB' does not exist

SQL> alter session set container=pdbtest;

Session altered.

SQL> grant c##role_hr to user_pdb;

Grant succeeded.

Local Role:

• Role Name should not starting with C##.
• Creates from a PDB.
• Can be assigned to local user of the PDB only.
• Local Role name is unique for a PDB.

SQL> create role role_local;

Role created.

SQL> grant create session, select any table to role_local;

Grant succeeded.

SQL> grant role_local to user_pdb;

Grant succeeded.

SQL> grant role_local to c##usercon;

Grant succeeded.

Create an object from common user:

Can be created an object (like Table or View etc) with same name on CDB and PDBs from common user.

Table create on a CDB using common user:

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show user
USER is "C##USERCON"

SQL> Create table root_test (id number);

Table created.

Table create on a PDB using common user:

SQL> show con_name
CON_NAME
------------------------------
PDBNODATA

SQL> show user
USER is "C##USERCON"

SQL> create table C##USERCON.root_test (id number);

Table created.

Table create on a PDB using common user:

SQL> show con_name
CON_NAME
------------------------------
PDBTEST

SQL> show user
USER is "C##USERCON"

SQL> create table C##USERCON.root_test (id number);

Table created.

Table with same name on all PDBs and Container for a common user:

col OWNER for A20
col OBJECT_NAME for A20
set linesize 100

SQL> SELECT a.con_id, b.name, owner, object_name, object_id
FROM cdb_objects a, v$containers b
WHERE a.con_id = b.con_id AND a.object_name = 'ROOT_TEST';

    CON_ID NAME                           OWNER                OBJECT_NAME           OBJECT_ID
---------- ------------------------------ -------------------- -------------------- ----------
         7 PDBNODATA                      C##USERCON           ROOT_TEST                 91941
         5 PDBTEST                        C##USERCON           ROOT_TEST                 91823
         1 CDB$ROOT                       C##USERCON           ROOT_TEST                 92447