Optimizer_Secure_View_Merging

This parameter can be either True or False. The default value is True.

False – This is similar to the behavior in earlier release such as 9.2. Oracle allows view/plsql function in queries to be merged / moved around during query optimization in order to try and get the lowest cost execution plan even if view/function have different owners.

True – Oracle does not use view merging or predicate move around /pushing in some cases where the query has user defined function and views ( regular, in line, un-nested etc) if the views/functions are owned by someone other than the person running the query.

The goal of this parameter is to prevent functions owned by some user B from seeing data of some other user A that is should not see

connect a/a

/* The entire would can see the data for x =1 only!!! */

create view v as select * from t where x =1;

grant select on v to public;

And then if someone queried:

select * from a.v where f(col) = something;

if we interpret that as

for x in (select * from a.v )

loop

       if ( f(col) = something) then output    

end loop;

Then f(col) would see only x =1 values from the table. However if we merge/push the predicates then we end up with:

select * from t where x =1 and f(col) = something;

Now the function F will possibly “see” data for rows that it should not be able to see, because you don’t know if F(col) will be evaluated before or after x=1 is,

For example

rajesh@ORA12C> create user a identified by a

  2             quota unlimited on users;

User created.

rajesh@ORA12C> create user b identified by b

  2             quota unlimited on users;

User created.

rajesh@ORA12C> grant create session,create table,

  2             create view,create procedure

  3             to a;

Grant succeeded.

rajesh@ORA12C> grant create session,create procedure,

  2             alter system to b;

Grant succeeded.

rajesh@ORA12C> conn a/a@ora12c

Connected.

a@ORA12C> create table t(x int, y varchar2(15));

Table created.

a@ORA12C> insert into t values(1,'I am ok');

1 row created.

a@ORA12C> insert into t values(2,'I am not OK');

1 row created.

a@ORA12C> commit;

Commit complete.

a@ORA12C> exec dbms_stats.set_table_stats(user,'T',numblks=>10000,numrows=>1000000);

PL/SQL procedure successfully completed.

a@ORA12C> create or replace function foo(x int)

2  returnint as

3  begin

  4     if x = 1 then return 1 ;

  5     else return 0 ;

  6     end if;

7  end;

8  /

Function created.

a@ORA12C> create or replace view v as select * from t where foo(x) = 1;

View created.

Intention: the world can see “where foo(x)=1”, no other data should be visible.

a@ORA12C> grant select on t to b;

Grant succeeded.

a@ORA12C> grant select on v to b;

Grant succeeded.

a@ORA12C> conn b/b@ora12c

Connected.

b@ORA12C>

b@ORA12C> create or replace function F(x varchar2)

2  return varchar2 as

3  begin

  4     dbms_output.put_line (' I see ='||x);

  5     return x;

6  end;

7  /

Function created.

That function will print out the data it ‘sees’

b@ORA12C> set autotrace on explain

b@ORA12C> select * from a.v where F(y) = 'I am ok';

         X Y

---------- ---------------

         1 I am ok

1 row selected.

I see =I am ok

Execution Plan

----------------------------------------------------------

Plan hash value: 1931062764

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      | 10000 |   214K|  2703   (4)| 00:00:01 |

|*  1 |  VIEW              | V    | 10000 |   214K|  2703   (4)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL| T    | 10000 |   214K|  2703   (4)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("F"("Y")='I am ok')

   2 - filter("FOO"("X")=1)

That is clear, FOO(x) is evaluated, then F(Y) is

b@ORA12C> alter system set OPTIMIZER_SECURE_VIEW_MERGING = false;

System altered.

b@ORA12C>

b@ORA12C> select * from a.v where F(y) = 'I am ok';

         X Y

---------- ---------------

         1 I am ok

1 row selected.

I see =I am ok

 I see =I am not OK

Oops, my function saw the data that it should not

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |   100 |  2200 |  2705   (4)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T    |   100 |  2200 |  2705   (4)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("F"("Y")='I am ok' AND "FOO"("X")=1)

b@ORA12C> set autotrace off

b@ORA12C>

b@ORA12C> alter system set OPTIMIZER_SECURE_VIEW_MERGING = true;

System altered.

b@ORA12C>

By default, we ensure that this remotely possible issue don’t even occur.