CODEWITHSUNDEEP
oracle-databaseoracle11g
Savke
Savke

Reputation: 131

Is it possible that one user creates a table in a schema of another user and have all privileges to his schema?

I have two users, USER1 and USER2. I want to do this:

  1. Give all permissions to the USER2 for USER1 schema. That USER2 can create table, insert, update, delete, drop etc.. in USER1 schema

  2. When USER2 creates a table, view, etc.., to create in USER1's schema.

  3. Creating new users, like USER3, to whom USER2 can give all permissions to USER1's schema, like permissions that USER2 have to schema of USER1.

Can I accomplish this, and if it's possible, how can I do it?

I have found some of the answer Grant permission from one user to another in Oracle

But there is not explained how to create ROLE for all tables but step by step granting select permission to the table one by one. What when USER2 have 100 tables, is there a way in one step do that, to grant select permission to all tables that is currently in USER2 schema, and future table that USER2 will create. Is it possible to manage this?

Upvotes: 0

Views: 592

Answers (1)

Kris Johnston
Kris Johnston

Reputation: 738

Instead of actually granting everything from one user to another, perhaps proxy connections would be a good fit for you.

With proxy connections, you can essentially login as someone else without knowing their password. This allows you to assume the identity of that user so you are guaranteed that anything that they can do, you can do as their proxy.

In addition, it also sets their schema as your schema so that any objects you create that aren't qualified with the schema name are created in their schema, not yours.

Of course, the downside is that since you become the proxied user, you lose your own grants for that connection.

sqlplus / as sysdba

alter user user1 grant connect through user2;
exit;

sqlplus user2[user1]
password: <Type in the password for user2>    

show user;
--------
USER1

create view mydual as select * from dual;

select owner from all_views where view_name = 'MYDUAL';
--------
USER1

In SQLDeveloper (4.1.5), you can specify the proxy connection for a connection by:

1) Press the Advanced button

2) Checking the Proxy Connection box

3) Type the username of who you want to proxy as in the Proxy Client dialog box

4) Leave the Proxy Password fiend empty

5) Press OK

6) Use the connection

A little old, but AskTom has a good article on proxy connections.

Upvotes: 2

Related Questions