Privilege Needed to compile in another schema

[gopi]

User1 wants to compile a stored procedure owned by another schema, say User2 schema.

The procedure begins with
CREATE OR REPLACE PROCEDURE user2.procedureABC AS ...
(Notice the porcedure explicitely mentions the schema owner)

All privs for the proc have been granted to User1:
User2> grant execute, debug, all on procedureABC to user1;

My problem is User1 still cannot compile the procedure from his own schema. One solution is to grant the system privilege "Alter any procedure" - this works but now User1 can compile any procedure in any schema.

Please advise.

Thank you!

[gandolf989]

Try grant alter, execute on procedureABC to user1;

[gopi]

Hmmm....I thought of that...look at the unfortunate message:

ORA-02225: only EXECUTE and DEBUG privileges are valid for procedures

SQL>

[gandolf989]

When I did my example, I actually did it for a package and it worked. I didn't realize that there was a difference. Maybe you can create another procedure owned by user2 to compile the other procedure and then grant execute for that procedure to user1. Either that or change you procedure to a package. I'm not sure why you can't grant alter on a procedure. Also, have you tried creating a synonym for the procedure and compiling the synonym? It might not work, but its worth a try.

[gopi]

does not work in 9i

unix> oerr ora 2225
02225, 00000, "only EXECUTE and DEBUG privileges are valid for procedures"
// *Cause: An attempt was made to grant or revoke an invalid privilege
// on a procedure, function or package.
// *Action: Do not attempt to grant or revoke any privilege besides EXECUTE
// or DEBUG on procedures, functions or packages.