Solved

Oracle Grant total schema access to user

Posted on 2007-11-14
7
26,237 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 84 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 83 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 83 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
 

Expert Comment

by:stalreja335
ID: 23387352
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle differnce between two timestamps 5 38
date show only hh:mm 2 39
plsql job on oracle 18 78
oracle query 3 26
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

733 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