There are two types of users as following:
Local User
- A user in a non-CDB maps to a local user in a PDB.
- A local user is defined in the PDB’s own data dictionary—and so is not known outside of
- that PDB.
- A local user can connect only to the PDB where it is defined.
- A local user is specific to a particular PDB and owns a schema in this PDB.
- According to the privileges granted, a user can work on the application data within the
- PDB or with other PDBs’ application using database links. Moreover, there cannot be any
- local users defined in the root.
Common User
- A common user is defined in the root’s data dictionary.
- Only common users can be defined in the root: Creating a common user allows the CDB administrator to create at once a user that is replicated in each PDB.
- A common user is known, not only where it is defined in the root, but also in every PDB that belongs to the CDB.
- A common user can perform administrative tasks specific to the root or PDBs, such as plugging and unplugging PDBs, starting up the CDB, or opening a PDB when granted the proper privileges.
List all common users
SQL> set pagesize 50
SQL> col username form a25
SQL> select distinct username from cdb_users where common = 'YES';
USERNAME
-------------------------
SPATIAL_WFS_ADMIN_USR
OUTLN
CTXSYS
SYSBACKUP
ORACLE_OCM
APEX_PUBLIC_USER
MDDATA
GSMADMIN_INTERNAL
SYSDG
ORDDATA
DIP
SYSKM
XS$NULL
OLAPSYS
SPATIAL_CSW_ADMIN_USR
SYSTEM
ORDPLUGINS
DBSNMP
ORDSYS
XDB
GSMCATUSER
SYS
APEX_040200
DVF
MDSYS
GSMUSER
FLOWS_FILES
AUDSYS
DVSYS
OJVMSYS
APPQOSSYS
SI_INFORMTN_SCHEMA
ANONYMOUS
LBACSYS
WMSYS
35 rows selected.
To view local user from CDB as following
SQL> col user_id form 99999
SQL> col username form a15
SQL> col name form a20
SQL> set linesize 250
SQL> SELECT *
2 FROM (
3 SELECT user_id, username, common, u.con_id, pdb.name, pdb.open_mode
4 FROM cdb_users u, v$pdbs pdb
5 WHERE u.con_id(+) = pdb.con_id)
6 WHERE common = 'NO'
7 OR common is null;
USER_ID USERNAME COM CON_ID NAME OPEN_MODE
------- --------------- --- ---------- -------------------- ----------
PDB3_CLONE MOUNTED
PDB3 MOUNTED
PDB2 MOUNTED
As you can see no user are shown and the PDBs are mounted state, we need to open the database to view the users.
SQL> alter pluggable database pdb2 open;
Pluggable database altered.
SQL> SELECT *
2 FROM (
3 SELECT user_id, username, common, u.con_id, pdb.name, pdb.open_mode
4 FROM cdb_users u, v$pdbs pdb
5 WHERE u.con_id(+) = pdb.con_id)
6 WHERE common = 'NO'
7 OR common is null;
USER_ID USERNAME COM CON_ID NAME OPEN_MODE
------- --------------- --- ---------- -------------------- ----------
102 PDB2_ADMIN NO 3 PDB2 READ WRITE
103 USER1_PDB2 NO 3 PDB2 READ WRITE
PDB3_CLONE MOUNTED
PDB3 MOUNTED
You can change session from CDB$ROOT to pluggable database by using following command. Once you are in that container you can perform the user admin as usual.
SQL> alter session set container=pdb2;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
PDB2
SQL> alter user user1_pdb2 account lock;
User altered.
SQL> alter user user1_pdb2 account unlock;
User altered.
SQL> create user test identified by oracle;
User created.
SQL> drop user test;
User dropped.