CONTAINER clause in Oracle Multi-Tenant Environment : Part I

Oracle 12c Multitenant added an optional CONTAINER clause to various DDL statements, by which one could specify the scope of execution of a DDL statement and whether it would effect one container or all containers across the CDB. With the advent of common users|roles|profiles in consolidated database, this clause assumes significance, as it allow a CDB level Database Administrator to manage these common entities without going into each individual PDBs.

Simple Use Case
--------------------
Suppose, the CDB administrator decides to lock out WMSYS user across the consolidated database. Since WMSYS is an Oracle supplied common user present in all the containers, one obvious way to accomplish this would be to log into each PDB and lock it. But it is somewhat cumbersome and does not cover any future PDBs, which might get plugged into the CDB. So the DBA could do something like the followings

  conn system/password@rootorcl
  alter session set container=MARKETING;
  alter user wmsys account lock;
  alter session set container=SALES;
  alter user wmsys account lock;
  alter session set container=HR;
  alter user wmsys acccount lock;

What if we would accomplish this in one single statement ?? Yes, that's what CONTAINER clause gives you. The ability to manage these common entities by sitting in CDB$ROOT and without iterating through each of the PDBs underneath.

  conn system/password@rootorcl
  alter user wmsys account lock CONTAINER=ALL

and the best part is, in future, if a new Pluggable database container get plugged in to the CDB, this statement will get executed inside the newly plugged PDB and WMSYS account will be locked in the new PDB as well (This gets accomplished via a small feature call "PDB Sync", which I hope to cover in my future blogs).

For entities local to a Pluggable database i.e, LOCAL users|roles|profiles, this clause even when specified will simply be ignored. However,  we shall see that CONTAINER=CURRENT also allows a PDB level administrator to set certain attributes for common users, like resource profile, proxy authorization etc.

Important Facts/Usage Notes
---------------------------------

• You can't specify this clause when connected to a non-Multitenant environment (Any attempt to do so will result into an ORA-65117 error)
• This clause can be specified when connected either to CDB$ROOT container or a Pluggable Database container.
• The only values allowed for container clause are CURRENT and ALL, raises ORA-65013 error otherwise.
• CONTAINER=ALL from within a Pluggable database container is not allowed (Any attempt to do so will result into an ORA-65050 error) and can be specified ONLY when connected to CDB$ROOT container
• CONTAINER=CURRENT can be specified both within a Pluggable database container as well as CDB$ROOT container.
• The clause is optional and when omitted, defaults to either CURRENT or ALL, depending on following two things
• A) Type of DDL statement being executed
• B) The container where the DDL statement is being executed
• The only DDL statements which accept CONTAINER clause are {CREATE|ALTER} {USER|ROLE|PROFILE} and ALTER SYSTEM.

In a subsequent post, I will go over each of these DDL statements along with usage examples.