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

A copy of this was sent to Vijay Damodaran <vijayd_at_nortel.com> (if that email address didn't require changing) On Wed, 23 Sep 1998 10:34:58 -0500, you wrote:

>Hi,
>
>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.
>

thats right -- procedures execute as OWNER, not as caller (thats why you can create a table, write a procedure that inserts into the table and grant execute on that procedure without having to grant INSERT on the table to all involved...)

How to fix your problem?

1. grant alter user to the owner of this procedure
2. remove the a_userid in parameter from the procedure
3. replace all references to a_userid with USER instead

That way, it can change any users password HOWEVER since it only uses the USER psuedo variable it will only change the password for the person currently logged in (so they cannot use it to change someone elses password as they are doing now)...

>
> Can u tell me why this happens??
>
> Thanks,
>
> VJ.
>
>--------------------

[snip]  

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.