VPD date issue between 10.1 and 10.2 databases

I have uncovered an issue today with VPD against date fields. The issue came up at a client site on a 10.2 database. When I got back to the hotel and tried this on my own database, which is 10.1, I got no issue and everything worked ok.

Therefore, I am asking if anyone is able to try out the following for me on different Oracle databases and platforms and let me know by email at michael@learndiscoverer.com whether it worked for you or not. I'd like to compile a list of database versions and platforms on which the issue occurs.

Step 1: create this table as a user

CREATE TABLE TST_DATE AS (
SELECT 1 PERSON_UID,
Trunc(SYSDATE) BIRTH_DATE,
To_Char(Trunc(SYSDATE), 'DD-MON-YYYY') CHAR_DATE,
123456 TAX_ID,
'Smith, Michael B.' FULL_NAME FROM dual);

Step 2: grant select rights on the table

GRANT SELECT ON TST_DATE TO PUBLIC;

Step 3: switch your login to SYS and run this script:

CREATE OR REPLACE FUNCTION F_CHECK_ITEM_TST(p_object_schema in varchar2, p_object_name varchar2)
RETURN VARCHAR2 IS
V_PREDICATE VARCHAR2(2000) := '1 = 2';
BEGIN
RETURN(V_PREDICATE);
END F_CHECK_ITEM_TST;

Step 4: Run this to grant access rights over the function

GRANT EXECUTE ON F_CHECK_ITEM_TST TO PUBLIC;

Step 5: Run this to enable the policy

BEGIN DBMS_RLS.ADD_POLICY(
OBJECT_SCHEMA => 'DRAKE',
OBJECT_NAME => 'TST_DATE',
POLICY_NAME => 'SecByTST',
FUNCTION_SCHEMA => 'SYS',
POLICY_FUNCTION => 'F_CHECK_ITEM_TST',
STATEMENT_TYPES => 'SELECT',
POLICY_TYPE => DBMS_RLS.DYNAMIC,
SEC_RELEVANT_COLS => 'BIRTH_DATE',
SEC_RELEVANT_COLS_OPT => DBMS_RLS.ALL_ROWS);
END;

Note: in the above policy change the OBJECT_SCHEMA name to be the name of the user who owns the table you created in Step 1

Step 6: Go into Discoverer Admin and import the table as a new folder.
Be sure to uncheck the "Date hierarchies" box and set "Default aggregate on data points" to detail. Grant access to any user.

Step 7: Using Desktop or Plus, log in as that user then try to query the folder with the date not included, there should be no problem.

Step 8: Now try to query with the date included
On my 10.2 database I get ORA-24334: no descriptor for this position. This happens when logged into Discoverer as the table owner, EUL owner or any other user. However, when logged into Discoverer as SYS which is exempt from all VPD policies, there is no error when the date is included.

We tried this in Discoverer Desktop, Plus and Viewer with the same results in all three. We can successfully query the table from SQL*Plus, SQL Developer and TOAD.

There also seems to be no issue at all with the 10.1 database so there seems to be an issue between Discoverer and the 10.2 database.

What do you think?

P.S. to cancel the policy use this script when logged in as SYS:

EXEC DBMS_RLS.drop_policy(
OBJECT_SCHEMA => 'DRAKE',
OBJECT_NAME => 'TST_DATE',
POLICY_NAME => 'SecByTST');