Create Oracle 10g Password Verification Function

Question

How can I create a function in ORACLE to verify the password?

Password must contain:

• at least 1 uppercase

• at least 1 lowercase

• at least 1 digit

• at least 8 characters long

• does not contain 3 consecutive letters of the username

So far I have achieved 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, '^.*[az].*$') -- this is for small letter from a -z and regexp_like(:PWD_STR, '^.*[AZ].*$') -- this is for capital letters and regexp_like(:PWD_STR, '^.*[0-9].*$') -- this is for numbers 

This is my first regular expression job, and I need help finding a solution for the last requirement, and I also want to know if I am on the right path.

Answer 1

I found this solution to work

 FOR i IN 1..LENGTH(PWD_STR)-2 LOOP IF INSTR(LOWER(USER_NAME),SUBSTR(LOWER(PWD_STR),i,3)) > 0 THEN RETURN FALSE; END IF; END LOOP; 

Answer 2

Oracle provides a function that will be compiled under SYS to verify the password and complexity. You will find it in $ORACLE_HOME/rdbms/admin/utlpwdmg.sql .

With different versions, the function has been changed and new features added. In 10g, the complexity check was pretty simple. Prior to 12c, there were two functions verify_function (10g) and verify_function_11G (11g). Since 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 can write your UDF to provide a stronger complexity check in password verification . Find features and content in new versions and apply the same logic in your UDF. Take a look at http://www.oradba.ch/2013/07/oracle-12c-new-password-verify-function/

Answer 3

As a general solution, I would click on (and) use the Password Complexity Check Function . Regarding your last requirement:

• ...
• does not contain 3 consecutive letters of the username

I need help finding a solution for the last requirement [...]

If performance is not a problem, you can probably solve it at the SQL level. For instance:

 SELECT COUNT(*) INTO nbr_matching_chunk FROM ( -- Split name in 3 chars chunks SELECT SUBSTR('Sylvain', LEVEL, 3) chunk FROM DUAL CONNECT BY LEVEL <= LENGTH('Sylvain')-2 ) NATURAL JOIN ( -- Split password in 3 chars chunks SELECT SUBSTR('painsword', LEVEL, 3) chunk FROM DUAL CONNECT BY LEVEL <= LENGTH('painsword')-2 ) 

If nbr_matching_chunk is 0, then there is no corresponding three-letter sequence.

_{Or a course for readability. I hard-coded username and password , but you should be able to replace it with your actual PL / SQL variables.}

Then you only need to wrap this in your PASSWORD_VERIFY_FUNCTION profile.