1. I am using below password verify function "VERIFY_FUNCTION" in my profile "privilged_profile".max. password length allowed is 10 char. But it accpets less than 10 char when i create new user / alter the existing password.

2. Below are the complexity i would like to enforce:

/* Password Complexity:
Minimum password length: 10 characters - not working
Allowed : 0 -9 ( Numerics ) - this works
Allowed : a-z & A-Z ( Alphabets ) - this works
Allowed : [ !#$`*+,-:;<=>?_ ] ( special characters ) - accepts only #, $, _ ( rest i need to enclose "" it seems )
New password must differ by atleast 3 characters of old password - not working
Password should not be same as username - this working
Password should not be reused - this working
*/

could you please guide me on the "not working parts in the above list ?

-- below is VERIFY_FUNCTION CONTENT

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 contains username
IF NLS_LOWER(password) like '%'||NLS_LOWER(username)||'%' THEN
raise_application_error(-20001, 'Password contains username');
END IF;
-- Check for the minimum length of the password
IF length(password) < 10 THEN
raise_application_error(-20002, 'Password length less than 10');
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;
-- 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(-20003, 'Password should 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(-20003, 'Password should 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>>
-- Check if the password differs from the previous password by at least
-- 3 letters
IF old_password = '' THEN
raise_application_error(-20004, 'Old password is null');
END IF;
-- Everything is fine; return TRUE ;
differ := length(old_password) - length(password);
IF abs(differ) < 3 THEN
IF length(password) < length(old_password) THEN
m := length(password);
ELSE
m := length(old_password);
END IF;
differ := abs(differ);
FOR i IN 1..m LOOP
IF substr(password,i,1) != substr(old_password,i,1) THEN
differ := differ + 1;
END IF;
END LOOP;
IF differ < 3 THEN
raise_application_error(-20004, 'Password should differ by at \
least 3 characters');
END IF;
END IF;
-- Everything is fine; return TRUE ;
RETURN(TRUE);
END;
/

a simple test may be appropriate.
deploy test function that only RAISES User Exception ALWAYS
what results when this function is supposedly in place?

I am going to post a number of questions that may or may not be relevant or get you closer to solving your mystery.

>C:\Users\user101>sqlplus test_user1/vik12#@testdb
are you sure that the same & only 1 DB is involved?
which user owns the VERIFY_FUNCTION?
does the user invoking the function have EXECUTE privilege on this function.
was COMMIT issued at appropriate time?
what proof exists that PRIVILEGED_PROFILE LIMIT profile exists with desired values?
what proof exists that the function ever gets invoked & started?
does any EXCEPTION handler code exist? if so, please post it.

I would rather ask lazy questions than try to replicate what you are attempting to create.

I tested your function and the minimum 10 characters and minimum 3 character difference seem to be working. Can you provide examples of combinations of new password and old password that cause it to not work properly?

I am sorry for late reply. Yes, I was wrong previously. Considering secuirty, Oracle has put a restriction that password verify function must be created only by SYS, and it will not accept if we try to create by any other db user other than SYS.

I was checking this on Amazon Oralce RDS instance previously and hence not able to achieve. I tried the same in a physical db env, and I am able to achieve that. I am able to get error if password length is set to < 10 char.