| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: priv problem with stored procedure
stored procedures never run with roles enabled. The owner of the procedure below needs to have had "grant alter user to <ThemSelves>" executed.
To test if you have the necessary priveleges in sql*plus (to see if a particular dynamic sql statement in pl/sql has a chance of succeeding at runtime) you should:
SQL> REM the set role none will mimic the set of privs available to SQL> REM a stored procedure/view/trigger/package/etc
SQL> set role none;
Role set.
SQL> alter user scott identified by tiger; alter user scott identified by tiger
*
That shows that I *do not* have the necessary based privelege of "alter user" needed to create a stored procedure that can alter the user.
On 2 Jul 1997 14:39:36 GMT, "Ellen Russell" <Ellen_Russell_at_dg.com> wrote:
>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;
>
>
>
>--
>Ellen_Russell_at_dg.com
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |