Create Password Verification Function Oracle 10g

Question

How is it possible to create a function in ORACLE to check the password?

The password should contain:

• at least 1 upper case

• at least 1 lower case

• at least 1 digit

• at least 8 characters long

• doesn't contain 3 consecutive letters of the user name

So far, I reached the following:

CREATE OR REPLACE FUNCTION dd_pwd_fun(username varchar2, password varchar2)
RETURN BOOLEAN IS

PWD_STR VARCHAR2
USER_NAME

BEGIN

PWD_STR = password;
USER_NAME=username;

IF LENGTH(PWD_STR) < 8 THEN
RETURN FALSE;
END IF;

if regexp_like(:PWD_STR, '^.*[a-z].*$') -- this is for small letter from a -z
and regexp_like(:PWD_STR, '^.*[A-Z].*$') -- this is for capital letters
and regexp_like(:PWD_STR, '^.*[0-9].*$') -- this is for numbers

This is my first time working with Regular Expressions and I need some help finding out a solution for the last requirement and also I want to know if I'm on the right track

Answer 1

Oracle provides a function to be compiled under SYS for password verification and it's complexity. You will find it in $ORACLE_HOME/rdbms/admin/utlpwdmg.sql.

With different releases, the function has been modified and new functions have been added. In 10g, the complexity check was quite simple. Before 12c, there were two functions verify_function (10g) and verify_function_11G (11g). With 12c, there are four more functions, ora12c_verify_function , ora12c_strong_verify_function and two helper functions complexity_check and string_distance.

Since you are on 10g, you could write your UDF to enforce a stronger complexity check in password verification. Search for the functions and it's content in newer versions, and apply similar logic in your UDF. Have a look at http://www.oradba.ch/2013/07/oracle-12c-new-password-verify-function/