User Password in Oracle with Examples

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

User Password in Oracle

Previously we learned about user profiles. We understood that user profiles are used to control passwords and resource limits. Now, we learn about passwords for users. First, we will learn about parameters.

As a DBA we have to make sure the users in the database use a strong password for logging into the database. In password parameters, we can control many options.

Account locking
Password aging and expiration
Password history
Password Complexity Verification

Account Locking:

Account locking enables the automatic locking of accounts for a set duration when users fail to log in to the system in the specified number of attempts or when accounts sit inactive for a predetermined number of days (meaning, users have not attempted to log in to their accounts.

Parameters:

FAILED_LOGIN_ATTEMPTS specifies the number of failed login attempts before the lockout of the account.
PASSWORD_LOCK_TIME specified the number of days for which the account is locked after the specified number of failed login attempts
INACTIVE_ACCOUNT_TIME specified the number of days an account can be inactive before it is locked.

Password Aging and Expiration:

This enables user passwords to have a lifetime after which the passwords expire and must be changed.

Parameters:

PASSWORD_LIFE_TIME determines the lifetime of the password in days, after which the password expires. If the password lifetime is set to 90 days user needs to change the password after 90 days.
PASSWORD_GRACE_TIME specifies a grace period of days for changing the password after the first successful login after the password has expired.

Password History:

This checks the new password to ensure that the password is not re-used for a specific amount of time or a specified number of password changes.

Parameters:

PASSWORD_REUSER_TIME specifies that a user cannot reuse a password for a given number of days. Sometimes the system asks us to change the password. We cannot use the same old password. This parameter blocks us to use the old password.
PASSWORD_REUSE_MAX specifies the number of password changes that are required before the current password can be reused.
PASSWORD_VERIFY_FUNCTION checks for password complexity for the SYS user.

Password Complexity Verification:

This makes a complexity check on the password to verify that it meets certain rules

Parameter: PASSWORD_VERIFY_FUNCITON

We have to choose the function

This is PL/SQL function that performs password complexity checks.
This function is owned by user SYS.
It must return Boolean (True or False)
A Model verification is provided in the script called utlpwdmg.sql ($ORACLE_HOME/rdbms/admin)

There are a few other password verification functions that are supplied by Oracle. We are going to discuss those functions.

Oracle Supplied Password Verification Functions:

Oracle introduced these functions to make rules. Oracle let us create our own functions which indicate that a dba can create his own functions. The dba can create a function where the rule to set a password will be 10 characters and two characters would be Capital and any character would be a special character.

We can create our own functions. Oracle introduced us to three functions and these functions are the default functions.

ORA12C_VERIFY_FUNCTION
ORA12C_STRONG_VERIFY_FUNCTION
VERIFY_FUNCTION_11G

These functions belong to sys user. These functions are present in a script called catpvf.sql. In oracle documentation, there is another file mentioned as utlpwdmg.sql script. This script doesn’t create the above functions. It is the script for Default Password Resource Limits.

Rules of Functions:

VERIFY_FUNCTION_11G:

The password contains no fewer than 8 characters and includes at least one numeric and one alphabetic character
The Password is not the same as the username, nor is it the username reversed or with the numbers 1-100 appended.
The password is not the same as the server name or the server name with the numbers 1-100 appended.
The password does not contain an oracle (for example, an oracle with numbers 1-100 appended).
The password is not too simple (welcome1, database1, abcdef123, computer2, etc.)
The password differs from the previous password by at least 3 characters
The password does not contain the double quotation character (“). However, it can be surrounded by double quotation marks.

ORA12_VERIFY_FUNCTION:

The password contains no fewer than 8 characters and includes at least one numeric and one alphabetic character
The password is not the same as the username or the username is reversed.
The password is not the same as the database name.
The password does not contain the word oracle(oracle123).
The password differs from the previous password by at least 8 characters.
The password contains at least 1 special character.
The password doesn’t contain the double quotation character (“). However, it can be surrounded by double- quotation marks.

ORA12C_STRONG_VERIFY_FUNCTION:

The password must contain at least 2 upper case characters, 2 lower case characters, 2 numeric characters, and 2 special characters. These special characters are as follows [ ~! @ #$%^&*() _-+= {}[]\|:;<>,.?/ (space) ]
The password must differ from the previous password by at least 4 characters.
The password does not contain the double-quotation character (“). It can be surrounded by double- quotation marks, however.

These are the rules provided by the oracle. No need to remember for the exam.

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