To generate grant instructions for tables in an Oracle DB specific schema
This recipe provides a simple yet effective mechanism to generate the necessary instructions to make it possible to grant privileges on specific tables in an Oracle DB schema.
Go to your Oracle SQL developer (or any other client connection you use) and execute the following instruction (adapt to your own needs):
SELECT 'GRANT ALL ON <schema>.' || table_name || ' to <schema>;' FROM dba_tables WHERE OWNER = '<schema>';
Now, simply copy the instructions and execute them.
Executing the previous statement’s generated instructions will grant access to all tables on schema <schema>.