In this article, I will provide a detailed guide on how to create password verify function Oracle 19c. We’ll walk you through each step of the process. Password verify function helps to define custom rules that passwords must meet when users change their passwords.
In our example, first, we will create password verify function called ‘Password_verify_function19’ and then create a profile called ‘SECURITY_CHECK_PROFILE’. In the SECURITY_CHECK_PROFILE we will provide Password_verify_function19 for the parameter PASSWORD_VERIFY_FUNCTION.
Table of Contents
we will create password verify function so that it follows the below password complexity rules:
- The minimum length of the password should be 12 characters
- Password should not contain the username, username reversed, server name, or database name.
- Password should not contain the compromised words: password, admin, user, database, welcome, oracle, computer and account
- Password should not contain a sequence of characters like 1234 or abcd
- Password should not contain repeating characters like 1111 or aaaa
- Password must contain at least one uppercase letter, one lowercase letter, one digit, and one special character.
1.Create password verify function:
First, go through the below script and change it as per your requirements. Once done you are good to create password verify function in your Oracle database.
CREATE OR REPLACE FUNCTION Password_verify_function19c
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
compromised_passwords CONSTANT SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST('password', 'admin', 'user', 'database', 'welcome', 'oracle', 'computer','account');
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ispunct boolean;
v_upper INTEGER;
v_lower INTEGER;
v_digit INTEGER;
v_special INTEGER;
db_name varchar2(40);
digitarray varchar2(20);
punctarray varchar2(25);
chararray varchar2(52);
i_char varchar2(10);
simple_password varchar2(10);
reverse_user varchar2(32);
word VARCHAR2(50);
v_server_name VARCHAR2(100);
v_database_name VARCHAR2(100);
BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
---Find the server name
v_server_name := SYS_CONTEXT('USERENV', 'SERVER_HOST');
---Find the database name
v_database_name := SYS_CONTEXT('USERENV', 'DB_NAME');
---Check if minimum length of the password is at least 12 characters
IF length(password) < 12 THEN
raise_application_error(-20001, 'Password length is less than 12');
END IF;
---Check if password contains the username
IF INSTR(NLS_LOWER(password),NLS_LOWER(username)) > 0 THEN
raise_application_error(-20002, 'Password cannot contain the username.');
END IF;
---Check if password contains the servername
IF INSTR(LOWER(password),LOWER(v_server_name)) > 0 THEN
raise_application_error(-20003, 'Password cannot contain the server name.');
END IF;
---Check if password contains database name
IF INSTR(LOWER(password),LOWER(v_database_name)) > 0 THEN
raise_application_error(-20004, 'Password cannot contain the database name.');
END IF;
---Check if password is same as the username reversed
FOR i in REVERSE 1..length(username) LOOP
reverse_user := reverse_user || substr(username, i, 1);
END LOOP;
IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THEN
raise_application_error(-20005, 'Password same as username reversed');
END IF;
---Check if password contains compromised words
FOR i IN 1..compromised_passwords.COUNT LOOP
word := compromised_passwords(i);
IF INSTR(LOWER(password), LOWER(word)) > 0 THEN RAISE_APPLICATION_ERROR(-20006, ' Your password contains a compromised word: ' || word || '. Please choose a different password.');
END IF;
END LOOP;
---Check if password contains sequences like "1234" or "abcd"
FOR i IN 1..LENGTH(password)-3 LOOP
IF
ASCII(SUBSTR(password, i, 1)) = ASCII(SUBSTR(password, i+1, 1)) - 1 AND
ASCII(SUBSTR(password, i+1, 1)) = ASCII(SUBSTR(password, i+2, 1)) - 1 AND
ASCII(SUBSTR(password, i+2, 1)) = ASCII(SUBSTR(password, i+3, 1)) - 1 THEN
RAISE_APPLICATION_ERROR(-20007, 'Your password contains a sequence of characters.');
END IF;
END LOOP;
---Check if password contains repeating characters like "aaaa" or "1111"
FOR i IN
1..LENGTH(password)-3 LOOP
IF SUBSTR(password, i, 1) = SUBSTR(password, i+1, 1)
AND
SUBSTR(password, i+1, 1) = SUBSTR(password, i+2, 1) AND
SUBSTR(password, i+2, 1) = SUBSTR(password, i+3, 1) THEN
RAISE_APPLICATION_ERROR(-20008, 'Your password contains repeating characters.');
END IF;
END LOOP;
---Check if password contains at least one uppercase letter, one lowercase letter, one digit and one special character
v_upper := REGEXP_COUNT(password, '[A-Z]');
v_lower := REGEXP_COUNT(password, '[a-z]');
v_digit := REGEXP_COUNT(password, '[0-9]');
v_special := REGEXP_COUNT(password, '[^a-zA-Z0-9]');
IF v_upper = 0 THEN
RAISE_APPLICATION_ERROR(-20009, 'Password must contain at least one uppercase letter.');
END IF;
IF v_lower = 0 THEN
RAISE_APPLICATION_ERROR(-20010, 'Password must contain at least one lowercase letter.');
END IF;
IF v_digit = 0 THEN
RAISE_APPLICATION_ERROR(-20011, 'Password must contain at least one digit.');
END IF;
IF v_special = 0 THEN
RAISE_APPLICATION_ERROR(-20012, 'Password must contain at least one special character.');
END IF;
RETURN TRUE;
END;
/
Do not just copy and paste the same script. Check the rules as per your environment and change accordingly. For example, you can add as many compromised words as you want.
2. Now create the profile as per the project requirement. I have created the profile as per the below requirement in my VirtualBox. Please change the parameter value as per your requirement:
- The account will be locked if the user provides three times incorrect passwords in a row (FAILED_LOGIN_ATTEMPTS)
- Account will remain locked for 1 day after FAILED_LOGIN_ATTEMPTS (PASSWORD_LOCK_TIME)
- The user password will expire after 120 days. After 120 days user needs to change their password to log into the database (PASSWORD_LIFE_TIME)
- Users can log in using an expired password and can change it within 7 days (PASSWORD_GRACE_TIME)
- Users can not use the last 24 used passwords (PASSWORD_REUSE_MAX)
- Users can not reuse a password within a year or 365 days (PASSWORD_REUSE_TIME)
- Users should follow the password complexity rules during password creation or change (PASSWORD_VERIFY_FUNCTION)
CREATE PROFILE "SECURITY_CHECK_PROFILE"
LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1
PASSWORD_LIFE_TIME 120
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_MAX 24
PASSWORD_REUSE_TIME 365
PASSWORD_VERIFY_FUNCTION Password_verify_function19c;
3. Create a database user and provide a create session grant. Change the user profile to SECURITY_CHECK_PROFILE.
SQL> create user SAM identified by sam123;
User created.
SQL> grant create session to SAM;
Grant succeeded.
SQL> alter user SAM profile SECURITY_CHECK_PROFILE;
User altered.
4. Now try to change the user password by not following the password complexity rules(password verify function), you will get the below error messages:
SQL> alter user SAM identified by sam123;
alter user SAM identified by sam123
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20001: Password length is less than 12
SQL> alter user SAM identified by hkb1Sh#gkdsame4;
alter user SAM identified by hkb1Sh#gkdsame4
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20002: Password cannot contain the username.
SQL> !hostname
machine1
SQL> alter user SAM identified by Skb1machine1#24
alter user SAM identified by Skb1machine1#24
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20003: Password cannot contain the server name.
SQL> select name from v$database;
NAME
---------
TEST
SQL> alter user SAM identified by Skb1test1#24;
alter user SAM identified by Skb1test1#24
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20004: Password cannot contain the database name.
SQL> alter user SAM identified by dh5n9#oracle3gjS;
alter user SAM identified by dh5n9#oracle3gjS
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20006: Your password contains a compromised word: oracle. Please choose a
different password.
SQL> alter user SAM identified by S5fhskdefg#279;
alter user SAM identified by S5fhskdefg#279
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20007: Your password contains a sequence of characters.
SQL> alter user SAM identified by S5fhskdef#1111;
alter user SAM identified by S5fhskdef#1111
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20008: Your password contains repeating characters.
SQL> alter user SAM identified by fh5fhskdef#1125;
alter user SAM identified by fh5fhskdef#1125
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20009: Password must contain at least one uppercase letter.
SQL> alter user SAM identified by SHT5#FJ916DGJAL;
alter user SAM identified by SHT5#FJ916DGJAL
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20010: Password must contain at least one lowercase letter.
SQL> alter user SAM identified by SsgjaybGkd#fg;
alter user SAM identified by SsgjaybGkd#fg
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20011: Password must contain at least one digit.
SQL> alter user SAM identified by Syhd67ndkkwl;
alter user SAM identified by Syhd67ndkkwl
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20012: Password must contain at least one special character.
I hope this article will help you to create password verify function in your database.
Pingback: How to set Oracle database user password to never expire