Re: Roles and Private Synonyms

In article <33C2ED4F.6B1D_at_helix.com.au>, Stephen Walsh <stevew_at_helix.com.au> writes
>I'm having a little difficulty finding information regarding how Oracle
>manages roles and private synonyms. My problem is as follows:
>
>I do not have any public synonyms defined.
>
>In a single Oracle Instance I have 2 schemas of the same database for
>separate use of a single application, eg. once machine maintenance and
>ones property maintenance:
> machine
> property
>
>You create roles as follows, for example:
>
> create role machine_general;
> grant select,update,delete,insert on machine.table1 to
>machine_general;
> grant select,update,delete,insert on machine.table2 to
>machine_general;
>
> create role property_general;
> grant select,update,delete,insert on property.table1 to
>property_general;
> grant select,update,delete,insert on property.table2 to
>property_general;
>
> And then you create private synonyms for your application access as
>follows:
>
> create synonym table1 for machine.table1;
> create synonym table2 for machine.table2;
>
> create synonym table1 for property.table1;
> create synonym table2 for property.table2;
>
>Questions:
>
>1. If you grant the privilege to the base object in a role, eg.
>machine.table1, does the synonym inheirit the same privileges??
Synonyms are just aliases that make it simpler to refer to database objects. Grants are not associated with synonyms but to theobjects. If a synonym is available to a user & the user accesses an object using the synonym, whether or not they are allowed to access the object depends on if they've been granted access to the base object.
>
>2. If a user "user1" has been granted the following roles:
> grant machine_general to user1;
> grant property_general to user1;
>
> I assume that, if the user starts the application against the machine
>schema,

How? if the user logs on as "USER1", then no association has been made with neither machine nor property yet. No synonyms to any of the machine & property objects are available to USER1.

If the application at some stage does a connect using the "MACHINE" username then Yes,
> then the synonyms defined for the machine schema are used because
>they are
> private to the machine schema eventhough the synonym names are the
>same in each
> schema. Is this correct?? My assumption is based on Oracle trying to
>resolve access at the schema level first, then moving up the chain.
>
>3. In addition to question 2, if the public synonym has not been defined
>then an access violation should be generated. Is this correct??
Yes. Trying to access another user's objects via a non-public, privately (not owned by the accessing user, USER1) owned synonym isn't going to succeed. You'll get something like "Object or View does not exist" error
>
>TIA
>regards
>Stephen
 

-- 
Bola Ogunlana