Grant access to multiple people working on a project to tables with names matching a particular pattern.

grant_access.sql

-- grant access to multiple people

DECLARE
  usernames VARCHAR2(1024) := '
    jimmy,
    bobby,
    franky,
    alice,
    mandy
  ';
  table_filter VARCHAR2(128) := 'foo|bar';
BEGIN
  
  FOR r IN (
    SELECT LOWER(t.table_name) table_name
    FROM user_tables t
    WHERE REGEXP_LIKE(t.table_name, '^(' || table_filter || '_)')
  ) LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON ' || r.table_name || ' TO ' || usernames;
  END LOOP;
  
END;
/