Password Function in Oracle PL/SQL
Password Function in Oracle PL/SQL
PL/SQL Function
Purpose: To verify the password while reset the password of the user .
Parameters to be passed: Total 3
1) Username , password,old_password
Technology: Oracle PL/SQL
sub scripts: None
Code:
|
CREATE OR REPLACE NONEDITIONABLE FUNCTION “SYS”.”PSSWD_VERIFY_FUNCTION”
( username VARCHAR2,
password VARCHAR2,
old_password VARCHAR2
)
RETURN BOOLEAN
IS
i integer;
m integer;
isnumber integer;
isalphabet integer;
isspecial integer;
alphabetfound CHAR(1);
numberfound CHAR(1);
specialfound CHAR(1);
specialcnt integer;
alphabets varchar2(52);
numbers varchar2(10);
specials varchar2(3);
BEGIN
alphabets := ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ’;
numbers := ‘0123456789’;
specials := ‘#$_’;
alphabetfound := ‘F’;
numberfound := ‘F’;
specialfound := ‘F’;
specialcnt := 0;
— Check if the password is null
IF ((password is null) OR (password = ”)) THEN
raise_application_error(-20001, ‘Password must begin with a alphabetic cha
racter and consists of alphabetic (a..z,A..Z) and numeric (0..9) and one special
(#,$,_) characters and 8 to 15 characters long and cannot be the same as userna
me and cannot be an Oracle reserved word’);
END IF;
— Check if the password is same as the username
IF UPPER(password) = UPPER(username) THEN
raise_application_error(-20001, ‘Password must begin with a alphabetic cha
racter and consists of alphabetic (a..z,A..Z) and numeric (0..9) and one special
(#,$,_) characters and 8 to 15 characters long and cannot be the same as userna
me and cannot be an Oracle reserved word’);
END IF;
m := length(password);
— Check for the minimum and maximum length of the password
IF ((m <8) OR (m > 15)) THEN
raise_application_error(-20001, ‘Password must begin with a alphabetic cha
racter and consists of alphabetic (a..z,A..Z) and numeric (0..9) and one special
(#,$,_) characters and 8 to 15 characters long and cannot be the same as userna
me and cannot be an Oracle reserved word’);
END IF;
— Check if password contains at least one alphabet and one number and one special characters
FOR i IN 1..m
LOOP
isalphabet := INSTR(alphabets,SUBSTR(password,i,1));
IF isalphabet > 0 THEN
alphabetfound := ‘T’;
ELSE
isnumber := INSTR(numbers,SUBSTR(password,i,1));
IF isnumber > 0 THEN
numberfound := ‘T’;
ELSE
isspecial := INSTR(specials,SUBSTR(password,i,1));
IF isspecial > 0 THEN
specialfound := ‘T’;
specialcnt := specialcnt + 1;
ELSE
raise_application_error(-20001, ‘Password must begin with a alpha
betic character and consists of alphabetic (a..z,A..Z) and numeric (0..9) and
one special (#,$,_) characters and 8 to 15 characters long and cannot be t
he sameas username and cannot be an Oracle reserved word’);
END IF;
END IF;
END IF;
END LOOP;
IF ((alphabetfound = ‘F’) OR (numberfound = ‘F’) OR (specialfound = ‘F’) OR (
specialcnt < 1)) THEN
raise_application_error(-20001, ‘Password must begin with a alphabetic cha
racter and consists of alphabetic (a..z,A..Z) and numeric (0..9) and one
special (#,$,_) characters and 8 to 15 characters long and cannot be the same
as username and cannot be an Oracle reserved word’);
ELSE
RETURN(TRUE);
END IF;
END;
/
|
Explanation: Password function that is used to verify while resetting the password. This function will be assigned to user profile .
How to run manually :
copy the code and run with sys user or copy the code in the file and save it and run the file.
for ex: file name: PSSWD_VERIFY_FUNCTION.sql
connect to SYS user
$SQL> @PSSWD_VERIFY_FUNCTION.sql
Sample output: when we create below is the o/p
“Function Created”
See Also:
Different kind of profile definitions. For Ex: DEFAULT, DBA_PROFILE,APP_PROFILE,MONITOR_PROFILE