Modifying DEFAULT profile.

Admin — Wed, 13 May 2009 10:35:05 GMT

There is a default password verify function under $ORACLE_HOME/rdbms/admin with filename utlpwdmg.sql. This script creates a password verify function named "verify_function" and alters the default profile with the below attributes:

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 90
PASSWORD_GRACE_TIME 5
PASSWORD_REUSE_TIME 1800
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 6
PASSWORD_LOCK_TIME 1/1440
PASSWORD_VERIFY_FUNCTION verify_function;

verify_function has the following attributes:
- Check if the password is same as the username
- Check for the minimum length of the password (default = 4)
- 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. ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd' words are not accepted as password by default)
- Check if the password contains at least one letter, one digit and one punctuation mark.
- Check if the password differs from the previous password by at least 3 letters.

Here is an example of the password verify function:

CREATE OR REPLACE FUNCTION "SYS"."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) < 8 THEN
raise_application_error(-20002, 'Password length less than 8');
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 and one character');
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;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one \
digit and one character');
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 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;
/

You can customize this script to have different password verify function attributes, profile attributes and to apply to another profile.

FranklinFaces.com - Oracle & SQL Server Database Forums for all IT Professionals / Oracle Forum / Oracle Database Administration / Modifying DEFAULT profile. / Latest Posts

Modifying DEFAULT profile.