CODEWITHSUNDEEP
oracle-databaseplsql
Matt Monarch
Matt Monarch

Reputation: 41

Implementing VPD predicate function - ORA-28110: policy function or package has error

I am trying to implement a predicate function on a table. The package and predicate function compile with no issues, but when I select on the table, I receive this error:

SELECT * FROM MSGG_GUIDES;

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

Here is the function I have defined. The code below is a snippet from the package body that the function is a part of. My end goal is to have this function define a policy for the 4 tables you see in the if statements. 

  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);

Here is how it is applied to the table in question (the one I'm selecting from).

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;
/

Any ideas as to why the select is erroring out? The function compiles without issue. The "show errors" command on either the package, the function or the policy returns "no errors". I have attempted a hard coded version of the function that does not use the get_user_id function as a troubleshooting step, but I still receive the same error. I also tried creating the function outside of the package and applying it to the policy, but that received the same error.

For full disclosure, below is the entire package specification and body creation script. You will see the hardcoded function version I mentioned (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;

/

Upvotes: 3

Views: 6179

Answers (1)

Matt Monarch
Matt Monarch

Reputation: 41

Well, for the next person who comes along, I was able to figure this out. The way that I've structured ADD_POLICY was not correct. I needed to include the package name that contains the function in the policy_function parameter. Here's the corrected version. You'll see I added MSGG_SESSION before the function name.

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;
/

Upvotes: 1

Related Questions