We help IT Professionals succeed at work.

How to grand Select access to all the schema tables that a database have to a user?

llarava
llarava asked
on
2,991 Views
Last Modified: 2013-12-19
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.
Comment
Watch Question

Commented:
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;
/
Senior Oracle DBA
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.