Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Design question

Re: Design question

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Fri, 19 Mar 2004 21:09:50 -0800
Message-ID: <1079759366.373666@yasure>


Ed Prochak wrote:

> Daniel Morgan wrote:
> 

>> seanm555_at_msn.com wrote:
>>
>>> Hello all,
>>> I have an oracle database that serves a single location. I want to
>>> be able
>>> to serve multiple locations, so I want to choose the best structure to
>>> implement this.
>>>
>>> Should I create multiple databases with the same schema and manage
>>> users and
>>> their grants separately, or can I create a single database that users
>>> can
>>> only see data that corresponds to their location? If it's possible to
>>> create a single database that allows users only to view data
>>> according to a
>>> condition (i.e. select * from ATABLE where LOCATION = 'ABC'), then I
>>> would
>>> like to know how to set that up. I only know how to control that in
>>> code on
>>> the application side, which is not secure since anybody could go in
>>> with an
>>> ODBC connection and have access to all the data in the tables instead of
>>> only the data for their location.
>>>
>>> Anyway, you can see that creating multiple databases and managing the
>>> users
>>> for this is more complicated than having a single database for all,
>>> but I
>>> would need a way to control what records a user has access to within the
>>> table. I'm using Oracle 9i, but would like to be compatible with
>>> anything
>>> from Oracle 8i+. Thanks,
>>>
>>> Sean McElroy
>>
>>
>>
>> Under no condition should you create a second database. Use a single
>> database and add a column with a location identifier. Then you can use
>> any number of techniques to keep the users and the data logically
>> segregated. Check these links out for ideas:
>>
>> http://www.psoug.org/reference/dbms_applic_info.html
>> http://www.psoug.org/reference/dbms_rls.html
>> http://www.psoug.org/reference/sys_context.html
>> http://www.psoug.org/reference/system_trigger.html
>> http://www.psoug.org/reference/views.html
>>
> 
> Daniel,
> I'm surprised that you didn't mention that sean's use of "multiple 
> databases" could really mean multiple schema/users. Of course dividing 
> locations as different users makes having different accounts for the 
> same location tricky!
> 
> Lots of good reference material though. Thanks!

Even if it did, and it could have, I intentionally didn't go there because the best case scenario ... he meant separate schemas ... would still involve duplicating tables, indexes, constraints, etc. It would still be a maintenance nightmare.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Fri Mar 19 2004 - 23:09:50 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US