I'm sure someone out there has done this and we're hoping to not have to re-invent the wheel.

We are looking for a table-driven PL/SQL script that issues privileges to tables in other schemas within the database. We want it to be table-driven to make it flexible. We are thinking about a new configuration table with columns like 1. the Oracle username or role name, 2. the target schema in which the privileges are to be granted, and 3. the type of privilege (for example, "INSERT" or "SELECT"). We want the script to select from this configuration table and check to see if the users/roles in this configuration table have the privileges in the target schema. If the users/roles do not have the privileges listed in the configuration table the script should issue the privileges.

Example:
The configuration table contains the username, the target schema, and the type of privilege. Here's five sample records.
USER1, SCHEMA1, INSERT
USER1, SCHEMA1, UPDATE
USER1, SCHEMA1, SELECT
ROLE1, SCHEMA1, SELECT
ROLE1, SCHEMA2, SELECT

1. If, for example, USER1 (an individual user account) currently has only SELECT privileges on all the tables in SCHEMA1, the script will issue the missing UPDATE and INSERT priveleges on all the tables in SCHEMA1 to USER1.

2. If, for example, ROLE1 (a database role) currently has SELECT privileges on only the tables in the SCHEMA1 schema, the script will issue the missing SELECT privileges on all the tables in the SCHEMA2 schema.

Is there a better way to accomplish this?

Thanks!

begin
   for x in ( select * from your_table )
   loop
      for y in (select * from dba_objects 
                 where owner = x.schema1
                   and object_type in ('TABLE', 'VIEW') )
      loop
          execute immediate 'grant ' || x.what || 
                            ' on "' || y.owner || '"."' || y.object_name || 
                            '" to "' || dbms_assert.simple_sql_name( x.username ) || '"';
      end loop;
   end loop;
end;