Connecting to Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 2 (12.2)

Connecting to a Container Database (CDB)

Connecting to the root of a container database is the same as  database instance.

On the database server you can use OS Authentication.

[oracle@ram ~]$ export ORACLE_SID=dbwr
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 16 02:10:26 2018

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.
SQL>

You can connect to other common users in similar way.

SQL> conn system/oracle
Connected.
SQL>

The v$services views can be used to display available services from the database.

SQL> col name for a20
SQL> col pdb for a20
SQL> select name , pdb from v$services order by name;

NAME                     PDB
--------------------   --------------
SYS$BACKGROUND           CDB$ROOT
SYS$USERS                CDB$ROOT
dbwr.carrierzone.com     CDB$ROOT
dbwrXDB                  CDB$ROOT
pdb1.carrierzone.com     PDB1
pdb5.carrierzone.com     PDB5
pdb6.carrierzone.com     PDB6

7 rows selected.

The lsnrctl utility allows you to display the available services from the command line

[oracle@ram ~]$ lsnrctl service

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 16-OCT-2018 02:19:58

Copyright (c) 1991, 2016, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ram.localdomain.com)(PORT=1521)))
Services Summary...
Service "777d95e41a572d82e053dd971d40e224.carrierzone.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "77813d4b379738f5e0536501a8c05864.carrierzone.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "77f4b206457338a1e0536501a8c0ed42.carrierzone.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "dbwr.carrierzone.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "dbwrXDB.carrierzone.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: ram.localdomain.com, pid: 32509>
(ADDRESS=(PROTOCOL=tcp)(HOST=ram.localdomain.com)(PORT=58780))
Service "pdb1.carrierzone.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "pdb5.carrierzone.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "pdb6.carrierzone.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully.

Connections using services

[oracle@ram admin]$ !sq
sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 16 02:30:30 2018

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> conn system/oracle@dbwr
Connected.
SQL>

The connection using a TNS requires an entry in the “$ORACLE_HOME/network/admin/tnsnames.ora” file

[oracle@ram admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DBWR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dbwr.carrierzone.com)
)
)
Displaying the Current Container

The show con_name and show con_id commands in SQL*Plus display the current container name and ID respectively.

SQL> sho con_name

CON_NAME
-----------------
CDB$ROOT
SQL> show con_id

CON_ID
------------------
1
SQL>

They can also be retrieved using the sys_context function.

SQL> select sys_context('userenv','con_name') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
---------------------------------------------------
CDB$ROOT

SQL> select sys_context('userenv','con_id') from dual;

SYS_CONTEXT('USERENV','CON_ID')
---------------------------------------------------
1

SQL>
Switching Between Containers

When logged in to the CDB as an appropriately privileged user, the alter session command can be used to switch between containers within the container database.

SQL> conn / as sysdba
Connected.
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> alter session set container = pdb5;

Session altered.

SQL> sho con_name

CON_NAME
------------------------------
PDB5

SQL> alter session set container =cdb$root;

Session altered.

SQL> sho con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>

Connecting to a Pluggable Database (PDB)

Direct connections to pluggable databases must be made by using a service. Each pluggable database automatically registers a service with the listener.

This is how any application will connect to a pluggable database, as well as administrative connections.

[oracle@ram ~]$ export ORACLE_SID=dbwr
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 16 02:47:52 2018

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> conn / as sysdba
Connected.
SQL> conn system/oracle@pdb5
Connected.
SQL>

The connection using a TNS alias requires an entry in the “$ORACLE_HOME/network/admin/tnsnames.ora” file, such as the one shown below.

[oracle@ram admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PDB5 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb5.carrierzone.com)
)
)

PDB users with the SYSDBA, SYSOPER, SYSBACKUP, or SYSDG privilege can connect to a closed PDB.

All other PDB users can only connect when the PDB is open. As with regular databases, the PDB users require the create session privilege to enable connections.

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Manage Users and Privileges For Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 2 (12.2)

Manage Users and Privileges:

When connected to a multitenant database the management of users and privileges is a little different to traditional Oracle environments. In multitenant environments there are two types of user.

  • Common User : The user is present in all containers (root and all PDBs).
  • Local User : The user is only present in a specific PDB. The same username can be present in multiple PDBs, but they are unrelated.

Likewise, there are two types of roles.

  • Common Role : The role is present in all containers (root and all PDBs).
  • Local Role : The role is only present in a specific PDB. The same role name can be used in multiple PDBs, but they are unrelated.

Some DDL statements have a container clause added to allow them to be directed to the current container or all containers.

Create Common Users :

While creating a common user the following requirements must all be met.

  • Must be connected to a common user with the create user privilege.
  • The current container must be the root container.
  • The username for the common user must be prefixed with “C##” or “c##” and contain only ASCII or EBCDIC characters.
  • Username must be unique across all containers.
  • The default tablespace, temporary tablespace , quota and profile must all reference objects that exist in all containers.
  • You can either specify the container=all clause, or omit it, as this is the default setting when the current container is the root.

now we can see how to create common users with and without the container clause from the root container.

[oracle@ram ~]$ ps -ef | grep pmon
oracle 37524 34096 0 18:42 pts/0 00:00:00 grep pmon
[oracle@ram ~]$ export ORACLE_SID=dbwr
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 16 18:42:58 2018

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.
SQL> conn / as sysdba
Connected.

Create the common user using the CONTAINER clause

SQL> create user c##user1 identified by password1 container=all;

User created.

SQL> grant create session to c##user1 container=all;

Grant succeeded.

Create the common user using the default CONTAINER setting

SQL> create user c##user2 identified by password2;

User created.

SQL> grant create session to c##user2;

Grant succeeded.
Create Local Users:

While creating a local user the following requirements must all be met.

  • Must be connected to a user with the create user privilege.
  • Username for the local user must not be prefixed with “C##” or “c##”.
  • Username must be unique within the PDB.
  • You can either specify the container=all clause, or omit it, as this is the default setting when the current container is a PDB.
Switch container while connected to a common user
SQL> conn / as sysdba
Connected.
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> alter session set container =pdb5;

Session altered.
Create the local user using the CONTAINER clause
SQL> create user user3 identified by password3 container=current;

User created.

SQL> grant create session to user3 container=current;

Grant succeeded.
Connect to a privileged user in the PDB
SQL> conn system/oracle@pdb5 
Connected.
Create the local user using the default CONTAINER setting
SQL> create user user4 identified by password4;

User created.

SQL> grant create session to user4;

Grant succeeded.
Create Common Roles:

Roles can be common or local. All Oracle-supplied roles are common and therefore available in the root container and all PDBs. Common roles can be created, provided the following conditions are must.

  • You must be connected to a common user with create role and the set container privileges granted commonly.
  • The current container must be the root container.
  • The role name for the common role must be prefixed with “C##” or “c##” and contain only ASCII or EBCDIC characters.
  • The role name must be unique across all containers.
  • The role is created with the container=all clause

Now we can see how to create a common role and grant it to a common and local user.

Create the common role
SQL> conn / as sysdba
Connected.
SQL> create role c##role1;

Role created.

SQL> grant create session to c##role1;

Grant succeeded.
Grant it to a common user
SQL> conn / as sysdba
Connected.
SQL> grant c##role1 to c##user1 container=all;

Grant succeeded.

SQL> alter pluggable database all open;

Pluggable database altered.
Grant it to a local user
SQL> alter session set container =pdb5;

Session altered.

SQL> grant c##role1 to user3;

Grant succeeded.

SQL>
Create Local Roles:

Local roles are created in a similar manner to pre-12c databases. Each PDB can have roles with matching names, since the scope of a local role is limited to the current PDB. The following conditions are must.

  • Must be connected to a user with the create role privilege.
  • If you are connected to a common user, the container must be set to the local PDB.
  • Role name for the local role must not be prefixed with “C##” or “c##”.
  • Role name must be unique within the PDB.

we can see now how to create local a role and grant it to a common user and a local user.

SQL> conn / as sysdba
Connected.
SQL>
Switch container
SQL> alter session set container = pdb5;

Session altered.

Alternatively , we can connect pluggable database through local  or common user  with PDB service.

Create the common role
SQL> create role role1;

Role created.

SQL> grant create session to role1;

Grant succeeded.
Grant it to a common user
SQL> grant role1 to c##user1;

Grant succeeded.
Grant it to a local user
SQL> grant role1 to user3;

Grant succeeded.
Granting Roles and Privileges to Common and Local Users:

The basic difference between a local and common grant is the value used by the container clause.

Common grants
SQL> conn / as sysdba
Connected.
SQL> grant create session to c##user1 container=all;

Grant succeeded.

SQL> grant create session to c##role1 container=all;

Grant succeeded.

SQL> grant c##role1 to c##user1 container=all;

Grant succeeded.

SQL>
Local grants
SQL> conn system/oracle@pdb5
Connected.
SQL> grant create session to user3;

Grant succeeded.

SQL> grant create session to role1;

Grant succeeded.

SQL> grant role1 to user3;

Grant succeeded.

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba