Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

optimizer_secure_view_merging and VPD

11 September 2011 12 Comments Written by Christian Antognini

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.

10gR2, 11gR1, 11gR2, Query Optimizer, TOP, Virtual Private Database
ITL Waits – Changes in Recent Releases (script)
Impact of STATISTICS_LEVEL on Cardinality Feedback and Adaptive Cursor Sharing

12 Comments

2 Pings/Trackbacks

  1. Tim Tim
    12 September 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?

    Reply
    • Christian Antognini Christian Antognini
      12 September 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

      Reply
  2. Tim Tim
    13 September 2011    

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

    Reply
  3. Tim Tim
    13 September 2011    

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

    Reply
    • Christian Antognini Christian Antognini
      13 September 2011    

      Hi Tim

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

      HTH
      Chris

      Reply
  4. Tim Tim
    14 September 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.

    Reply
  5. phoenixbai phoenixbai
    17 September 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

    Reply
  6. phoenixbai phoenixbai
    17 September 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”
    Reply
  7. Christian Antognini Christian Antognini
    19 September 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

    Reply
  8. Bhaskar Bhaskar
    22 December 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

    Reply
  9. Conditional SQL- 6 | Oracle Scratchpad Conditional SQL- 6 | Oracle Scratchpad
    22 June 2016    

    […] to false). For a more detailed explanation and demonstration of the issues, see this item on Christian Antognini’s […]

  10. Exotic SQL: Hints that can change results – svenweller Exotic SQL: Hints that can change results – svenweller
    13 December 2017    

    […] There are also some side effects with VPD. See https://antognini.ch/2011/09/optimizer_secure_view_merging-and-vpd/ […]

  1. Conditional SQL- 6 | Oracle Scratchpad on 22 June 2016 at 13:16
  2. Exotic SQL: Hints that can change results – svenweller on 13 December 2017 at 21:25

Leave a Reply Cancel reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.