Password Complexity

[lgargano]

I need to implement in my Oracle database a password policy, it should the follow characteristics:

Expire password, password history, password complexity and lock account on failed logon

The Oracle provides everything, but the item password complexity ( it is using the function that I created with utlpwdmg.sql script) doesn't works for my needs, I need the function that:

The minimum length of the password (8 characters)
Password should contain at least one digit (number);
Password should contain at least one character;
Password should contain at least one character (capital letter)

Question: Is there any function ready ? Or, maybe, is there any function that can be changed ?

[davey23uk]

you should change the function provided by oracle, you requirements are virtually the same so shouldnt be too hard to change it to meet your needs

[lgargano]

I have changed the function, but doesn't works,
Can you help me ?
See bellow:

CREATE OR REPLACE FUNCTION verify_function_new
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
isdigit boolean;
ischar boolean;
ischar_cl boolean;
digitarray varchar2(20);
chararray varchar2(52);
chararray_cl varchar2(52);

BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyz';
chararray_cl:= 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';


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

-- Check if the password contains at least one character, one character (capital letter) and one digit.


-- 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;
END IF;
END LOOP;
END LOOP;
IF isdigit = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one digit');
END IF;


-- 2. Check for the character
<>
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;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one character');
END IF;

-- 3. Check for the character (capital letter)
<>
ischar_cl:=FALSE;
FOR i IN 1..length(chararray_cl) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(chararray_cl,i,1) THEN
ischar_cl:=TRUE;
END IF;
END LOOP;
END LOOP;
IF ischar_cl = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one character - capital letter');
END IF;

-- Everything is fine; return TRUE ;
RETURN(TRUE);
END;
/