Ytukyg

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

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;

/