Solved

Oracle Grant total schema access to user

Posted on 2007-11-14
7
25,962 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
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 73

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Migrating an SQL 2008 database to Oracle 12c 3 90
case statement in where clause with not exist 15 46
Get the parent node - XMLTYPE 9 56
Oracle - SQL Parse String 5 20
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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

895 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

17 Experts available now in Live!

Get 1:1 Help Now