oracle - 实现 VPD 谓词函数 - ORA-28110 : policy function or package has error

Question

我正在尝试在表上实现谓词函数。包和谓词函数编译没有问题，但是当我在表上选择时，我收到此错误:

SELECT * FROM MSGG_GUIDES;

ORA-28110: policy function or package VPD674.MSGG_SECURITY_POLICY has error

这是我定义的函数。下面的代码是该函数所属的包体中的一个片段。我的最终目标是让这个函数为您在 if 语句中看到的 4 个表定义一个策略。

  function MSGG_SECURITY_POLICY (schema_in varchar2, NAME_IN varchar2)
    return varchar2 
    IS 
    where_stmt varchar2(5000);
    BEGIN

      if schema_in='VPD674' and NAME_IN='MSGG_GUIDES' THEN
        where_stmt := 'PERSON_ID = MSGG_SESSION.get_user_id';

      elsif schema_in='VPD674' and NAME_IN='MSGG_ORDERS' THEN
        where_stmt := 'ORDERING_PERSON = MSGG_SESSION.get_user_id';

      elsif schema_in='VPD674' and NAME_IN='MSGG_SIGHTING_REPORTS' THEN
        where_stmt := 'PERSON_ID = MSGG_SESSION.get_user_id';

      elsif schema_in='VPD674' and NAME_IN='MSGG_TRIP_HISTORY' THEN
        where_stmt := 'GUIDE_ID = MSGG_SESSION.get_user_id';

      end if;

    return (where_stmt);

以下是它如何应用于相关表格(我从中选择的表格)。

BEGIN
DBMS_RLS.ADD_POLICY(
  object_schema => 'VPD674',
  object_name => 'MSGG_GUIDES',
  policy_name => 'MSGG_SECURITY_POLICY1',
  function_schema => 'VPD674',
  policy_function => 'MSGG_SECURITY_POLICY',
  statement_types => 'SELECT');

END;
/

关于为什么选择出错​​的任何想法？该函数编译没有问题。包、函数或策略上的“显示错误”命令返回“无错误”。我尝试了一个不使用 get_user_id 函数作为故障排除步骤的硬编码版本的函数，但我仍然收到相同的错误。我还尝试在包外创建函数并将其应用于策略，但收到了同样的错误。

为了完整披露，下面是整个包规范和正文创建脚本。您将看到我提到的硬编码函数版本 (MSGG_SECURITY_POLICY_G)。

CREATE OR REPLACE PACKAGE MSGG_SESSION AS 
    PROCEDURE authenticate (current_username varchar2, current_password varchar2);  
    function get_user_id 
  RETURN NUMBER;
  function MSGG_SECURITY_POLICY (schema_in varchar2, NAME_IN varchar2)
  RETURN VARCHAR2;
  function MSGG_SECURITY_POLICY_G (schema_var IN VARCHAR2,  table_var  IN VARCHAR2)
  RETURN VARCHAR2;
end MSGG_SESSION;
/

CREATE OR REPLACE PACKAGE BODY MSGG_SESSION AS 
    person_id_var NUMBER;

  function get_user_id 
        return NUMBER IS BEGIN

            return(person_id_var);

        end get_user_id;

    PROCEDURE authenticate (current_username varchar2, current_password varchar2) IS BEGIN

            SELECT personID 
      INTO person_id_var
            FROM MSGG_USER
            WHERE (current_username=username and current_password=password);

      DBMS_OUTPUT.put_line(person_id_var);

        END authenticate;   

  function MSGG_SECURITY_POLICY (schema_in varchar2, NAME_IN varchar2)
    return varchar2 
    IS 
    where_stmt varchar2(5000);
    BEGIN

      if schema_in='VPD674' and NAME_IN='MSGG_GUIDES' THEN
        where_stmt := 'PERSON_ID = MSGG_SESSION.get_user_id';

      elsif schema_in='VPD674' and NAME_IN='MSGG_ORDERS' THEN
        where_stmt := 'ORDERING_PERSON = MSGG_SESSION.get_user_id';

      elsif schema_in='VPD674' and NAME_IN='MSGG_SIGHTING_REPORTS' THEN
        where_stmt := 'PERSON_ID = MSGG_SESSION.get_user_id';

      elsif schema_in='VPD674' and NAME_IN='MSGG_TRIP_HISTORY' THEN
        where_stmt := 'GUIDE_ID = MSGG_SESSION.get_user_id';

      end if;

    return (where_stmt); 

    end MSGG_SECURITY_POLICY;

FUNCTION MSGG_SECURITY_POLICY_G( 
  schema_var IN VARCHAR2,
  table_var  IN VARCHAR2
 )
 RETURN VARCHAR2
 IS
  return_val VARCHAR2 (400);
 BEGIN
  return_val := 'PERSON_ID = ''14''';
  RETURN return_val;
 END MSGG_SECURITY_POLICY_G;

end MSGG_SESSION;

/

Answer 1

好吧，对于下一个出现的人，我能够弄清楚这一点。我构建 ADD_POLICY 的方式不正确。我需要在 policy_function 参数中包含包含函数的包名称。这是更正后的版本。您会看到我在函数名称前添加了 MSGG_SESSION。

BEGIN
DBMS_RLS.ADD_POLICY(
  object_schema => 'VPD674',
  object_name => 'MSGG_GUIDES',
  policy_name => 'MSGG_SECURITY_POLICY1',
  function_schema => 'VPD674',
  policy_function => 'MSGG_SESSION.MSGG_SECURITY_POLICY',
  statement_types => 'SELECT');
END;
/