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