Access to Oracle schema objects from another user without using a user prefix

Question

I have a user who has many tables against their account. Lets say UserA. I can do SELECT * FROM TABLE , and everything is fine. If I log in as another UserB user, but make a read-only connection for this user, I cannot access the table, I must use SELECT * FROM UserA.TABLE

Is there a way in Oracle somewhere to allow UserB to access UserA tables without having to prefix the user with the table name?

Answer 1

After logging in as UserB, run the following statement:

 ALTER SESSION SET current_schema = UserA; 

After that, you do not need a prefix for the names of your table.

You can create a login trigger that will do this automatically if you do not want to start it manually.

Answer 2

You can also do this by creating a synonym in the table:

 CREATE SYNONYM TABLE FOR UserA.TABLE;