Oracle Password Verification Function

I am sharing a password verification function that will satisfy most of the common complexity requirements. I have modified default password verification function that is available in ORACLE_HOME by default.

• It will enforce below:
• Alphanumeric
• At least 8 characters
• Password should not contain username
• At least one punctuation mark
• Should be mixed case

CREATE OR REPLACE FUNCTION f_passwordVerification(p_username    IN VARCHAR2,
                                                 p_password    IN VARCHAR2,
                                                 p_oldPassword IN VARCHAR2)
  RETURN BOOLEAN IS

  l_password VARCHAR2(100) := UPPER(p_password);
  l_alpha    BOOLEAN := FALSE;
  l_numeric  BOOLEAN := FALSE;
  ispunct    BOOLEAN := FALSE;
  ismixed    BOOLEAN := FALSE;
  punctarray varchar2(25);
  invalidPassword EXCEPTION;

BEGIN

  ------------------------------------------
  -- Check for alpha and numeric characters.
  FOR j IN 1 .. LENGTH(p_password) LOOP
    IF INSTR('01234567890', SUBSTR(l_password, j, 1)) != 0 THEN
      l_numeric := TRUE;
    ELSIF INSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ', SUBSTR(l_password, j, 1)) != 0 THEN
      l_alpha := TRUE;
    END IF;
    EXIT WHEN l_alpha AND l_numeric;
  END LOOP;

  IF NOT (l_alpha AND l_numeric) THEN
     raise_application_error(-20020, 'Password should be alphanumeric!!');
  END IF;
  ------------------------------------------

  -- Check length is bewteen 8
  IF LENGTH(l_password) < 8 THEN
    raise_application_error(-20020, 'Password should have atleast 8 characters!!');
  END IF;
  ------------------------------------------

  -- Check length username in contained in password
  IF instr(upper(p_password), upper(p_username)) > 0 THEN
    raise_application_error(-20020, 'Password should not contain username!!');
  END IF;
  ------------------------------------------

  -- Check if the password contains at least one punctuation mark
  punctarray := '!"#$%&()``*+,-/:;<=>?_';

  ispunct := FALSE;
  FOR i IN 1 .. length(punctarray) LOOP
    FOR j IN 1 .. LENGTH(p_password) LOOP
      IF substr(p_password, j, 1) = substr(punctarray, i, 1) THEN
        ispunct := TRUE;
      END IF;
    END LOOP;
  END LOOP;
  IF ispunct = FALSE THEN
    raise_application_error(-20020, 'Password should contain at least one punctuation mark!!');
  END IF;

  ------------------------------------------
  -- check for mixed case
  ismixed := FALSE;

  FOR i IN 1 .. length(p_password) LOOP
    if INSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ', SUBSTR(p_password, i, 1)) != 0 then
      FOR j IN 1 .. length(p_password) LOOP
        if INSTR('abcdefghijklmnopqrstuvwxyz', SUBSTR(p_password, j, 1)) != 0 then
          ismixed := TRUE;
        end if;
      end loop;
    end if;
  end loop;

  IF ismixed = FALSE THEN
     raise_application_error(-20020, 'Password should be mixed case!!');    
  END IF;

  RETURN(TRUE);

--EXCEPTION
  --WHEN invalidPassword THEN
    --RETURN(FALSE);  
END;
/