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 := '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?