Link to home
Start Free TrialLog in
Avatar of llarava
llaravaFlag for Afghanistan

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.
Avatar of konektor
konektor
Flag of Czechia image

connect to user, which has access to tables of users A, B, C - it has to be granted with grant option on theese tables, or DBA.
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;
/
ASKER CERTIFIED SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial