Re: comparing view based security/ vpd based security

Comments in-line.

chopras_at_gmail.com (Sandeep Chopra) wrote in message news:<58b9cdcf.0409221304.43b606e0_at_posting.google.com>...
> Couple quick questions:
>
> 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.

This would be easy to test but yes, VPD CAN. VPD basically rewrites your query:

select from emp;

into

select * from (select * from emp where salary > 50000);

This is done whether you select a single table or join it with something else. It's always there.

> 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.

Don't exactly understand the question.

> 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)
>

As of 9i, nope, you'll have to use a view. I can't be sure if 10g will meet your needs but I do know that VPD there has such a thing called "column masking". Can't test it since I don't have a 10g instance handy.

For a comprehensive discussion of this topic, see AskTom.

http://asktom.oracle.com/pls/ask/f?p=4950:8:11175065604496442671::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1969395750585,

HTH. Received on Thu Sep 23 2004 - 01:44:32 CDT