1

When i excute a merge statement:

merge into employees e using new_employees n
on (e.employee_id = n.employee_id)
when matched then
update set e.salary = n.salary
when not matched then
insert (employee_id,first_name,last_name,email,phone_number,hire_date,
           job_id,salary,commission_pct,manager_id,department_id)
values (n.employee_id,n.first_name,n.last_name,n.email,n.phone_number,
           n.hire_date,n.job_id,n.salary,n.commission_pct,n.manager_id,
           n.department_id);

it reports:ORA-28138:An invalid policy predicate was specified. What's wrong with me?

I find this error is caused by Fine_Grained Auditing.I had created a policy before as below:

 DBMS_FGA.add_policy (object_schema       => 'primer',
                object_name         => 'employees',
                policy_name         => 'audit_policy',
                audit_condition     => 'employee_id = 100',
                audit_column        => 'phone_number,salary',
                handler_schema      => 'primer',
                handler_module      => 'FGA_SEND_MAIL(primer,employees,audit_policy)',
                ENABLE              => TRUE,
                statement_types     => 'select,insert,update,delete',
                audit_trail         => DBMS_FGA.db_extended,
                audit_column_opts   => DBMS_FGA.any_columns);

create or replace procedure primer.fga_send_mail (p_schema   in varchar2,
                                          p_obj      in varchar2,
                                          p_pol      in varchar2)
as
begin
   dbms_output.put_line ('Audit Trail Generated');
   dbms_output.put_line ('SQL=' || sys_context ('USERENV', 'CURRENT_SQL'));
   dbms_output.put_line ('USER' || sys_context ('USERENV', 'SESSION_USER'));

end;

What wrong with it?

Jakub P
  • 542
  • 4
  • 21
gaofeng
  • 393
  • 1
  • 3
  • 11
  • Is there a VPD policy applied on your employees table. You can check the same by running the following query select * from user_policies where object_name = 'EMPLOYEES' – phonetic_man Dec 01 '16 at 06:42
  • I excuted the statement,it report none. – gaofeng Dec 01 '16 at 06:52
  • I find the prceceding statement can be executed as user `sys`.Does merge need special privilege? – gaofeng Dec 01 '16 at 07:13
  • do you have update grant ? – CompEng Dec 01 '16 at 07:14
  • The employees table is in his own schema. – gaofeng Dec 01 '16 at 08:14
  • This error is related to VPD/RLS. Figure out what policies you / your collegues / your DBA have activated and add the relevant policy functions to the question. It might be invalid under certain cicrumstances, e.g. the schema might not be properly qualified. The problem is not in the MERGE statement. – Codo Dec 01 '16 at 08:19

0 Answers0