Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: tough choices

Re: tough choices

From: Howard J. Rogers <>
Date: Sat, 26 Jun 2004 07:41:20 +1000
Message-ID: <40dc9be3$0$18668$>

"Tony" <> wrote in message > "Howard J. Rogers" <> wrote in message news:<40dc009f$0$18196$>...
> > "Serge Rielau" <> wrote in message
> > news:cbgou8$dmp$
> >
> >
> > > > * Security Policies (policies attached to tables and views that
> > > > determine what rows can be accessed based on information known about
> > > > 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
> > handled with views. I have a sales table. I want customers to access
> > but they must only see their own rows. If all I've got are views, I've
> > to create a different view for each user. And change my application so
> > references the right view at the right time as new views are added
> > 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.

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

Original text of this message