Wrong Results With ANSI Joins and VPD policies / Inline Views (Doc ID 1073138.1)

Last updated on MARCH 21, 2010

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.4 to 11.2.0.1.0 - Release: 10.2 to 11.2
Information in this document applies to any platform.

Symptoms

Wrong results are produced when using an ANSI outer join on a table protected by a RLS policy. The problem can be reproduced also with an inline view that simulates the behaviour of RLS.



conn / as sysdba

drop table scott.CZ_USR_TYP;
drop table scott.AM_TI_UDF;

CREATE table scott.CZ_USR_TYP
( "USRT_TBL_NAM" CHAR(18) NOT NULL ENABLE,
"USRT_FLD_NUM" NUMBER(3,0) NOT NULL ENABLE,
"USRT_FLD_NAM" CHAR(30) NOT NULL ENABLE,
"USRT_FLD_REQRD_S" NUMBER(5,0) NOT NULL ENABLE,
"USRT_FLD_LGTH" NUMBER(3,0),
"USRT_FLD_TYP" CHAR(1) NOT NULL ENABLE,
"USRT_MIN" NUMBER(18,0),
"USRT_MAX" NUMBER(18,0),
"USRT_MASK" CHAR(20),
"USRT_VLD_VALS_S" NUMBER(5,0) NOT NULL ENABLE,
"USRT_UI_SEQ" NUMBER(5,0) NOT NULL ENABLE,
"USRT_UI_RO_S" NUMBER(5,0) NOT NULL ENABLE,
"USRT_LST_UPDT_TS" TIMESTAMP (6) NOT NULL ENABLE,
CONSTRAINT "PK_CZ_USR_TYP" PRIMARY KEY ("USRT_TBL_NAM", "USRT_FLD_NUM") ENABLE );

CREATE TABLE scott.AM_TI_UDF
( "TIUDF_TI_NUM" CHAR(28) NOT NULL ENABLE,
"TIUDF_FLD_NUM" NUMBER(3,0) NOT NULL ENABLE,
"TIUDF_FLD_VAL" CHAR(40) NOT NULL ENABLE,
CONSTRAINT "PK_AM_TI_UDF" PRIMARY KEY ("TIUDF_TI_NUM", "TIUDF_FLD_NUM"));

insert into scott.AM_TI_UDF (TIUDF_TI_NUM, TIUDF_FLD_NUM, TIUDF_FLD_VAL) values ('1234580001852805 ', 1, '0001 ');
insert into scott.AM_TI_UDF (TIUDF_TI_NUM, TIUDF_FLD_NUM, TIUDF_FLD_VAL) values ('1234580001852805 ', 2, '01 ');
insert into scott.AM_TI_UDF (TIUDF_TI_NUM, TIUDF_FLD_NUM, TIUDF_FLD_VAL) values ('1234560007327473 ', 1, '0001 ');
insert into scott.AM_TI_UDF (TIUDF_TI_NUM, TIUDF_FLD_NUM, TIUDF_FLD_VAL) values ('1234560007327473 ', 2, '1 ');
insert into scott.AM_TI_UDF (TIUDF_TI_NUM, TIUDF_FLD_NUM, TIUDF_FLD_VAL) values ('1234560007327473 ', 3, '000012300001935000 ');
insert into scott.AM_TI_UDF (TIUDF_TI_NUM, TIUDF_FLD_NUM, TIUDF_FLD_VAL) values ('1234580002843589 ', 1, '0001 ');
insert into scott.AM_TI_UDF (TIUDF_TI_NUM, TIUDF_FLD_NUM, TIUDF_FLD_VAL) values ('1234580002843589 ', 2, '1 ');
insert into scott.AM_TI_UDF (TIUDF_TI_NUM, TIUDF_FLD_NUM, TIUDF_FLD_VAL) values ('1234580005722962 ', 1, '0001 ');
insert into scott.AM_TI_UDF (TIUDF_TI_NUM, TIUDF_FLD_NUM, TIUDF_FLD_VAL) values ('1234580005722962 ', 2, '1 ');
insert into scott.AM_TI_UDF (TIUDF_TI_NUM, TIUDF_FLD_NUM, TIUDF_FLD_VAL) values ('1234580009292467 ', 1, '0001 ');
insert into scott.AM_TI_UDF (TIUDF_TI_NUM, TIUDF_FLD_NUM, TIUDF_FLD_VAL) values ('1234580009292467 ', 2, '2 ');
insert into scott.AM_TI_UDF (TIUDF_TI_NUM, TIUDF_FLD_NUM, TIUDF_FLD_VAL) values ('1234580003204112 ', 1, '0001 ');
insert into scott.AM_TI_UDF (TIUDF_TI_NUM, TIUDF_FLD_NUM, TIUDF_FLD_VAL) values ('1234580003204112 ', 2, '2 ');
insert into scott.AM_TI_UDF (TIUDF_TI_NUM, TIUDF_FLD_NUM, TIUDF_FLD_VAL) values ('1234580009410093 ', 1, '0001 ');


insert into scott.CZ_USR_TYP (USRT_TBL_NAM, USRT_FLD_NUM, USRT_FLD_NAM, USRT_FLD_REQRD_S, USRT_FLD_LGTH, USRT_FLD_TYP, USRT_MIN, USRT_MAX, USRT_MASK, USRT_VLD_VALS_S, USRT_UI_SEQ, USRT_UI_RO_S, USRT_LST_UPDT_TS)
values ('AM_TI_UDF', 1, 'Branch ID', 1, 4, '1', 0, 9999, null, 1, 0, 0, to_timestamp('11-11-2008 16:05:28.209586', 'dd-mm-yyyy hh24:mi:ss.ff'));
insert into scott.CZ_USR_TYP (USRT_TBL_NAM, USRT_FLD_NUM, USRT_FLD_NAM, USRT_FLD_REQRD_S, USRT_FLD_LGTH, USRT_FLD_TYP, USRT_MIN, USRT_MAX, USRT_MASK, USRT_VLD_VALS_S, USRT_UI_SEQ, USRT_UI_RO_S, USRT_LST_UPDT_TS)
values ('AM_TI_UDF', 2, 'Printer ID', 1, 2, '1', 0, 99, null, 1, 0, 0, to_timestamp('02-02-2009 12:55:27.651179', 'dd-mm-yyyy hh24:mi:ss.ff'));
insert into scott.CZ_USR_TYP (USRT_TBL_NAM, USRT_FLD_NUM, USRT_FLD_NAM, USRT_FLD_REQRD_S, USRT_FLD_LGTH, USRT_FLD_TYP, USRT_MIN, USRT_MAX, USRT_MASK, USRT_VLD_VALS_S, USRT_UI_SEQ, USRT_UI_RO_S, USRT_LST_UPDT_TS)
values ('AM_TI_UDF', 3, 'Affiliated Account ID', 0, 18, '1', null, 999999999999999999, null, 0, 0, 0, to_timestamp('05-01-2009 14:22:13.330987', 'dd-mm-yyyy hh24:mi:ss.ff'));
insert into scott.CZ_USR_TYP (USRT_TBL_NAM, USRT_FLD_NUM, USRT_FLD_NAM, USRT_FLD_REQRD_S, USRT_FLD_LGTH, USRT_FLD_TYP, USRT_MIN, USRT_MAX, USRT_MASK, USRT_VLD_VALS_S, USRT_UI_SEQ, USRT_UI_RO_S, USRT_LST_UPDT_TS)
values ('AM_TI_UDF', 4, 'Charge Card Billing Date', 0, 2, '1', 1, 31, '2,0 ', 1, 4, 0, to_timestamp('13-04-2009 15:04:01.184780', 'dd-mm-yyyy hh24:mi:ss.ff'));
insert into scott.CZ_USR_TYP (USRT_TBL_NAM, USRT_FLD_NUM, USRT_FLD_NAM, USRT_FLD_REQRD_S, USRT_FLD_LGTH, USRT_FLD_TYP, USRT_MIN, USRT_MAX, USRT_MASK, USRT_VLD_VALS_S, USRT_UI_SEQ, USRT_UI_RO_S, USRT_LST_UPDT_TS)
values ('AM_TI_UDF', 5, 'Charge Card Payment Due Date', 0, 2, '1', 1, 31, '2,0 ', 1, 5, 0, to_timestamp('13-04-2009 16:20:42.747975', 'dd-mm-yyyy hh24:mi:ss.ff'));
insert into scott.CZ_USR_TYP (USRT_TBL_NAM, USRT_FLD_NUM, USRT_FLD_NAM, USRT_FLD_REQRD_S, USRT_FLD_LGTH, USRT_FLD_TYP, USRT_MIN, USRT_MAX, USRT_MASK, USRT_VLD_VALS_S, USRT_UI_SEQ, USRT_UI_RO_S, USRT_LST_UPDT_TS)
values ('AM_TI_UDF', 6, 'Charge Card Credit Limit', 0, 18, '1', 0, 999999999999999999, '18,2 ', 0, 6, 0, to_timestamp('13-04-2009 16:20:26.216724', 'dd-mm-yyyy hh24:mi:ss.ff'));
commit;


CREATE OR REPLACE FUNCTION "SCOTT"."F_CONF_PM" (object_schema IN VARCHAR2, object_name VARCHAR2) return VARCHAR2 as con varchar2(20);
BEGIN
RETURN ('1=1');
END F_CONF_PM;
/



exec dbms_rls.drop_policy (object_schema => 'SCOTT',-
                                           object_name => 'AM_TI_UDF',-
                                           policy_name => 'CONF_AM_TI_UDF');


begin
    dbms_rls.add_policy(object_schema => 'SCOTT',-
                                     object_name => 'AM_TI_UDF',-
                                     policy_name => 'CONF_AM_TI_UDF',-
                                     function_schema => 'SCOTT',-
                                     policy_function => 'F_CONF_PM',-
                                     sec_relevant_cols => 'TIUDF_TI_NUM',-
                                     sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);
end;
/



When using an ANSI outer join the results are wrong:


SQL> conn scott/tiger
Connected.

SQL> select tiudf_fld_num, tiudf_fld_val
     from SCOTT.cz_usr_typ
     left join SCOTT.am_ti_udf on usrt_fld_num = tiudf_fld_num and tiudf_ti_num = '1234580005722962'
     where usrt_tbl_nam = 'AM_TI_UDF'
     order by usrt_tbl_nam, usrt_ui_seq, usrt_fld_num;

TIUDF_FLD_NUM TIUDF_FLD_VAL
------------- ----------------------------------------




6 rows selected.


When using an Oracle type outer join the results are correct:

SQL> select tiudf_fld_num, tiudf_fld_val
           from SCOTT.cz_usr_typ , SCOTT.am_ti_udf
           where usrt_fld_num = tiudf_fld_num(+) and tiudf_ti_num = '1234580005722962'
           and usrt_tbl_nam = 'AM_TI_UDF'
           order by usrt_tbl_nam, usrt_ui_seq, usrt_fld_num;

TIUDF_FLD_NUM             TIUDF_FLD_VAL
---------------------     ----------------------------------------
1                         0001
2                            1



If we simulate RLS by adding an inline view and if we continue to use the ANSI join, we get incorrect results as well:

conn scott/tiger

select tiudf_fld_num, tiudf_fld_val
from SCOTT.cz_usr_typ left join
(SELECT CASE WHEN (1=1) THEN "TIUDF_TI_NUM" ELSE NULL END "TIUDF_TI_NUM","TIUDF_FLD_NUM","TIUDF_FLD_VAL" FROM "SCOTT"."AM_TI_UDF") AM_TI_UDF on usrt_fld_num = tiudf_fld_num and tiudf_ti_num = '1234580005722962'
where usrt_tbl_nam = 'AM_TI_UDF' order by usrt_tbl_nam, usrt_ui_seq, usrt_fld_num;

TIUDF_FLD_NUM TIUDF_FLD_VAL
------------- ----------------------------------------




6 rows selected.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms