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

Posted on 2007-10-12
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.
Question by:llarava
    LVL 9

    Expert Comment

    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
      cursor  c_x is
        select table_name from all_tables where owner in ('A','B','C');
      for r_x in c_x loop
        execute immediate 'grant select on '||r_x.table_name||' to michaelm';
      end loop;
    LVL 34

    Accepted Solution

    If you are trying to do all tables in the database then the SELECT ANY TABLE privilege may accomplish what you are looking for.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now