Create a user
SQL> create user b identified by b;
User created.
SQL> grant select table,create session to b;
From target schema idl
SQL>conn idl/idl;
connected
sql>GRANT SELECT ON AUDIRAIL TO b ;
From source schema
sql>conn b/b;
connected
SQL> select * from idl.audirail;
select * from tab where tname = 'AUDIRAIL';
You can write a simple procedure to achieve this :
set serveroutput on
DECLARE
sql_txt VARCHAR2(300);
CURSOR tables_cur IS
SELECT table_name
FROM dba_tables
where owner='idl'
and table_name in ('audirail', 'audirail2') ;
BEGIN
dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:='GRANT SELECT ON '||audirail|| TO GPS;
execute immediate sql_txt;
END LOOP;
END;
/
