FAQ
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

Search Discussions

  • Potluri, Venu (IDS AIS OPS/SE) at Mar 21, 2005 at 4:08 pm
    Did you look at the verify function that oracle supplies? There is a
    verify_function.sql that needs to be executed.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Goulet, Dick
    Sent: Monday, March 21, 2005 4: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

    If you are not an intended recipient of this e-mail, please notify the =
    sender, delete it and do not read, act upon, print, disclose, copy, =
    retain or redistribute it. Click here for important additional terms =
    relating to this e-mail. http://www.ml.com/email_terms/

    --
    http://www.freelists.org/webpage/oracle-l
  • Grabowy, Chris at Mar 21, 2005 at 4:13 pm
    Not a password generator, but might be of interest...

    The Memorability and Security of Passwords - Some Empirical Results
    Jianxin Yan, Alan Blackwell, Ross Anderson, Alasdair Grant
    Cambridge University Computer Laboratory

    http://www.ftp.cl.cam.ac.uk/ftp/users/rja14/tr500.pdf

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Goulet, Dick
    Sent: Monday, March 21, 2005 4: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
  • Knight, Jon at Mar 21, 2005 at 4:13 pm
    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
    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
  • Rjamya at Mar 22, 2005 at 6:43 am
    Dick,

    this is what we use ... you can change to suit your needs. BTW unless
    enclosed in double-quotes, oracle passwords are case-insensitive, so
    capital letters is a moot point IMO.

    PROCEDURE Generate ( USERID VARCHAR2 ) IS
    newpass varchar2(20);
    dbname varchar2(10);
    BEGIN

    dbms_output.enable(100000);
    newpass := dbms_random.string('U',4)||TO_CHAR(SYSDATE,'SS')||DBMS_RANDOM.STRING('U',2);
    execute immediate 'alter user '||USERID||' identified by '||newpass;
    select name into dbname from v$database;
    dbms_output.put_line('The new password for '||USERID||' is

    '||newpass||' in the '||dbname||' database.');
    execute immediate 'alter user '||USERID||' password expire';
    execute immediate 'alter user '||USERID||' account unlock';

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    Null;
    WHEN OTHERS THEN
    -- Consider logging the error and then re-raise
    RAISE;

    END generate;
    /

    Raj
  • Greg Norris at Mar 22, 2005 at 8:45 am

    On Tue, 22 Mar 2005 06:38:43 -0500, rjamya wrote:
    BTW unless enclosed in double-quotes, oracle passwords are
    case-insensitive, so capital letters is a moot point IMO.
    Actually, they're case-insensitive regardless... see below. As near
    as I can tell, double-quoting a password is useful only for
    syntactical reasons.

    SQL> select * from v$version;

    BANNER

    Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
    PL/SQL Release 9.2.0.5.0 - Production
    CORE 9.2.0.6.0 Production
    TNS for Solaris: Version 9.2.0.5.0 - Production
    NLSRTL Version 9.2.0.5.0 - Production

    SQL> create user test
    2 identified by "foobar"
    3 default tablespace users;

    User created.

    SQL> grant connect to test;

    Grant succeeded.

    SQL> connect test/foobar_at_lvlshr1d
    Connected.
    SQL> connect test/"foobar"@lvlshr1d
    Connected.

    --
    "I'm too sexy for my code." - Awk Sed Fred.
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 21, '05 at 4:04p
activeMar 22, '05 at 8:45a
posts6
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase