VPD Policy Tester

1

My VPD policies were causing troubles when I was running the Forms: ORA-28113 Policy Predicate Has Error . I could not easily figure out which Policy it was, as my data manipulation caused several triggers to fire that in turn performed queries and DML. So I wrote a simple procedure that can help me test all policies and find which ones are invalid.

It’s simple and really useful. Ideally it would disable all policies except the one being tested to be sure that any error is caused by that one. Feel free to add that functionality, I am under some time pressure right now.

create or replace
procedure policy_tester
is
  l_count number;
  procedure pl(p_text in varchar2)
  is
  begin
    dbms_output.put_line(p_text);
  end pl;
begin
  for ply in ( select object_name, object_owner
               ,      policy_name , package
               from   all_policies
             ) loop
    begin
      execute immediate 'select count(*) from '||ply.object_owner||'.'||ply.object_name
      into l_count;
      pl('Policy '||ply.policy_name||' on '||ply.object_owner||'.'||ply.object_name||' seems ok. ('||l_count||' records found)');
    exception
      when others
      then
        pl('Error ('||sqlerrm||') in policy '||ply.policy_name||' on '||ply.object_owner||'.'||ply.object_name);
    end;
  end loop;
end policy_tester;
/

Example output:

Policy PKS_CET_DML_ABS_AUT on AGS.AGS_CATALOGUS_ELEMENTEN seems ok. (0 records
found)
Policy PKS_V2400CPT_FUN_AUT on AGS.AGS_V_2400F_CPT seems ok. (0 records found)
Policy PKS_V2400CGP_FUN_AUT on AGS.AGS_V_2400F_CGP seems ok. (10 records found)
Policy PKS_V3100_FUN_AUT on AGS.AGS_V_3100F_PLN seems ok. (1 records found)
Policy PKS_V3201_FUN_AUT on AGS.AGS_V_3200F_PLN seems ok. (1 records found)
Error (ORA-28113: policy predicate has error) in policy PKS_V3301_FUN_AUT on
AGS.AGS_V_3300F_PLN
Policy PKS_V3401_FUN_AUT on AGS.AGS_V_3400F_PSR seems ok. (0 records found)
Policy PKS_V4100_FUN_AUT on AGS.AGS_V_4100F_RMG seems ok. (0 records found)
Policy PKS_V4200_FUN_AUT on AGS.AGS_V_4200F_RMG seems ok. (1 records found)
Policy PKS_V4300_FUN_AUT on AGS.AGS_V_4300F_RMG seems ok. (0 records found)
Policy PKS_V4400_FUN_AUT on AGS.AGS_V_4400F_PSR seems ok. (0 records found)
Policy PKS_V5101_FUN_AUT on AGS.AGS_V_5101F_PSR seems ok. (1 records found)
Policy PKS_V5102_FUN_AUT on AGS.AGS_V_5102F_MTG seems ok. (0 records found)
Policy PKS_V5201_FUN_AUT on AGS.AGS_V_5201F_MTG seems ok. (0 records found)
Error (ORA-28113: policy predicate has error) in policy PKS_V5302_FUN_AUT on
AGS.AGS_V_5300F_MTG
Policy PKS_V5301_FUN_AUT on AGS.AGS_V_5300F_PSR seems ok. (0 records found)
...
Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

1 Comment

  1. Pete Finnigan on

    Hi Lucas,

    This is a very useful little script. A great idea, thanks for sharing it. If you could put it in a .sql file and add it to your site somewhere i will link to it from my Oracle security tools page. If you don’t have time I will add a link to this blog entry instead.

    cheers

    Pete Finnigan