ORACLE DATABASE AND PL/SQ ONLY REPLY
twada3
oracle_lab_4.docxSurname 2
Part B - Password Policy
CREATE OR REPLACE FUNCTION is481_password
(username varchar2,
password varchar2,
old_password varchar2)
RETURN char(1) IS
BEGIN
n
end; 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 password = username THEN THEN
END IF;
raise_application_error(-20001, 'Password same as user');
END IF; THEN
--Check for the minimum length of the password
IF length(password) < 6 THEN THEN
END IF;
END IF;
raise_application_error(-20002, 'Password length less than 4');
END IF; THEN
--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
THEN
END IF;
END IF; raise_application_error(-20002, 'Password too simple');
END IF; THEN
--Check if the password contains at least one letter,
--one digit and one punctuation mark.
--1. Check for the digit
--You may delete 1. and replace with 2. or 3.
isdigit:=FALSE;
END IF;
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 THEN
END IF;
isdigit:=TRUE;
GOTO findchar;
END IF; THEN
END
END IF; LOOP;
END LOOP;
IF isdigit = FALSE THEN THEN
END IF;
raise_application_error(-20003, 'Password should contain at least one
digit, one character and one punctuation');
END IF;
--2. Check for the character
<< THENfindchar
END IF;>>
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 THEN
END IF;
ischar:=TRUE;
GOTO findpunct;
END IF; THEN
END
END IF; LOOP;
END LOOP;
IF ischar = FALSE THEN THEN
END IF;
raise_application_error(-20003, 'Password should contain at least one digit,
one character and one punctuation');
END IF;
--3. Check for the punctuation
<< THENfindpunct
END IF;>>
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 THEN
END IF;
ispunct:=TRUE;
GOTO endsearch;
END IF; THEN
END
END IF; LOOP;
END LOOP;
IF ispunct = FALSE THEN THEN
END IF; raise_application_error(-20003, 'Password should
contain at least one digit, one character and one punctuation');
END IF;
<< THENendsearch
END IF;>>
--Check if the password differs from the previous password by at least 3 letters
IF old_password = '' THEN THEN
END IF;
raise_application_error(-20004, 'Old password is null');
END IF; THEN
--Everything is fine; return TRUE ;
differ := length(old_password) - length(password);
END IF;
IF abs(differ) < 3 THEN THEN
END IF;
IF length(password) < length(old_password) THEN THEN
END IF;
m := length(password);
ELSE
m:= length(old_password);
END IF; THEN
differ := abs(differ);
END IF;
FOR i IN 1..m LOOP
IF substr(password,i,1) != substr(old_password,i,1) THEN THEN
END IF;
differ := differ + 1;
END IF; THEN
END
END IF; LOOP;
IF differ < 3 THEN THEN
END IF;
raise_application_error(-20004, 'Password should differ by at
least 3 characters');
END IF; THEN
END
END IF; IF; THEN
--Everything is fine; return TRUE ;
RETURN(TRUE);
END IF;
END;
--Creating the profile with user is481_prof
CREATE PROFILE is481_prof LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_EXPIRE_WARNING 1
PASSOWRD_REUSE_TIME 30
PASSOWRD_REUSE_TIME UNLIMITED
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 13
PASSWORD_VERIFY_FUNCTION is481_password
ALTER USER DBSEC PROFILE is481_prof;
