Grant for several Oracle tables

Question

I need to create a database user, where he can have permission from all DML, but when creating Grant I have to specify table by table, there is some way to give permission to the whole database, follow my example:

GRANT select, update, delete, insert ON BANCO01.TABELA01 TO STACKOVERFLOW

However, as I said, I’m having to set table by table!

Answer 1

I like to generate the scripts of grant at hand, because we usually perform once, through a query:

SELECT 'GRANT select,update,delete,insert ON ' || x.table_name || ' TO STACKOVERFLOW;'
  FROM all_tables x
 WHERE x.owner = USER;

Then the result I copy and execute.

---

Or the same case using BULK COLLECT, when we have a large volume of tables:

DECLARE
  TYPE t_cursor IS REF CURSOR;
  TYPE t_string_array IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
  vcursor      t_cursor;
  varraystring t_string_array;
  i            BINARY_INTEGER;

BEGIN
  OPEN vcursor FOR
    SELECT 'GRANT select,update,delete,insert ' || x.table_name || ' TO STACKOVERFLOW'
      FROM all_tables x
     WHERE x.owner = USER;

  LOOP
    FETCH vcursor BULK COLLECT
      INTO varraystring;
    EXIT WHEN varraystring.count = 0;
    FOR i IN varraystring.first .. varraystring.last LOOP
      EXECUTE IMMEDIATE varraystring(i);
    END LOOP;
    EXIT WHEN vcursor%NOTFOUND;
  END LOOP;
  CLOSE vcursor;

END;

There are other more complex ways to do it, but I believe this one solves your case.

Answer 2

You can also use EXECUTE in a loop. Ex:

FOR x IN (SELECT * FROM all_tables)
LOOP   
EXECUTE IMMEDIATE 'GRANT select,update,delete,insert ON ' || x.table_name || ' TO STACKOVERFLOW'; 
END LOOP;