How To Create Oracle Profiles

We will create oracle profile and explain with all details.

What are Oracle SQL Profiles?

Oracle Profiles allow us to manage users according to certain policies in Oracle.

Below you can find explanations of the most important ones from my point of view.

SESSIONS_PER_USER

You can limit the users concurrent session with this parameter.

IDLE_TIME

It is given in minutes. For users using this profile, sessions that remain inactive for the specified number of minutes are killed.

Default value of IDLE_TIME is unlimited.

FAILED_LOGIN_ATTEMPTS

After the specified number of false login attempts, the user is brought to lock status.

Default value of FAILED_LOGIN_ATTEMPTS is 10 times.

PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME

PASSWORD_LIFE_TIME is the maximum time that the user password can be used. For example, if you set it to 180, the user will expire after 180 days.

Default value of PASSWORD_LIFE_TIME is 180 days.

If PASSWORD_GRACE_TIME is set with PASSWORD_LIFE_TIME, the user will be prompted to change their password for the time specified in PASSWORD_GRACE_TIME.

If this time is over, the password will expire if you have not changed your password yet.

If PASSWORD_GRACE_TIME is not set, it will default to UNLIMITED.

This means that users will be prompted to change their password every time they log in, but the password will not expire.

So if you are setting PASSWORD_LIFE_TIME, you should also set PASSWORD_GRACE_TIME.

Default value of PASSWORD_GRACE_TIME is 7 days.

PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX

These two parameters are related to each other. PASSWORD_REUSE_TIME ensures that a password is not reused during the number of days set. PASSWORD_REUSE_MAX gives the number of password changes that need to be changed so that the current password can be reused.

For example, if PASSWORD_REUSE_TIME is set to 30 and PASSWORD_REUSE_MAX is set to 3, the user can use the same password if there is a password change at least 3 times after 30 days.

If one of these parameters is UNLIMITED, the user will never be able to reuse the same password.

PASSWORD_LOCK_TIME

If the user is Locked, it shows how many days are waiting before the lock is automatically opened.

Default value of PASSWORD_LOCK_TIME is 1 day.

PASSWORD_VERIFY_FUNCTION

The PASSWORD_VERIFY_FUNCTION parameter is used to define the function for which the user password policies are to be specified.

You can change the default password policy in Oracle by using PASSWORD_VERIFY_FUNCTION.

If you execute the file utlpwdmg.sql in the directory “$ORACLE_HOME/rdbms/admin”, this function will automatically occur.

You can then use this function as in the script below.

How To Create Oracle Profiles?

You can create a profile using the following script.

How To Change Oracle Profile Settings?

You can use ALTER command to change oracle profile settings;