Re: Multi-schema database query question

In this situation, it sounds like you might have to create private synonyms in the
user's accounts that need to access these tables and then grant the privileges
to these users through a role.

"Julie Warden" <Julie_Warden.spamfree_at_hotmail.com> wrote in message news:ujseusstiv62k8isfeauqhlm885jfp48os_at_4ax.com...
> I have a question about queries with a multi schema database. The
> database has 2 identical schemas like this:
>
> schema: corp plant
> owner: corp plant
>
> user1 and user2 have been added to the plant schema.
>
> If I connect as plant I can run:
> select wonum, qty from workorder;
>
> user1 and user2 can't run that, but must run:
> select wonum, qty from plant.workorder;
>
> We have a bunch of reports (30+) that need to run against
> either schema. We would prefer to run without qualification.
>
> Is there a way to set up a user so they default only to a certain
> schema?
>
> Notes:
> 1. users do not cross schemas.
> 2. each schema is in its own tablespace, and the users
> have their schema tablespace as their default
>
> Example:
> create user user1 default tablespace plant;
> grant select on workorder to user1.
>
> Any help is greatly appreciated.
>
> Thanks,
>
>
>
Received on Sat Oct 14 2000 - 17:47:58 CDT