I am creating Role based VPD policies for a pilot project and have issues when enabling the Policies. when the policies are enabled the data in the policy enabled table vanishes. Below are details,
when the user login, his login name is tied to a role and that role is referenced in lookup table to get the company id as a predicate in all sql searches. When the user is querying the table no data is fetched. Also when the table owner does sql on the table VPD_T2 no data is fetched in sqlplus and no error is thrown.
This is on windows 10, 64 bits, oracle 12c EE 12.2.0.1.0,
CREATE OR REPLACE FUNCTION VPD_TTDEV1_FN1
(
v_schema IN VARCHAR2,
v_name IN VARCHAR2
)
RETURN VARCHAR2
AS
BEGIN
return 'company_id in
( select company_id
from lookup
where RL_name in
(select role from session_roles))';
END VPD_TTDEV1_FN1;
create table lookup
(
RL_name varchar2(30),
company_id number(6),
constraint lookup_pk primary key (RL_name, company_id)
)
organization index;
BEGIN
DBMS_RLS.add_policy
(object_schema => 'STSDEV',
object_name => 'VPD_T2',
policy_name => 'Restrict_VPD_T2',
statement_types => 'SELECT',
function_schema => 'SECMGR',
policy_type => DBMS_RLS.STATIC,
policy_function => 'VPD_TTDEV1_FN1');
END;
/
BEGIN
DBMS_RLS.ENABLE_POLICY ('STSDEV','VPD_T2','Restrict_VPD_T2', TRUE );
END;
create table VPD_T1 (company_id number (6), cname varchar2(30));
CREATE table VPD_T2 (company_id number(6), cdata varchar2(30));
any idea where am I missing any or is this running into bug?
when I use sql developer I get ORA:00904 "ORA_ROWSCN" invalid identifier prevents data to show.