Common Roles in Oracle with Examples
In this article, I am going to discuss Common Roles in Oracle with Examples. Please read our previous article where we discussed Common Users and Common Privileges in Oracle with Examples.
Common Roles in Oracle with Examples
Until now we have learned about common users and common privileges. Now, let’s try to learn about the common roles. We have already learned that the role is just like a bracket. If we provide any privileges to that role the role will accept. Assigning the role to the user is easier than assigning the privilege to the user.
We have a container database. The container database has a root database. We already discussed that the common user can be created in the root database only. In order to create a common user, we need to use the prefix c##. If we want to create a common role, we have to use the same prefix. We have used the c## prefix to create a user. Similarly, we have to use c## to create a role.
Let’s assume the common role as the c##role, then we grant sysdba privilege to the c##role then we grant the c##role to the common user. Then the common user can connect to all the pluggable databases that are present in the container database.
Practically we don’t create a common user or common roles. Only the system user manages the admin roles and the pluggable databases have individual admin users.
Ways to grant a common role:
Grant the role to the user in all containers: Grant <common_role> to <common_user> container=all;
Grant the role to the user in one PDB only: Grant <common or local role> to <common or local user>;
Examples to Understand Common Roles 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
So, we are in the root database. All the roles in the root database are common roles. Let’s check the list of roles in the root database.
Query: select * from dba_roles;
We can see all the roles present in the output are common roles. The column common says YES.
Step 2: Create Role and Grant Privileges in Oracle
Let us try to create a role using the below statement.
Statement: create role test_role;
We are getting errors as there is no prefix mentioned before the role name as it is the root database, we have to mention the role name with a prefix as this role will be the common role. Let’s go ahead and check the prefix of the common user using “show parameter common_user_prefix”.
We can see the common_user_prefix is c##. So, the common role will be created using the prefix c##.
Statement: create role c##test_role container=all;
We can see the role c##test_role is created. This role can be used in all the pluggable databases. Let’s go ahead and grant some system privileges to this role.
Statement: Grant create session, create table to c##test_role container=all;
Now, the role c##test_role has two system privileges.
Step 3: Create a user and assign a role to the new user in Oracle.
Let us go ahead and create a common user and assign the common role to that user.
Statement: create user c##dark identified by goodluck;
We can see the user is created. Now, let’s go ahead and grant the role c##test_role to c##dark user.
Statement: Grant c##test_role to c##dark container=all;
As we can see we have granted the role c##test_role to the user c##dark.
Step 4: Test the connection for new user c##dark.
Click on the new connection and connect using c##dark. Try the connection to the jayadb container database.
Now, let’s try to check the connection to another pluggable database. We will be testing for the JAYAPDB database.
We can see the connection to JAYAPDB is also a success, as we have mentioned container=all in the grants that we have granted to the user c##dark. Let’s go ahead and create another common user.
Step 5: Create another user.
Let us go ahead and create another common user to test further common role privileges.
Statement: create user c##prakash identified by goodluck;
Now, that the user is created. We are not going to grant any privileges in the root database. Let’s grant some privileges in the pluggable database JAYAPDB.
Statement: alter session set container=jayapdb;
Now, that we have connected to the pluggable database. Let’s go ahead and grant the common role to the new user.
Statement: grant c##test_role to c##prakash;
Now, the user c##prakash can only create sessions in the pluggable database, because we have granted the privileges inside the pluggable database. C##prakash cannot connect to any other database other than JAYAPDB pluggable database. Let’s go ahead and check the user connectivity to the pluggable database.
We can see the connectivity of the user c##prakash is successful to the database JAYAPDB. Now, let’s try to connect to the database JAYADB.
We can see the connection of the user c##prakash to the database JAYADB is a failure. We can see the user c##prakash lacks create session privilege. If you are in the pluggable database and grant the common role, then the role works only in the pluggable database.
Note: These common users are only for our practice on how to manage roles and users. In General, we have a common user called a system that grants and revokes all the privileges to the users in the database. This user also creates admin users for the pluggable databases.
In the next article, I am going to discuss Information about User Accounts in Oracle with Examples. Here, in this article, I try to explain Common Roles in Oracle with Examples and I hope you enjoy this Common Role in Oracle article.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.