Re: How do I get the current Password?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 23 Jul 1998 20:20:43 GMT
Message-ID: <35b79a0b.29006429@192.86.155.100>

A copy of this was sent to Troy Perchotte <max_at_headroom.com> (if that email address didn't require changing) On Thu, 23 Jul 1998 08:53:03 -0700, you wrote:

>Is there any way then to verify a user's password?
>Something that would return a true if correct.
>
>Troy Perchotte
>
>
>Thomas Kyte wrote:
>
>> You cannot get the password of the currently (or not currently) logged in user.
>>
>>
>> Thomas Kyte
>> tkyte_at_us.oracle.com
>> Oracle Government
>> Herndon VA
>
>

Sort of, it requires the following steps:

1. select out the encoded password for the user from the dba_users table
2. alter that user to be identified by the password they gave you (the one you are testing)
3. select out that encoded password
4. compare them -- if different put back the one you get in 1.
5. return true or false based on 4.

The code to do it is below -- you need SELECT on DBA_USERS and the (very powerful) ALTER USER privelege granted directly to the schema that will compile this. Make sure you understand what/how this works before using it -- there is a brief period of time between steps 3 and 4 whereby you could inadverntantly switch the users password to something else and not set it back (say the machine crashed between 3 and 4)...

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

REM test it
set serveroutput on
begin

    if ( check_password.is_valid( 'scott', 'tiger' ) ) then

        dbms_output.put_line( 'scott/tiger is valid' );     else

        dbms_output.put_line( 'scott/tiger is NOT valid' );     end if;
    if ( check_password.is_valid( 'scott', 'lion' ) ) then

        dbms_output.put_line( 'scott/lion is valid' );     else

        dbms_output.put_line( 'scott/lion is NOT valid' );     end if;
end;
/  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jul 23 1998 - 15:20:43 CDT