Grant permissions to a user to grant select to specific tables in several schemas in Oracle

Question

I administer a small datawarehouse in Oracle. My user dwhmanager was given the SELECT ANY TABLE privilege:

GRANT SELECT ANY TABLE TO dwhmanager WITH ADMIN OPTION;

The idea was to have the ability to grant access to tables in several schemas to the developers in the datawarehouse.

Example:

GRANT SELECT ON DWHFINANCE.SALES TO johndeveloper;

However, I found my self getting the infamous ORA-01031: insufficient privileges

I can however, give "johndeveloper" the SELECT ANY TABLE privilege as well, and from that, he could select on any table on the datawarehouse, however, that's too much, not my intend.

So, what permission to I need to ask to the main DBA to grant me in order to have the power to give permissions in individual tables in other schemas?

Answer 1

You'd need to have select access on the specific table granted to you with the admin option

GRANT SELECT ON <<table name>> 
   TO dwhManager
 WITH ADMIN OPTION;

That would need to be run for each table. You could do that in a loop with dynamic SQL

BEGIN
  FOR t IN (SELECT * 
              FROM dba_tables
             WHERE owner = <<schema owner>>)
  LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON ' ||
                         t.owner || '.' || t.table_name ||
                         ' TO dwhManager WITH ADMIN OPTION';
  END LOOP;
END;

You'd need to do a new grant every time you create a new table.

Answer 2

You probably want GRANT ANY OBJECT PRIVILEGE

AS DBA:

GRANT GRANT ANY OBJECT PRIVILEGE TO dwhmanager;

AS dwhmanager:

grant select on DWHFINANCE.SALES TO johndeveloper;

Presto!

Answer 3

For me it worked without the WITH ADMIN OPTION and with double quotes in the name of the table and schema

set serveroutput on format word_wrapped;
BEGIN
  FOR t IN (SELECT * 
              FROM dba_tables
             WHERE owner = 'EIDPKI')
  LOOP  
    DBMS_OUTPUT.put_line('GRANT SELECT ON "' || t.owner || '"."' || t.table_name || '" TO OTC_READONLY');
    EXECUTE IMMEDIATE 'GRANT SELECT ON "' || t.owner || '"."' || t.table_name || '" TO YourUser';
  END LOOP;
END;