?
Solved

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

Posted on 2014-07-15
16
Medium Priority
?
1,761 Views
Last Modified: 2014-07-17
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>>
0
Comment
Question by:sikyala
  • 6
  • 5
  • 5
16 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40197350
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
 

Author Comment

by:sikyala
ID: 40197352
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40197356
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40197386
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40197391
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40197414
>>how about something like this?

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

Accepted Solution

by:
sdstuber earned 1332 total points
ID: 40197420
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
 

Author Comment

by:sikyala
ID: 40198041
I actually just found out the password should be at least 12 characters long
0
 

Author Comment

by:sikyala
ID: 40198047
i'll try sdstuber
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 668 total points
ID: 40198084
>>I actually just found out the password should be at least 12 characters long

if length(password) < 12 then
...
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40198142
0
 

Author Comment

by:sikyala
ID: 40199641
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1332 total points
ID: 40199728
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
 

Author Comment

by:sikyala
ID: 40200459
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40200671
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
 

Author Closing Comment

by:sikyala
ID: 40202195
thank you
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Fine Tune your automatic Updates for Ubuntu / Debian
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question