Home -> Usenet -> c.d.o.tools -> Trying to change user's password using PL/SQL procedure.

Trying to change user's password using PL/SQL procedure.

From: Vijay Damodaran <vijayd_at_nortel.com>
Date: Wed, 23 Sep 1998 10:34:58 -0500
Message-ID: <36091521.E2BF8573_at_nortel.com> 



Hi,
[Quoted] 
I have written a procedure which will be called by Dev2000 forms application
to change a user's passwd.

I am using Oracle 7.3.2 and PL/SQL 2.2.3. The procedure is as follows
and currently contains a lot of

debug messages.

The procedure calls "alter user ..." using dbms_sql.execute.

The steps that I took to implement this procedure are:

   1. Logged in to SQLPlus as "gsmdba" (Does not have DBA
privilege) and compiled the procedure

        "ChangePasswd". No 
errors.

   2. Created public synonym "ChangePasswd" for "gsmdba.ChangePasswd".

   3. Granted execute on "ChangePasswd" to public.

 The problem is that I can only change the passwd for gsmdba with
this procedure. Even when I login

  as someone else, I can change passwd for gsmdba, but not for
the user whom I logged in as. I seems

  as if the procedure executes as the owner of the procedure.

 

   Can u tell me why this happens??

   Thanks,

   VJ.

--------------------

  Here is the source code:

  =================


create or replace




procedure changePasswd(a_userid IN varchar2,




        a_new_passwd IN varchar2)




AS




  uid varchar2(20);




  sql_stmt   varchar2(100) := 'alter user "' || upper(a_userid)
|| '" identified by ' || a_new_passwd;




  exec_cursor     integer default dbms_sql.open_cursor;




  rows_processed  number  default 0;




BEGIN




  dbms_output.enable;




  select user into uid from dual;




  dbms_output.put_line('In changePasswd user = ' || uid);




  dbms_output.put_line('Before parsing, sqlstmt = "' || sql_stmt
|| '"');




  dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.NATIVE );




  dbms_output.put_line('before executing');




  rows_processed := dbms_sql.execute(exec_cursor);




  dbms_output.put_line('before closing cursor');




  dbms_sql.close_cursor( exec_cursor );




  dbms_output.put_line('after closing cursor.');




  return;




EXCEPTION




  WHEN OTHERS THEN




    dbms_output.put_line('within exeception - ' || SQLERRM);




    RAISE;




END;




/



 

 

  These are the outputs that I get:

   =======================

   A. Logged in as GSMDBA. Trying to change passwd for GSMDBA.
This works fine.

-------------

               
GSMDBA SQL> execute changepasswd('GSMDBA', 'testing');

                            
In changePasswd user = GSMDBA

                            
Before parsing, sqlstmt = "alter user "GSMDBA" identified by testing"

                            
before executing

                            
before closing cursor

                            
after closing cursor.

-------------

 

   B. Logged in as CNT69081. Trying to change passwd for CNT69081.
This DOES NOT WORK.

-------------

                
CNT69081 SQL> execute changepasswd('CNT69081', 'GSMDBA');

                             
In changePasswd user = CNT69081

                             
Before parsing, sqlstmt = "alter user "CNT69081" identified by GSMDBA"

                             
within exeception - ORA-01031: insufficient privileges

                              
begin changepasswd('CNT69081', 'GSMDBA'); end;

                             
*

                             
ERROR at line 1:

                             
ORA-01031: insufficient privileges

                             
ORA-06512: at "GSMDBA.CHANGEPASSWD", line 23

                             
ORA-06512: at line 1

-------------

   C. Logged in as CNT69081. Trying to change passwd for GSMDBA.
This SHOULD FAIL, but it does

         not and that is not
right.

-------------

               
CNT69081 SQL> execute changepasswd('GSMDBA', 'testing');

                            
In changePasswd user = CNT69081

                            
Before parsing, sqlstmt = "alter user "GSMDBA" identified by testing"

                            
before executing

                            
before closing cursor

                            
after closing cursor.

-------------

 

Thanks,

VJ.

-- 
Vijay Damodaran
vijayd_at_nortel.com
Work: (972) 685-8150

 
Received on Wed Sep 23 1998 - 17:34:58 CEST












Original text of this message