in Oracle RLS, when using a predicate to filter a table, then in a second predicate on another table, if i reference the first table, is the first predicate in effect or not?

Question

I use the Oracle VPD facility(aka RLS, aka FGAC). Suppose that i use the predicate
'(WHERE) id = 'XXXX''
on TableA and then i use the predicate
'(WHERE) col in (SELECT col From TableA)'
on TableB.

Will TableA be filtered or not? Filtering is done only outside of predicate definitions, when you query the tables?. Will i need to make predicate on TableB like this:

'(WHERE) col in (SELECT col From TableA where id ='XXXX')'?

Thanks

Answer 1

Yes, TableA will be automatically filtered using the predicate you specified(id = 'XXXX'). Oracle automatically applies the filters wherever you use(select from, in this case) the table, even in subqueries, if the filter criteria is met(see sec_relevant_cols).