Privileges for PL/SQL ...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Privileges for PL/SQL ...

  1. #1
    Join Date
    Feb 2004
    Posts
    77

    Privileges for PL/SQL ...

    If I want to execute the following command in PL/SQL procedure:

    'grant select, insert, update, delete on owner.table_name to public'

    using execute immediate.

    What kind of privileges does the USER executing the procedure needs.

    Assuming the table owner is different than the user running the procedure.

    Thanks in advance.

  2. #2
    Join Date
    Sep 2001
    Posts
    52

    Re: Privileges for PL/SQL ...

    grant with admin options

  3. #3
    Join Date
    Feb 2004
    Posts
    77
    What kind of privilege should it be?

    example: grant select any table to user1 with admin option. ???

  4. #4
    Join Date
    Feb 2004
    Posts
    77
    Found it. It is

    grant GRANT ANY OBJECT PRIVILEGE to user1;

    Thanks.

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    Originally posted by oracle_faq
    Found it. It is

    grant GRANT ANY OBJECT PRIVILEGE to user1;

    Thanks.
    Thants like hanging up a picture with a sledge hammer.
    You might not want to grant admin option to public.
    Just grant execute on the procedure to the people who need it.

    Code:
    grant select, insert, update, delete 
       on owner.table_name to schema_owner with grant option;
    Last edited by gandolf989; 08-18-2004 at 12:40 PM.

  6. #6
    Join Date
    Feb 2004
    Posts
    77
    The scenario I am trying to solve is as below.

    1. Create a table in a procedure.
    2. Grant insert update select delete on the table...within the same procedure.

    Since I am creating the table in a different schema that the user who is executing the procedure...the procedure execution fails. So was looking for a privilege I need to give to the user to make it work. This user already has DBA privs. The only problem is that PL/SQL does not see these privs...so I have to grant them explicitly.

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    create any table
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    Originally posted by oracle_faq
    The scenario I am trying to solve is as below.

    1. Create a table in a procedure.
    2. Grant insert update select delete on the table...within the same procedure.

    Since I am creating the table in a different schema that the user who is executing the procedure...the procedure execution fails. So was looking for a privilege I need to give to the user to make it work. This user already has DBA privs. The only problem is that PL/SQL does not see these privs...so I have to grant them explicitly.
    This privilege Should work.
    Code:
    GRANT create any table TO schema_owner;
    It is probably best if you give out privileges that user need and no more.

  9. #9
    Join Date
    May 2002
    Posts
    2,645
    You may also want to consider the "Privileges granted via role are disabled inside stored procedures" limitation.

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