what is VPD in oracle

 VPD stands for virtual private database in oracle. Using VPD we can enable security at the row and column level for a table  to user.

For example . I have an employee table with a salary column , using VPD we can restrict users having employee privileges to access only their salary and users with manager privileges can access salary column data of all users.

With VPD, you can define security policies based on user attributes such as their identity, role, or session context. These policies are implemented through the use of database functions called "policy functions" that are associated with tables or views. These policy functions are invoked automatically by the database whenever a user attempts to access the data.

When a user queries a table or view that is protected by VPD, the associated policy function evaluates the user's session attributes and dynamically adds WHERE clauses to the SQL statement, limiting the result set to only the rows that the user is authorized to access. This process happens transparently, without requiring any modifications to the application code.

VPD provides a powerful mechanism for enforcing row-level and column-level security. It enables organizations to enforce fine-grained access control policies based on business rules, data ownership, or any other criteria. By restricting access to sensitive or confidential data, VPD helps organizations protect their data from unauthorized access and ensure compliance with privacy regulations.

CREATE TABLE Employees (
  EmployeeID   NUMBER,
  FirstName    VARCHAR2(50),
  LastName     VARCHAR2(50),
  Salary       NUMBER
);

CREATE OR REPLACE FUNCTION salary_policy_function (
  schema_name  IN VARCHAR2,
  object_name  IN VARCHAR2
) RETURN VARCHAR2
IS
  predicate VARCHAR2(4000);
BEGIN
  IF (USER_ROLE('MANAGER') = 1) THEN
    -- Managers can access all salary data
    predicate := '';
  ELSE
    -- Employees can only access their own salary
    predicate := 'EMPLOYEEID = SYS_CONTEXT(''USERENV'', ''SESSION_USERID'')';
  END IF;
  
  RETURN predicate;
END;
/

BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema   => 'SCHEMA_NAME',
    object_name     => 'EMPLOYEES',
    policy_name     => 'SALARY_POLICY',
    function_schema => 'SCHEMA_NAME',
    policy_function => 'SALARY_POLICY_FUNCTION',
    statement_types => 'SELECT',
    update_check    => FALSE,
    enable          => TRUE
  );
END;

/