Oracle Password Policy

-- This script sets the default password resource parameters
-- This script needs to be run to enable the password features.
-- However the default resource parameters can be changed based
-- on the need.
-- A default password complexity function is also provided.
-- This function makes the minimum complexity checks like
-- the minimum length of the password, password not same as the
-- username, etc. The user may enhance this function according to
-- the need.
-- This function must be created in SYS schema.
-- connect sys/ as sysdba before running the script

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) < isdigit =" FALSE">>
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 except @');
END IF;
-- 3. Check for the punctuation
<>
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 except @');
END IF;

<>
-- Check if the password differs from the previous password by at least 3 letters

IF old_password IS NOT NULL THEN
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;
END IF;
-- Everything is fine; return TRUE ;
RETURN(TRUE);
END;
/

-- This script alters the default parameters for Password Management
-- This means that all the users on the system have Password Management
-- enabled and set to the following values unless another profile is
-- created with parameter values set to different value or UNLIMITED
-- is created and assigned to the user.

ALTER PROFILE LD_BACKOFFICE LIMIT
PASSWORD_LIFE_TIME 30
PASSWORD_GRACE_TIME 5
PASSWORD_REUSE_TIME 30
PASSWORD_REUSE_MAX 3
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME Unlimited
PASSWORD_VERIFY_FUNCTION verify_function;




-----------------------------------------------------------------------------------------------------