VPD | DBMS_RLS | Policy Function | FGAC

VPD | DBMS_RLS | Policy Function | FGAC

In the context of Virtual Private Database (VPD), the most critical component is the policy function—a user-defined function that returns a predicate (a WHERE clause condition). However, this function only becomes a policy function when it is explicitly registered through the DBMS_RLS.ADD_POLICY procedure. Until then, it's just a regular function.Once associated via ADD_POLICY, Oracle automatically invokes this function before any user query (e.g., SELECT) on the protected table, and it appends the returned condition as a WHERE clause to that query. This dynamic and transparent mechanism is what enables Fine-Grained Access Control (FGAC) at the row level.

---

Query set to check VPD | DBMS_RLS | Policy Function

SELECT * FROM DBA_CONTEXT;
SELECT * FROM DBA_POLICIES;

-- Check if the context namespace exists
SELECT * FROM DBA_CONTEXT
WHERE NAMESPACE LIKE '%CTX_FOR_SOFT_PARSING%';  -- actual context name


--  Policy name
SELECT * FROM DBA_POLICIES
WHERE POLICY_NAME = 'CFR_HDR_POLICY'; 
-- actual Policy name


-- Policy function details - CFR_HDR_SECURITY_FN
SELECT name, type, line, text
FROM all_source
WHERE UPPER(text) LIKE '%CFR_HDR_SECURITY_FN%';


-- DBA_POLICIES details
SELECT OBJECT_OWNER,
       OBJECT_NAME,
       POLICY_NAME,
       PF_OWNER,
       PACKAGE,
       FUNCTION,
       POLICY_TYPE,
       ENABLE
  FROM DBA_POLICIES
 ORDER BY OBJECT_OWNER, OBJECT_NAME;