CAN WE GRANT ACCESS TO MULTIPLE TABLES AT A TIME IN ORACLE?
The answer is NO
kIsH@Xhydra<>grant select on u1.t,u2.t1 to u3;
grant select on u1.t,u2.t1 to u3
*
ERROR at line 1:
ORA-00905: missing keyword
kIsH@Xhydra<>grant select on u1.t,select on u2.t1 to u3;
grant select on u1.t,select on u2.t1 to u3
*
ERROR at line 1:
ORA-00905: missing keyword
1)Workaround can be to use the script to generate multiple grant statements
set heading off
select 'grant select on '||owner||'.'||table_name|| ' to U3;' from dba_tables where owner in ('U1','U2');
2)Use ROLES as a standard way
kIsH@Xhydra<>create role MULTITABLEROLE;
Role created.
kIsH@Xhydra<>set heading off
kIsH@Xhydra<>select 'grant select on '||owner||'.'||table_name|| ' to U3;' from dba_tables where owner in ('U1','U2');
grant select on U1.T to U3;
grant select on U2.T1 to U3;
kIsH@Xhydra<>grant MULTITABLEROLE to U3;
Grant succeeded.