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
