Thursday, February 18, 2010

VPD + bad ANYDATA practices can really bite

After several days of intense testing, 4 SRs with Oracle Support (and another with the ERP vendor), and the very helpful information from Maxim Demenko about "out-of-range" date values, I have developed a testcase that demonstrates how using bad ANYDATA practices in the context of VPD can really mess you up.

Some background:
We have an application that recently started to utilize ANYDATA. Unfortunately, the application did not implement validation checks, and the nature of ANYDATA makes table check constraints a near impossibility (I have not found any good ways to go about it). So we (not I, but colleagues) developed VPD rules to validate data. After a month of testing, a tester noticed that we had some really funny dates, ranging from 4290 BC to 5090 BC.

We tried tracing (10046, 10053, 10730), but nothing jumped out at us; except we may have uncovered a new bug, but more on that in a second. We tried using LogMiner, but Oracle Support finally convinced us that LogMiner does not support ANYDATA. :-( Finally we just started shooting in the dark, testing different combinations of rules and data inputs.

We stumbled upon the fact that using CAST to convert ANYDATA into a datatype has bad consequences. In particular, if you try something like cast(some_anydata_column as varchar2(1)) and the column is a DATE, for example, you get a ora-3113/ora-7445 (under 10.2.0.4 + JanPSU2010). The fine folks who had written our RLS policies had used CAST extensively, and the ironic part is that no errors were being generated on the application side. Instead, bad dates were sneaking into the dataset.

After reading the documentation a bit more, I discovered that ANYDATA is an object-oriented object (much to my surprise), and it has member functions. We had a hard time trying to figure out exactly how to use the member functions since one needs to instantiate a member first, and the documentation does not give any examples, let alone explain the usage of "object-oriented" in a relationship database. Finally I stumbled upon using sys.anydata as an instantiation, which seemed to work well for us.

Why did Oracle develope ANYDATA?!? It seems anti-RDBMS. And it makes things messy for us DBA types. As I explained to my colleagues, object-oriented data buckets are great for developers, up until they break. Then they are a pain to figure out.

I still have an outstanding question of exactly how the ANYDATA column overflows into the DATE field and gives us whacked out dates. If any Oracle gurus out there want to chime in, please do so.

Here is the code I used to replicate our issue:

drop user test cascade;
drop user test_no_vpd cascade;

create user test_no_vpd identified by test4#;
grant create session, EXEMPT ACCESS POLICY to test_no_vpd;

create user test identified by test3#;
grant create session, alter session, resource, create any context to test;
grant execute on dbms_rls to test;
connect test/test3#;

CREATE TABLE GORSDAV (
GORSDAV_TABLE_NAME VARCHAR2(30 CHAR) NOT NULL,
GORSDAV_VALUE SYS.ANYDATA NOT NULL,
GORSDAV_ACTIVITY_DATE DATE NOT NULL,
pill_1 number default 1,
pill_2 number default 2,
pill_3 number default 3)
;

insert into gorsdav values ('some_table_1',sys.anydata.convertnumber(1),sysdate,0,0,0);
insert into gorsdav values ('some_table_1',sys.anydata.convertdate(sysdate),sysdate,0,0,0);
insert into gorsdav values ('some_table_1',sys.anydata.convertvarchar2('Y'),sysdate,1,0,0);
insert into gorsdav values ('some_table_2',sys.anydata.convertvarchar2('Yes'),sysdate,0,0,0);
insert into gorsdav values ('some_table_2',sys.anydata.convertvarchar2('Y'),sysdate,0,0,3);
insert into gorsdav values ('some_table_2',sys.anydata.convertvarchar2('No'),sysdate,0,0,0);
insert into gorsdav values ('some_table_3',sys.anydata.convertvarchar2('MaybeSo'),sysdate,0,0,0);

commit;

-- Using FGAC example from http://www.orafusion.com/art_fgac.htm

-- A dummy procedure to satisfy the CREATE CONTEXT command; does not actually do anything

PROMPT Create Application Role Procedure
create or replace procedure
set_testapp_role(p_user varchar2 default sys_context('userenv', 'session_user')) is
v_ctx varchar2(16) := 'testapp_ctx';
begin
dbms_session.set_context(v_ctx,'rolename','APP_OWNER');
end;
/


PROMPT Create context
create or replace context testapp_ctx using set_testapp_role;


-- This is just a mock up test; I am not concerned about real-life roles or security,
-- thus I am returning the same predicate no matter who the user is

PROMPT Create security function
create or replace function testapp_security_function (p_schema varchar2, p_object varchar2)
return varchar2 is
begin
return '(sys.anydata.accessvarchar2(gorsdav_value) = ''Y'' and pill_1 = 1) or pill_1 <> 1';
end;
/


PROMPT Create RLS Table Policy
declare
begin
DBMS_RLS.ADD_POLICY (
object_schema => 'TEST',
object_name => 'GORSDAV',
policy_name => 'TESTAPP_POLICY',
function_schema => 'TEST',
policy_function => 'TESTAPP_SECURITY_FUNCTION',
statement_types => 'SELECT,UPDATE,INSERT,DELETE',
update_check => TRUE,
enable => TRUE,
static_policy => FALSE);
end;
/

PROMPT Inserting a control row into the table to show the date and insert are fine
insert into gorsdav values ('some_table_4',sys.anydata.convertvarchar2('123456789'),sysdate,0,0,0);
commit;

PROMPT Selecting data from table - should return eight rows with no errors
select * from gorsdav;

-- The following function uses CAST to get the varchar2 data; however, a majority of the
-- data is larger than the CAST target, thus we get an error. Even if we use varchar2(200),
-- some datatypes are DATE and NUMBER.

PROMPT Create "bad" security function
create or replace function testapp_security_function (p_schema varchar2, p_object varchar2)
return varchar2 is
begin
return '((cast(gorsdav_value as varchar2(1)) = ''Y'' and pill_1 = 1) or pill_1 <> 1)';
end;
/

PROMPT Inserting into table - this will work with no problems.
insert into gorsdav values ('some_table_4',sys.anydata.convertvarchar2('Y'),sysdate,0,2,0);

commit;


PROMPT Inserting into table - this will complete successfully, but will insert a "bad" date
insert into gorsdav values ('some_table_4',sys.anydata.convertvarchar2('123456789'),sysdate,0,0,0);

commit;

-- PROMPT Selecting data from table - should hang for about 10 seconds and kick you out with
-- PROMPT ORA-3113 and ORA-7445 in the alert.log
-- select * from gorsdav;

grant select on test.gorsdav to test_no_vpd;

PROMPT Connecting as a non-VPD user (exempt access policy)
connect test_no_vpd/test4#
select * from test.gorsdav;



10 comments:

Anonymous said...

Just one comment.

http://www.orafaq.com/node/1853 is a very good introduction to ANYDATA. The author Kevin Meade is very careful to test the anydata type. such as:
when sys.anydata.gettypename(temp1.b) = 'SYS.DATE' then
to_char(pkg_temp1.getdate(temp1.b),'dd-mon-rrrr hh24:mi:ss')
when sys.anydata.gettypename(temp1.b) = 'SYS.VARCHAR2' then
pkg_temp1.getvarchar2(temp1.b)

It might help you to get further in your investigation to see if types are changing mysteriously.

Charles Schultz said...

Actually, I did reference his material as I was wandering through Google hits, and it is good stuff. But it did not answer my questions. Do you see any particular points that might shed light on my curiosity?

Charles Schultz said...

Tested in 10.2.0.4.2, 10.2.0.2 and 11.2.0.1.

seanm95 said...

It might help to verify the type before doing the cast.

Also the article did not use cast.

Instead he uses these functions to return the data.

Maybe you could replace your:
cast(gorsdav_value as varchar2(1))
with:
my_pkg.getvarchar2(gorsdav_value)
to see if the my_anydata.getvarchar2 behaves any better than cast.


function getvarchar2 (anydata_p in sys.anydata) return varchar2 is
x number;
thevarchar2_v varchar2(4000);
begin
x := anydata_p.getvarchar2(thevarchar2_v);
return (thevarchar2_v);
end;

Charles Schultz said...

Right, we already took care of that. It took me a while to realize you had to use sys.anydata as the instantiator, but we got it.

The point of my blog was that using CAST can really screw up your data. Oracle Support is filing a bug on this behavior, as it looks like an overflow problem.

Log Buffer said...

"[...]Charles Schultz demonstrates how VPD + bad ANYDATA practices can really bite: [...]"

Log Buffer #180

Charles Schultz said...

Tom Kyte showed me a condensed version more suitable to test cases.


connect / as sysdba

drop user test cascade;
create user test identified by test;
grant create session, resource to test;

grant execute on dbms_rls to test;

connect test/test;

CREATE TABLE t ( when VARCHAR2(13), val SYS.ANYDATA, dt DATE );
insert into t values ('before policy',sys.anydata.convertvarchar2('123456789'),sysdate);


create or replace function testapp_security_function (p_schema varchar2, p_object varchar2)
return varchar2
is
begin
return '((cast(val as varchar2(1)) = ''Y'') or 1=1 )';
end;

/

begin
DBMS_RLS.ADD_POLICY (
object_schema => 'TEST',
object_name => 'T',

policy_name => 'TESTAPP_POLICY',
function_schema => 'TEST',
policy_function => 'TESTAPP_SECURITY_FUNCTION',
statement_types => 'SELECT,UPDATE,INSERT,DELETE',
update_check => TRUE,
enable => TRUE,
static_policy => FALSE);
end;
/
insert into t values ('after policy',sys.anydata.convertvarchar2('123456789'),sysdate);
connect / as sysdba
column val format a10
column dump format a40
select when, val, dt, dump(dt) dump from test.t t;

Charles Schultz said...

BDE finally has a solution, but they are only applying it to version 12.1, not even a backport to any version in 11g. Crazy.

Quote:
This is from the bug:
" This function does not consider the CAST buffer length () when copying the anydata value into the result buffer. In the test case, all nine bytes of the value are copied, resulting in memory corruption, in this case,
GORSDAV_ACTIVITY_DATE is overwritten with bad data. It should have only copied one byte."
So, you are right, the problem is caused by a memory overflow.

Anonymous said...

Is this "cast(some_anydata_column as varchar2(1))" production code? if so you are casting a date which might has 10 Bytes to a varchar2(1)!

Charles Schultz said...

And that's exactly the problem. Yes, this came out of production code (obviously, I whipped up the demo on my own, but the concept is from Production).