How to grant select on all tables in a schema in oracle database?
Ezana Answered question 09/11/2022
We can use a PL/SQL and write a simple loop statement to automatically execute the GRANT SELECT.
Example:
1 2 3 4 5 6 7 | BEGIN FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner='FINANCE') LOOP EXECUTE IMMEDIATE 'grant select on '||R.owner||'.'||R.table_name||' to MEREBA'; END LOOP;END;/ where 'FINANCE' is the owner schema and 'MEREBA' is the grantee. |
Ezana Answered question 09/11/2022