Accessing Table of other owner

Hi,
I using Oracle 8.1.7 and have the following problem:
I Have two users, User1 and User2, and a have one tablespace TS_DATA.
User1 have full access on TS_DATA (Create Tables, Drop Tables and others). User1 have created the table below:

CREATE TABLE USER1.TB_TEST1
( CODE NUMBER NOT NULL PRIMARY KEY,
DESC VARCHAR2(50) NOT NULL);

How can I grant permission to USER2 to do SELECT on TB_TEST1 without put the owner before the table name?
For example:
SELECT * FROM TB_TEST1

Create a synonym for the table.

I have 10 user and 250 tables. I have to create 2500 synonym. This solution don’t solve my problem.
I need other solution that don’t use synonym.

there is not other solution, create synonyms

Create public synonyms, that way you only need to create 250 and why does that not solve the problem?

Quote:

---

Originally posted by tpereira
I have 10 user and 250 tables. I have to create 2500 synonym. This solution don’t solve my problem.
I need other solution that don’t use synonym.

---

What Jim meant was create public synonyms which is accessible to all. Additionally create a role, grant select on user1's tables to that role and grant that role to the other 10 users.

You have two options (apart the one mentioned by jovery):

- create PUBLIC synonyms for that tables (so you'll have only 250 synonyms, not 2500)
- allow your users to perform the following : ALTER SESSION SET CURRENT_SCHEMA = USER1;

Quote:

---

Originally posted by jmodic
You have two options (apart the one mentioned by jovery):

- create PUBLIC synonyms for that tables (so you'll have only 250 synonyms, not 2500)
- allow your users to perform the following : ALTER SESSION SET CURRENT_SCHEMA = USER1;

---

does that then allow users to change data in tables in that schema?

Quote:

---

Originally posted by davey23uk
does that then allow users to change data in tables in that schema?

---

If they have permission to, yes.

As for public synonyms, I would really stay away from them unless you know this is the ONLY application that will ever be on that database.

Quote:

---

Originally posted by marist89
If they have permission to, yes.

As for public synonyms, I would really stay away from them unless you know this is the ONLY application that will ever be on that database.

---

Totally agree Jeff but as the original poster did not say whether the user would/would not have objects of there own I offered the synonym route.

Thanks,
the solution post by davey23uk (Alter Session command) resolve my problem.
With this solution, the USER2 can do only SELECT on TB_TEST1 without the owner.

Very Good solution.
Congratulation.

wasnt my solution, but glad you got it working

I hope this should help.

1) Create a role and grant select on table to role.
2) Grant role to each user.
3) Create public synonyms.

This way from next time when you create tables there will be less effort to give access on tables to users.

You dont need to give 100 commands for 100 users. use vi editor and make the changes and run the script in database.
it just takes 5-10 min to do this.

Thanks