Saturday, March 2, 2013

1 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;

image

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;

image

Create login trigger use sys

CREATE OR REPLACE TRIGGER VPD1.SET_SECURITY_CONTEXT
AFTER LOGON ON DATABASE
BEGIN
  VPD1.CONTEXT_PACKAGE.SET_CONTEXT;
END;
/

image

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;
/

image

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);

image

 

--- 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;

image

image

image

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

image

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; 

1 comments:

  1. Thanks for the blog loaded with so many information. Stopping by your blog helped me to get what I was looking for. vpn

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates