Hello,
I am trying to use VPD in my database (12.1.0.2). I have noticed some issues with the enforcement of policies in queries that join two (or more) tables, each of which have policies defined on multiple columns in the table. To explain in detail, consider the following tables in a schema "my_schema":
T1 (C1, C2, C3, C4)
T2 (B1, B2, B3, B4)
T1 and T2 can be joined on T1.C1 and T2.B1 (this is not a foreign key relation).
I defined policies on T1.C1, T1.C2, T1.C3, and T2.B1, T2.B2, T2.B3.
I issued two queries:
Q1: select T1.C1, T2.B1 from T1 join T2 on (T2.B1 = T1.C1)
Q2: select T1.C1, T2.B1 from T1, T2 where (T2.B1 = T1.C1)
Without VPD, these two queries would return same results. However, with VPD, I notice that the two queries get subject to different sets of policies, and therefore return different results. I tried few different policies and queries, and in all of those cases Q1 returns fewer results than Q2 in presence of VPD.
I used dbms_utility.expand_sql_text to look at the rewritten query generated in the two cases. Below are the traces produced for each query:
Q1: select T1.C1, T2.B1 from T1 join T2 on (T2.B1 = T1.C1)
select A1."C1_0", A1."C1_1" from
(select A3.C1 "C1_0", A2.B1 "C1_1" from
(select A4.* from
(select A8.* from
(select A9.* from
my_schema.T1 A9
where <policy-of-T1.C3>
) A8
where <policy-of-T1.C2>
) A4
where <policy-of-T1.C1>
) A3,
(select A5.* from
(select A6.* from
(select A7.* from
my_schema.T2 A7
where <policy-of-T2.B1>
) A6
where <policy-of-T2.B2>
) A5
where <policy-of-T2.B3>
) A2
where A2.B1=A3.C1
) A1
Q2: select T1.C1, T2.B1 from T1, T2 where (T2.B1 = T1.C1)
select A1.B1, A2.C1 from
(select A3.* from
(select A7.* from
(select A8.* from
my_schema.T1 A8
) A7
) A3
where <policy-of-T1.C1>
) A2,
(select A4.* from
(select A5.* from
(select A6.* from
my_schema.T2 A6
) A5
where <policy-of-T2.B1>
) A4
) A1
where A1.B1=A2.C1
In short, the rewritten join query invokes all the predicates defined on all columns of each table in the query. Whereas, an equivalent query that does not use a join invokes only the policies of the columns accessed in the query.
I have the following questions:
(1) What is the expected enforcement behaviour of VPD policies in a join query? I would expect that only the policies of the columns used in the join query should be enforced, which is the behaviour shown with query of the form Q2, but not Q1.
(2) Is the VPD enforcement intended to be different for a query using join, and an equivalent query not using a join?
Regards