Creating USER Accounts and Connecting to Container Database and Pluggable databases in Multitenant Environment

You can create 2 types of users in Multitenant databases

1. Common User
2. Local User

Common User:- A common user is created in root CDB. Common user can connect to root CDB and all PDB’s including future PDB’s which you may plug.

You should not create any objects in Common User account as it will cause problems while connecting and disconnecting PDB”s

Local User:- A local user is created in a PDB database and he can connect and has privileges in that PDB only.

Creating a common user account.

SQL> create user c##admin identified by adminpwd container=all;

SQL> grant connect,resource to c##admin;

SQL> conn c##admin/adminpwd

Creating a Local User

SQL> alter session set container=icapdb1;

SQL> create user scott identified by tiger quota 50M on users;
SQL> grant connect,resource to scott;

To connect to pluggable database icapdb1 as scott user you have to connect through EZConnect method or through TNSNames

To Connect through EZConnect

SQL> conn scott/tiger@192.168.50.129/icapdb1

To connect through TNSNames you have to add entry in the TNSNames.ora file.

Open TNSNames.ora file add the following entry

$ cd $ORACLE_HOME/network/admin
$ vi tnsnames.ora

icapdb1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.129)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = icapdb1)
)
)

Now to connect

$sqlplus scott/tiger@icapdb1