Password verify_function - Oracle8

From: <rwidlund_at_my-dejanews.com>
Date: Fri, 03 Jul 1998 07:30:47 GMT
Message-ID: <6ni1b7$dn2$1@nnrp1.dejanews.com>

Hi,

Has anyone managed to get the example of verify_function as written on page 19-16 in the manual "Oracle Server Oracle8 Administrators Guide, June 1997" ?.

It works fine to compile and assign it as an Password Verification Routine on a Profile. The checks for Digits,characters and punctuation works fine !. The check for that a use must change the password on at least 3 letters doesnt work.
The function takes 3 parameters, username,password,old_password. The username and password (new) is sent to the funtion but the 3rd parameter is NULL and therefore any checks against the oldpassword doesnt work.

If you change the row: "IF old_password = '' THEN" to "IF old_password IS NULL" then an alter user will always fail since the parameter is indeed NULL.

The function is attached below. I've done some minor changes.

I'm using Oracle8 8.0.4.1.3 on a Winnt 4.0.

Cheers,
Rickard Widlund

/********************************************************************/

Create or replace function verify_password (
  username IN varchar2,
  password IN varchar2,
  old_password IN 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:='!"#$%&()''*+,-/:;<=>?_';

 IF password = username THEN

     raise_application_error(-20001, 'Password same as user');   END IF;   IF length(password) < 4 THEN

     raise_application_error(-20002, 'Password length less than 4');   END IF;   IF NLS_LOWER(password) IN ('welcome','database','account','user',

         'password','oracle','computer','abcd','qwerty') THEN
      raise_application_error(-20003,'Password to simple');

  END IF;   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,'Digit: Password should contain at least one digit, one character and one punctuation'); END IF;

<<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;
      END IF;

    END LOOP;
  END LOOP;   IF ischar = FALSE THEN raise_application_error(-20005,'Character: Password should contain at least one digit, one character and one punctuation');

  END IF;
<<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(-20006,'Punctuation: Password should contain at least one digit,

            one character and one punctuation');   END IF;
<<endsearch>>

-- Always fails if IS NULL is checked.
-- IF old_password IS NULL THEN
IF old_password = '' THEN

    raise_application_error(-20007,'Old password is NULL');   END IF;   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(-20008,'Password should differ by at least 3 characters');

    END IF;
  END IF;   RETURN TRUE; END;

/****************************************************************************/

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Fri Jul 03 1998 - 02:30:47 CDT