DatabaseOracle

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 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 120 minutes after that session will automatically log off.

How to check profile allocated to the user in Oracle

How to assign created profile to a user in Oracle

How to drop a profile in Oracle

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 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 a 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

Its 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 time 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 is 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 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 restriction while setting a password. Value is verify_function_11g.

Please check below example to understand the –

If the above function is not available then we need to run below password manager function
We need to below for resource parameter to take in effect.

Related Articles

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Close

Adblock Detected

Please consider supporting us by disabling your ad blocker