Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: grant any privilege

    Our analysts are able to work across schemas, so they've got the GRANT ANY PRIVILEGE privilege in the database. We're working on an application for end-users which will display the contents of our LOOKUP tables, but we're only storing the SQL in order to draw this information in the app:

    Code:
    select CODE, DESCRIPTION from lookup_table
    We'd like to allow this application to grant SELECT access on the lookup tables as the SQL is stored along with the table information, via a procedure like

    Code:
    create or replace procedure app_s.grant_select (p_schema varchar2, p_table varchar2) as
          ddl_string varchar2(100);
    BEGIN
    
        ddl_string := 'GRANT SELECT ON '|| p_schema ||'.'|| p_table ||' TO APP_S_ROLE';
        EXECUTE IMMEDIATE ddl_string;
    
    END;
    Mostly this is for convenience's sake, as the analysts will be using the app to enter this information. This way they won't have to remember to switch back to SQL*Plus to grant the priv.

    After granting GRANT ANY PRIVILEGE to APP_S, this PROCEDURE still doesn't work. Is there an alternative method or privilege which will allow this procedure to run?

    -cf

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    You may need to grant th "GRANT ANY OBJECT PRIVILEGE" (oracle 9i+)
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Yeppers ... it is.


    Thanks,
    -Chuckers

Posting Permissions

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