Strange stored procedure

From: Joe <joetin_at_netvigator.com>
Date: Wed, 22 Mar 2000 22:51:45 +0800
Message-ID: <38D8DE01.61E595F5@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 - 08:51:45 CST