Pluggable Database Admin in Oracle

Back to: Oracle DBA Tutorials

Pluggable Database Admin in Oracle

In this article, I am going to discuss Pluggable Database Admin in Oracle with Examples. Please read our previous article where we discussed Sys User Example in Oracle.

Pluggable Database Admin in Oracle

Now, we will learn about the pluggable admin user. We already learned how to create an admin pluggable database.

Note: Open Linux virtual machine and start the listener and start the database. Once the database is started, open the SQL developer, and connect to the database as “sqlplus / as sysdba”. Let us go ahead and check the database name and the user.

Let us go ahead and connect to the pluggable database.

Command: alter session set container=jayapdb;

Now, we have connected to the pluggable database. Let us check the container id and the container name.

Command: show con_id

We have the container id as 3. Let us go ahead and check the tablespaces present in the pluggable database jayapdb.

Query: select * from v$tablespace;

There are these 5 tablespaces as a system, sysaux, undo, temp, users. Let us go ahead and create a new user and assign the pluggable database admin role to that user.

Statement: create user pdb1admin identified by goodluck default tablespace users temporary tablespace temp account unlock;

We are creating a new user called pdb1admin with a password as goodluck and we are unlocking the account.

Now, the user is created. Let us grant the dba roles to the user pdb1admin. We are going to grant create session and dba roles to pdb1admin.

Statement: grant create session, dba to pdb1admin;

We have granted all the privileges that are required for an administrator. Let us check the user status from dba_users.

Query: select * from dba_users where username =upper(‘PDB1ADMIN’);

This user pdb1admin is not a common user as we have created this user in the pluggable database jayapdb. Let us go ahead and check the privileges of the user pdb1admin. Open a terminal and connect to the database jayapdb as the user pdb1admin.

As we have logged in to the database, let us check the user and the container name.

We have connected to the pluggable database and the user is pdb1admin. Let us check the privileges now. Let us now check the session privileges of this user.

Query: select * from session_privs;

We can see 237 rows of system privileges. So, the user is a DBA who can create a user, and drop a table. The user can do everything but cannot connect back to the container database. Let us try to connect to the CDB$ROOT database.

Command: alter session set container =CDB$ROOT

We are unable to connect to the CDB$ROOT database because the user is only dba in the pluggable database but the user is not dba in other databases. So, this indicates that the pluggable database administrator will have access only to the pluggable database. The user cannot have any access to the root database.

In the next article, I am going to discuss Role Granted to Another Role in Oracle with Examples. Here, in this article, I try to explain Pluggable Database Admin in Oracle with Examples. I hope you enjoy this Pluggable Database Admin in Oracle with Examples article.