3 Virtual Private Database (VPD) 2: Limit the data base on User context

One of the feature in the VPD is allow the data’s visibility base on the user/application context.

In this exercise, I am going to:

• Create 3 users, VPD1,VPD2,VPD3.
• Create the table under VPD1 and grant VPD2 and VPD3 user permission.
• Create another table: user_list and use it to control who can access the table.

The end result for this testing are:

• VPD1 can see all the data in the user_data table.
• VPD2 and VPD3 only can see the data they insert.

Set up the environment

The login_name in the user_list table must be upper case.Because I am going to use SYS_CONTEXT to evaluate the SESSION_USER. The SESSION_USER return as upper case name for the user.

CREATE USER VPD1 IDENTIFIED BY VPD1 DEFAULT TABLESPACE users;

CREATE USER VPD2 IDENTIFIED BY VPD2 DEFAULT TABLESPACE users;

CREATE USER VPD3 IDENTIFIED BY VPD3 DEFAULT TABLESPACE users;

ALTER USER VPD1 QUOTA UNLIMITED ON users;

ALTER USER VPD2 QUOTA UNLIMITED ON users;

ALTER USER VPD3 QUOTA UNLIMITED ON users;

GRANT CONNECT,resource TO VPD1,VPD2,VPD3;

GRANT execute on DBMS_RLS to VPD1; 

GRANT create any context, create public synonym TO VPD1;

--- Connect as VPD1

CREATE TABLE user_list 

(id          NUMBER(10)   NOT NULL, 

 login_name  VARCHAR2(30) NOT NULL,

 comments  VARCHAR2(20) NOT NULL);

INSERT INTO user_list VALUES (100,'VPD2','oralce user VPD2');

INSERT INTO user_list VALUES (200,'VPD3','oracle user VPD3');

COMMIT;

CREATE TABLE user_data 

(column1     VARCHAR2(50) NOT NULL,

 user_id     NUMBER(10)   NOT NULL);

GRANT SELECT, INSERT ON user_data TO VPD2, VPD3;

Connect as VPD1 and create the context and package

We can use show error to list if there is any error message during the package compile time.

CREATE CONTEXT VPD1 USING VPD1.CONTEXT_PACKAGE;

CREATE OR REPLACE PACKAGE CONTEXT_PACKAGE AS

  PROCEDURE SET_CONTEXT;

END;

/

CREATE OR REPLACE

PACKAGE BODY CONTEXT_PACKAGE

IS

PROCEDURE SET_CONTEXT

IS

  v_ouser VARCHAR2(30);

  v_id    NUMBER;

BEGIN

  DBMS_SESSION.SET_CONTEXT('VPD1','SETUP','TRUE');

  v_ouser := SYS_CONTEXT('USERENV','SESSION_USER');

  BEGIN

    SELECT id INTO v_id FROM user_list WHERE login_name = v_ouser;

    DBMS_SESSION.SET_CONTEXT('VPD1','USER_ID', v_id);

  EXCEPTION

  WHEN NO_DATA_FOUND THEN

    DBMS_SESSION.SET_CONTEXT('VPD1','USER_ID', 0);

  END;

  DBMS_SESSION.SET_CONTEXT('VPD1','SETUP','FALSE');

END SET_CONTEXT;

END CONTEXT_PACKAGE;

/

SHOW ERRORS

GRANT EXECUTE ON VPD1.CONTEXT_PACKAGE TO PUBLIC;

CREATE OR REPLACE PUBLIC SYNONYM CONTEXT_PACKAGE FOR VPD1.CONTEXT_PACKAGE;

Create login trigger use sys

CREATE OR REPLACE TRIGGER VPD1.SET_SECURITY_CONTEXT

AFTER LOGON ON DATABASE

BEGIN

  VPD1.CONTEXT_PACKAGE.SET_CONTEXT;

END;

/

create security policy/function

CREATE OR REPLACE

PACKAGE BODY SECURITY_PACKAGE

IS

FUNCTION SELECT_SECURITY(

    Owner   VARCHAR2,

    Objname VARCHAR2)

  RETURN VARCHAR2

IS

  Predicate VARCHAR2(2000);

BEGIN

  Predicate                                := '1=2';

  IF (SYS_CONTEXT('USERENV','SESSION_USER') = 'VPD1') THEN

    Predicate                              := NULL;

  ELSE

    Predicate := 'USER_ID = SYS_CONTEXT(''VPD1'',''USER_ID'')';

  END IF;

  RETURN Predicate;

END SELECT_SECURITY;

FUNCTION INSERT_SECURITY(

    Owner   VARCHAR2,

    Objname VARCHAR2)

  RETURN VARCHAR2

IS

  Predicate VARCHAR2(2000);

BEGIN

  Predicate                                := '1=2';

  IF (SYS_CONTEXT('USERENV','SESSION_USER') = 'VPD1') THEN

    Predicate                              := NULL;

  ELSE

    Predicate := 'USER_ID = SYS_CONTEXT(''VPD1'',''USER_ID'')';

  END IF;

  RETURN Predicate;

END INSERT_SECURITY;

END SECURITY_PACKAGE;

/

Attaché the policy to the table

exec DBMS_RLS.ADD_POLICY(OBJECT_SCHEMA=> 'VPD1',OBJECT_NAME=>'user_data',POLICY_NAME=>'INSERT_POLICY',FUNCTION_SCHEMA=>'VPD1',POLICY_FUNCTION=>'SECURITY_PACKAGE.INSERT_SECURITY',STATEMENT_TYPES=>'INSERT',UPDATE_CHECK=>true,ENABLE=>true);

exec DBMS_RLS.ADD_POLICY(OBJECT_SCHEMA=> 'VPD1',OBJECT_NAME=>'user_data',POLICY_NAME=>'SELECT_POLICY',FUNCTION_SCHEMA=>'VPD1',POLICY_FUNCTION=>'SECURITY_PACKAGE.SELECT_SECURITY',STATEMENT_TYPES=>'SELECT',UPDATE_CHECK=>true,ENABLE=>true);

 

--- VPD2

show user

desc vpd1.user_data;

INSERT INTO vpd1.user_data (column1, user_id) VALUES ('Data from VPD2', 1);  -- Fail

INSERT INTO vpd1.user_data (column1, user_id) VALUES ('Data from VPD2', 100);  -- Success

INSERT INTO vpd1.user_data (column1, user_id) VALUES ('Data from VPD2', 200);  -- Fail

COMMIT;

--- VPD3

INSERT INTO vpd1.user_data (column1, user_id) VALUES ('Data from VPD3', 1);  -- Fail

INSERT INTO vpd1.user_data (column1, user_id) VALUES ('Data from VPD3', 100);  -- Fail

INSERT INTO vpd1.user_data (column1, user_id) VALUES ('Data from VPD3', 200);  -- Success

COMMIT;

VPD2 and VPD3 only can see the data it insert.  VPD1 can see all the data.

Clean up the ENVIRONMENT

drop user VPD1 cascade;

drop user VPD2 cascade;

drop user VPD3 cascade;

drop PUBLIC SYNONYM SECURITY_PACKAGE; 

drop PUBLIC SYNONYM CONTEXT_PACKAGE;  

drop context VPD1;