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.

Leave a Reply