VPD Policy Implementation - ORACLE Database

VPD Policy Implementation - ORACLE Database

 VPD policies Oracle

CREATE TEST USERS in DB

-- Owner schema for objects

CREATE USER app_owner IDENTIFIED BY AppOwner#123

DEFAULT TABLESPACE users

TEMPORARY TABLESPACE temp

QUOTA UNLIMITED ON users;

GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE, CREATE ANY CONTEXT TO app_owner;

-- Test application user

CREATE USER app_user IDENTIFIED BY AppUser#123

DEFAULT TABLESPACE users

TEMPORARY TABLESPACE temp;

GRANT CREATE SESSION TO app_user;

CONNECT TO USER

CONN app_owner/AppOwner#123 

CREATE THE SAMPLE TABLE AND DATA

CREATE TABLE employees (

    emp_id         NUMBER PRIMARY KEY,

    emp_name       VARCHAR2(100),

    department_id  NUMBER,

    salary         NUMBER

);

INSERT TEST DATA

 

INSERT INTO employees VALUES (1, 'John',  10, 1000);

INSERT INTO employees VALUES (2, 'Mary',  20, 1200);

INSERT INTO employees VALUES (3, 'David', 10, 1500);

INSERT INTO employees VALUES (4, 'Sarah', 30, 1800);

COMMIT; 

CREATE PACKAGE

CREATE OR REPLACE PACKAGE sec_ctx_pkg AS

    PROCEDURE set_dept_context(p_dept_id NUMBER);

END sec_ctx_pkg;

/

CREATE PACKAGE BODY

CREATE OR REPLACE PACKAGE BODY sec_ctx_pkg AS

    PROCEDURE set_dept_context(p_dept_id NUMBER) IS

    BEGIN

        DBMS_SESSION.SET_CONTEXT(

            namespace => 'APP_CTX',

            attribute => 'DEPT_ID',

            value     => p_dept_id

        );

    END;

END sec_ctx_pkg;

/ 

CREATE CONTEXT

CREATE CONTEXT app_ctx USING sec_ctx_pkg; 

CREATE THE VPD POLICY FUNCTION.

CREATE OR REPLACE FUNCTION emp_policy_fn (

    p_schema IN VARCHAR2,

    p_object IN VARCHAR2

) RETURN VARCHAR2

AS

    l_dept_id VARCHAR2(30);

BEGIN

    l_dept_id := SYS_CONTEXT('APP_CTX', 'DEPT_ID');

    IF l_dept_id IS NULL THEN

        RETURN '1=2';

    END IF;

    RETURN 'department_id = ' || l_dept_id;

END;

/

ATTACH THE VPD POLICY TO THE TABLE CREATED.

BEGIN

    DBMS_RLS.ADD_POLICY(

        object_schema   => 'APP_OWNER',

        object_name     => 'EMPLOYEES',

        policy_name     => 'EMP_DEPT_VPD',

        function_schema => 'APP_OWNER',

        policy_function => 'EMP_POLICY_FN',

        statement_types => 'SELECT,UPDATE,DELETE',

        update_check    => TRUE

    );

END;

/ 

GRANT ACCESS TO THE USER

 

GRANT SELECT, UPDATE, DELETE ON employees TO app_user;

GRANT EXECUTE ON sec_ctx_pkg TO app_user;