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
