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,
What you are describing is referred to as a "Virtual Private Database"
or VPD. Search the Oracle documentation on technet.oracle.com and
you'll find lots of information and complete step by step examples
(Chapter 12 Implementing Application Security Policies in the
"Application Developer's Guide"). Basically you'll setup a secure
application context, a policy function and security policies that do
exactly what you want. They allow you to add a where clause to any
query against the specified tables.
--
Richard
Received on Fri Mar 19 2004 - 12:21:00 CST
