Search code examples
sqloraclerow-level-security

Oracle VPD how to reference the table

I'm struggling with the following problem. I want to restrict access to a table using Oracle VPD. In the where clause that the select policy function returns I need a reference to the table in a subquery as follows:

select * from Table t1
where not exists (select 1 from Table t2 where t2.name=t1.name and t2.date=t1.date)

(The example doesn't make a lot of sense except to illustrate the problem)

I have no control over the select part of the query.

select * from Table t1

The table can be given any alias or none at all. So therefore I have no idea how to reference the table in the where clause. Is there a way to get around this?

(Oracle 10GR2)

Solution

  • I think you will need to use NOT IN:

    (name, datecol) not in (select name, datecol from table2)