Oracle VPD 'Set_Context' Not Working

Question

I've written a VPD where there're Admin users such as JAdmin, PAdmin, belonging to company_ID = 90 and FAdmin, KAdmin belonging to Company_ID = 91. There are other employees in the Employee table belonging to the two companies. There're other tables with information from both companies such as Timesheet, Payroll_Period, etc. There is also a table named Company_Administrators which hold a list of Admins and their Company_ID's. Objective is to use Set_Context to get the company_ID of the Admins at log in and use it to display the information from ONLY their company. The code:

CREATE USER JAdmin IDENTIFIED BY JAdmin
DEFAULT TABLESPACE IA643_TBS
TEMPORARY TABLESPACE TEMP
ACCOUNT UNLOCK;

CREATE USER FAdmin IDENTIFIED BY FAdmin
DEFAULT TABLESPACE IA643_TBS
TEMPORARY TABLESPACE TEMP
ACCOUNT UNLOCK;

CREATE USER PAdmin IDENTIFIED BY PAdmin
DEFAULT TABLESPACE IA643_TBS
TEMPORARY TABLESPACE TEMP
ACCOUNT UNLOCK;

CREATE USER KAdmin IDENTIFIED BY KAdmin
DEFAULT TABLESPACE IA643_TBS
TEMPORARY TABLESPACE TEMP
ACCOUNT UNLOCK;

GRANT CONNECT, RESOURCE TO JAdmin;
GRANT CONNECT, RESOURCE TO FAdmin;
GRANT CONNECT, RESOURCE TO PAdmin;
GRANT CONNECT, RESOURCE TO KAdmin;

CREATE OR REPLACE PUBLIC SYNONYM COMPANY 
FOR DBA643.COMPANY;
GRANT INSERT, SELECT, UPDATE, DELETE ON COMPANY 
TO FAdmin, JAdmin, PAdmin, KAdmin;

CREATE OR REPLACE PUBLIC SYNONYM EMPLOYEE 
FOR DBA643.EMPLOYEE;
GRANT INSERT, SELECT, UPDATE, DELETE ON EMPLOYEE 
TO FAdmin, JAdmin, PAdmin, KAdmin;

CREATE OR REPLACE PUBLIC SYNONYM TIMESHEET 
FOR DBA643.TIMESHEET;
GRANT INSERT, SELECT, UPDATE, DELETE ON TIMESHEET 
TO FAdmin, JAdmin, PAdmin, KAdmin;

CREATE OR REPLACE PUBLIC SYNONYM PAYROLL_PERIOD 
FOR DBA643.PAYROLL_PERIOD;
GRANT INSERT, SELECT, UPDATE, DELETE ON PAYROLL_PERIOD 
TO FAdmin, JAdmin, PAdmin, KAdmin;

CREATE OR REPLACE PUBLIC SYNONYM DAILY_WORK_HOURS 
FOR DBA643.DAILY_WORK_HOURS;
GRANT INSERT, SELECT, UPDATE, DELETE ON DAILY_WORK_HOURS 
TO FAdmin, JAdmin, PAdmin, KAdmin;


Conn sys as sysdba

CREATE USER sysadmin_ctx IDENTIFIED BY secAdmin;
GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE PROCEDURE, CREATE TRIGGER, ADMINISTER
DATABASE TRIGGER TO sysadmin_ctx IDENTIFIED BY secAdmin;
GRANT EXECUTE ON DBMS_SESSION TO sysadmin_ctx;
GRANT EXECUTE ON DBMS_RLS TO sysadmin_ctx;
GRANT RESOURCE TO sysadmin_ctx;

GRANT SELECT ON Company_Administrators TO sysadmin_ctx; 


CREATE OR REPLACE CONTEXT Company_Admin USING PKG_Comp_Admin;
CREATE OR REPLACE PACKAGE PKG_Comp_Admin IS
PROCEDURE Get_Company_ID;
END;
/
CREATE OR REPLACE PACKAGE BODY PKG_Comp_Admin IS
PROCEDURE Get_Company_ID IS
V_Company_ID NUMBER;
BEGIN
    SELECT Company_ID
    INTO V_Company_ID
    FROM DBA643.Company_Administrators
    WHERE System_Username = SYS_CONTEXT('USERENV', 'SESSION_USER');
    DBMS_SESSION.SET_CONTEXT('COMPANY_ADMIN', 'CompanyID', 'V_Company_ID');
    EXCEPTION
    WHEN NO_DATA_FOUND THEN NULL;
    END;
END;
/
SHOW ERROR;



CREATE OR REPLACE FUNCTION Company_Admin_fun (P_schema_name IN varchar2, 
P_object_name IN varchar2) RETURN varchar2 IS
V_where varchar2(300);
BEGIN
    IF User = 'DBA643' then
    V_where := '';
ELSE
    V_where := 'Company_ID = '||NVL(SYS_CONTEXT('Company_Admin', 'CompanyID'),0);
    END IF;
RETURN V_where;
END;
/


EXEC DBMS_RLS.DROP_Policy ('DBA643','COMPANY','COMPANY_POLICY');
EXEC DBMS_RLS.DROP_Policy ('DBA643','EMPLOYEE','EMPLOYEE_POLICY');
EXEC DBMS_RLS.DROP_Policy ('DBA643','TIMESHEET','TIMESHEET_POLICY');
EXEC DBMS_RLS.DROP_Policy ('DBA643','DAILY_WORK_HOURS','DAILY_WORK_HOURS_POLICY');


EXEC DBMS_RLS.ADD_Policy ('DBA643','COMPANY','COMPANY_POLICY','sysadmin_ctx','Company_Admin_fun','SELECT, UPDATE, DELETE, INSERT', TRUE);
EXEC DBMS_RLS.ADD_Policy ('DBA643','EMPLOYEE','EMPLOYEE_POLICY','sysadmin_ctx','Company_Admin_fun','SELECT, UPDATE, DELETE, INSERT', TRUE);
EXEC DBMS_RLS.ADD_Policy ('DBA643','TIMESHEET','TIMESHEET_POLICY','sysadmin_ctx','Company_Admin_fun','SELECT, UPDATE, DELETE, INSERT', TRUE);
EXEC DBMS_RLS.ADD_Policy ('DBA643','DAILY_WORK_HOURS','DAILY_WORK_HOURS_POLICY','sysadmin_ctx','Company_Admin_fun','SELECT, UPDATE, DELETE, INSERT', TRUE);


CREATE OR REPLACE TRIGGER After_Logon_Trigger
AFTER LOGON
ON DATABASE
BEGIN
    sysadmin_ctx.PKG_Comp_Admin.Get_Company_ID;
END;
/

When I log in as any of the Admins and do a select query, I get 'no rows selected'. I think I have narrowed down the issue to the Set_context part because when I use the following command after logging in as one of the Admins, it doesn't show the updated value in the Company_Admin context which should be the Admin's Company ID.

select SYS_CONTEXT('Company_Admin', 'CompanyID') from dual

Any help would be much appreciated. Thank you.

Answer 1

Have you tried creating the context with the ACCESSED GLOBALLY clause?

If the set_context operation runs on a different session from the session on which you query your context's namespace - you'll get no data. A context created without the ACCESSED GLOBALLY clause, will hold data on session level only. When creating it with the ACCESSED GLOBALLY clause, the data can be accessed on instance level.

P.S

I don't think there's a way to create a context on RAC level due to the fact that the context holds it's data on the server's RAM, without a known option (at least on 11gR2) to duplicate the context's data to each RAC node. Also, it's problematic to rely on contexts due to the fact that there's no way to save the data in case of instance shutdown (if one plans to use it as classic global variable or global counter - the namespace's data should be set on instance startup every time).

Answer 2

You're setting the context to the wrong value (the literal string V_Company_ID instead of the variable value) - instead of

DBMS_SESSION.SET_CONTEXT('COMPANY_ADMIN', 'CompanyID', 'V_Company_ID');

it should be

DBMS_SESSION.SET_CONTEXT('COMPANY_ADMIN', 'CompanyID', V_Company_ID);

Some further suggestions for debugging:

• write a log entry in your sysadmin_ctx.PKG_Comp_Admin.Get_Company_ID procedure that logs the user
• if you're using Oracle 12c: use dbms_utility.expand_sql_text to see what the VPD appends to your original SQL statement