user privileges

[manjunathk]

Hi friends,
I have a procedure owned by user1. A global temporary table and a package are also a part of the procedure.
I wanted to give user2 , privilege to execute this procedure.
So, i granted execute privilege on both package and procedure and also, select, delete etc on the global temp table.
Still, when I try to execute the procedure as user2, it comes out with the message that "ORA-00942: table or view does not exist".
What else is needed to be done to execute the procedure as user2?
I am running 8.1.6 on compaq tru-64
Thanks
manjunath

[lacey]

I believe you need to grant user2 the execute any procedure privilege in order for user2 to execute the procedure owned by another user...

[lacey]

also....are you referencing the table name as owner.table_name.......or you can creat a synonym for the table...for user2

[manjunathk]

Thanks Lacey,
But does not grant any procedure will enable user2 to execute all procedures? I want user2 to be able to execute just one procedure.
Thanks
manjunath

[lacey]

yes that is true however you can not grant object level privileges with triggers or procedures....the only options you have is to grant a user "execute any procedure" or "execute any trigger" or actually have the user own the procedure or the trigger.....I know this sucks...I have had a similar problem in the past...and unless this has changed you either have to have the user own the object or grant execute any.......

[jmodic]

Originally posted by lacey
yes that is true however you can not grant object level privileges with triggers or procedures....the only options you have is to grant a user "execute any procedure" or "execute any trigger" or actually have the user own the procedure or the trigger.....I know this sucks...I have had a similar problem in the past...and unless this has changed you either have to have the user own the object or grant execute any.......

1. There is no such privilege as "GRANT EXECUTE ANY TRIGGER". Triggers are executed automatically, you can't (directly) control for which user they will be executed and for which won't.
2. Sure you can can "GRANT EXECUTE ON myproc TO anyone_I_wish". No need to GRANT EXECUTE ANY PROCEDURE, you can grant per particular procedure/function/pacakage.

Both those points are unchanged since PL/SQL is available from inside the database (Oracle7).