PDB_DBA in Oracle

Back to: Oracle DBA Tutorials

PDB_DBA in Oracle

In this article, I am going to discuss Understanding PDB_DBA in Oracle with Examples. Please read our previous article where we discussed Role Granted to Another Role in Oracle with Examples.

Understanding PDB_DBA in Oracle

Now, we are going to learn about PDBA_DBA. Especially when we are using DBCA for creating pluggable databases. There is a role called PDB_DBA. Now, we will be creating a new pluggable database and then we will be creating a new pluggable database admin to the database using the tool DBCA.

Creating Pluggable Database PDB10:

Step 1: Open DBCA.

Open the Linux machine and open the terminal and enter the command “DBCA”.

If you enter the command “DBCA” then there will be a popup as above opening the DBCA.

Step 2: Manage Pluggable Database

Once the DBCA is opened you will be able to see the below options. Just select the option “manage pluggable database” and click Next.

Step 3: Create a New pluggable Database

You will see the below options. If you need to create a pluggable database select “create a pluggable database” or if you need to drop a database select “delete pluggable database”. We are selecting “create pluggable database” and clicked Next.

Step 4: Select the container database.

Once you select the option to create a pluggable database and click next you will get the below page asking to select the container database. Therefore, we have only one container database we will be selecting the container database. We are selecting the JAYADB database. Leave the username and password column. Click Next.

Step 5: Create a pluggable database from PDB$SEED.

Once you click the next DBCA bring to the below page asking to select the pluggable database. You can select any database but we are using PDB$SEED since it is a template database to create multiple pluggable databases. Click Next.

Step 6: Pluggable Database Details.

Once you select the template database and click next the DBCA asks you to provide the pluggable database name and administrator username and password for the administrator. We are giving the pluggable database name as PDB10 and username as pdb10admin and password for the user pdb10admin.

Once you click next you will see a popup that the password doesn’t match the oracle requirements. Ignore the popup and click next.

Step 7: Create a pluggable database summary.

The Storage is a file system and the Database location is the default that is taken by DBCA and then it asks to create the Default user tablespace. Select the option. This is the default option. Click Next.

Step 8: Create a Database Summary.

You can see create database summary which displays the pluggable database name as PDB10 and the pluggable database source as default and datafile location.

Click finish and the process of creating pluggable database starts.

Wait for a few minutes until the process becomes 100%.

The database is successfully created. So, we have created the pluggable database PDB10 and the admin user pdb10admin user.

Checking the Privileges:

Let us go ahead and check the privileges that were assigned to the pdb10admin user.

Step 1: Open Linux virtual machine and connect to the database as “sqlplus / as sysdba”. Let’s go ahead and check the database name.

Step 2: Check the existence of the pdb10admin user and check privileges.

Let us go ahead and check the existence of the pdb10admin user. We have to check this using the view cdb_users because we have created this user in a pluggable database and we have connected to the CDVB$ROOT database.

Query: select * from cdb_users where username=’pdb10_admin’;

We can see the user is present but this user is not a common user because we have created this user in the container database. Now, that the user is present, let us check the CBD privileges whether there are any direct sys privileges granted or not.

Query: select * from cdb_sys_privs where grantee=’PDB10_ADMIN’;

We don’t see any direct privileges assigned to this user. Let’s check if there are any roles assigned to this user using view cdb_role_privs

Query: select * from cdb_role_privs where grantee=upper(‘PDB10_AMDIN’);

We can see there is a role that is granted to the pdb10admin user. We can see the ADMIN_OPTION is yes. From this, we can conclude that the DBCA tool didn’t provide any system privileges directly. The tool DBCA granted a role called PDB_DBA to the user.

Step 3: Check Privileges for PDB_DBA.

Let us connect to the pluggable database pdb10 and check the privileges of the role PDB_DBA.

Command: alter session set container=pdb10;

Now, that we are in the pluggable database PDB10. Let’s check the system privileges for the role PDB_DBA.

Query: select * from role_sys_privs where role=’PDB_DBA’;

We can see the role PDB_DBA has two privileges like create a session and creating a pluggable database. Let’s check if there are any roles assigned to the role PDB_DBA using the view dba_role_privs.

Query: select * from dba_role_privs where GRANTEE=’PDB_DBA’;

Let’s check the privileges that are assigned to the role “CONNECT” using the view role_sys_privs.

Query: select * from role_sys_privs where ROLE=’CONNECT’;

Note: If we go through what we have seen. We get the idea that the tool DBCA has granted a role called PDB_DBA to the user pdb10admin and the role PDB_DBA has other roles like the container role. So, this is the same topic that we learned earlier about granting a role to another role. DBCA doesn’t grant dba privileges to the user. We need to grant these dba privileges separately by logging to the CDB$ROOT database as sysdba and granting dba privileges to pdb10admin.

In the next article, I am going to discuss Admin Option in Oracle with Examples. Here, in this article, I try to explain Understanding PDB_DBA in Oracle with Examples. I hope you enjoy this Understanding PDB_DBA in Oracle article.