SQL: Give grants to all tables in a schema

Use this procedure and just execute it as normal SQL query

BEGIN
   FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner='Schema_name 1') LOOP
      EXECUTE IMMEDIATE 'grant select,insert,update,delete on '||R.owner||'.'||R.table_name||' to <>';
   END LOOP;
END;

Schema Name 1 : This is like to which schema you needed grants.
Schema name 2: This like which schema needed those grants.

For example: SchemaA should have access on all tables,view of Schema B, then
Schename Name 1 would be Schema B and Schema Name 2 would be Schema A.

Thanks
Raja

0 comments:

Post a Comment