[Last Call] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2007-10-12
Medium Priority
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

Expert Comment

ID: 20066232
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 35

Accepted Solution

johnsone earned 1500 total points
ID: 20066522
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html 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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

830 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