Trying to hide certain columns based on the user logged in and running into issues with the policy function. The value I’m trying to filter is stored in this format – ” |1234| “and I need to exclude it. Appreciate input on how to filter the value in the above format. Thanks!
BEGIN
DBMS_RLS.drop_POLICY (object_schema => 'Schema1',
object_name => 'RT_TABLE',
policy_name => 'AUDIT_DOMAINSECURE');
END;
CREATE OR REPLACE FUNCTION AUDITDOMAIN_SECURE_FNC (p_schema VARCHAR2,
p_obj VARCHAR2)
RETURN VARCHAR2
AS
l_user VARCHAR2 (20);
l_predicate VARCHAR2 (100);
BEGIN
SELECT USER INTO l_user FROM DUAL;
IF l_user NOT IN ('TOM')
THEN
l_predicate := 'DOMAIN != |3721|';
END IF;
RETURN l_predicate;
END AUDITDOMAIN_SECURE_FNC;
BEGIN
DBMS_RLS.add_policy (object_schema => 'SCHEMA1' -- specify the schema containing the object
,
object_name => 'RT_TABLE' -- specify the object name within the schema.
,
policy_name => 'AUDIT_DOMAINSECURE' -- specify the policy name. Policy name is unique for an object.
,
function_schema => 'SCHEMA1' -- specify the schema in which the policy function is created
,
policy_function => 'AUDITDOMAIN_SECURE_FNC' -- specify the name of the policy function
,
statement_Types => 'SELECT' -- Operations when this policy applies. SELECT
,
sec_relevant_cols => 'DOMAIN,TABLE_ID' -- ALL relevant columns to be hidden from users
-- ,sec_relevant_cols_opt=> dbms_rls.ALL_ROWS
);
END;