Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to check valid user/pwd in stored procedure ???

Re: How to check valid user/pwd in stored procedure ???

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 22 Jan 2000 09:26:02 -0500
Message-ID: <b1fj8ssc68ikngnp5iouafibosb5g5vepm@4ax.com>


A copy of this was sent to Saverio Tirone <saverio.tirone_at_semagroup.it> (if that email address didn't require changing) On Fri, 21 Jan 2000 11:39:09 +0100, you wrote:

>In a PL/SQL stored procedure I need to validate a login and password
>(same RDBMS) .
>Is there any funcion to do that ? If not, how can I write this function
>??
>The function valid_user(login varchar2,pwd varchar2) shoud return a
>true/false value.
>
>Thank you in advance for your help
>
>
>

You can do this but it requires we momentarily change that users password (for a brief instant of time). It works like this:

o you give me a username/password.
o I read the digested password for that user out of dba_users and hold it. o I alter that user identified by the password you gave me. o I read that digested password out and compare it to the other one. o if equal -- return true;
o if not equal -- put the original digested password back for that user (restore their password) and then return false.

the owner of this package needs the ALTER USER priv (very powerful, make sure you understand the implications) granted directly to them *not* via a role (see http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html for a discussion of this). Also SELECT on DBA_USERS must be granted directly to them as well by SYS or INTERNAL.

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

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;
/
connect scott/tiger
connect tkyte/tkyte

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Jan 22 2000 - 08:26:02 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US