Granting Permissions to Users for Specific Tables in Oracle

oracleoracle-12cpermissionsSecurity

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?

Best Answer

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.

Related Question