12c, CDB, Multitenant, PDB, Users

Container Clause for Common Users in Oracle Multitenant Databases

Leave a comment

In Oracle 12c multitenant databases, a user defined in the CDB is considered a Common user.    Common users with correct privileges can access all PDBs in the CDB.   The restriction of which database a user can and cannot access in the multitenant database architecture is governed by the CONATINER clause.  This article looks at the creation of Common users and the use of the container clause to allow access to multiple PDBs.

 

  1. Logon to your server as the oracle software owner.

 

[larry@linux2 ~]$ su – oracle

Password:

Last login: Wed Jul 26 10:07:02 EDT 2016 on pts/2

[oracle@linux2 ~]$

 

  1. Logon to oracle as the sys user.

 

[oracle@linux2 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 26 14:30:43 2016

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 

SQL>

 

  1. Create the user c##cdb_dba. NOTE: users names in the root container must begin with c##… or C##…

 

SQL> create user c##cdb_dba identified by password;

 

User created.

 

  1. Just as in a traditional Oracle database a user has no privileges until they are granted and what a user can do is limited by these privileges. But an additional clause exists in CDB and PDB which determines where a common user can use their privileges, this clause is CONTAINER.

 

  1. In this first example, we will use the clause CONTAINER=CURRENT, meaning that the privilege is available in the root container only.

SQL> grant all privileges to c##cdb_dba container=CURRENT;

 

Grant succeeded.

 

 

  1. Now logon to the user c##cdb_dba and attempt to create another user account and see that you can connect to the root containe.

 

SQL> connect c##cdb_dba/password

Connected.

SQL>

 

 

  1. In this database we have two PDBs: PDB1 and PDB2,   attempt to connect to both PDBs and not that the operation fails.   But you can reconnect to root CDB  CDB$ROOT.

 

SQL> alter session set container=PDB1;

ERROR:

ORA-01031: insufficient privileges

 

 

SQL> alter session set container=PDB2;

ERROR:

ORA-01031: insufficient privileges

 

 

SQL> alter session set container=CDB$ROOT;

 

Session altered.

 

SQL>

 

  1. Reconnect as the sys user and grant “all privileges” with a container = ALL. Meaning that you can connect to both root, pdb1 and pdb2.   At the root level you can specify ALL and CURRENT for the clause CONTAINER.  Now you can connect to all containers in PDB

 

SQL> grant all privileges to c##cdb_dba container=all;

 

Grant succeeded.

 

SQL> connect c##cdb_dba/password

Connected.

SQL> alter session set container=pdb1;

 

Session altered.

 

SQL> alter session set container=pdb2;

 

Session altered.

 

SQL> alter session set container=cdb$root;

 

Session altered.

 

SQL>

 

 

Larry Catt

OCP

Leave a Reply