RE: Password generator

From: Knight, Jon <jknight_at_concordefs.com>
Date: Mon, 21 Mar 2005 15:09:18 -0600
Message-ID: <17ECCBDCF27C544583F2CAD928F953260221FB7B@memex1.corp.cefs.int>

Dick,
I don't have password function, per say, but here's a little text utility that will help if you do have to write it yourself. It's in a package called jck_text, but you can change that to whatever. I think I've included all the dependencies.

Jon Knight

PACKAGE CONSTANTS

  con_tab            constant varchar2(1) := chr ( 9 );
  con_lf             constant varchar2(1) := chr ( 10 );
  con_cr             constant varchar2(1) := chr ( 13 );
  con_space          constant varchar2(1) := chr ( 32 );

 


----------------------------------------------------------------------------

strip_chars -- M#000
Removes characters from p_text that are in the sets in p_flags
p_flags is a single string containing a maximum of 4 charaters:
"A" for Alpha characters
"N" for Numeric characters
"S" for Special characters
"W" for Whitespace
Example: strip_chars('(901) 371-8000','ASW') returns '9013718000'.

--

function strip_chars (

    p_text             in     varchar2
   ,p_flags            in     varchar2
  ) return varchar2 is
    v_alpha                   boolean;
    v_numeric                 boolean;
    v_special                 boolean;
    v_whitespace              boolean;

    v_ret                     varchar2(32767);  -- M#006
    v_char                    varchar2(1);
    v_flags                   varchar2(4);
  begin  -- strip_chars
    v_flags      := upper ( substr ( p_flags ,1 ,4 ) );
    v_alpha      := ( instr ( v_flags ,'A' ) != 0 );
    v_numeric    := ( instr ( v_flags ,'N' ) != 0 );

v_special := ( instr ( v_flags ,'S' ) != 0 ); v_whiteSpace := ( instr ( v_flags ,'W' ) != 0 );

for v_ndx in 1..length ( p_text ) loop

      v_char := substr ( p_text ,v_ndx ,1 );
      -- M#006 Now passing v_char to functions to determine character class,
      --       instead of hard coded values.
      if ( v_alpha      and jck_text.is_alpha ( v_char ) ) then
        v_char := null;
      end if;
      if ( v_numeric    and jck_text.is_numeric ( v_char ) ) then
        v_char := null;
      end if;
      if ( v_special    and jck_text.is_special ( v_char ) ) then
        v_char := null;
      end if;
      if ( v_whitespace and jck_text.is_whitespace ( v_char ) ) then
        v_char := null;
      end if;
      v_ret := v_ret || v_char;

    end loop;
    return v_ret;
  end strip_chars;

is_alpha -- M#005

--

  function is_alpha (
    p_string in varchar2
  ) return boolean is

    v_ndx                     binary_integer := 0;
    v_len                     binary_integer := 0;
    v_ret                     boolean := false;

  begin -- is_alpha
    v_len := length ( p_string );
    if ( v_len > 0 ) then

      loop
        v_ndx := v_ndx + 1;
        v_ret := instr (
                   'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
                  ,substr ( p_string ,v_ndx ,1 )
                 ) != 0;
        exit when not v_ret;
        exit when v_ndx = v_len;
      end loop;

end if;

    return v_ret;
  exception
    when others then
      return v_ret;
  end is_alpha;

is_numeric -- M#005

--

  function is_numeric (
    p_string in varchar2
  ) return boolean is

    v_ndx                     binary_integer := 0;
    v_len                     binary_integer := 0;
    v_ret                     boolean := false;

  begin -- is_numeric
    v_len := length ( p_string );
    if ( v_len > 0 ) then

      loop
        v_ndx := v_ndx + 1;
        v_ret := instr (
                   '0123456789'
                  ,substr ( p_string ,v_ndx ,1 )
                 ) != 0;
        exit when not v_ret;
        exit when v_ndx = v_len;
      end loop;

end if;

    return v_ret;
  exception
    when others then
      return v_ret;
  end is_numeric;

is_special -- M#005

--

  function is_special (
    p_string in varchar2
  ) return boolean is

    v_ndx                     binary_integer := 0;
    v_len                     binary_integer := 0;
    v_ret                     boolean := false;

  begin -- is_special
    v_len := length ( p_string );
    if ( v_len > 0 ) then

      loop
        v_ndx := v_ndx + 1;
        v_ret := instr (
                   '~!@#$%^&*()_-+=|[]{};:''"<>,.?/\`'
                  ,substr ( p_string ,v_ndx ,1 )
                 ) != 0;
        exit when not v_ret;
        exit when v_ndx = v_len;
      end loop;

end if;

    return v_ret;
  exception
    when others then
      return v_ret;
  end is_special;

is_whitespace -- M#005

--

  function is_whitespace (
    p_string in varchar2
  ) return boolean is

    v_ndx                     binary_integer := 0;
    v_len                     binary_integer := 0;
    v_ret                     boolean := false;

  begin -- is_whitespace
    v_len := length ( p_string );
    if ( v_len > 0 ) then

      loop
        v_ndx := v_ndx + 1;
        v_ret := substr ( p_string ,v_ndx ,1 ) in (
                   jck_text.con_tab
                  ,jck_text.con_lf
                  ,jck_text.con_cr
                  ,jck_text.con_space
                 );
        exit when not v_ret;
        exit when v_ndx = v_len;
      end loop;

end if;

    return v_ret;
  exception
    when others then
      return v_ret;
  end is_whitespace;

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Goulet, Dick

Sent:	Monday, March 21, 2005 3:00 PM
To:	oracle-l_at_freelists.org
Subject:	Password generator

Before I go off re-inventing the wheel, does anyone have a function, procedure, package that can be stored in an Oracle database that will create random passwords? I need 8+ characters with at least two of the following, numbers, capital letters, and special characters(commas, periods, slashes, etc...)
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Mon Mar 21 2005 - 16:13:45 CST