Patrix78
asked on
How access to another table in other user?
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>Just to mention, there is another way ;-)
Still need the grants...
Still need the grants...
![Avatar of Alex [***Alex140181***]](https://filedb.experts-exchange.com/files/public/2022/11/13/8257e912-ee3c-4a93-9df0-fe9a8a1515cf.jpeg){kind=small}
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 :-))
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 :-))
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.
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.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
As user1
Open in new window
As user2
Open in new window