Accessing Table of other owner

[tpereira]

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

[jovery]

Create a synonym for the table.

[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.

[davey23uk]

there is not other solution, create synonyms

[jovery]

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

[adewri]

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.

[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;

[davey23uk]

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?

[marist89]

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.

[jovery]

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.