期望UDT在虚拟专用数据库策略中获得NUMBER

Question

我有以下问题:我想实现一个'行级安全策略'在我的数据库表，并添加了这个功能:

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;

我从DBMS_RLS调用ADD_POLICY将其添加到其他策略

 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;

当我在sql plus中调用函数而不将其添加为RLS策略时，返回预期的'where'子句

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

但是如果我把它作为一个VPD-Policy调用，就会出现这个错误消息

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

我想这是因为结果是一个选择，而不是一个VARCHAR，但添加TO_CHAR既不是"选择TO_CHAR(…)到return_value"也不是"返回TO_CHAR(return_value)"似乎解决了这个问题。

其他策略都可以。

谢谢你的帮助。

马提亚

Answer 1

尝试获取变量的所有者，然后连接

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。我不确定is empty是什么意思…我改成了is null