Creation of Oracle Multitenant user and privileges at CDB and PDB level

The advantage of the Multitenant option that allows a single user can be found in all containers such as CDB Root or PDB we call this user COMMON. The Database Multitenant module also allows a LOCAL user that is only present in a PDB or several that may not be related to each other. We can also understand that there are two types of function one in the CDB Root in all PDB or LOCAL present in a PDB.



Common User Creation Criteria

  • Connect to a Root Container;

  • With guidance from Oracle the common user must have the following prefixes ("C ##" or "c ##") to differentiate from the user 'LOCAL' and contain only ASCII characters.;

  • Deve ser único em todos os contêineres.

Note: "Anything related to the COMMON user is necessary to use the prefixes to differentiate it from the local users, this is good practice".


COMMON User creation command


First example use of the CONTAINER clause.

CONN / AS SYSDBA

CREATE USER c##_user_exp1 IDENTIFIED BY oracle1 CONTAINER=ALL;
GRANT CREATE SESSION TO c##_user_exp1 CONTAINER=ALL;

Create the common user using the standard CONTAINER configuration.


Second example, standard CONTAINER configuration.

CREATE USER c##_user_exp2 IDENTIFIED BY oracle2;
GRANT CREATE SESSION TO c##_user_exp2;

Create common functions

All functions follow the same criteria mentioned above.

--# First Create the common role.
CONN / AS SYSDBA
CREATE ROLE c##common_role1;
GRANT CREATE SESSION TO c##common_role1;

--# Second example Grant it to a common user.
GRANT c##common_role1 TO c##_user_exp1 CONTAINER=ALL;

Example Grant it to a local user.

ALTER SESSION SET CONTAINER = ORADES_PDB1;
GRANT c##common_role1 TO user_local_exp1;


Local user creation


  • You must connect with a user who has CREATE USER privileges;

  • Do not use a prefixed local user ("C ##" or "c ##");

  • Use a local PDB exclusive user;

  • Use the 'CONTAINER = CURRENT' clause to determine the current PDB.


LOCAL User Creation Criteria


Enter the PDB where the user will be created in this example pdb name 'ORADES_PDB1'

ALTER SESSION SET CONTAINER=ORADES_PDB1;

First example LOCAL user creation command calculate 'CONTAINER'

CREATE USER user_local_exp1 IDENTIFIED BY useroracle1 CONTAINER = CURRENT; 
GRANT CREATE SESSION TO user_local CONTAINER = CURRENT; 

Second example, PDB standard LOCAL user creation command, connect to a privileged user in the PDB.


conn system/password@orades_pdb1

CREATE USER user_local_exp2 IDENTIFIED BY useroracle2;
GRANT CREATE SESSION TO user_local_exp2;

Local functions are created in a similar way to the LOCAL user creation criteria already mentioned above.


CONN / AS SYSDBA 

-- Switch container.
ALTER SESSION SET CONTAINER =orades_pdb1;

-- Create the common role.
CREATE ROLE role_exp1;
GRANT CREATE SESSION TO role_exp1;

-- Grant it to a common user.
GRANT role_exp1 TO c##_user_exp1;

-- Grant it to a local user.
GRANT role_exp1 TO user_local_exp2;

This is how we finished another procedure for creating Oracle Multitenant users.