DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: how to grant update on procedures?

  1. #1
    Join Date
    Mar 2001
    Posts
    287
    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!

  2. #2
    Join Date
    Apr 2000
    Location
    Baltimore, MD
    Posts
    759
    If I understand you correctly, you would have to grant create procedure to user, since effectively speaking, one would be creating or replacing it.

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  4. #4
    Join Date
    Mar 2001
    Posts
    287
    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)


  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  6. #6
    Join Date
    Jul 2000
    Posts
    296
    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.

  7. #7
    Join Date
    Mar 2001
    Posts
    287
    Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width