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;