select 'grant '|| privilege || ' to <schema> ;' from dba_sys_privs where privilege like '%SELECT%'; select 'grant '|| privilege || ' to <schema> ;' from dba_sys_privs where privilege like '%EXECUTE%';
A ROLE hierarchy like the above makes it MUCH easier to control 'who can do what'. You can easily grant/revoke privileges to EXACTLY the users that you want by issuing a grant/revoke to the appropriate role. This helps ensure that you don't accidentally grant/revoke a privlege to the wrong user.
-- create a role for read only access to EVERYTHING we define later create role read_only_everything_role; -- create a role for read only access to scott create role read_only_scott_role; -- create a role for read only access to hr create role read_only_hr_role; -- provide the grants that a read only user might need from scott grant select on scott.emp to read_only_scott_role grant select any dictionary to read_only_scott_role grant select on scott.dept to read_only_scott_role grant select any dictionary to read_only_scott_role -- provide the grants that a read only user might need from hr grant select on hr.employees to read_only_hr_role; grant select any dictionary to read_only_test_role grant select on hr.departments to read_only_hr_role grant select any dictionary to read_only_hr_role -- provide the grants to the EVERYTHING role grant read_only_scott_role to read_only_everything_role; grant read_only_hr_role to read_only_everything_role; -- create the read only users -- create a read only user for scott only create user read_only_scott_user identified by read_only_scott_user -- create a read only user for hr only create user read_only_hr_user identified by read_only_hr_user -- create a read only user for EVERYTHING create user read_only_everything_user identified by read_only_everything_user -- grant connect and resource to the users so they can connect grant connect, resource to read_only_scott_user; grant connect, resource to read_only_hr_user; grant connect, resource to read_only_everything_user; -- grant the read only roles to the appropriate user grant read_only_scott_role to read_only_scott_user; grant read_only_hr_role to read_only_hr_user; grant read_only_everything_role to read_only_everything_user;
will grant SELECT access on every table in A's schema to the READ_ONLY_A role.
BEGIN FOR t IN (SELECT * FROM dba_tables WHERE owner = 'A') LOOP EXECUTE IMMEDIATE 'GRANT SELECT ON a.' || t.table_name || ' TO read_only_a'; END LOOP; END;