2

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?

3 Answers 3

2

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.

2
  • Isn't there any other way to do it? I already have done that for several of our schemas, but some of them I don't own it, i..e I don't create objects there. However, I do have the need to have select access to any of the objects there. I would need to ask the schema owner to run such script for me. Jul 1, 2016 at 19:19
  • @CraigStevenson - The schema owner or the DBA would need to run the script, yes. You could ask the DBA to grant you the grant any object privilege as Nick suggests understanding that this would be a vastly more powerful privilege. Jul 1, 2016 at 19:22
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!

5
  • That would work. The downside would be that dwhmanager would be able to grant much more than SELECT. You could grant delete or update on any table in the database to any other user. That's quite a bit more powerful than you strictly need. Jul 1, 2016 at 17:33
  • That is true. As a DBA, I wouldn't grant out that privilege because as you said dwhManager could grant out delete/update/insert without reviewing auditing on a regular basis. But it definitely works :)
    – Nick S
    Jul 1, 2016 at 17:36
  • @NickS, could you please elaborate in the "GRANT GRANT...." part, I've never seen that before? Could it then be something like "GRANT GRANT SELECT ANY TABLE TO dwhmanager;" ?? Jul 1, 2016 at 19:34
  • The "GRANT ANY OBJECT PRIVILEGE" is a system privilege. So my answer is saying grant the system privilege GRANT ANY OBJECT PRIVILEGE". It looks funny because of the double grant but that is not a special keyword or function.
    – Nick S
    Jul 1, 2016 at 19:38
  • 4
    I would question why (1) the warehouse manager needs this level of privilege, and (2) why the OP is having to tell the DBA how to do it. On the first question, it should be the DBA that grants the privileges, on request from the appropriate manager. That's called 'separation of duties' and is fundamental to security. On the second question, if the DBA doesn't know how to do it, you've got the wrong DBA.
    – EdStevens
    Jul 1, 2016 at 21:02
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.