Manage Password and Resources in Oracle using Profile

In this article, you will learn how to manage password and resources in oracle using profile.

Why we should use Profiles in Oracle?

Profiles in Oracle helps us to manage password and restrict resource consumption.

In this tutorial of ‘profiles in Oracle’, we are going to learn session management in Oracle, resource management in Oracle and password management in Oracle with the help of parameters available in profile.  Herein we have tried to cover almost all parameters in profiles in Oracle. Hope this will help you.

Purpose of Profile in Oracle

To restrict resource consumption and allows to manage password.

  • One profile can be assigned to multiple users.
  • One user can have one profile at a time
  • Only DBA can create a profile.
  • When we alter users profile, it will be applicable from next logged-in session, not from the current  or previous  session
  • View to check details about the profile in Oracle:- dba_profiles  and  dba_users

How to create a Profile in Oracle/Command to create a profile in Oracle?

Below command will create a profile with the name ‘profile_test1’  which defines that user can open only 2 concurrent sessions at a time also it specifies ideal_time as 15 minutes, it means if a session is ideal for more than 15 minutes then that session will automatically log off.   It also specifies connect time as 120 minutes which means, the session can remain connected to the database for maximum of 120 minutes after that session will automatically log off.

sql> create profile  profile_test1  limit

sessions_per_user 2

idle_time 15

connect_time 120;

How to check profile allocated to the user in Oracle

sql>select username ,profile from dba_users;

How to assign created profile to a user in Oracle

sql>alter user scott profile profile_test1;

How to drop a profile in Oracle

sql>drop profile profile_test1 cascade;

Resource management in Oracle using profile parameters

Connect_time

It specifies the total time in minutes a session may remain connected to the database. After specified minutes a session logs off.

CPU_per_call

It limits the CPU time used by each call within a transaction. It includes parsing, executes and fetch. This much of time one statement can use at max. If any statement uses more CPU it will be failed.

CPU_per_session

It limits CPU’s total time used during a session.

sessions_per_user

It specifies the number of concurrent sessions that can be opened by users.

If the value for sessions_per_user = 2 then that user can open 2 sessions simultaneously.

Idle_time

It limits the amount of time the session is idle. If idle_tile is 15 minutes and the session is idle for 15 minutes, the session automatically logs off.

logical_reads_per_session

It limits the total number of data blocks read(from SGA+DIsk read) per session.

logical_reads_per_call

It limits the total number of data blocks reads per statement.

composite_limit

It’s an overall limit of all the above parameters in terms of the service unit.

User password management in Oracle using profile parameters

Failed_login_time

It specifies the number of times the user can enter the wrong password.

password_life_time

It specifies the number of days the password should be valid. If we don’t change the password within these days then the password will expire.

password_grace_time

When the password expires within the next password_grace_time days you can change the password.

password_lock_time

If failed_login_attempts are set to 3, 4th-time account is locked. Then account should be locked for password_lock_time days.  After password_lock_time days account will be unlocked.

Password_reuse_time

The definition for password_reuse_time says that the same password cannot be used again till password_reuse_time days.

Password_reuse_max

It specifies the number of times we can use the same password again.

Password_verify_function

This parameter is inbuild provided by Oracle to impose restrictions while setting a password. Value is verify_function_11g.

Please check below example to understand the –

sql>alter profile Test1  limit Password_verify_function verify_function_11g;

If the above function is not available then we need to run below password manager function

sql>?/rdbms/admin/utlpwdmg.sql

We need to below for resource parameters to take into effect.

 set resource_limit parameter =True

thanks for reading these articles. you’ll also like to read the below articles.

Oracle Database Administrators

Oracle 11gR2 RAC

Oracle Database Security Tutorials

Oracle Performance Tuning