Re: priv problem with stored procedure

In article <01bc86f6$65692e40$3b34dd80_at_EllenRus.us.dg.com>, Ellen_Russell_at_dg.com says...
>
>I've created a stored procedure to allow users to change their oracle
>password from our Powerbuilder app. However, when I try to run it, I get
>an insufficient privileges error. I've granted execute access on the
>stored procedure and even running it as myself with dba priv's I get this
>error. The stored procedure is shown below anyone have any idea's:
>
>Create Or Replace Procedure sp_change_password
>(i_client_id in varchar2, i_new_pass in varchar2) AS
>v_cursor Integer;
>v_ret INTEGER;
>v_cmd varchar2(50);
>BEGIN
> v_cmd := 'ALTER USER ' || i_client_id || ' IDENTIFIED BY ' ||
>i_new_pass;
> v_cursor := dbms_sql.open_cursor;
> dbms_sql.parse(v_cursor,v_cmd,dbms_sql.NATIVE);
> v_ret := dbms_sql.execute(v_cursor);
> dbms_sql.close_cursor(v_cursor);
>END;
 You cannot use ROLE based privileges. Even SYSTEM would need  SQL> grant ALTER USER to SYSTEM
 (I've just tried it - it works)

 How do you prevent a user from changing the password of  another user, even SYSTEM !! Or did I miss something.

 Regards Garry  

Garry Lawton                                      Tel: 43-512-507-2314
EDV-Zentrum der Universitaet Innsbruck            Fax: 43-512-507-2944
Technikerstr. 13 
6020 Innsbruck, Austria                e-mail: garry.lawton_at_uibk.ac.at
----------------------------------------------------------------------