Wednesday 17 November 2010

Granting access to multiple tables

Simple anonymous script to grant read access to a number of other user's tables


-- While as a DBA
DECLARE
BEGIN
FOR x in (SELECT owner, table_name FROM all_tables WHERE OWNER IN('SCOTT', 'MYAPP', 'HR'))
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || x.owner || '.' || x.table_name || ' TO myuser';
END LOOP;
END;