SQL & PL/SQL

Hi,

I'm using VPD(RLS) to filter data based.

I have a performance issue due to the VPD predicate my VPD function generate.

I have two tables:
- MARKER which is the table I want to secure and contains a primary key named marker_id and a filed use by VPD name VPD_IS_PUBLIC.
- DATASECURITY which is the table containing security information and tells to VPD wich data can be seen for a given role.

I put the script at the end of the message.

If you look to the code, the VPD function can return two predicates:
VPD_IS_PUBLIC=1

or

VPD_IS_PUBLIC=1 or marker_id in (select field_id from datasecurity where roleid=3)

The problem with the second query is that more I have data in my MARKER table more the query is slow.
This is because of "VPD_IS_PUBLIC=1 or" statement.

Is there another way to write that predicate?



Does someone can help me?

Cheers,
Sebastien

CREATE TABLE DATASECURITY
(
OBJECT_DB_ID NUMBER(10),
ROLEID NUMBER(10),
FIELD_ID NUMBER(10),
CREATION_DATE TIMESTAMP(6) WITH TIME ZONE DEFAULT systimestamp CONSTRAINT NN_DATASECURITY_CREATIONDATE NOT NULL,
CONSTRAINT PK_DATASECURITY
PRIMARY KEY
(OBJECT_DB_ID, ROLEID, FIELD_ID)
);


CREATE INDEX INDEX_DATASECURITY ON DATASECURITY
(FIELD_ID);

CREATE OR REPLACE TRIGGER TRG_AFT_INSERT_DATASECURITY
AFTER INSERT
ON DATASECURITY
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
BEGIN
UPDATE marker
SET vpd_is_public = 0
WHERE marker_id = :new.field_id;
END TRG_BEF_INSERT_DATASECURITY;
/

CREATE TABLE MARKER
(
MARKER_ID NUMBER(10) NOT NULL,
MARKER_TYPE_ID NUMBER(10),
MARKER_ACC VARCHAR2(50 BYTE),
VERSION VARCHAR2(10 BYTE),
DISPLAY_SYNONYM_ID NUMBER(10),
SPECIES_ID NUMBER(10) NOT NULL,
GERMPLASM_ID NUMBER(10),
LIBRARY_ID NUMBER(10),
DESCRIPTION VARCHAR2(2000 BYTE),
DATE_CREATED DATE,
DATE_UPDATED DATE,
VPD_IS_PUBLIC NUMBER(1) DEFAULT 1 CONSTRAINT NN_MARKER_VPD_IS_PUBLIC NOT NULL,
CONSTRAINT PK_MARKER
PRIMARY KEY
(MARKER_ID)
);


CREATE INDEX INDEX_MARKER ON MARKER
(VPD_IS_PUBLIC);

-- This function return the value stored in the session client info
CREATE OR REPLACE FUNCTION CSFDS.get_cwid
RETURN VARCHAR2
IS
retval VARCHAR2 (50);
BEGIN
DBMS_APPLICATION_INFO.READ_CLIENT_INFO (retval);
RETURN retval;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END get_cwid;
/

-- This function sets the value to the session client info
CREATE OR REPLACE FUNCTION CSFDS.set_cwid (cwid IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
DBMS_APPLICATION_INFO.set_client_info (cwid);
RETURN 1;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END set_cwid;
/


-- This function is the vpd function.
-- It returns differents predicate according the value in the session client info
CREATE OR REPLACE FUNCTION CSFDS.vpd (sch_name IN VARCHAR2, tab_name IN VARCHAR2)
RETURN VARCHAR2
IS
retval VARCHAR2 (500) DEFAULT '' ;
l_roleid NUMBER DEFAULT 3 ;
BEGIN
IF GET_CWID = 'csfds'
THEN
retval := 'VPD_IS_PUBLIC=1 or ';
retval :=
retval
|| 'marker_id in (select field_id from datasecurity where roleid='
|| l_roleid
|| ')';
ELSE
retval := 'VPD_IS_PUBLIC=1';
END IF;

RETURN retval;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END vpd;
/

-- To create the VPD policy
BEGIN
SYS.DBMS_RLS.ADD_POLICY (
object_schema => 'CSFDS'
,object_name => 'MARKER'
,policy_name => 'TEST_VPD'
,function_schema => 'CSFDS'
,policy_function => 'VPD'
,statement_types => 'SELECT'
,policy_type => dbms_rls.dynamic
,long_predicate => FALSE
,sec_relevant_cols => 'MARKER_ID,MARKER_TYPE_ID,MARKER_ACC,VERSION,DISPLAY_SYNONYM_ID,SPECIES_ID,GERMPLASM_ID,LIBRARY_ID,DESCRIPTION,DATE_CREATED,DATE_UPDATED,VPD_IS_PUBLIC'
,sec_relevant_cols_opt => NULL
,update_check => TRUE
,static_policy => FALSE
,enable => TRUE );
END;
/

Edited by: sfrade on Mar 18, 2009 2:00 PM