Compile Privilege

[lesstjm]

Is there a right I can grant to a user that would allow them to compile objects in my schema?

[davey23uk]

Originally posted by lesstjm
Is there a right I can grant to a user that would allow them to compile objects in my schema?

guessing grant any procedure would do that for you

[lesstjm]

user1@tesdb>grant any procedure to user2;
grant any procedure to user2
*
ERROR at line 1:
ORA-00990: missing or invalid privilege

[marist89]

http://download-west.oracle.com/docs...2a.htm#2073689

[jmodic]

Originally posted by lesstjm
Is there a right I can grant to a user that would allow them to compile objects in my schema?

No, no way to limit that compiling objects privilege to only one schema. It's all or nothing - you can only grant that for the whole database, not for a particular schema. You would need to grant that user two system privileges:

ALTER ANY PROCEDURE;
ALTER ANY TRIGGER;

The problem with triggers is that he will not be only able to compile them, he'll also have the power to disable/enable them.

[srt]

Give
Grant execute on proc-name to user2.
create public synonym.

[slimdave]

The most secure way would probably be to create a definer's rights stored procedure in your schema to accept an object name and type and compile it, and grant execute privilege to the other user on that procedure.