Re: Limiting Table Access

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 Received on Fri Mar 19 2004 - 05:50:46 CET