Common Users and Common Privileges in Oracle

Back to: Oracle DBA Tutorials

Common Users and Common Privileges in Oracle

In this article, I am going to discuss Common Users and Common Privileges in Oracle with Examples. Please read our previous article where we discussed Creating Roles in Oracle with Examples.

Common Users and Common Privileges in Oracle

We will discuss common users and local users. But we have already discussed common users and local users. You can refer to this doc for additional info. Common Users vs Local Users in Oracle – Dot Net Tutorials 

Apart from that, we will be learning additional information about common users and local users. We have a container database that consists of a root container. In order to create a common user, we must be logged in to the container database. Actually, we are not allowed to create local users in the root database. We can only create common users using the root database.

To create a common user, we must use a prefix something like c##. We can view this prefix using a command called “show parameter common_user_prefix”. So, in order to create a common user, we must be logged in to the root database and we need to create the common user using the prefix that is defined in the database. The statement will be as follows.

Statement: create user c##admin identified by password container=all;

Here container=all is optional. The statement will work fine if we didn’t include container=all. If we create a common user the user can log in to all the pluggable databases that are present in the container database.

The local user is a user that is created locally in the pluggable database. The user can only log in to the pluggable database. The user1 can only log in to PDB1. The user2 can only log in to PDB2.

Practically we do not create common users. Generally, common users that we use are only sysdba users. The system user manages everything. The system user creates the pluggable database admin pdb1admin to the pluggable databases.

Privileges in Oracle:

Common Privileges in Oracle:

If we have created a common user, initially the common user does not have any privileges. In order to provide the common privileges, we have to mention container=all. Because container=all allows the user to connect to all the pluggable databases.

Statement: grant create session to c##admin container=all;

Private Privileges in Oracle:

These are the privileges that are associated only with the pluggable database. If the user is granted private privilege, then he can access that privilege only in that pluggable database.

Examples to Understand Common Users and Common Privileges in Oracle

Note: Open the Virtual machine and start the database. Once the database is started check the listener and that all the services are up and running. Check if the pluggable databases are running or not. Once everything is fine Open SQL developer and connect to the database as sqlplus / as sysdba. We will be performing the task step-wise.

Step 1: Connect to the pluggable database.

Let us check the current container.

Command: show con_name

Let us check the list of users using the table dba_users

Query: select * from dba_users;

We can see all the users present in the container database. All these users are common users. If we can go ahead and check the common column.

We can see all the users are common users. We cannot create a local user in the container database. Let us try to create a local user.

Step 2: Create a user in the container database in Oracle.

Let us try to create a user with the username jaya.

Statement: create user jaya identified by goodluck;

We can see the error “invalid common user or role name”. this error indicates that we can only create only common user. In order to create a common user, we must the prefix to create the common user. Let us try to check the prefix using the command “show parameter common_user_prefix”.

We can see the prefix is c##. Let us insert that prefix and try to create the user.

Statement: create user c##jaya identified by goodluck container=all;

We can see the user is created with the prefix c##. Note that the container =all is purely optional.

Step 3: Grant privileges to the common user in Oracle.

After creating the common user, the common user c##jaya does not have any privileges assigned to the c##jaya. Let us go ahead and grant the create session privilege to the user c##jaya.

Statement: grant create session to c##jaya;

As we have granted the create session, the user c##jaya can only log in to the container database JAYADB. Let us check that by opening the SQL developer and connecting to the database JAYADB using the c##jaya user.

We can see the connection to c##jaya user is successful to the JAYADB database. Let us now try to connect to the pluggable database JAYAPDB.

There is an error connecting to the pluggable database JAYAPDB using the user c##jaya as the user lacks create session privilege. Let us try to grant the create session privilege with container=all and then try to check the connection.

We have granted the create session privilege to user c##jaya with container=all, let us try to check the connection again.

We can see now the status of the connection is a success. So, we understood that if we create a common user then if the common user needs to connect to all the pluggable databases, we have to include container=all in any grant statement.

In the next article, I am going to discuss Common Roles in Oracle with Examples. Here, in this article, I try to explain Common Users and Common Privileges in Oracle with Examples and I hope you enjoy this Common Users and Common Privileges in Oracle article.