Re: SqlBase, Informix ==> Oracle

in article 3bc58863$0$21080$ba620e4c_at_news.skynet.be, Franky Thiel at franky.thiel_at_infohos.be wrote on 10/11/01 4:54 AM:

> Now we are working with SqlBase and Informix and have created a 5 databases
> for 1 project. We now want to convert to Oracle but in Oracle you create 1
> database and in that database you create 5 times a schema. The problem is
> that we have tables with the same name in schema 1 and in schema 3. In
> Sqlbase it was in database 1 & 3. If we create public synonyms in Oracle whe
> have a problem because some tables exists in database 1 & 3.
>
> If we want to do a select in Oracle we must do SELECT ..... FROM
> owner.tablename instead of SELECT ... FROM tablename and that's because we
> cannot create the public synonyms.
> Is there another solutions for this because otherwise we had to change all
> our select statements in our applications.
>
>
> Thanks for your help,
>
>
> Franky
>
>

Hmm - instead of 5 different schemas (and what happens if you have more than 5 projects), why don't you create the schema once, with a single owner. Grant public access to the schema so all users can see the same shared set of tables but can't drop etc. Then use VPD (Virtual Private Database) to maintain the data seperation between the projects on the tables that are common - i.e implement security policies based on project id. Much, much simpler design, you can set it up to be transparent to the application, it will scale well from 1 to millions of projects, and the same security model will be applied no matter how you access the data - for instance, you could allow Excel/ODBC access to the tables, and users will still only be able to see their own data.

See http://otn.oracle.com/deploy/security/oracle8i/pdf/vpd_wp6.pdf for a better description of VPD. Received on Sat Oct 13 2001 - 05:38:45 CEST