-
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 Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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,
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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.
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|