Re: Strange stored procedure

The answer to this is ROLES.
Roles are not enabled in stored procedures by default. In Oracle 8.1.5 (release 8i) you have the choice of invoking the procedure with definers rights (creator of procedure) or invokers rights (the person who runs it). The latter choice should resolve your problem

For further details refer to the website of Thomas Kyte, http://osi.oracle.com/~tkyte

Regards,

Sybrand Bakker, Oracle DBA

Joe <joetin_at_netvigator.com> wrote in message news:38D8DE01.61E595F5_at_netvigator.com...
> 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
>
Received on Wed Mar 22 2000 - 10:02:04 CST