Re: Default schema name for users

anuj_singh_at_unibyte-systems.com wrote:

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

Close, but not quite:

The actual syntax for a synonym is

create synonym Table1 for UserB.Table1

Grant (whatever) on Table1 to UserA

The public option means to add the word public in the create synonym statement create public synonym Table1 for UserB.Table1 .....

Grant (whatever) on Table1 to (whomever, any role, etc)

Hth,   Received on Thu Jul 13 2000 - 00:00:00 CDT