I need to create a database user where he can have all DML permissions, but when creating the grant I have to specify table by table, there is some form of permission for the whole bank, follow my example :
GRANT select, update, delete, insert ON BANCO01.TABELA01 TO STACKOVERFLOW
But like I said, I'm having to set one table per table!
I like to generate the grant scripts at hand, since we usually run 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 think it will solve your case.
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;