Standard Password Validation SQL Block
Standard Password Format Like
* One uppercase letter
* One lowercase letter
* One digit
* One special character
DECLARE
V_USERNAME VARCHAR2(100);
V_OLD_PASSWORD VARCHAR2(60);
PASSWORD VARCHAR2(60);
chars integer := 8;
letter integer := 1;
lower integer := 0;
upper integer := 1;
digit integer := 1;
special integer := 1;
digit_array varchar2(10) := '0123456789';
alpha_array varchar2(26) := 'abcdefghijklmnopqrstuvwxyz';
cnt_letter integer := 0;
cnt_upper integer := 0;
cnt_lower integer := 0;
cnt_digit integer := 0;
cnt_special integer := 0;
len integer := 0;
delimiter boolean := FALSE;
ch varchar2(100);
pw_lower varchar2(256);
BEGIN
V_USERNAME := :P9991_USER_NAME;
V_OLD_PASSWORD := :P9991_OLD_PASSWORD;
PASSWORD := :P9991_NEW_PASSWORD;
len := NVL (length(password), 0);
pw_lower := NLS_LOWER(password);
FOR i in 1..len LOOP
ch := substr(password, i, 1);
IF ch = '"' THEN
delimiter := TRUE;
ELSIF instr(digit_array, ch) > 0 THEN
cnt_digit := cnt_digit + 1;
ELSIF instr(alpha_array, NLS_LOWER(ch)) > 0 THEN
cnt_letter := cnt_letter + 1;
IF ch = NLS_LOWER(ch) THEN
cnt_lower := cnt_lower + 1;
ELSE
cnt_upper := cnt_upper + 1;
END IF;
ELSE
cnt_special := cnt_special + 1;
END IF;
END LOOP;
IF len>20 THEN
RETURN 'Password Must be less than 20 Characters';
ELSE
IF V_OLD_PASSWORD = PASSWORD THEN
RETURN 'New password cannot be same as old Password' ;
ELSE
IF delimiter = TRUE THEN
RETURN ('password must NOT contain a '|| 'double-quote character, which is '|| 'reserved as a password delimiter');
ELSE
IF chars IS NOT NULL AND len < chars THEN
RETURN( 'Password length less than ' ||chars);
ELSE
IF letter IS NOT NULL AND cnt_letter < letter THEN
RETURN( 'Password must contain at least ' ||letter || ' letter(s)');
ELSE
IF upper IS NOT NULL AND cnt_upper < upper THEN
RETURN('Password must contain at least ' ||upper || ' uppercase character(s)');
ELSE
IF lower IS NOT NULL AND cnt_lower < lower THEN
RETURN('Password must contain at least ' ||lower || ' lowercase character(s)');
ELSE
IF digit IS NOT NULL AND cnt_digit < digit THEN
RETURN('Password must contain at least ' ||digit || ' digit(s)');
ELSE
IF special IS NOT NULL AND cnt_special < special THEN
RETURN('Password must contain at least ' ||special || ' special character(s)');
ELSE
IF instr(pw_lower, NLS_LOWER(V_USERNAME)) > 0 THEN
RETURN('Password contains the username');
ELSE
RETURN NULL;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END;
.png)
