Re: Design question

From: Ed Prochak <ed.prochak_at_magicinterface.com>
Date: Fri, 19 Mar 2004 23:45:38 -0500
Message-ID: <ACP6c.137$eM.87@fe03.usenetserver.com>

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!

-- 
Ed Prochak
running    http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost

Received on Fri Mar 19 2004 - 22:45:38 CST