Multiple databases in one instance

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