llarava
asked on
How to grand Select access to all the schema tables that a database have to a user?
ORACLE Database
I need to find the way to grant SELECT access to all the tables that differents schemas have on a Database.
Ex: Database name ACCOUNTING contains 3 different schemas A, B and C
How to grant the user MICHAELM select access to the tables on this 3 schemas for the ACCOUNTING database. Of course without using Enterprise Manager Console.
I need a SQL to do that due the amount of tables that every schema has.
I guess is something like that but I really don't know how to user the command
GRANT SELECT ON <schema>.* TO <user>;
Thank you.
I need to find the way to grant SELECT access to all the tables that differents schemas have on a Database.
Ex: Database name ACCOUNTING contains 3 different schemas A, B and C
How to grant the user MICHAELM select access to the tables on this 3 schemas for the ACCOUNTING database. Of course without using Enterprise Manager Console.
I need a SQL to do that due the amount of tables that every schema has.
I guess is something like that but I really don't know how to user the command
GRANT SELECT ON <schema>.* TO <user>;
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if you cannot log as such user, connect as A, then B, then C and run
declare
cursor c_x is
select table_name from all_tables where owner in ('A','B','C');
begin
for r_x in c_x loop
execute immediate 'grant select on '||r_x.table_name||' to michaelm';
end loop;
end;
/