Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle Grant total schema access to user

Posted on 2007-11-14
7
Medium Priority
?
27,042 Views
Last Modified: 2013-12-19
I have a user (user A) in oracle who has a lot of objects under their schema, I wish to give access to these tables to another user (user B).

I want to avoid the following if possible
1. creating public synomyns for all the objects under user A.
2. having to grant User B access to User A's on a object level (for example 1000000 tables and 1 million grants.

Is there a way to do this ? I have seen something to do with proxy users but I am not sure I have understood it correctly.
0
Comment
Question by:richard_gleed
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 21

Accepted Solution

by:
oleggold earned 336 total points
ID: 20280909
You could write a simple loop :
begin

for i in
(select distinct table_name from all_tables where owner='USER_A')
loop
execute immediate 'grant all on '||i.table_name||' to USER_B';
end loop;
end;
/
0
 

Author Comment

by:richard_gleed
ID: 20280951
yes I'm aware of that but I am looking for a cleaner way of doing this if possible, I don't want to be having to run this script to grant access multiple times a day to ensure user B has access to all of user A's schema
0
 
LVL 18

Assisted Solution

by:Jinesh Kamdar
Jinesh Kamdar earned 332 total points
ID: 20281091
Oracle allows granting either system privileges or object privileges. For object privileges, you can specify only an object, not a schema. So ultimately u vil have to issue a grant stmt. to each table in schema A. You can use something like this to make things easier : -

GRANT ALL PRIVILEGES ON table_1 TO user_b;

At best, if you want to avoid PL/SQL, you can write a SELECT (below) to generate all the GRANT statements for u, dump its results in a SQL-script and run it on SQLPLUS prompt.

SELECT 'GRANT ALL PRIVILEGES ON ' || table_name || ' TO USER_B'
FROM user_tables;
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 332 total points
ID: 20284250
If you have multiple user's that you'll have to keep maintaining, then try granting to a role and then grant that role to each user.

That way the user's don't have to be modified just the role.

To keep them in sych automatically you can create ddl triggers to automatically grant anytime that schema creates a new table.

Or a dbms job to find ungranted objects and grant them.


I'm curious as to why you have such dynamic table creation.  
Seems odd that you wouldn't be able to keep up.

Hopefully it's not because an application is creating and destroying tables to store "temporary" information.



0

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

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…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

597 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