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.
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;
/
- 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;
/
