Re: Strange stored procedure

A copy of this was sent to Joe <joetin_at_netvigator.com> (if that email address didn't require changing) On Wed, 22 Mar 2000 22:51:45 +0800, you wrote:

>I have two Oracle users, which Oracle is 8.1.5 on Solaris.
>I have a stored procedure will use the package dbms_sql.
>
>I can create the procedure on both schemas successfully.
>I can execute the procedure on both schemas successfully.
>
>However, if I create the procedure in schema "SIG_TEST", create
>the public synonym on the procedure and grant execute
>right to user/schema "B", when I execute the procedure in
>schema "B", the following error occurs:
>
>*
>ERROR at line 1:
>ORA-01031: insufficient privileges
>ORA-06512: at "SYS.DBMS_SYS_SQL", line 487
>ORA-06512: at "SYS.DBMS_SQL", line 32
>ORA-06512: at "SIG_TEST.MAINTAIN_USER_DBA", line 32
>ORA-06512: at line 1
>
>I wonder why this appears since I can execute the procedure if the
>procedure
>is created in 'B' own schema. The procedure is MAINTAIN_USER_DBA.
>
>Any help is appreciated. Thanks in advanced.
>
>Thanks
>Joe
>joetin_at_netvigator.com

plsql by default (and in 8.0 and before ONLY) runs with the base privs of the OWNER of the procedure (no roles).

see http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html

when SIG_TEST created the procedure and B ran it -- it ran with the set of privs granted directly to SIG_TEST only.

--
http://osi.oracle.com/~tkyte/  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Mar 22 2000 - 11:38:13 CST