RE: DESIGN suggestions needed.

[Jack C. Applewhite]

Title: DESIGN suggestions needed.

Andrey,

 

Perhaps 3 physical DBs are unnecessary.  Have you investigated Oracle8i's "Virtual Private Database" feature - AKA Fine-Grained Access Control or Row Level Security?  It allows you to guarantee that each User has access to only the data to which she has priviledges.  No views or complex application coding is required.

 

I implemented this in a Web-enabled application last year.  It works very well.

 

See "Fine-Grained Access Control" in chapter 27 of "Oracle8i Concepts" for more info.

 

Jack

--------------------------------
Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Andrey Bronfin
Sent: Wednesday, June 27, 2001 5:55 AM
To: Multiple recipients of list ORACLE-L
Subject: DESIGN suggestions needed.

Dear all !

We have a client-server application which runs agains one Oracle DB.
Now , we've got a requiremenet to split our DB into 3 databases  ( one for each country we support - don't ask me why ) .

So , we need to design it the way our existing application will continue working , i.e. the "split" behind the scene will be transparent to the application .

I thought of creating 3 DB links to each one of the databases for each object and then create a view :
create view emp as
select * from emp@DB1
union all
select * from emp@DB2
union all
select * from emp@DB3
;

But now the problem is with updates , inserts and global relations ( i..e an employee in DB1 may belong to a department in DB3) .

did someone gone through a similar stuff ?

Please advice !
thanks a lot in advance !