Re: Limiting Table Access

jcave_at_ddbcinc.com (Justin Cave) wrote in message news:<c83193c7.0403182050.7bcacc8f_at_posting.google.com>...
> Wesley <wesleypad_at_hotmail.com> wrote in message news:<ZgF5c.67363$Up2.26791_at_pd7tw1no>...
> > Hi, I am hoping someone could point me in the right direction.
> >
> > I am creating a adhoc site which will allow remote users to view and create
> > reports from our database.
> >
> > All the users data is mixed together and is stored in single tables, I have
> > found that I can create a View Table for each User in order to limit them
> > to their data only.
> >
> > My problem is all the SQL statments created for the adhoc site are spefic
> > to a user. Not very portable.
> >
> > I have thought of using the SYNONYMS command but this seem to require each
> > user having there own Login/Password setup to the Oracle database. I am
> > trying to do this all though one Login/Password to the database.
>
> Why the restriction on a single username & password for all the users?
> This strikes me as a perfect use for Oracle row-level security/
> virtual private database technologies. This is easier to implement if
> you have separate logins for each user, but if you have an application
> front-end that you can configure to identify the user by setting some
> context variable, you can make row-level security work. This should
> be a lot easier to maange than a bunch of views and dynamic SQL
> rewriting in your code.
>
> Justin Cave
> Distributed Database Consulting, Inc.
> http://www.ddbcinc.com/askDDBC

Search for FGAC in http://asktom.oracle.com/ , a lot of ideas. I think that this is what you're looking for. Nothing to invent, use the Oracle native features. Received on Tue Mar 30 2004 - 09:54:03 CEST