Creation of Common Profile in Oracle

Back to: Oracle DBA Tutorials

Creation of Common Profile in Oracle

In this article, I am going to discuss the Creation of a Common Profile in Oracle with Examples. Please read our previous article where we discussed Resource Parameters in Oracle with Examples.

Creation of a Common Profile in Oracle

Now, we will be doing exercises related to user profiles. We will be learning how to create a common profile. Usually, we don’t create user profiles in the container database. But learning this concept is interesting. We will be following this in steps. Let’s go ahead and learn how to create user profiles in the database.

Step 1: Connect to SQL Developer

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’s go ahead and check the database name and the user.

Step 2: Check the Default Profiles in Oracle

There is a data dictionary called dba_profiles which contains all the profiles present in the database. Let’s go ahead and check the DEFAULT dba_profiles. Oracle creates the default profiles.

Query: select * from dba_profiles where profile=’DEFAULT’;

We can see there are 17 resources within the default profile. There are two resource_type. One is KERNEL and the other is PASSWORD. Let’s check about the KERNEL resource_type.

These are the resources associated with KERNEL. The resources like SESSIONS_PER_USER determine how many sessions can be logged in by the user. The IDLE_TIME indicates how much time the user can be idle.

There are a few other complex parameters like COMPOSE_LIMIT and LOGICAL_READS_PER_CALL that required expert dba to handle these parameters. These values are mentioned as unlimited in the default profile. We have another resource type mentioned as a password. Let’s check that resource_type.

These are resource_type with the password. We can see there are a few parameters like FAILED_LOGIN_ATTEMPS which is limited to 10. The PASSWORD_LIFE_TIME is set to 180 days. After 180 days user needs to change the password.

There is a column mentioned as common. We don’t see any of the parameters as common because this is not a common profile. The good practice is to create a user profile at the pluggable database level.

Step 3: Create a Common User Profile in Oracle.

Let us go ahead and create the common user profile.

Statement:

Create profile C##BASIC_PROFILE
Limit
SESSIONS_PER_USER 4
IDLE_TIME 15
FAILED_LOGIN_ATTEMPS 3
PASSWORD_LIFE_TIME 180
Container=all

We have mentioned the profile name as c##BASIC_PROFILE because we are creating this profile as a common profile in the ROOT database. We have mentioned the parameters like SESSIONS_PER_USER as 4 which means the user login with only 4 sessions on his name. FAILED_LOGIN_ATTEMPTS are 3 which shows that after three wrong passwords the account will be locked. IDLE_TIME is mentioned as 15 so, after 15 minutes if the user doesn’t perform any task, the session will be killed. The PASSWORD_LIFE_TIME is mentioned as 180. Finally, we should mention container=all because this is a common profile. Let’s go ahead and create the common profile.

We can see the profile is created. Let’s go ahead and check the details of the profile using the data dictionary dba_profiles.

Query: select * from dba_profiles where profile=’BASIC_PROFILE’;

We can see there are some values where it is mentioned as DEFAULT because if we didn’t mention any value then oracle will take this as default. We didn’t mention the COMPOSE_LIMIT and oracle mentioned it as a DEFAULT value.

Step 4: Create a User and Assign a Profile to the User in Oracle.

Let us check the profile by creating a user and assigning the profile to the user.

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

The user is created now. Let’s go ahead and grant some privileges to the user. We will be granting CREATE SESSION, CREATE TABLE, and UNLIMITED TABLESPACE to the user c##newuser.

Statement: grant create session, create table, unlimited tablespace to c##newuser container=all;

As the user c##newuser is created. Let’s check the details of the user.

Query: select * from dba_users where username=upper(‘c##newuser’);

We can see the user is assigned with default tablespace as users and temporary tablespace as the temp. This user is a common user so the column common is displayed as yes. There is a column mentioned as a profile which is the default because we haven’t mentioned the profile to the user. Let’s go ahead and assign the profile to the user c##newuser.

Statement: alter user c##newuser profile C##BASIC_PROFILE container=all;

So, we have assigned the profile to c##newuser. Let’s go ahead and check the user details if the profile is assigned or not.

Query: select * from dba_users where username=upper(‘c##newuser’);

We can see the user has the profile of c##newuser.

Step 5: Test this profile

Let us go ahead and test this profile with the parameter SESSIONS_PER_USER. Let’s open a terminal and connect to the user c##newuser.

In a similar way let’s go ahead and open another 4 sessions with the user c##newuser.

We have opened all four sessions, let’s try to open the fifth session of the user c##newuser.

We are getting this error which indicates the maximum number of sessions has been exceeded. So, the profile is working now. Once this is done. Let’s close all these sessions and let’s test the FAILED_LOGIN_ATTEMPTS. We will be testing the user by entering the wrong password.

We have entered the password as abcd instead of goodluck. Let’s try this again 3 times in order to breach the failed_login_attempts.

After entering the wrong password multiple times, the account is locked. So, the account cannot be used anymore. So, only dba can unlock the account. Let’s go ahead and connect as sysdba and unlock the account.

So, we are connected as sysdba. Let’s go ahead and unlock the account.

Statement: alter user c##newuser account unlock;

So, we understood how to create a common profile. Previously we discussed that the profiles cannot impose resource limitations on users unless the RESOURCE_LIMIT parameter is set to TRUE. Let’s check if the parameter is TRUE.

Command: show parameter res

We can see the parameter resource_limit is set to true and that is the reason our test on the profile succeeded.

In the next article, I am going to discuss the Creation of Profiles in Oracle Pluggable Database with Examples. Here, in this article, I try to explain the Creation of a Common Profile in Oracle with Examples. I hope you enjoy this Creation of Common Profile in Oracle article.