Creating the Application Schema in the Wrong Container
PRODCDB is a 19c multitenant database on a single instance, hosting 30 pluggable databases. Every PDB contains a schema named APP owned by an application account with the same password. Application deployments use one standard script that connects as SYSDBA to the root and runs CREATE USER app IDENTIFIED BY ... followed by GRANT CREATE SESSION, RESOURCE TO app; this now fails with ORA-65096. To unblock a release, a DBA rewrote it as CREATE USER c##app IDENTIFIED BY ... CONTAINER=ALL and GRANT CREATE SESSION, RESOURCE TO c##app CONTAINER=ALL, which succeeded. The application then connected to the APPSALES PDB through the appsales service using c##app and immediately received ORA-00942 on SELECT from ORDERS, even though ORDERS exists in that PDB and the account could log in. The application also depends on a role named APP_WRITE that already exists as a local role inside each PDB and carries the necessary object privileges.
1. Explain what ORA-65096 was telling the DBA, and how you would decide between a local and a common account for this application.
Read the worked answer
The session was connected to CDB$ROOT, so the object being created belonged to the root container. In the root you can only create common users, and a common user's name must begin with the common user prefix, by default C##, which is exactly what ORA-65096 reports. A local user is created by first switching container, ALTER SESSION SET CONTAINER = APPSALES, or by connecting with a service that resolves to that PDB, and then issuing a plain CREATE USER.
The decision does not hinge on the error; it hinges on identity. A common user is a single account that exists in every container, with one password and container-wide administration, and is appropriate for a DBA or a monitoring account that must reach all PDBs. Here, 30 PDBs each have an independent APP schema, independent data, and a deployment process that targets one PDB at a time. That is a textbook local user. Making APP common forces one password across all 30 PDBs, couples every schema's security to the others, and does nothing to give the account the local objects it needs.
If a single application really must span several PDBs with one identity, a common user is defensible, but then the privilege model must be designed for it up front rather than retrofitted.
2. The account can log in to APPSALES but gets ORA-00942 on ORDERS. What is missing, and how do you grant it correctly given that APP_WRITE is a local role?
Read the worked answer
Logging in proves CREATE SESSION propagated; it says nothing about object access. System privileges granted with CONTAINER=ALL apply across containers, but object privileges on a table inside APPSALES must be granted while connected to APPSALES, because the table only exists there. The local role APP_WRITE holds those grants for the local APP account, and the common user has never been granted it.
Two workable shapes exist. Grant the local role to the common user from inside the PDB, without CONTAINER=ALL: ALTER SESSION SET CONTAINER = APPSALES; GRANT APP_WRITE TO c##app; and repeat per PDB. Grants made inside a container affect only that container, so this is a set of local grants on a common identity. Alternatively, create a common role C##APP_WRITE and, inside each PDB, grant the object privileges on the application tables to that common role, then grant the role to c##app with CONTAINER=ALL. The second shape is cleaner for 30 PDBs but requires the object grants to be reissued in every container anyway.
Whichever you choose, do not try to use CONTAINER=ALL with a local role; CONTAINER=ALL changes must apply identically to all containers, and a role that exists in only some of them cannot be propagated that way.
3. How would you audit this across all 30 PDBs, and what password and account hygiene issues does the common-user design create?
Read the worked answer
From the root, use the container views and the CONTAINERS() clause rather than connecting to each PDB: CDB_USERS shows which accounts are common and which are local, with COMMON and ORACLE_MAINTAINED flags; CDB_ROLE_PRIVS, CDB_SYS_PRIVS, and CDB_TAB_PRIVS show grants with their container; V$PDBS and V$CONTAINERS show open mode and con_id. Inside a specific PDB, DBA_USERS and DBA_TAB_PRIVS reflect only that container, which is why the same query run in two places can disagree.
Hygiene matters because a common user is a single credential valid in every PDB, so a compromise reaches 30 databases at once, and a password change affects all of them simultaneously. Local accounts, by contrast, have per-PDB passwords and per-PDB profiles, and profile settings such as FAILED_LOGIN_ATTEMPTS and password verification are local unless created as common. Also confirm the common user is not accidentally a member of powerful common roles, and check whether SYSDBA-style common privileges were used as a shortcut.
The correct end state for this application is local APP accounts with local roles, one deployment path per PDB, and a small number of genuinely common accounts reserved for administration.