We help IT Professionals succeed at work.

How access to another table in other user?

Patrix78
Patrix78 asked
on
Hello

i have two user in the database (user1 and user2)

The user system can access to table of user1 and user2.

user1 can not see table of user2 and viceversa.

Is there way to permit access the table of user1 to user2

patrice
Comment
Watch Question

Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
>>Is there way to permit access the table of user1 to user2

As user2:
grant select on table1 to user1;

As user1:
grant select on table2 to user2;

You might even create synonyms so the users don't have to add the schema:
as user 1:
select * from user2.table2;

With a synonym created as user2 user1 can then do:
select * from table2;
Just to mention, there is another way ;-)

As user1
alter session set current_schema = user2;

Open in new window


As user2
alter session set current_schema = user1;

Open in new window

Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>Just to mention, there is another way ;-)


Still need the grants...
Still need the grants...
For sure, but you'd get rid of the (public) synonyms...

Moreover, this becomes quite handy when using EBR ;-)
Try using public synonyms within a EBR enabled environment :-))
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I never advocate PUBLIC anything in Oracle.  I wouldn't miss it if Oracle eventually dropped that role completely.
I wouldn't miss it if Oracle eventually dropped that role completely.
Me neither ;-) Good one :-))
Mark GeerlingsDatabase Administrator

Commented:
Public synonyms aren't the only ones available.  You may want to use private ones.  For example, if user1 has a table named "products" and user2 has a table named "customers", and you want the opposite schema to use them easily, as user1:
create synonym customers for user2.customers;

And as user2:
create synonym products for user1.products;

That will allow you to refer to them from the opposite schema without needing to use the full [schema].[table_name] syntax you would need without synonyms.

But, synonyms do not replace the need for grants.  If you have just a synonym in user1's schema for a table in user2's schema, but user2 has not granted select (and/or update and/or delete, depending on what you are trying to do) the synonym will not help you.
Steve WalesSenior Database Administrator

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.