ORACLE12C Multi-tenant Manage users, roles, permissions

The Oracle database-C multi-tenant option allows a single container database (CDB) to host multiple separate pluggable databases (PDB).
So how do we manage user rights in container database (CDB) and pluggable Database (PDB).


Background:
When connected to a multi-tenant database, the management of user rights is slightly different from the traditional Oracle single database environment. There are two types of users in a multitenant environment.
Common users (Common user): The user exists in all containers (root and all Pdb).
Local User: The user exists only in a specific PDB. The same user name can exist in multiple PDB creation, but there is no relationship between them.
Similarly, there are two types of roles.
Common role (Common role): This role is in all containers (root and all Pdb).
Local role: The role exists only in a specific PDB. You can create the same role names in multiple PDB, but there is no relationship between them.
Some DDL statements are expanded so that they can be directed to the current container or to the container clause of all containers. Its use will be demonstrated in the following sections.


I. Create and co-users
Conditions:
1. You must be connected to a common user with the Create user privilege.
2. The current database must be the root container database.
3. The user name of a common user must be prefixed with "c##" or "c##" and contain a unique ASCII or EBCDIC character.
4. In all containers the common user name must be unique.
5.DEFAULT tablespace, temporary tablespace, quota, and profile must exist in all referenced objects in all containers.
You can specify the Container=all clause, or ignore it, because this is the default setting when the current container is the root.
6. Public users can have different permissions in different CDB and PDB databases.
Operation:
When creating a public user in CDB, the same user is also created in PDBs. If the GRANT command in CDB is empowered, if the Container=all is not specified, the empowerment is only effective in the CDB and does not take effect in the PDB, and the user needs to be able to access the PDB and need to switch to the PDB to re-assign the power. If the Container=all is assigned, the assignment takes effect in the CDB and also in the PDB.


Container=all not specified when assigned:
Sql> Show Con_name
Con_name
------------------------------
Cdb$root
sql> Create User C # #andy02 identified by Andy;
Sql> Grant Create session to C # #andy02;
C:\users\andy>sqlplus C # #andy02/[email protected]:1521/andycdb
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Production
C:\users\andy>sqlplus C # #andy02/[email protected]:1521/pdb01
ERROR:
Ora-01045:user C # #ANDY02 lacks CREATE SESSION privilege; Logon denied
Sql> alter session set CONTAINER=PDB01;
Sql> Grant Create session to C # #andy02;
C:\users\andy>sqlplus C # #andy02/[email protected]:1521/pdb01
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Production


Assign Container=all:
Sql> Show Con_name
Con_name
------------------------------
Cdb$root
sql> Create User C # #andy identified by Andy;
Sql> Grant Create session to C # #andy Container=all;
C:\users\andy>sqlplus C # #andy/[email protected]:1521/andycdb
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Production
C:\users\andy>sqlplus C # #andy/[email protected]:1521/pdb01
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Production


Second, create Local Users
Conditions:
1. You must be connected to a user with the Create user privilege.
2. The user name of the local user does not have to be prefixed with "c##" or "c##".
3. The user name must be unique within the PDB.
4. When the current container is a PDB, you can specify the container=current clause, or ignore it, because this is the default setting.
Operation:
Sql> Show Con_name
Con_name
------------------------------
PDB01
Sql> create user Andy identified by Andy;
Sql> Grant create session to Andy;
C:\users\andy>sqlplus Andy/[email PROTECTED]:1521/PDB01
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Production
--Go to the CDB to see if the local user ANDY exists, and does not exist.
Sql> Show Con_name


Con_name
------------------------------
Cdb$root
Sql> Select username,created from dba_users where username= ' ANDY ';
No rows selected


Iii. Creating a common role
Similar to the user described earlier, roles can be common or local.
All of the roles provided by Oracle are visible in the root container and all PDB. The following conditions are required to create a common role.
Conditions
1. You must connect to the common user create role with the normally granted set container permission.
2. The current container must be a root container.
3. Role names for common roles must be prefixed with "c##" or "c##" and contain unique ASCII or EBCDIC characters.
4. The role name must be unique in all containers.
5. Role creation and CONTAINER=ALL clauses
6. Public roles can be given to public users or local users
Operation:
Note: You need to specify Container=all when the CDB gives role permissions, otherwise it is not visible in the PDB.
Sql> Show Con_name
Con_name
-------------------
Cdb$root
sql> Create role C # #role_andy;
Public role empowerment to a common user:
Sql> Grant Select on Dba_objects to C # #role_andy Container=all;
Sql> Grant C # #role_andy to C # #andy Container=all;


C:\users\andy>sqlplus C # #andy/[email protected]:1521/andycdb
Sql>
Set Lin 300;
Set pagesize 300;
Col username for A30;
Col granted_role for A30;
SELECT * from User_role_privs;


USERNAME granted_role ADM DEL DEF os_ COM INH
------------------------------ ------------------------------ --- --- --- --- --- ---
C # #ANDY C # #ROLE_ANDY No no yes No yes No
Sql> Select COUNT (*) from dba_objects;


COUNT (*)
----------
72635


C:\users\andy>sqlplus C # #andy/[email protected]:1521/pdb01
Set Lin 300;
Set pagesize 300;
Col username for A30;
Col granted_role for A30;
SELECT * from User_role_privs;


USERNAME granted_role ADM DEL DEF os_ COM INH
------------------------------ ------------------------------ --- --- --- --- --- ---
C # #ANDY C # #ROLE_ANDY No no yes No Yes Yes
Sql> Select COUNT (*) from dba_objects;
COUNT (*)
----------
72629


Public role empowerment to a local user:
Sql> alter session set CONTAINER=PDB01;
Sql> Grant C # #role_andy to Andy;
C:\users\andy>sqlplus Andy/[email PROTECTED]:1521/PDB01
Sql> Select COUNT (*) from dba_objects;
COUNT (*)
----------
72629


Iv. creating a local role
Local roles are created in a similar manner to the pre-12 C database. Each PDB can have a matching name because the local scope is limited to the role of the current PDB.
The following conditions must be met.
Conditions:
1. You must be connected to a user with the Create role permission.
2. If you are connected to a public user, the container must be set to local PDB.
3. The role name for the local role does not have to be prefixed with "c##" or "c##".
4. The role name must be unique within the PDB.
5. Local roles can be assigned to public users (scoped to the PDB operation, without affecting CDB permissions) or Local users.
Operation:
Sql> alter session set CONTAINER=PDB01;
Create role Pdb_role;
Grant SELECT on Dba_tables to Pdb_role;


Local role empowering to a common user:
Grant Pdb_role to C # #andy;
C:\users\andy>sqlplus C # #andy/[email protected]:1521/pdb01
Sql> Select COUNT (*) from Dba_tables;
COUNT (*)
----------
2106
C:\users\andy>sqlplus C # #andy/[email protected]:1521/andycdb
Sql> Select COUNT (*) from Dba_tables;
Select COUNT (*) from Dba_tables
*
ERROR at line 1:
Ora-00942:table or view does not exist


Local role empowerment to a local user:
Grant Pdb_role to Andy;
C:\users\andy>sqlplus Andy/[email PROTECTED]:1521/PDB01
Sql> Select COUNT (*) from Dba_tables;
COUNT (*)
----------
2106

ORACLE12C Multi-tenant Manage users, roles, permissions