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;
Thanks for the blog loaded with so many information. Stopping by your blog helped me to get what I was looking for. vpn
ReplyDeletea given article is very interesting and very useful for my admin thank you very much and sorry for my permission to share the article here
ReplyDeleteCara mengatasi badan berlemak
Obat untuk mengatasi penebalan dinding rahim
Obat herbal kanker lidah
This is highly informatics, crisp and clear. I think that everything has been described in systematic manner so that reader could get maximum information and learn many things. vpn services
ReplyDelete