Re: verify existing oracle password before changing

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/05/18
Message-ID: <8g0kf0$pu6$1@nnrp1.deja.com>#1/1

In article <si6ldcgq8ta81_at_corp.supernews.com>,   Ed Gormsen <ed_gormsen_at_hotmail.com> wrote:
> I am creating a web based application using the oracle application
 server
> toolkit. After a user has already logged on to the system, they will
 have
> the ability to change their password using a simple web page. This
 is
> easy enough to do with the alter user statement, but for security
 reasons
> I would like to prompt them for their original password first and
 then
> verify that password against "dba_users" before allowing the change.
>
> I understand that oracle guards their password encryption/hash
 routines,
> but is there a function call I could call in a pl/sql program that
 would
> check to see if the original password is correct?
>
> Thanks for your Help,
> Ed
>
> --
> Posted via CNET Help.com
> http://www.help.com/
>

there is nothing builtin to do that. In order to do that, you can

• save their current digested password
• alter them to have the password they give you that you want to check
• read that digested password out
• compare them
  • if different put the first digest back return FALSE
  • else return TRUE

it would look like this:

create or replace package check_password as

    function is_valid( p_username in varchar2, p_password in varchar2 )     return boolean;

end;
/

create or replace package body check_password as

g_alter_cmd varchar2(50)
default 'alter user $U identified by $P';

g_reset_cmd varchar2(50)
default 'alter user $U identified by values ''$P''';

function rep_up( p_str in varchar2, p_u in varchar2, p_p in varchar2 ) return varchar2
is
begin

      return replace(replace(p_str,'$U',p_u),'$P',p_p); end;

procedure execute_immediate( stmt in varchar2 ) as

    exec_cursor integer default dbms_sql.open_cursor;     rows_processed number default 0;
begin

    dbms_sql.parse(exec_cursor, stmt, dbms_sql.native );
    rows_processed := dbms_sql.execute(exec_cursor);
    dbms_sql.close_cursor( exec_cursor );

exception

    when others then

      if dbms_sql.is_open(exec_cursor) then
        dbms_sql.close_cursor(exec_cursor);
      end if;
      raise;

end execute_immediate;

function is_valid( p_username in varchar2, p_password in varchar2 ) return boolean
is

    l_dba_users1        dba_users%rowtype;
    l_dba_users2        dba_users%rowtype;

begin

    select * into l_dba_users1

      from dba_users
     where username = upper(p_username);

    execute_immediate( rep_up( g_alter_cmd, p_username, p_password ) );

    select * into l_dba_users2

      from dba_users
     where username = upper(p_username);

    if ( l_dba_users1.password <> l_dba_users2.password )     then

        execute_immediate( rep_up( g_reset_cmd, p_username, l_dba_users1.password));

    end if;

    return l_dba_users1.password = l_dba_users2.password; end is_valid;

end;
/
show errors

note that if they supply a "bad" password and the system crashes AFTER you've switched but before you restore the good one -- their password will have been changed.

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Thu May 18 2000 - 00:00:00 CDT