Hello Tom,
I don't understand the purpose of the OPTIMIZER_SECURE_VIEW_MERGING initialization parameter.
Excerpt from
http://download.oracle.com/docs/cd/E14072_01/server.112/e10820/initparams164.htm :
"
OPTIMIZER_SECURE_VIEW_MERGING enables the optimizer to use view merging to improve query performance without performing the checks that would otherwise be performed to ensure that view merging does not violate any security intentions of the view creator.
Values:
* false
Oracle does not perform security checks that may prevent view merging and predicate move-around.
* true
Oracle performs checks to ensure that view merging and predicate move-around do not violate any security intentions of the view creator.
To take advantage of query rewrite for a particular query, you must disable the OPTIMIZER_SECURE_VIEW_MERGING parameter.
"
I encountered performance problems with the default value OPTIMIZER_SECURE_VIEW_MERGING=TRUE. And I can't fathom which security advantages I gain.
Example follows...
Setup:
======
SQL> select * from v$version
2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
SQL> create user usera identified by usera
2 /
Utente creato.
SQL> grant unlimited tablespace to usera
2 /
Concessione riuscita.
SQL> create table usera.t_master (
2 n number,
3 v varchar2(30)
4 )
5 /
Tabella creata.
SQL> insert into usera.t_master
2 (
3 select rownum, rpad('x',30, 'x') from dba_objects s, dba_objects s2
4 where rownum <= 1000000
5 )
6 /
Create 1000000 righe.
SQL> alter table usera.t_master add constraint pk_t_master primary key (n)
2 /
Tabella modificata.
SQL> exec dbms_stats.gather_table_stats(ownname => 'USERA',tabname => 'T_MASTER',cascade => true)
Procedura PL/SQL completata correttamente.
SQL> create view usera.v_master as select * from usera.t_master
2 /
Vista creata.
SQL> create or replace function usera.p_identity(p_num number)
2 return number
3 as
4 begin
5 return p_num;
6 end;
7 /
Funzione creata.
SQL> create user userb identified by userb
2 /
Utente creato.
SQL> grant create session to userb
2 /
Concessione riuscita.
SQL> grant select on usera.t_master to userb
2 /
Concessione riuscita.
SQL> grant select on usera.v_master to userb
2 /
Concessione riuscita.
SQL> grant execute on usera.p_identity to userb
2 /
Concessione riuscita.
TEST 1: OPTIMIZER_SECURE_VIEW_MERGING = TRUE
============================================
SQL> conn userb/userb@mamas4
Connesso.
SQL> explain plan for
2 SELECT
3 AA.*
4 FROM
5 usera.v_master B,
6 (SELECT A.*
7 FROM
8 usera.v_master A
9 WHERE a.N = :n
10 ) AA
11 WHERE
12 B.N = AA.N
13 and usera.p_identity(aa.n) = 1
14 /
Spiegato.
SQL> select * from table(dbms_xplan.display())
2 /
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 725804292
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 578 (2)| 00:00:07 |
|* 1 | HASH JOIN | | 1 | 43 | 578 (2)| 00:00:07 |
|* 2 | VIEW | | 1 | 30 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| T_MASTER | 1 | 36 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_T_MASTER | 1 | | 2 (0)| 00:00:01 |
| 5 | VIEW | V_MASTER | 1000K| 12M| 571 (1)| 00:00:07 |
| 6 | INDEX FAST FULL SCAN | PK_T_MASTER | 1000K| 4882K| 571 (1)| 00:00:07 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."N"="AA"."N")
2 - filter("USERA"."P_IDENTITY"("AA"."N")=1)
4 - access("N"=TO_NUMBER(:N))
Selezionate 20 righe.
SQL>
TEST 2: OPTIMIZER_SECURE_VIEW_MERGING = FALSE
=============================================
SQL> conn system@mamas4
Immettere la password:
Connesso.
SQL> alter system set optimizer_secure_view_merging=false;
Modificato sistema.
SQL>
SQL> conn userb/userb@mamas4
Connesso.
SQL> explain plan for
2 SELECT
3 AA.*
4 FROM
5 usera.v_master B,
6 (SELECT A.*
7 FROM
8 usera.v_master A
9 WHERE a.N = :n
10 ) AA
11 WHERE
12 B.N = AA.N
13 and usera.p_identity(aa.n) = 1
14 /
Spiegato.
SQL> select * from table(dbms_xplan.display())
2 /
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
Plan hash value: 2660555632
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_MASTER | 1 | 36 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_T_MASTER | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N"=TO_NUMBER(:N))
filter("USERA"."P_IDENTITY"("N")=1)
Selezionate 15 righe.
SQL>
USERB selects the same rows in both test1 and test2.
I don't see what enhanced security I obtain by doing the INDEX FAST FULL SCAN in test1?
I think I miss some fundamental reasons, more so given that OPTIMIZER_SECURE_VIEW_MERGING=TRUE is the default.
Thank you in advance for your enduring efforts to help the Oracle community!
Tony
there is a doc bug listed against this, it should be defined as:
In 10g / 11g the parameter can be TRUE or FALSE with the following
behaviour:
FALSE This is similar to behaviour in earlier releases such as 9.2.
Oracle allows views and PLSQL functions in a query to be
merged / moved around during query optimization in order to
try and get the lowest cost execution plan even if the
views / functions have different owners.
TRUE This is the default value.
Oracle does not use view merging or predicate move-around/pushing
in some cases where the query has user-defined functions and
views (regular, inline, unnested,etc) if the views / functions
are owned by someone other than the person running the query.
If OPTIMIZER_SECURE_VIEW_MERGING = FALSE then individual users can be
given permission to merge other users views etc.. by granting them the
MERGE ANY VIEW privilege.
The default value of this parameter is NOT affected by the setting of
OPTIMIZER_FEATURES_ENABLE.
the goal of this parameter is to prevent a function owned by some user B from seeing data of some other user A that is should not see. For example, if you have a view:
connect a/a
/* the entire world can see data for X=1 only!!! */
create view v as select * from t where x = 1;
grant select on v to public;
and then someone queried:
select * from a.v where f(col) = something;
if we interpret that as:
for x in (select * from a.v) -- where x=1 is applied first
loop
if (f(col)=something) then output
end
then f() will only see x=1 values from the table. However, if we merge/push the predicate, we end up with:
select * from t where x=1 and f(col)=something;
Now function F will possibly "see" data for rows 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:
ops$tkyte%ORA11GR2> create user a identified by a quota unlimited on users;
User created.
ops$tkyte%ORA11GR2> create user b identified by b;
User created.
ops$tkyte%ORA11GR2> grant create session, create procedure, create view, create table to a;
Grant succeeded.
ops$tkyte%ORA11GR2> grant create session, create procedure, alter system to b;
Grant succeeded.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> connect a/a
Connected.
a%ORA11GR2>
a%ORA11GR2> create table t ( x int, y varchar2(30) );
Table created.
a%ORA11GR2> insert into t values ( 1, 'ok to see' );
1 row created.
a%ORA11GR2> insert into t values ( 2, 'NOT ok to see' );
1 row created.
a%ORA11GR2> exec dbms_stats.set_table_stats( user, 'T', numrows => 1000000, numblks => 100000 );
PL/SQL procedure successfully completed.
a%ORA11GR2>
a%ORA11GR2> create function y_f( p_x in number ) return number
2 as
3 begin
4 if (p_x) = 1
5 then
6 return 1;
7 else
8 return 0;
9 end if;
10 end;
11 /
Function created.
a%ORA11GR2> create view v as select * from t where y_f(x) = 1;
View created.
<b>Intention: the world can see "where y_f(x) = 1" only. No other data should be visible</b>
a%ORA11GR2>
a%ORA11GR2> grant select on t to b;
Grant succeeded.
a%ORA11GR2> grant select on v to b;
Grant succeeded.
a%ORA11GR2>
a%ORA11GR2> connect b/b
Connected.
b%ORA11GR2> set linesize 10000
b%ORA11GR2>
b%ORA11GR2> create or replace function f ( p_y in varchar2 ) return varchar2
2 as
3 begin
4 dbms_output.put_line( 'I see: ' || p_y );
5 return p_y;
6 end;
7 /
Function created.
<b>that function will print out the data it 'sees'</b>
b%ORA11GR2>
b%ORA11GR2> associate statistics with functions F default cost(0,0,0);
Statistics associated.
<b>make F really cheap, apparently - for the demo, just to get a predicable "I forced the order of the predicate evaluation'</b>
b%ORA11GR2>
b%ORA11GR2> set autotrace on explain
b%ORA11GR2> alter system set optimizer_secure_view_merging = true;
System altered.
b%ORA11GR2> select * from a.v v1 where f(y) = 'ok to see';
X Y
---------- ------------------------------
1 ok to see
I see: ok to see
Execution Plan
----------------------------------------------------------
Plan hash value: 1931062764
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 292K| 27206 (1)| 00:05:27 |
|* 1 | VIEW | V | 10000 | 292K| 27206 (1)| 00:05:27 |
|* 2 | TABLE ACCESS FULL| T | 10000 | 292K| 27206 (1)| 00:05:27 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("F"("Y")='ok to see')
2 - filter("Y_F"("X")=1)
<b>there it is clear that the Y_F(X)=1 is evaluated and then f(y) is</b>
b%ORA11GR2> alter system set optimizer_secure_view_merging = false;
System altered.
b%ORA11GR2> select * from a.v v1 where f(y) = 'ok to see';
X Y
---------- ------------------------------
1 ok to see
I see: ok to see
I see: NOT ok to see
<b>whoops, my function saw data that it should not</b>
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 3000 | 27207 (1)| 00:05:27 |
|* 1 | TABLE ACCESS FULL| T | 100 | 3000 | 27207 (1)| 00:05:27 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("F"("Y")='ok to see' AND "Y_F"("X")=1)
<b>and now we can see the converse is true</b>
b%ORA11GR2> alter system set optimizer_secure_view_merging = true;
System altered.
b%ORA11GR2> set autotrace off
by default, we ensure that this remotely possible issue does not occur.