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

Thread: Privilege Needed to compile in another schema

  1. #1
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510

    Privilege Needed to compile in another schema

    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!


  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Try grant alter, execute on procedureABC to user1;

  3. #3
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    Hmmm....I thought of that...look at the unfortunate message:

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

    SQL>

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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.

  5. #5
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    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.

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