Hi everyone ,
I am trying to apply row level security for a table based on login using VPD, but facing error
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
scripts:
created function using below script
create or replace function emp_rls
(p_schema in varchar2,p_object in varchar2) return varchar2
is
v_empno number;
BEGIN
SELECT emp_id INTO v_empno
FROM dvsys.emp
WHERE emp_name = SYS_CONTEXT('userenv','session_user');
IF v_empno =121 THEN
RETURN '1=1';
ELSE
RETURN 'emp.emp_id='|| v_empno;
END IF;
END;
Next
added policy using below script
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'DVSYS',
object_name => 'EMP',
policy_name => 'EMP_ROW',
function_schema => 'DVSYS',
policy_function => 'EMP_RLS',
statement_types => 'SELECT'
);
END;
trace file error:
*** 2017-03-29 09:03:39.339
----------------------------------------------------------
Policy function execution error:
Logon user : THOTA
Table/View : DVSYS.EMP
VPD Policy name : EMP_ROW
Policy function: DVSYS.EMP_RLS
ORA-04024: self-deadlock detected while trying to mutex pin cursor 0x7FF90851DAA8
ORA-06512: at "DVSYS.EMP_RLS", line 5
ORA-06512: at line 1
*** 2017-03-29 09:03:39.339
----------------------------------------------------------
Policy function execution error:
Logon user : THOTA
Table/View : DVSYS.EMP
VPD Policy name : EMP_ROW
Policy function: DVSYS.EMP_RLS
ORA-28112: failed to execute policy function
ORA-06512: at "DVSYS.EMP_RLS", line 5
ORA-06512: at line 1
let me know if you guys require more information.
Thanks in advance