Re: comparing view based security/ vpd based security

On 22 Sep 2004, chopras_at_gmail.com wrote:
> Couple quick questions:
>
> consider the following two tables
>
> emp (empid, ename, dno, salary) empif = pk, dno = fk
> dept (dno, dname, dlocation, numPeople) dno = pk
>
> now consider the following view
>
> create view empDept as
> select e.empid, e.ename, e.salary, d.dname
> from e.emp, d.dept
> where e.dno = d.dno and e.salary > 50000;
>
> If one were to emulate this query by using VPD, it would be
> easy to insert the e.salary > 50000 restriction. However,
> since VPD policies attach themselves to a single table,view, or
> synonym, it does not seem possible that joins on two tables or
> more can be emulated in VPD. Moreover, in general is it
> possible to ever include data from two different tables (in
> this instance from both emp and dept) in the outer select
> clause using VPD.

You can attach VPD to views, if this helps.

> In addition, views can be used for column removal. A simple
> example on the emp table would be:
>
> create view empMinusId as
> select ename, dno, salary
> from emp; (removing eid)
>
> Is it possible to do such column removal using VPD. It doen't
> seem possible that this is possible since VPD can only add
> predicates to the WHERE clause and cannot influence the outer
> projection (select clause)

VPD does also support bringing back "NULL" for a particular column, but it doesn't support removing it entirely from the result.

> Any comments on these topics would be much appreciated. ??
>
> Thanks a bunch...

-- 
Galen Boyer