Solved

Oracle Grant total schema access to user

Posted on 2007-11-14
7
26,054 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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.

776 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