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.
llaravaAsked:
Who is Participating?
 
johnsoneSenior Oracle DBACommented:
If you are trying to do all tables in the database then the SELECT ANY TABLE privilege may accomplish what you are looking for.
0
 
konektorCommented:
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;
/
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.