Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003

    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:

    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

    create or replace procedure app_s.grant_select (p_schema varchar2, p_table varchar2) as
          ddl_string varchar2(100);
        ddl_string := 'GRANT SELECT ON '|| p_schema ||'.'|| p_table ||' TO APP_S_ROLE';
        EXECUTE IMMEDIATE ddl_string;
    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?


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


    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
    Yeppers ... it is.


Posting Permissions

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