Common Users and Local Users in a CDB
Two Kinds of Users
| Type | Exists in | Name rule | Use for |
|---|---|---|---|
| Common user | Root and every PDB | Must start with C## | DBAs and tools that manage many PDBs |
| Local user | One PDB only | Any valid name | Applications and normal users |
Simple example: A common user is a building manager with a master key to every apartment. A local user is a resident who only has a key to their own door.
Create a Common User
sql
-- connect to CDB$ROOT as SYSDBA
SHOW CON_NAME
CREATE USER c##dba_admin IDENTIFIED BY "AdminPwd#1" CONTAINER = ALL;
GRANT CREATE SESSION, SET CONTAINER TO c##dba_admin CONTAINER = ALL;
SET CONTAINER lets this user switch between PDBs. Without it, the user can log in but cannot move to other containers.
Create a Local User
sql
ALTER SESSION SET CONTAINER = FREEPDB1;
CREATE USER app_owner IDENTIFIED BY "AppPwd#1"
DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE TO app_owner;
Common mistake: Creating application users as common users in the root. Applications should always use local users inside their own PDB.
The Scope of a GRANT
| Grant statement | Applies to |
|---|---|
GRANT x TO c##u CONTAINER = CURRENT | Only the container you are in |
GRANT x TO c##u CONTAINER = ALL | Root and all PDBs |
Common Roles
sql
CREATE ROLE c##monitoring CONTAINER = ALL;
GRANT SELECT ANY DICTIONARY TO c##monitoring CONTAINER = ALL;
GRANT c##monitoring TO c##dba_admin CONTAINER = ALL;
List Users and Their Type
sql
SELECT con_id, username, common, account_status
FROM cdb_users
WHERE oracle_maintained = 'N'
ORDER BY con_id, username;
common column | Meaning |
|---|---|
| YES | Common user |
| NO | Local user |
Connecting
bash
# common user in the root
sqlplus c##dba_admin/AdminPwd#1@localhost:1521/FREE
# local user in a PDB
sqlplus app_owner/AppPwd#1@localhost:1521/FREEPDB1
Remember: Connecting to the CDB service (
FREE) puts you in the root. Connecting to a PDB service (FREEPDB1) puts you in that PDB.
Conclusion
Common users (C##) manage many containers and local users live inside one PDB. Keep applications local and give common users only the privileges they need.
Quick Summary: C## = common, exists everywhere. Local = one PDB. Apps use local users.

