Re: Limiting Table Access

Wesley <wesleypad_at_hotmail.com> wrote in message news:<5dl6c.852246$X%5.163169_at_pd7tw2no>...
> "Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in
> news:84idndIg4ZLcKsrdRVn-jg_at_comcast.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.
> >|
> >| It seems I need a method to redefine the View Names during a
> >| connection only.
> >|
> >| Any help will be appreciated.
> >
> >
> > what version of oracle?
> > how are you accessing the database? client/server, web? what tool?
> > how many users?
> > how can you differentiate which data belong to which user?
> >
> > ;-{ mcs
> >
>
>
> We are using Oracle 8i, with a server side connection
> The web site is being developed using ASP.NET
> The predicted number of users would be less then a hundred.
> The data is being differentiated through an ID field in the tables.
>
> My current solution is to create Table Views for each user then before
> execute a SQL statment, parse through the statment replacing the main
> table names with the curent users table view name. Is there any major
> drawback to doing this, other then creating a lot of table views?
>
> Thanks

If you are parsing their queries and changing something like this SELECT * from THECOMMONTABLE where x='Y' ;

into this
SELECT * from VIEWID5TABLE where x='Y' ;

Is that easier than changing their query to this SELECT * from THECOMMONTABLE where x='Y' AND ID=5 ; ?

seems like a style issue to me. Performance should not vary either way, at least on the DB side. You still have a lot of parsing back and forth on the queries though.

And the users of this site will be allowed to enter any query they like? yuck That means they could see dictionary tables and figure out hacks to break your "security". Fact is, all they have to be able to do is fake the ID which determines their view.

I don't think I like this at all.

good luck.

   Ed Received on Thu Mar 18 2004 - 23:16:16 CET