Re: tough choices

"Tony" <andrewst_at_onetel.net.uk> wrote in message news:c0e3f26e.0406250739.487a81ce_at_posting.google.com... > "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<40dc009f$0$18196$afc38c87_at_news.optusnet.com.au>...
> > "Serge Rielau" <srielau_at_ca.eye-be-em.com> wrote in message
> > news:cbgou8$dmp$1_at_hanover.torolab.ibm.com...
> >
> >
> > > > * Security Policies (policies attached to tables and views that
> > > > determine what rows can be accessed based on information known about
the
> > > > user)
> > > Can be handled with views. Let the DBMS do what the DBMS does best.
> >
> > Mark can answer for himself, but no this sort of thing can't reasonably
be
> > handled with views. I have a sales table. I want customers to access
it...
> > but they must only see their own rows. If all I've got are views, I've
got
> > to create a different view for each user. And change my application so
it
> > references the right view at the right time as new views are added
because
> > new customers are acquired. Views just won't cut it.

>
> I wouldn't dispute that FGAC is a better way to achieve this, but it
> isn't true that if you used views you would need a view per user.
> Long ago in Oracle 7 I worked on a project that implemented access
> control via views that looked something like:
>
> create view emp_view as
> select * from emp
> where deptno in
> ( select deptno
>   from   users
>   where  username = USER
> );
>
> Only one view was required per table.  Of course, the predicate was
> actually rather more complex than that, but you should get the idea.

Right. Now, what about If the user is this sort of customer, he's allowed to look at those sorts of rows and columns, but if he's this other type of customer, he can see a different selection of rows and columns. And I want to test for his IP address as well as his username. And 12 other conditions as well. And I have other users of the table who aren't customers, but who access the same table, and should have a completely different sort of where clause applied. And I'm not entirely sure what the future may bring, but I suspect there will be even more, distinct, 'consumer groups' for this table in the future....

Do-able in one view? Maybe. I wouldn't like to see the code at the end of it though, and you'd better not get run over by a bus, since you're the only one that really understands it. I certainly think as the complexity increases, the single view ceases to be a viable option.

Then there's the point I missed by I see others have made: If I can hack into your box, I can see all the rows of the table. If you can hack into mine, the FGAC policies still apply, and you can't see any more than you could by using the front-end application.

Regards
HJR Received on Fri Jun 25 2004 - 16:41:20 CDT