Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

oracle VPD row level security

Venkat Thota - BIPMar 29 2017 — edited Apr 2 2017

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

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 1 2023
Added on Mar 29 2017
12 comments
651 views