Re: Dynamic SQL problem
Date: 2000/05/17
Message-ID: <8furd5$r36$1_at_nnrp1.deja.com>
In article <8fubja$7r0$1_at_nnrp1.deja.com>,
sigdock_at_my-deja.com wrote:
> Hi all,
>
> I am trying to build a procedure which can be used to change the
> password of a user. To verify that a user can only change his own
> password I'm using a connect-statement in a DBMS_SQL statemant.
However
> this results in a ORA-0900, invalid statement, error. Does anybody
have
> any idea how this can be solved?
>
> t.i.a.
well, you cannot connect in plsql as you are already connected and there is no way to create another connection....
one way to do this is to ignore the "old_password" all together -- the user is already logged in -- they already supplied it. You know they know their password by the very fact they are running this stored procedure.
If you are trying to make it so that you verify at the moment in time the procedure is run that the person running it is who they are logged in as (to avoid someone walking by someones logged in terminal and changing their password), you could use this procedure:
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;
/
it works by checking the digested password that currently exists with the new digested password that would exist if their password was the one they supplied... If the two do not match -- it puts back the old password.
This procedure has (unavoidable) a flaw in that if the database goes down AFTER we alter the user and BEFORE we put it back -- we may lose that users password. The window is very small but exists. There is no real way to avoid it.
So, your procedure below would become simply:
....
if ( check_pass.is_valid( USER, old_password ) )
then
dbms_sql.parse( ..., 'alter user ' || user || ' identified by ' ||
new_password );
end if;
...
>
> B.J. Meinders
> Oracle DBA
> ASR-ICT
>
> Source Code:
>
> Create or replace procedure
> CHANGE_PASSWORD ( OldPass IN VARCHAR2,
> NewPass IN VARCHAR2,
> NewPass_Ver IN VARCHAR2 )
> As
> --
> -- Declarations
> --
> Cursor_Num INTEGER;
> Length_Passw INTEGER;
> Cmd_String VARCHAR2(100);
> Cursor USERNAME_CURSOR is
> select User
> from DUAL;
> UserNm VARCHAR2(32);
> --
> Cursor DATABASE_CURSOR is
> select name
> from v$database;
> Dbasenm VARCHAR2(4);
> --
> Begin
> --
> -- Verify new password
> --
> If NewPass <> NewPass_Ver then
> DBMS_OUTPUT.PUT_LINE('Password verification error, password not
> changed');
> Else
> Begin
> --
> -- Get username
> --
> open USERNAME_CURSOR;
> fetch USERNAME_CURSOR
> into UserNm;
> close USERNAME_CURSOR;
> Exception
> When others then
> Raise_Application_Error(-20001,'Error while fetching
username');
> End;
> --
> Begin
> --
> -- Get databasename
> --
> open DATABASE_CURSOR;
> fetch DATABASE_CURSOR
> into DbaseNm;
> close DATABASE_CURSOR;
> Exception
> When others then
> Raise_Application_Error(-20002,'Error while fetching
> databasename');
> End;
> --
> Begin
> --
> -- Check old username / password
> --
> Cmd_String := 'connect '||UserNm||'/'||OldPass;
> DBMS_OUTPUT.PUT_LINE(cmd_string);
> Cursor_Num := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(Cursor_Num, Cmd_String, DBMS_SQL.NATIVE);
> DBMS_SQL.CLOSE_CURSOR(Cursor_Num);
> Exception
> When others then
> DBMS_OUTPUT.PUT_LINE(sqlerrm);
> Raise_Application_error(-20000,'Error during login');
> End;
> --
> -- Change password
> --
> Begin
> Cmd_String:= 'alter user '||UserNm||' identified by '||NewPass ;
> Cursor_Num := DBMS_SQL.OPEN_CURSOR;
> If Cursor_Num is null
> then null;
> Else
> DBMS_SQL.PARSE(Cursor_Num, Cmd_String, DBMS_SQL.Native);
> DBMS_SQL.CLOSE_CURSOR(Cursor_Num);
> End If;
> Exception
> When others then
> Raise_Application_Error(-20003,'Error during changing
> password');
> End;
> End If;
> Exception
> When others then
> DBMS_OUTPUT.PUT_LINE(sqlerrm);
> Raise_Application_Error(-20004,'Unhandled error');
> End;
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
-- 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 Wed May 17 2000 - 00:00:00 CEST