Re: Multiple databases in one instance

This would certainly work, however what do you do in the case where USER252 needs access to SCHEMA1, SCHEMA22, and SCHEMA14? In your scheme, you could not implement this requirement if USER252 intended to run multiple sessions, each accessing a different schema.

There are still options though. Consider the previously unsupported statement "ALTER SESSION SET CURRENT_SCHEMA". This basically allows you to resolve objects in an alternate schema, so if USER252 connects and issues:

ALTER SESSION SET CURRENT_SCHEMA=SCHEMA14 all unqualified object references will resolve to SCHEMA14, or any other schema specified in the statement. Since this is a session level change, USER252 could have multiple sessions running concurrently against different schemas.

Hth

-Kevin

"Tim Shute" <tshute_at_nisoft.com> wrote in message news:8147635c4574edd1281a61cb934d7043.46509_at_mygate.mailgate.org...
> Hi all
>
> We need to put multiple, separate 'sets' of data into one instance, each
set of
> data owned by a different schema. This is to run on a centralised database
> server on a central server to clients around the globe.
>
> I intend to allow different users access to only one of the sets of data
by
> granting them rights individually (each user works at a different client
site).
>
> For example, SCHEMA1 has a table BANANAS and SCHEMA2 also has a table
BANANAS.
> USER1 should only have access to SCHEMA1's objects and USER2 should only
have
> access to SCHEMA2's objects. So, I created 2 roles ROLE1 and ROLE2 and
granted
> rights to those roles: -
> > CONNECT SCHEMA1/PASSWORD_at_BIGDATABASE
> Connected
> > GRANT ALL ON BANANAS TO ROLE1;
> Grant succeeded
> > GRANT ROLE1 TO USER1;
> Grant succeeded
>
> > CONNECT SCHEMA2/PASSWORD_at_BIGDATABASE
> Connected
> > GRANT ALL ON BANANAS TO ROLE2;
> Grant succeeded
> > GRANT ROLE2 TO USER2;
> Grant succeeded
>
> I then log on as each user and create a private synonym: -
>
> > CONNECT USER1/PASSWORD_at_BIGDATABASE
> Connected
> > CREATE SYNONYM BANANAS FOR SCHEMA1.BANANAS;
> Synonym created
>
> > CONNECT USER2/PASSWORD_at_BIGDATABASE
> Connected
> > CREATE SYNONYM BANANAS FOR SCHEMA2.BANANAS;
> Synonym created
>
> This means that, in our application a user logs on and runs the SQL
statement
> "SELECT * FROM BANANAS" and the data that is returned depends on who the
user
> is.
>
> Is this best practice? Is there a reasonable limit to how many set of data
can
> be handled this way? Is there a better way of doing it?????
>
> Thanks in advance for your answers.
>
> Tim.
> ==
>
>
> --
> Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
Received on Thu Jan 03 2002 - 10:01:44 CST