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>>
sikyalaSenior Database AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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.
0
sikyalaSenior Database AdministratorAuthor Commented:
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;
0
slightwv (䄆 Netminder) Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
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?
0
sdstuberCommented:
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

0
slightwv (䄆 Netminder) Commented:
>>how about something like this?

Much cleaner than mine.  Checking the length was the first thing that came to mind!
0
sdstuberCommented:
on further review, the password length < 4 check isn't needed either.

With 4 categories each requiring 2 characters, the password must always be at least 8 characters long


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 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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sikyalaSenior Database AdministratorAuthor Commented:
I actually just found out the password should be at least 12 characters long
0
sikyalaSenior Database AdministratorAuthor Commented:
i'll try sdstuber
0
slightwv (䄆 Netminder) Commented:
>>I actually just found out the password should be at least 12 characters long

if length(password) < 12 then
...
0
sdstuberCommented:
0
sikyalaSenior Database AdministratorAuthor Commented:
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
0
sdstuberCommented:
change 2003 to 20003

but I don't see 2003 in your attachment, which means the code posted must not be the code you ran.

>> my client wants all the checks even though they may overlap

did you explain that overlaps don't help?

As a work around try this...

Do all the "real" checks first, and then the superfluous ones at the end that way they will always pass.  If they don't, it's because one of the more important checks failed earlier.

For example, move all of the "too simple" checks to the end.  If all of your other rules pass, then they will too, if and of the other rules fail, then the function will abort with the appropriate error and you don't need to actually get to the useless rules.
0
sikyalaSenior Database AdministratorAuthor Commented:
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.
0
sdstuberCommented:
since you're changing the requirements you should close this question and open a new one with the full specification of what you want.
0
sikyalaSenior Database AdministratorAuthor Commented:
thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.