optimizer_secure_view_merging and VPD

At page 189 of TOP I wrote the following piece of text:

In summary, with the initialization parameter optimizer_secure_view_merging set to TRUE, the query optimizer checks whether view merging could lead to security issues. If this is the case, no view merging will be performed, and performance could be suboptimal as a result. For this reason, if you are not using views for security purposes, it is better to set this initialization parameter to FALSE.

What I didn’t consider when I wrote it, it is the implication of predicate move-around related to Virtual Private Database (VPD). In fact, as described in the documentation, that parameter controls view merging as well as predicate move-around.

To point out what the impact is, let’s have a look to an example based on the description provided in TOP:

• Say you have a very simple table with one primary key and two more columns.

CREATE TABLE t (
  id NUMBER(10) PRIMARY KEY,
  class NUMBER(10),
  pad VARCHAR2(10)
);

• For security reasons, you define the following policy. Notice the filter that is applied with the function to partially show the content of the table. How this function is implemented and what it does exactly is not important.

CREATE OR REPLACE FUNCTION s (schema IN VARCHAR2, tab IN VARCHAR2) RETURN VARCHAR2 AS
BEGIN
  RETURN 'f(class) = 1';
END;
/

BEGIN
  dbms_rls.add_policy(object_schema   => 'U1',
                      object_name     => 'T',
                      policy_name     => 'T_SEC',
                      function_schema => 'U1',
                      policy_function => 'S');
END;
/

• Now let’s say that a user who has access to the table creates the following PL/SQL function. As you can see, it will just display the value of the input parameters through a call to the package dbms_output.

CREATE OR REPLACE FUNCTION spy (id IN NUMBER, pad IN VARCHAR2) RETURN NUMBER AS
BEGIN
  dbms_output.put_line('id=' || id || ' pad=' || pad);
  RETURN 1;
END;
/

• With the initialization parameter optimizer_secure_view_merging set to FALSE, you can run two test queries. Both return only the values that the user is allowed to see. In the second one, however, you are able to see data that you should not be able to access.

SQL> SELECT id, pad
  2  FROM t
  3  WHERE id BETWEEN 1 AND 5;

        ID PAD
---------- ----------
         1 DrMLTDXxxq
         4 AszBGEUGEL

SQL> SELECT id, pad
  2  FROM t
  3  WHERE id BETWEEN 1 AND 5
  4  AND spy(id, pad) = 1;

        ID PAD
---------- ----------
         1 DrMLTDXxxq
         4 AszBGEUGEL
id=1 pad=DrMLTDXxxq
id=2 pad=XOZnqYRJwI
id=3 pad=nlGfGBTxNk
id=4 pad=AszBGEUGEL
id=5 pad=qTSRnFjRGb

• With the initialization parameter optimizer_secure_view_merging set to TRUE, the second query returns the following output. As you can see, the function and the query display the same data.

SQL> SELECT id, pad
  2  FROM t
  3  WHERE id BETWEEN 1 AND 5
  4  AND spy(id, pad) = 1;

        ID PAD
---------- ----------
         1 DrMLTDXxxq
         4 AszBGEUGEL
id=1 pad=DrMLTDXxxq
id=4 pad=AszBGEUGEL

The execution plans that are used in the two situations are the following. As you can see only the second one guarantee that the policy defined via VPD is applied before the predicate based on the SPY function. Interestingly enough the other predicate based on the ID column is applied before the one of the policy. Hence, the query optimizer can choose an access path that takes advantage of the primary key.

• optimizer_secure_view_merging = FALSE

---------------------------------------------------
| Id  | Operation                   | Name        |
---------------------------------------------------
|   0 | SELECT STATEMENT            |             |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T           |
|*  2 |   INDEX RANGE SCAN          | SYS_C009970 |
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("SPY"("ID","PAD")=1 AND "F"("CLASS")=1))
   2 - access("ID">=1 AND "ID"<=5)

• optimizer_secure_view_merging = TRUE

----------------------------------------------------
| Id  | Operation                    | Name        |
----------------------------------------------------
|   0 | SELECT STATEMENT             |             |
|*  1 |  VIEW                        | T           |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T           |
|*  3 |    INDEX RANGE SCAN          | SYS_C009971 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SPY"("ID","PAD")=1)
   2 - filter("F"("CLASS")=1)
   3 - access("ID">=1 AND "ID"<=5)

Based on these observations, the summary that is provided by TOP at page 189 should be amended as follows:

In summary, with the initialization parameter optimizer_secure_view_merging set to TRUE, the query optimizer checks whether view merging or predicate move-around could lead to security issues. If this is the case, they will not be performed, and performance could be suboptimal as a result. For this reason, if you are not using views or VPD for security purposes, it is better to set this initialization parameter to FALSE.