VPD Policy Function

I’m new to oracle security and I have a question about Virtual Private Database: Let’s assume i have the following table which is called “Payroll” created by a user called “PCM”

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

Now what i want to achieve is the following: Anyone with the dept = accounting" can select all other rows with dept != accounting but anyone with dept != accounting can only view his/her record. My problem is: since we can’t apply a select statement on the table we are restricting the access to inside the policy function (which is payroll) and since applying a policy function on the (payroll) table will be applied to any view based on it, what’s the logic to solve this problem?? what is the solution, I’ve wrote the following “I’m connected as another user not the owner of the payroll table so I’m connected as a user called ANNE”:

CREATE OR REPLACE CONTEXT payroll_ctx USING payroll_ctx_pkg;
CREATE OR REPLACE PACKAGE payroll_ctx_pkg IS 
  PROCEDURE set_dept;
 END;
/
CREATE OR REPLACE PACKAGE BODY payroll_ctx_pkg IS
  PROCEDURE set_dept
  AS
    v_dept varchar2(400);
  BEGIN
     SELECT dept INTO v_dept FROM PCM.PAYROLL
        WHERE EMP_ID = SYS_CONTEXT('USERENV', 'SESSION_USER');
     DBMS_SESSION.SET_CONTEXT('payroll_ctx', 'dept', v_dept);
  EXCEPTION
   WHEN NO_DATA_FOUND THEN
   DBMS_SESSION.SET_CONTEXT('payroll_ctx', 'dept', 'E0');
  END set_dept;
END;
/

Considering that the users who will try to access the table have the names of the emp_id column,now:

CREATE TRIGGER set_dept_trig AFTER LOGON ON DATABASE
 BEGIN
  ANNE.payroll_ctx_pkg.set_dept;
 END;
/

Now the problem(i know it’s wrong) but can’t find the solution:

create or replace function sec_fun (p_schema varchar2, p_obj varchar2)
return varchar2
as
    vv_dept varchar2(400);
    payroll_pred varchar2(400);
begin
    payroll_pred := '1=2';
    vv_dept := SYS_CONTEXT('payroll_ctx', 'dept');
    if (vv_dept != 'accounting') then
        payroll_pred := 'DEPT =''' || vv_dept ||'''';
    else
        payroll_pred:='DEPT !=''' || vv_dept ||'''';
    end if;
   return payroll_pred;
end;
/

And then:

BEGIN
 DBMS_RLS.ADD_POLICY (
  object_schema    => 'PCM', 
  object_name      => 'PAYROLL', 
  policy_name      => 'payroll_policy', 
  function_schema  => 'ANNE',
  policy_function  => 'sec_fun',
  statement_types  => 'select');
END;
/

and the error message i’m getting when a user E1 tries to select from payroll:

no rows selected

what i’m doing wrong?? I have edited the question and the result based on the answers below.

Answer

I’m going to make some guesses but if none of them solve your problem you will need to look for the diagnostic information the database provides. ORA-28112 will produce a trace file in the user_dump_dest directory on the database server. This should give you all the information you need to diagnose and fix any errors in your policy code. If you don’t have access to that directory you will need to ask your friendly DBA/Sysadmin team to help.

So, the guesses.

You’re setting vv_dept to a string of 'dept = SYS_CONTEXT(''payroll_ctx'', ''dept'')'. In the IF block you concatenate that to a WHERE clause filter. So your final string is actually:

DEPT =dept = SYS_CONTEXT('payroll_ctx', 'dept')

(It will never execute DEPT != branch because you’re assigning that string to vv_dept not executing it, so vv_dept can never equal 'Accounting'.)

This is clearly wrong and so it’s no surprise that Oracle hurls ORA-28112. Fortunately the solution is equally clear: just tidy the assignment of vv_dept:

vv_dept := SYS_CONTEXT('payroll_ctx', 'dept');

The other thing to look at is the formatting on the policy itself. You are testing for equality of a string, so you need to wrap it it in quotes to make valid SQL:

        payroll_pred := 'DEPT =''' || vv_dept ||'''';

And the same for the other branch, obviously.

One other thing to consider. If you logon as a user who is not in payroll table (not sure whether that’s possible) you execute this:

WHEN NO_DATA_FOUND THEN NULL;

Consequently, that session has no context set which means the policy function will fail if the user subsequently tries to query payroll. A better approach would be to set the context with a null for dept, then in the policy add a test for vv_dept is null which applies 1=2 or something similar.

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Well you’d certainly get that if you were running your posted sec_fun code, because you defined vv_dept as varchar2(20) and the assigned string is longer than twenty characters. But you say you are running my suggested revisions, so it can’t be that. What is the size of payroll.dept?

Last word.

VPD policies are just a special case of dynamic SQL, and the core teaching remains: dynamic SQL is hard because it turns compilation errors into runtime errors. Likewise the main debugging tool is still writing an execution handle which logs the whole assembled string to a table (or file).

---

Incidentally, I think there’s a flaw in the business logic. As it stands now nobody can look at the records for the Accounting department (except for power users with the EXEMPT ACCESS POLICY privilege).