CODEWITHSUNDEEP
oracle-databaseplsql
Miriam Arbaji
Miriam Arbaji

Reputation: 329

VPD policy fails with ORA-28113: policy predicate has error

I have a table called payroll created by 'MANAGER' which contains the following:

EMP_ID               DEPT                      TOTAL      TAXES
-------------------- -------------------- ---------- ----------
E1                   accounting                 2400        100 
E2                   sales                      2500         75 
E3                   research                   3000        110 
E4                   operations                 4200        120 
E5                   sales                      4800        130 
E6                   sales                      2500         75 
E7                   accounting                 5200        140 
E8                   accounting                 2700        105

I also have table called employees, created also by 'MANAGER', containing:

ENAME                USER_ID              DEPT               
-------------------- -------------------- --------------------
SAMI                 E4                   operations           
ALI                  E7                   accounting           
MIRIAM               E5                   sales

I also have beside 'MANAGER' two users 'ALI' and 'SAMI' and I want to limit their access to 'payroll' table based on their departments, so I gave them the select privilege on both tables 'EMPLOYEES' and 'payroll' and I wrote the following policy function:

create or replace function sec_fun (p_schema varchar2, p_obj varchar2)
return varchar2
as
    v_dept MANAGER.employees.dept%type;
    v_user varchar2(100);
    v_id MANAGER.employees.user_id%type;
begin
    v_user:= SYS_CONTEXT('userenv', 'SESSION_USER');;
    select dept,user_id into v_dept, v_id from MANAGER.EMPLOYEES where ename=v_user;
    if (v_dept!= 'accounting') then
        return 'EMP_ID=' ||v_id;
    else
        return 'DEPT !=' || v_dept;
    end if;
exception
    when NO_DATA_FOUND then
        return null;
end;
/

then I wrote:

begin
    dbms_rls.add_policy(
      'MANAGER',
      'payroll',
      'p1',
      'MANAGER',
      'sec_fun',
      'select');
end;
/

now when I connect as 'ALI' then write

SELECT * FROM MANAGER.PAYROLL;

I get the following error which I looked and edited my policy function many times after reading the various solutions through the internet:

Error at Command Line:1 Column:23
Error report:
SQL Error: ORA-28113: policy predicate has error
*Cause:    Policy function generates invalid predicate.
*Action:   Review the trace file for detailed error information.

any help is so much appreciated. thank you in advance

Upvotes: 0

Views: 2066

Answers (1)

alexgibbs
alexgibbs

Reputation: 2480

There are a few things that come up with FGAC that can trigger this situation.

First, if the predicate-generating function is itself invalid, the policy will always throw this exception. In the example provided here, the assignment of v_user (line 8) has double semicolons ;; which should cause compilation to fail.

When you run the create statement for sec_fun, you should get a message like the following:

Warning: Function created with compilation errors.
Elapsed: 00:00:00.483

Since the predicate-generator has a syntax error, the policy applied to PAYROLL will always fail with the ORA-28113.

One thing to note is that the ORA-28113 includes a helpful recommendation:
"Review the trace file for detailed error information."
When a policy fails, the database writes a trace file detailing the underlying cause.

Anyway, after dropping the double semicolons, this should at least compile. But there are other problems looming ahead that will also trigger this.

The next problem up is that the return statements as written are not valid predicates either because they don't create valid string literals.

For example, "EMP_ID = E1" is not usable because it includes a literal that is not enclosed in quotations. The same applies for the "DEPT != accounting" predicate.

In the reworked version below, these have been quoted.

Additionally, FGAC usually prefers a valid predicate be provided from all execution branches.
Perhaps returning NULL when NO_DATA_FOUND could work in your situation, but it might be preferable to return a valid predicate (that filters all data). You might prefer to throw an exception for an employee that doesn't exist; in that case NULL might be acceptable. It is just a recommendation/thought.

One other thing to note is that using SYS_CONTEXT can have some complications and be more verbose than is required. Unless there is some indirection/nesting/proxying going on that would make the current user incorrect, one can just use the USER function instead, and avoid some sys_context complications.

Here's an alternative version that should compile, not throw errors, and filter along the lines you have described:

CREATE OR REPLACE FUNCTION SEC_FUN(P_SCHEMA VARCHAR2 , P_OBJ VARCHAR2) RETURN VARCHAR2 
AS
  V_DEPT EMPLOYEES.DEPT%TYPE;
  V_ID EMPLOYEES.USER_ID%TYPE;
BEGIN
  SELECT EMPLOYEES.DEPT, EMPLOYEES.USER_ID INTO V_DEPT, V_ID FROM MANAGER.EMPLOYEES WHERE EMPLOYEES.ENAME = USER;
  IF (V_DEPT != 'accounting')
  THEN
    RETURN 'EMP_ID = ' || CHR(39)||V_ID||CHR(39);
  ELSE
    RETURN 'DEPT != ' || CHR(39)||V_DEPT||CHR(39);
  END IF;
  EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
    RETURN '1 = 0';
END;
/

Upvotes: 0

Related Questions