Re: Default schema name for users

In article <396d4da5$0$778_at_proctor>,
  "Jaco Grobler" <jacog_at_pillar.co.za> wrote:
> Hi All
>
> I would like to know if there are a way to define a default schema
 name
> other than the users own schema for a userid. I come from a Sybase
> background where you can create a object in the dbo. schema and if no
 schema
> name are supplied when trying to access the object and the object does
 not
> exist in the users schema it will go and look for the object in the
 dbo
> schema
>
> We are busy converting to Oracle but are experiencing problems with
 trying
> to access objects in a certain schema without specifying the schema
 name.
> The existing stored procs etc. do not use the schema name and it will
 be
> quit a lot of work having to place the schema name in front of every
 object.
>
> TIA
> Jaco Grobler
>
>

Hi,

If I understand your question correctly then you are trying to access objects (Tables, views, Procedures etc.) from various users' schema.

I hope the following example helps.

Assume 2 users UserA and UserB.
UserB wants to access two tables from UserA's Schema - Table1 and Table2.

UserA will have to grant appropriate privileges to access these tables to UserB.

UserA will create two synonyms for the tables. The syntax will be as following:

create synonym UserB.Table1 for Table1;
create synonym UserB.Table2 for Table2;

Once created UserB will be able to use the tables without using schema name as Oracle will be able to
look up the table using the synonym.e.g.

Previous statement:
Select * from UserA.Table1;
New Statement
Select * from Table1;

Optionally if these Tables are required by other users then a public synonym may be created (You will
still have to grant appropriate privileges to the users). The syntax changes slightly,

create public synonym Table1 for Table1;

Anuj..

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jul 13 2000 - 00:00:00 CDT