Home » RDBMS Server » Security » Required slight modification to oracle supplied Password Verification Function (10g)
icon8.gif  Required slight modification to oracle supplied Password Verification Function [message #301798] Thu, 21 February 2008 14:54 Go to next message
anjum.suri
Messages: 14
Registered: February 2008
Junior Member
Hi Guys, I need an urgent help in modifying password verification function. My requirement is rather then checking for all three conditions (i.e. for digit, character and litral)I need function to check only two conditions (for e.g. abcdef~# OR 12356#@~ OR acbdjd1234 OR abcd#~123) atleast two condition should met. Please help me writing code, i am too bad in writing program

create or replace FUNCTION password_test_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(35);
chararray varchar2(60);

BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
punctarray:='!"#$%&()``*+,-/:;<=>?_\^{[]}|.~';

-- Check for the minimum length of the password
IF length(password) < 7 THEN
raise_application_error(-20002, 'Password length less than 7');
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(-20004, '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;
--GOTO endsearch;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20004, '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(-20004, 'Password should contain at least one digit, one character and one punctuation');
END IF;

<<endsearch>>

-- Everything is fine; return TRUE ;
RETURN(TRUE);
END;
Re: Required slight modification to oracle supplied Password Verification Function [message #301799 is a reply to message #301798] Thu, 21 February 2008 15:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Instead of "is..." variables use a single "total" variable of integer type that you initialize to 0.
Then instead of setting "is..." variable to true, add 1 to "total".
At the end check if total is < 2 then raise error otherwise return true.

Regards
Michel
Re: Required slight modification to oracle supplied Password Verification Function [message #301801 is a reply to message #301799] Thu, 21 February 2008 15:06 Go to previous messageGo to next message
anjum.suri
Messages: 14
Registered: February 2008
Junior Member
Hi Michel...

Thanks for the reply, but I didnt get, actually the function is not written by me; and I m not even .01% programmer, can I request you to write me a code or modify it as you have suggested; I shell be really thankful to you...

please help!!!
Re: Required slight modification to oracle supplied Password Verification Function [message #301855 is a reply to message #301801] Fri, 22 February 2008 00:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Make the following changes.
Comment/remove "Check for the minimum length of the password" part if you don't need it.
Comment/remove all "IF is... = FALSE THEN ... END IF;" parts
Change the declarations (comment/remove is... variables):
differ integer;
total integer := 0;
digitarray varchar2(20);

Change all lines "is... := TRUE;" by "total := total + 1;"
After "<<endsearch>>" add:
IF total < 2 THEN 
  raise_application_error(-20000, '<your message>');
END IF;

Regards
Michel


Re: Required slight modification to oracle supplied Password Verification Function [message #301913 is a reply to message #301855] Fri, 22 February 2008 04:33 Go to previous message
anjum.suri
Messages: 14
Registered: February 2008
Junior Member
Thanks alot Michel; it worked fine for me

thanks again!!!
Previous Topic: How to have essentially DML and no DDL for a user schema?
Next Topic: Could I use trigger to move 'EXPIRED' to 'LOCKED' ?
Goto Forum:
  


Current Time: Thu Mar 28 09:55:13 CDT 2024