asked on

how do i get password verify function to check for 2 digits, 2 upper char, 2 lower char, and 2 special characters

I am trying to implement my clients password policy that requires 2 digits, 2 upper char, 2 lower char, and 2 special characters. The password_verify_11g function I believe only checks for the presence of 1 of each option. How would I modify the if statement to check for 2 of each?

-- Check if the password contains at least one letter, one digit and one
-- punctuation mark.
-- 1. Check for the digit
isdigit:=FALSE;
m := length(password);
FOR i IN 1..10 LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(digitarray,i,1) THEN
isdigit:=TRUE;
GOTO findchar;
END IF;
END LOOP;
END LOOP;
IF isdigit = FALSE THEN
raise_application_error(-20008, 'Password must contain at least one digit, one character and one punctuation');
END IF;
-- 2. Check for the character
<<findchar>>
ischar:=FALSE;
FOR i IN 1..length(chararray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(chararray,i,1) THEN
ischar:=TRUE;
GOTO findpunct;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20009, 'Password must contain at least one \
digit, one character and one punctuation');
END IF;
-- 3. Check for the punctuation
<<findpunct>>
ispunct:=FALSE;
FOR i IN 1..length(punctarray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(punctarray,i,1) THEN
ispunct:=TRUE;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;
IF ispunct = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one \
digit, one character and one punctuation');
END IF;

<<endsearch>>

slightwv (䄆 Netminder)

I hate the loop through character examples out there. It can be simplified.

two upper case characters:
length(password)-length(regexp_replace(password,'[A-Z]')) > 1

Same sort of thing for the rest.

sikyala

ASKER

here is the beginning of the function

CREATE OR REPLACE FUNCTION verify_function
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ispunct boolean;
digitarray varchar2(20);
punctarray varchar2(25);
chararray varchar2(52);

BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
punctarray:='!"#$%&()``*+,-/:;<=>?_';

-- Check if the password is same as the username
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20001, 'Password same as or similar to user');
END IF;

-- Check for the minimum length of the password
IF length(password) < 4 THEN
raise_application_error(-20002, 'Password length less than 4');
END IF;

-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd') THEN
raise_application_error(-20002, 'Password too simple');
END IF;

slightwv (䄆 Netminder)

Guess I should just post them:

numbers:
length(password)-length(regexp_replace(password,'[0-9]')) > 1

lower case
length(password)-length(regexp_replace(password,'[a-z]')) > 1

'special': anything not a letter number or a space?

length(password)-length(regexp_replace(password,'[^0-9a-zA-Z ]')) > 1

slightwv (䄆 Netminder)

The 'too simple' check really doesn't need to be there because of the previous rules...

None of those words meet the minimums above so why code it?

Sean Stuber

how about something like this?

using regexp_instr makes it easy to check for any number of character categories.

regexp_instr(password,category,1, X)

where X is the number of the category that should be present.
If regexp_instr doesn't find at least that many it will return 0

CREATE OR REPLACE FUNCTION check_two(username VARCHAR2, password VARCHAR2, old_password VARCHAR2)
    RETURN BOOLEAN
IS
BEGIN
    -- Check if the password is same as the username
    IF NLS_LOWER(password) = NLS_LOWER(username)
    THEN
        RAISE_APPLICATION_ERROR(-20001, 'Password same as or similar to user');
    END IF;

    -- Check for the minimum length of the password
    IF LENGTH(password) < 4
    THEN
        RAISE_APPLICATION_ERROR(-20002, 'Password length less than 4');
    END IF;

    -- check for 2 digits
    IF REGEXP_INSTR(
           password,
           '[0-9]',
           1,
           2
       ) = 0
    THEN
        RAISE_APPLICATION_ERROR(-20003, 'Password should contain at least 2 digits');
    END IF;

    -- check for 2 upper
    IF REGEXP_INSTR(
           password,
           '[A-Z]',
           1,
           2
       ) = 0
    THEN
        RAISE_APPLICATION_ERROR(-20004, 'Password should contain at least 2 upper-case characters');
    END IF;

    -- check for 2 lower
    IF REGEXP_INSTR(
           password,
           '[a-z]',
           1,
           2
       ) = 0
    THEN
        RAISE_APPLICATION_ERROR(-20005, 'Password should contain at least 2 lower-case characters');
    END IF;

    -- check for 2 special
    IF REGEXP_INSTR(
           password,
           '["#$%&()!`*+,-/:;<=>?_]',
           1,
           2
       ) = 0
    THEN
        RAISE_APPLICATION_ERROR(-20003, 'Password should contain at least 2 special characters');
    END IF;

    RETURN TRUE;
END;

Open in new window

slightwv (䄆 Netminder)

>>how about something like this?

Much cleaner than mine. Checking the length was the first thing that came to mind!

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sikyala

ASKER

I actually just found out the password should be at least 12 characters long

sikyala

ASKER

i'll try sdstuber

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

Use the code I posted in

https://www.experts-exchange.com/questions/28476280/how-do-i-get-password-verify-function-to-check-for-2-digits-2-upper-char-2-lower-char-and-2-special-characters.html?anchorAnswerId=40197391#a40197391

change the 4 to 12

sikyala

ASKER

my client wants all the checks even though they may overlap I kept everything and changed only the section where the function is checking for char digit and special character. I didn't add the latter part of the function in this question. So I have attached the contents of the function. When I try to create a user with a password that should fail at the 2 upper character rule I get the following error:

ORA 28003 password verification for the specified password failed
ORA 21000 error number argument to raise_application_error of -2003 is out of range
verify-function-11G.txt

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sikyala

ASKER

yes that was a typo. I can now remove all other stuff and leave the 2 upper char, 2 lower char, 2 digit, and 2 special char
But I need to add another rule there can be no more than 3 characters of the same class
for example 4 lower case char, 4 numbers, 4 special characters can not appear consecutively in a password.

Sean Stuber

since you're changing the requirements you should close this question and open a new one with the full specification of what you want.

sikyala

ASKER

thank you