Design question

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 Received on Fri Mar 19 2004 - 12:07:04 CST