User Profiles in Oracle

Back to: Oracle DBA Tutorials

User Profiles in Oracle with Examples

In this article, I am going to discuss User Profiles in Oracle with Examples. Please read our previous article where we discussed Admin Option in Oracle with Examples.

User Profiles in Oracle

Now, we will be learning about user profiles. This topic is very big and it contains a very huge information.

Resource Limits:

Generally, the user profile is used for resource limits and password parameters that restrict database usage and database instance resources for a user.

For example, in resource limits we have a parameter called idle time. Suppose any user had logged in to the database. We can limit the idle time of a user. Suppose we can configure the idle time to 20 minutes. If the user did not perform any task or perform any operation or SQL operations for 20 minutes then the oracle session will terminate automatically.

Password Aging and Expiration:

So, we can inform the user to change the password after one month or two months. We can manage all these parameters in a user profile.

• So, a user profile is a named set of resource limits and password parameters that restrict database usage and database instance resources for a user.
• If you assign a profile to a user, then that user cannot exceed those limits.
• Every user, including the administrator, is assigned to only one profile.
• By default, when you create a user, it will be assigned to the default profile, unless you specified another profile.

We can create a common profile that has a prefix as c##. The creation of the common profile is the same as the common user. We have to connect as sysdba to create a common profile.

The syntax for creating the common profile is as follows.

Syntax: CREATE PROFILE C##PROFILE limit __ container=all;

In order to create a local profile, we have to connect to the administrator in the pluggable database.

Syntax: create profile profile_name limit __;

Assigning Profiles:

There are two ways to assign a profile:

• Commonly
• Locally

Commonly: The profile assignment is replicated in all current and future containers.

Syntax: alter user <common_user> profile <common_profile> container=all;

Locally: The profile assignment occurs in one PDB (stand-alone or application container) only.

Syntax: alter user <common or local user> profile <profile_name>

We use the profile to control resource limit and password to the user.

In the next article, I am going to discuss User Passwords in Oracle with Examples. Here, in this article, I try to explain User Profiles in Oracle with Examples. I hope you enjoy this User Profiles in Oracle article.