Oracle Virtual private Database (VPD) - ORA-28113 policy predicate has error

Question

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;

Answer 1

The value I'm trying to filter is stored in this format - " |1234| "

If it's stored as a string, which it seems to be, then your predicate needs to compare the column to a string value; which means you'll need to enclose the value in single quotes within the predicate, and as that is already a string you will need to escape the quotes around that value.

So instead of:

l_predicate := 'DOMAIN != |3721|';

you can do:

l_predicate := 'DOMAIN != ''|3721|''';

to escape the inner quotes, or use the alternative quoting mechanism (q-quoting):

l_predicate := q'^DOMAIN != '|3721|'^';

Here the text literal is in the form q'^...some value...^', which allows the text within that - ...some value... - to include quotes without have to escape them all. (I've used ^ as delimiters, but you can use any character that will not be in your value).