Common Users vs Local Users in Oracle

Common Users vs Local Users in Oracle

In this article, I am going to discuss Common Users vs Local Users in Oracle Database. Please read our previous article where we discussed Oracle Data Dictionary. This is another important concept for oracle databases that were released after 12c and future releases.

Common Users vs Local Users in Oracle

Common User: A common user is a database account that is created in the root container database and is inherited by all the PDBs in the CDB, including future PDBs. This defines a user who is created to commonly use in both container and the pluggable database. For example, user accounts like sys user and system user.

Note: A common user cannot have the same name as any local user across all the PDBs.

Oracle supplied administrative accounts such as SYS user and SYSTEM user are common users. We can also create a common user on our own but we need to use a user-defined prefix like C## for example C##dotnet. The user-defined prefix can be defined in a parameter called common_user_prefix.

Local User: It is a database user created in a specific pluggable database. We have already created a local user named hr in JAYAPDB. We can create the same local accounts with the same password in one or more than one pluggable database.

Example:

HR user in PDB called PDB1
HR user in PDB called PDB2
HR user in PDB called PDB3

Note: We can create an application based on the pluggable database. We can create schemas, tables, and indexes inside a pluggable database. We can clone this pluggable database to another pluggable database. In this way, we can create multiple same users in different pluggable databases.

Exercise:

Until now we have discussed the common users and local users. Now let’s have a practical exercise on this topic. Important Note: Before starting this exercise make sure that the database is started and the pluggable database is opened.

Command: alter pluggable database JAYAPDB open;

Open SQL Developer and connect to the database using sys user as sysdba.

Common Users:

Step 1: Let us first check to which database we have logged in. The same command we are using since the beginning.

Command: show con_name.

Common Users

So, we have logged in to the container database. The next step is to check the list of common users.

Step 2: Checking the list of common users

We have a data dictionary named cdb_users where this data dictionary shows users present in both container and the pluggable database. The column username shows the username of the account and the column common shows whether the account is the common user or not and the con_id shows the container id where the user is present. Let’s execute the query.

Query: select username,common,con_id from cdb_users order by username;

Checking the list of common users

We have the user ANONYMOUS and this user is a common user because this user exists in both the container database as well as the pluggable database. We found it using the con_id. It has con_id as 3 and con_id as 1. Let’s take another user CTXSYS and this user also has the con_id as 3 and another con_id as 1. If you scroll down a little bit, you will find the user hr. This user hr is not a common user because it is created only in the pluggable database. It has con_id as 3. There is no other con_id for the user hr and the common column is indicated as NO.

Common Users vs Local Users in Oracle Database

We have a common user and these common users will be shared among all the pluggable databases. Even if we create any new pluggable database in the future the common users will be replicated to the new pluggable database. Let us run another query to check the number of common users present.

Query: select distinct(username) from cdb_users where common=’YES’;

Here we are using distinct because distinct doesn’t show the duplicates of the usernames.

Common Users vs Local Users in Oracle Database

Let’s check the count of users.

Common Users vs Local Users in Oracle Database

We have 36 common users present in both databases. We have listed the number of common users. Now, let’s try to check the number of local users present.

Step 3: Checking the number of local users.

Until now we have queried with common=’YES’. Now let us try with common=’NO’ which is the list of local users.

Query: select username,common,con_id from cdb_users where common=’NO’ order by username;

Checking the number of local users

We have only two local users one is the hr user where the con_id is 3 and the other user PDBADMIN where the con_id is also 3. So, there are two local users which exist in con_id 3. These local users will be restricted to only the pluggable database. We can create these accounts on multiple pluggable databases.

Step 4: Creating a common user.

We have discussed that a common user can be created using the user-defined prefix. The command show parameter common_user_prefix is used to obtain the user-defined prefix in the database.

Command: show parameter common_user_prefix

Creating a common user

Now we can the common_user_prefix is c##. So, if we need to create a common user the username needs to start with c##. Let’s try to create without the prefix and see how this works.

Command: create user JAY identified by JAY;

Common Users vs Local Users in Oracle Database

We are getting an error indicating “invalid common user or role name”. Now we understood that creating a common user without prefixes doesn’t work. So, we must use a user-defined prefix to create a common user.

Command: CREATE USER C##JAY IDENTIFIED BY JAY;

Common Users vs Local Users in Oracle Database

We can see the user is created. So, it’s clear that we need to use the user-defined prefix to create the common user. Now let’s check whether the user is created and present in the list of common users. It’s the same query we used to check the list of common users.

Query: select distinct(username) from cdb_users where common=’YES’;

Common Users vs Local Users in Oracle Database

So, there is a user C##JAY common user.

Note: If we create any pluggable databases in the future no need to create common users individually. These common users will be applied automatically.

Local Users in Oracle:

We have seen the creation of common users. Now let’s have a look at the local user. A local user is just a normal user we generally create and work which is restricted to its own database. This is created in the individual database separately.

Step 1: Connect to the pluggable database.

Until now we worked in the container database. Let’s now move from the container database to the pluggable database.

Command: alter session set container=JAYAPDB;

Connect to the pluggable database

Now we have moved to the container database. Let’s start the creation of the local user.

Step 2: Creation of local user.

We have moved from the container database to the pluggable database. The creation of the local user doesn’t require any user-defined prefix. We use a normal create statement to create the local user.

Command: create user dotnet identified by dotnet;

Creation of local user

The creation of the local user is successful. So, it is completely different from the common user. Let’s check whether the created user is present in the local user list.

Step 3: We need to take the list of local users present in the pluggable database. We have used the query before.

Query: select username,common,con_id from cdb_users where common=’NO’ order by username;

Common Users vs Local Users in Oracle Database

So, in this article, we learned about common users and local users and how to create both common users and local users.

In the next article, I am going to discuss Dynamic Performance Views in Oracle Database. Here, in this article, I try to explain Common Users vs Local Users in Oracle and I hope you enjoy this Common User vs Local User in Oracle article.

Leave a Reply

Your email address will not be published. Required fields are marked *