Expected UDT got NUMBER in Virtual Private Database Policy

Question

I have following problem: I want to implement a 'Row Level Security Policy' to a table in my database and added this function:

FUNCTION app_user_is_master_owner(
    schema_in IN VARCHAR2,
    object_in IN VARCHAR
)
RETURN VARCHAR2
IS  return_value VARCHAR2(100);
BEGIN
SELECT 'OWNER_FK = ' || 
    (SELECT mo.owner_id 
    FROM MASTER_OWNER mo
    WHERE upper(mo.owner_name) = SYS_CONTEXT('USERENV', 'SESSION_USER')) ||
    ' OR OWNER_FK IS EMPTY' 
    INTO return_value
    FROM DUAL;
RETURN return_value;
END app_user_is_master_owner;

I called ADD_POLICY from DBMS_RLS to add it to the other policys

 BEGIN
 DBMS_RLS.ADD_POLICY(
 object_schema => 'MY_SCHEMA',
 object_name => 'MASTER_DATA',
 policy_name => 'app_user_is_mo_policy',
 function_schema => 'MY_SCHEMA',
 policy_function => 'MY_RLS_POLICYS.app_user_is_master_owner',
 statement_types => 'SELECT, INSERT, UPDATE, DELETE'
 );
 END;

When I call the function within sql plus without adding it as RLS Policy the expected 'where' clause is returned

SQL> select my_rls_policys.app_user_is_master_owner('A','A') from dual;
 MY_RLS_POLICYS.APP_USER_IS_MASTER_OWNER('A','A')
 --------------------------------------------------------------------------------
 OWNER_FK = 4000 OR OWNER_FK IS EMPTY

but if I call it as a VPD-Policy there is this error message.

SQL> SELECT * FROM MASTER_DATA;
SELECT * FROM MASTER_DATA
                        *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected UDT got NUMBER

I guess it's because the result is a Select and not a VARCHAR, but adding TO_CHAR to neither the "select to_char(...) into return_value" nor "return TO_CHAR(return_value)" seems to solve the problem.

Other policys works just fine.

Thanks for your help.

Matthias

score 0 · Accepted Answer · answered Jun 23 '14 at 13:31

0

Try fetching the owner to a variable and then concatenate

function app_user_is_master_owner(
    schema_in in varchar2,
    object_in in varchar
) return varchar2 
is
    predicate   varchar2(100);
    owner_id    master_owner.owner_id%type;
begin
    select  owner_id 
    into    owner_id
    from    master_owner
    where   upper(owner_name) = sys_context('userenv', 'session_user');

    predicate := '(owner_fk = ' || owner_id || ' or owner_fk is null)';

    return predicate;
end app_user_is_master_owner;

P.S not sure what is empty means ... i changed it to is null.

answered Jun 23 '14 at 13:31

haki

9,389
15
62
110

Two more things while your at it, define a larger size for generated string such as predicate (I usualy use the limit for synamic string ~ varchar2(4001)) and add a case for `NO_DATA_FOUND`. – haki Jun 23 '14 at 14:48
I'll consider it, right now it's just an example for a tutorial I try to make. But thanks for the advice. – mSchnell Jun 23 '14 at 15:13

Expected UDT got NUMBER in Virtual Private Database Policy

1 Answers1