user privileges
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: user privileges

  1. #1
    Join Date
    Oct 2000
    Posts
    211
    Hi friends,
    I have a procedure owned by user1. A global temporary table and a package are also a part of the procedure.
    I wanted to give user2 , privilege to execute this procedure.
    So, i granted execute privilege on both package and procedure and also, select, delete etc on the global temp table.
    Still, when I try to execute the procedure as user2, it comes out with the message that "ORA-00942: table or view does not exist".
    What else is needed to be done to execute the procedure as user2?
    I am running 8.1.6 on compaq tru-64
    Thanks
    manjunath

  2. #2
    Join Date
    Dec 2000
    Posts
    15
    I believe you need to grant user2 the execute any procedure privilege in order for user2 to execute the procedure owned by another user...

  3. #3
    Join Date
    Dec 2000
    Posts
    15
    also....are you referencing the table name as owner.table_name.......or you can creat a synonym for the table...for user2

  4. #4
    Join Date
    Oct 2000
    Posts
    211
    Thanks Lacey,
    But does not grant any procedure will enable user2 to execute all procedures? I want user2 to be able to execute just one procedure.
    Thanks
    manjunath

  5. #5
    Join Date
    Dec 2000
    Posts
    15
    yes that is true however you can not grant object level privileges with triggers or procedures....the only options you have is to grant a user "execute any procedure" or "execute any trigger" or actually have the user own the procedure or the trigger.....I know this sucks...I have had a similar problem in the past...and unless this has changed you either have to have the user own the object or grant execute any.......

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by lacey
    yes that is true however you can not grant object level privileges with triggers or procedures....the only options you have is to grant a user "execute any procedure" or "execute any trigger" or actually have the user own the procedure or the trigger.....I know this sucks...I have had a similar problem in the past...and unless this has changed you either have to have the user own the object or grant execute any.......
    1. There is no such privilege as "GRANT EXECUTE ANY TRIGGER". Triggers are executed automatically, you can't (directly) control for which user they will be executed and for which won't.
    2. Sure you can can "GRANT EXECUTE ON myproc TO anyone_I_wish". No need to GRANT EXECUTE ANY PROCEDURE, you can grant per particular procedure/function/pacakage.

    Both those points are unchanged since PL/SQL is available from inside the database (Oracle7).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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