How to grant privileges to one user to access other users in Oracle?-CodePudding

There are 3 schemas: DEMO1, DEMO2, DEMO3 I want to grant DEMO1 permissions to perform all the operations on DEMO2 and not DEMO3.

ALL Operations means: Select, Update, Insert, Delete

How can I grant the privileges for that in Oracle SQL Developer?

CodePudding user response：

You can't really do that. With many DDL privileges - as astentx pointed out - you either are constrained to what you own, or you can affect ANY table in the system, not just one other user. There's no middle ground unless you're also working with add-on enterprise products like Database Vault. If you're talking about DML (insert, update, delete of data), then grant the specific table privileges to a role and grant the role to DEMO1.

create role demo2_dml;
grant insert, update, delete on demo2.table_a to demo2_dml;
grant insert, update, delete on demo2.table_b to demo2_dml;
...
grant role demo2_dml to demo1;
alter user demo1 default role all;

Alternatively, if you must have DDL privileges as well, you could give DEMO1 proxy privileges to become DEMO2 and assume all of its privileges on its objects.

alter user demo2 grant connect through demo1;

Then connect using demo1[demo2] as the username, with demo1's password:

connect demo1[demo2]/demo1password@database_service

demo1 then becomes demo2 (without needing to know demo2's password) and can do anything demo2 would be able to do. demo1 would not have access to its own objects while doing this, however.