Management of Users and privilleges in Oracle Database 12c / 19c in container & Pluggable database

 [oracle@LNX-D1V-ORCL01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 15 07:59:58 2024

Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB                            READ WRITE NO

 

Create the common user using the CONTAINER clause

SQL> create user c##user1 identified by c##user1 container=all;

User created.

SQL> grant create session to c##user1 container=all;

Grant succeeded.

Create the common user using the default CONTAINER setting

SQL> create user c##user2 identified by c##user2;

User created.

SQL> grant create session to c##user2;

Grant succeeded.

Create Local User in Specific Pluggable Database

While creating a local user the following requirements must all be met.

• Must be connected to a user with the create user privilege.
• Username for the local user must not be prefixed with “C##” or “c##”.
• Username must be unique within the PDB.
• You can either specify the container=all clause, or omit it, as this is the default setting when the current container is a PDB.

Create the local user using the CONTAINER clause

SQL> create user user1 identified by user1 container=current;

User created.

SQL> grant create session to user1 container=current;

create user user2 identified by user2 container=current;

grant create session to user2  container=current;

Create the local user using the default CONTAINER setting

Switch to Pluggable db = pdb

create user user3 identified by user3;

Role

Roles can be common or local. All Oracle-supplied roles are common and therefore available in the root container and all PDBs. Common roles can be created, provided the following conditions are must.

• You must be connected to a common user with create role and the set container privileges granted commonly.
• The current container must be the root container.
• The role name for the common role must be prefixed with “C##” or “c##” and contain only ASCII or EBCDIC characters.
• The role name must be unique across all containers.
• The role is created with the container=all clause

Create common role

SQL> conn / as sysdba

Connected.

SQL> create role c##role1;

Role created.

SQL> grant create session to c##role1;

Grant succeeded

Grant it to a common user

SQL> conn / as sysdba

Connected.

SQL> grant c##role1 to c##user1 container=all;

Grant succeeded.

SQL> alter pluggable database all open;

Pluggable database altered.

create role c##role1;

create role c##role2;

grant create session to c##role1;

grant create trigger to c##role2;

Grant common role to common user

==From cdb$root

grant c##role1 to c##user1 container=all;

grant c##role1 to c##user1 ;

Local Role

Local roles are created in a similar manner to pre-12c databases. Each PDB can have roles with matching names, since the scope of a local role is limited to the current PDB. The following conditions are must.

• Must be connected to a user with the create role privilege.
• If you are connected to a common user, the container must be set to the local PDB.
• Role name for the local role must not be prefixed with “C##” or “c##”.
• Role name must be unique within the PDB.

Create local role

Login to pdbs

create role role1;

create role role2;

grant create session to role1;

grant create trigger to role2;

Grant local role to common user

==From pdb

grant role1 to c##user1;

Grant local role to local user

grant role1 to user1;

To check common user name in root container database

By default it is 36 users that are common. All users in cdb are common.

select username from dba_users;

To check common user in pdb 

All common user in cdb will be available in pdb (local db). i.e 41 rows

select username from dba_users where common='YES';

To check local user in pdb  

By default there will be only one local user i.e. PDBADMIN

select username from dba_users where common='NO';