Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: How to check valid user/pwd in stored procedure ???
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;
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