Oracle 12c New Features

Online Oracle Training

LESSON 16

How to manage users in a CDB (Multitenant Container) and PDBs (Pluggable Databases)

There are two types of users: common users and local users.

The common users will be created in the root container and start with prefix of c## or C##. To create a local use, you need to login to the pluggable database using net service name and create user not start with c## or C## prefix.

Let us create a common user in the root container and see how other pluggable database containers will see it.

Create a common user in the root container.

# . ./.profile

# sqlplus /nolog

SQL> connect / as sysdba

--Just to make sure where you are

Get the container name

SQL> show con_name

SQL> CREATE USER c##user4all IDENTIFIED BY mypass;

Let us view the default tablespaces were assigned for it in all containers.

SQL> SELECT username, default_tablespace, temporary_tablespace, con_id

   2   FROM cdb_users

   3   WHERE username = "c##user4all"

   4   ORDER by 4;

Notice that the default tablespace and temporary tablespace for the mypdb1 container are not USERS and TEMP because we changed them.

Notice that you cannot create a local user in the root container. A local user or role is not allowed in CDB$ROOT.

A common user can access to all containers with the same password unless you go to different container and change or alter its password for that container.

SQL> connect c##user4all/mypass@mypdb1

Connected.

SQL> connect c##user4all/mypass@mypdb2

Connected.

Changing its password in the mypdb1 container

SQL> connect sys/mypass@mypdb1 as sysdba

SQL> ALTER USER c##user4all IDENTIFIED BY nomypass;

To create a local user (mylocal_user)

Connect to the container then create a local user.

SQL> connect sys/mypass@mypdb1 as sysdba

Create a local user

SQL> CREATE USER mylocal_user IDENTIFIED BY mypass;

User created.

Check mylocal_user default tablespace and temporary tablespace.

SQL> SELECT username, default_tablespace, temporary_tablespace

   2   FROM dba_users

   3   WHERE username = "mylocal_user";

To change its default tablespace to USERS

SQL> ALTER USER mylocal_user DEFAULT TABLESPACE users;

Check mylocal_user default tablespace and temporary tablespace again.

SQL> SELECT username, default_tablespace, temporary_tablespace

   2   FROM dba_users

   3   WHERE username = "mylocal_user";

Notice that the default tablespace was changed to users.

Good Luck!