optimizer_secure_view_merging and plsql_declarations

optimizer_secure_view_merging=true

The Original Problem

A customer is currently upgrading some Oracle databases from 11.2 to 19c. One query was extremely slow on the new test system and my job was to find out why. The root cause was that the database parameter optimizer_secure_view_merging was set to a different value. In 19c true and false in 11.2. This lead to a different and in fact bad execution plan in 19c.

Now the question was, should the customer keep the default value of optimizer_secure_view_merging in 19c and rewrite the slow query or change the parameter to false as in 11.2 to get the good performance without a code change?

What About the opt_param Hint?

Actually, the first thing I tried was the opt_param('optimizer_secure_view_merging','false') hint. Unfortunately, this does not work in 19c. It’s a known bug 28504113. Fixed in 23c. However, I can’t really recommend waiting for 23c, right?

What About the merge view Privilege?

The merge any view privilege is a good option for highly privileged users and roles. But it should not be granted lightly to any ordinary role or user.

The merge view privilege can be granted per view to a user or role. This has a similar scope as a hint in the subquery of a view without having to change the code. In fact, it is an excellent option to override the optimizer_secure_view_merging setting for a view. We could grant merge view on <owner>.<view_name> to public to mimic the scope of a hint in the subquery of a view.

However, the customer uses a metadata driven approach to generate the grants for end user roles as part of the application. And it would require a change of the application to handle this exceptional case. Of course, this grant can easily be hard coded for the view in question, but this is something the customer would like to avoid.

Christian Antognini’s Recommendation

Chris explains optimizer_secure_view_merging on page 289 to 291 in Troubleshooting Oracle Performance, 2nd Edition. On page 291 he writes the following:

If you’re neither using views nor VPD for security purposes, I advise you to set the optimizer_secure_view_merging initialization parameter to FALSE.

In my case, the customer uses views and protects them with Virtual Private Database policies. According to Chris, the customer should keep the default value true for optimizer_secure_view_merging. A sound advice.

What Security Risk Are We Talking About?

Troubleshooting Oracle Performance, 2nd Edition comes with an allfiles.zip file. It contains a script optimizer_secure_view_merging.sql in the folder chapter09. Chris used this script to explain the impact of optimizer_secure_view_merging in his book. I reuse this script here with minor changes.

Let’s connect as user sys and create a database user u1 for the application data and code and a user u2 as connect user (with passwords which work in Autonomous Databases). We also disable optimizer_secure_view_merging.

Now we connect as user u1 and create a table t with 6 rows. and a function f to filter rows in the view v.

Let’s connect as user u2 to query the view.

Only two of five rows are returned due to the where clause in the view. So far so good.

The user u2 has the right to create own functions. And that is a security risk. Why? Because the user can write a spy function like in the next example:

Look at the server output for id 3, 4 and 5. By using the spy function in the where clause the user can get access to all rows in table t. This is only possible because

  • the database parameter optimizer_secure_view_merging is set to false,
  • the optimizer applies the spy function to an intermediate result and
  • the user u2 has the create procedure privilege.

When you call alter system set optimizer_secure_view_merging=true scope=memory; then the result of the previous query looks like this:

The spy function does not reveal protected data anymore. Thanks to optimizer_secure_view_merging=true.

The Next Problem

The customer’s connect users do not have create procedure privileges. After all, It’s a PinkDB application. Hence I could recommend to set optimizer_secure_view_merging=false, because the connect users would not be able to write their own spy functions, right?

Wrong. For two reasons.

Firstly, the user could have access to an existing function that might be misused, e.g. a logger function.

Secondly, we are on 19c. And since 12.1 we have plsql_declarations to write PL/SQL functions and procedures in the with_clause of a select statement. As a result, I can write a spy function without the create procedure privilege. For example like this:

Again, look at the server output for id 3, 4 and 5. Protected data is revealed, even if the user has only the create session privilege and optimizer_secure_view_merging is set to true. IMO this is clearly a security bug.

What Database Versions Are Affected?

I assume that all Oracle Database versions from 12.1 onwards are affected. Including Autonomous Databases. I have explicitly tested the following versions:

  • OCI as of 2022-10-30:
    • Autonomous Database 21c (ATP)
    • Autonomous Database 19c (ADW, AJD)
  • On-Premises
    • Oracle Database XE 21c
    • Oracle Database Enterprise Edition 19c (19.16)

What Can We Do?

I created SR 3-31087264311 for this issue. I expect that either a workaround is provided or a bug is opened and a fix will be available soon. I’ll update this blog post accordingly.

In any case, if you have views or VPD policies for security purposes, set optimizer_secure_view_merging=true and ensure that the connect users do not have the create procedure privilege. Follow the principle of least privileges.

As a workaround you can use Fine-Grained Auditing to identify statements with plsql_declarations. You can either log them or abort the execution as in the following example:

You have to register a policy for each view. This can be easily generated. However, you should consider that such a policy has an impact on performance. Therefore, you should define such a policy only when needed.


Updated on 2022-11-02, documented workaround via DBMS_FGA.

Updated on 2022-11-03, updated workaround regarding comments before plsql_declarations and support for all DML statements.

3 Comments

  1. blank rvo says:

    Hi Philipp,

    Thank you for this eye-opening article!

    The plot would thicken a little should the user U2 have the CREATE VIEW privilege, as that would enable to conveniently hide the inline PL/SQL function into a view, thereby circumventing the above FGA policy.

    (Granting CREATE VIEW to end users in a context where VPD and/or FGA would be used is probably not a good idea in the first place.)

    Regards,

  2. blank IUDITH MENTZEL says:

    Hi Philipp,

    Though I don’t have too much practical experience with DBMS_FGA, your post however reminded me of something that I read many years ago,
    in a post from Lucas Jellema:

    https://technology.amis.nl/it/select-trigger-in-oracle-database-introducing-fine-grained-auditing/

    There is a note in this post, stating the following:

    “Note: while your Handler can slow down the Select operation, it cannot abort it nor can it change the query being executed. Even when your Handler raises an exception, the query will proceed unhamperedly and return its results.”

    So, as I understand from here, raising an exception in an FGA policy handler, as in your proposed workaround, will NOT prevent
    the SELECT from executing.

    I would be grateful if you could confirm or infirm the correctness of the note above

    I just thought about another possible option that seems to solve the problem, namely, using a NO_MERGE hint in the view definition itself.

    For your example, this works for both cases, using a stored function or a WITH clause function:

    create or replace view v2 as
    select /*+ NO_MERGE */ *
    from t
    where f(class) = 1
    /

    View created.

    select id, pad
    from v2
    where id between 1 and 5
    and spy(id, pad) = 1
    /

    ID PAD
    ———————
    1 DrMLTDXxxq
    4 AszBGEUGEL

    2 rows selected.

    id=1 pad=DrMLTDXxxq
    id=4 pad=AszBGEUGEL

    with
    function spy(
    in_id in number,
    in_pad in varchar2
    ) return number as
    begin
    dbms_output.put_line(‘id=’
    || in_id
    || ‘ pad=’
    || in_pad);
    return 1;
    end;
    select id, pad
    from v2
    where id between 1 and 5
    and spy(id, pad) = 1;
    /

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

    id=1 pad=DrMLTDXxxq
    id=4 pad=AszBGEUGEL

    As a general comment:

    Considering the fact that the optimizer might decide in some cases to ignore hints for one reason or another,
    using a hint is not always a 100% solution, so, I think that using MERGE VIEW is probably the way to go.
    If Oracle already decided to introduce a documented feature that specifically targets this scenario, I don’t think that it is good
    to avoid using it because of administrative issues.

    Also, I think that Oracle could have implemented this feature also as part of the view definition itself, using a MERGEABLE/NONMERGEABLE
    clause, aka the equivalent of an “enforced” hint.
    This would make the view not mergeable for ALL users, while the MERGE VIEW privilege may be granted selectively to specific users
    or roles.

    Cheers & Best Regards,
    Iudith Mentzel

    • Hi Iudith,

      There is a note in this post, stating the following:

      “Note: while your Handler can slow down the Select operation, it cannot abort it nor can it change the query being executed. Even when your Handler raises an exception, the query will proceed unhamperedly and return its results.”

      So, as I understand from here, raising an exception in an FGA policy handler, as in your proposed workaround, will NOT prevent
      the SELECT from executing.

      I would be grateful if you could confirm or infirm the correctness of the note above

      Well, I can confirm that this is not correct.

      Firstly, the documentation clearly states the following:

      Function name of the event handler. Include the package name if necessary. This function is invoked only after the first row that matches the audit condition in the query is processed. If the procedure fails with an exception, then the user’s SQL statement fails as well.

      Secondly, and more importantly, I have tested it. I ran the following query as user u2 after creating the policy as described above:

      The statement was not executed and no results were returned. However, the execution was logged in dba_fga_audit_trail.

      I just thought about another possible option that seems to solve the problem, namely, using a NO_MERGE hint in the view definition itself.

      Yes, in this simplistic case you can do that. However, IMO this is still a workaround and not a solution. optimizer_secure_view_merging=true should ensure that such predicates are not applied to an intermediate result no matter where the predicate was declared. In the end we do not want to add no_merge hints in every view.

      Cheers,
      Philipp

Leave a 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.