Re: Validating Oracle passwords

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 27 Oct 1998 15:51:41 GMT
Message-ID: <3638ea89.6832905@192.86.155.100>

A copy of this was sent to tonysheehan_at_my-dejanews.com (if that email address didn't require changing) On Tue, 27 Oct 1998 15:29:36 GMT, you wrote:

>How can I validate a users password in Oracle without using SQL*Plus
>or scripting? I want to use a PL-SQL code block, stored procedure,
>or function etc. Something that can be executed from a custom client
>server program.
>
>I have a client server application that uses Oracle as its database.
>One screen within the application allows users to change their own
>Oracle password. I want the screen to work in the same way that a
>UNIX change password does (i.e. they would have to enter their
>old password first before they can change to their new password).
>
>Is it possible to verify that the password they enter is valid?
>
>Any help would be appreciated!
>
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum

Yes you can. You need to have a user that has the 'ALTER USER' privelege (a pretty strong privelege, be care with it) and select on DBA_USERS granted directly to them (not via a ROLE). they can own this package and this package, when given a user and password, returns true or false depending on whether the password is valid or not. You would grant execute on this package to the 'correct' users:

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);

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

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

end;
/
show errors

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 Tue Oct 27 1998 - 09:51:41 CST