How do you grant privileges to other users so that they can modify your procedure?
Grant update, alter on test_proc to scott; -- This does not work!
Printable View
How do you grant privileges to other users so that they can modify your procedure?
Grant update, alter on test_proc to scott; -- This does not work!
If I understand you correctly, you would have to grant create procedure to user, since effectively speaking, one would be creating or replacing it.
You can't. If you realy wan't to do that, you have to grant CREATE ANY PROCEDURE to those users, so that they can
CREATE OR REPLACE PROCEDURE scott.test_proc .....
They essentially have to owerwrite (using ... OR REPLACE ... option) the existing procedure to modify it.
Note also that this is *extremely* powerful privelege, with it user can create/replace procedure in *any* schema except in SYS's. With this privilege (accompanied probably with EXECUTE ANY PROCEDURE) a skilfull user can literaly destroy your entire database or make whatever he/she pleases in any schema!
I would never grant this privilege to any user that I wouldn't grant DBA role!
Jurij,
SQL> connect system/manager
Connected.
SQL> grant create any procedure to demo;
Grant succeeded.
SQL> connect demo/demo
Connected.
SQL> CREATE OR REPLACE PROCEDURE SCOTT.fwd( num in number) IS
2 rtn number:=1;
3 BEGIN
4 for i in 1..num loop
5 DBMS_OUTPUT.PUT_LINE(to_char(i));
6 end loop;
7 END;
8 /
CREATE OR REPLACE PROCEDURE SCOTT.fwd( num in number) IS
*
ERROR at line 1:
ORA-01031: insufficient privileges
Why? (demo has create any procedure privilege)
How is the CREATE ANY PROCEDURE granted to DEMO? I assume it is via one of the roles, not directly as explicit grant?
It has to be granted directly to user, not via role. I just tried your example with the same users DEMO and SCOTT and it works if DEMO has an explicit privilege CREATE ANY PROCEDURE.
HTH,
Probably the procedure SCOTT.FWD already existed, DEMO doesn't have privilege to ALTER scott's procedures.
To create a procedure in another schema you need privilege CREATE ANY PROCEDURE. To alter (replace) a procedure in another schema you need privilege ALTER ANY PROCEDURE. To drop a procedure in another schema you need privilege DROP ANY PROCEDURE.
I agree with jmodic that you should be carefull with granting these privileges to users.
Thank you!