Re: Design question

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 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)