Virtual Private Databases (VPD) allow multiple users to access a single schema whilst preventing them from accessing data that is not relevant to them. Although this type of access can be controlled by the application, access via other methods (SQL*Plus) would leave the data open to abuse. VPD uses Fine-Grained Access Control to limit which data is visible to specific users. Setting up a VPD involves the following steps.
Related articles.
First we must create a user to act as the schema owner for this example. Obviously, you will perform the following tasks using your current schema owner.
CONNECT sys/password@service AS SYSDBA; CREATE USER schemaowner IDENTIFIED BY schemaowner DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; GRANT connect, resource TO schemaowner; CREATE USER user1 IDENTIFIED BY user1 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; GRANT connect, resource TO user1; CREATE USER user2 IDENTIFIED BY user2 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; GRANT connect, resource TO user2; GRANT EXECUTE ON DBMS_RLS TO PUBLIC; CONN schemaowner/schemaowner@service CREATE TABLE users (id NUMBER(10) NOT NULL, ouser VARCHAR2(30) NOT NULL, first_name VARCHAR2(50) NOT NULL, last_name VARCHAR2(50) NOT NULL); CREATE TABLE user_data (column1 VARCHAR2(50) NOT NULL, user_id NUMBER(10) NOT NULL); INSERT INTO users VALUES (1,'USER1','User','One'); INSERT INTO users VALUES (2,'USER2','User','Two'); COMMIT; GRANT SELECT, INSERT ON user_data TO user1, user2;
Grant CREATE ANY CONTEXT
to the schema owner then create the context and context package.
CONNECT sys/password@service AS SYSDBA; GRANT create any context, create public synonym TO schemaowner; CONNECT schemaowner/schemaowner@service; CREATE CONTEXT SCHEMAOWNER USING SCHEMAOWNER.context_package; CREATE OR REPLACE PACKAGE context_package AS PROCEDURE set_context; END; /
Next we create the context_package body which will actually set the user context.
CREATE OR REPLACE PACKAGE BODY context_package IS PROCEDURE set_context IS v_ouser VARCHAR2(30); v_id NUMBER; BEGIN DBMS_SESSION.set_context('SCHEMAOWNER','SETUP','TRUE'); v_ouser := SYS_CONTEXT('USERENV','SESSION_USER'); BEGIN SELECT id INTO v_id FROM users WHERE ouser = v_ouser; DBMS_SESSION.set_context('SCHEMAOWNER','USER_ID', v_id); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_SESSION.set_context('SCHEMAOWNER','USER_ID', 0); END; DBMS_SESSION.set_context('SCHEMAOWNER','SETUP','FALSE'); END set_context; END context_package; / SHOW ERRORS
Next we make sure that all users have access to the Context_Package.
GRANT EXECUTE ON SCHEMAOWNER.context_package TO PUBLIC; CREATE PUBLIC SYNONYM context_package FOR SCHEMAOWNER.context_package;
Next we must create a trigger to fire after the user logs onto the database.
CONNECT sys/password@service AS SYSDBA; CREATE OR REPLACE TRIGGER SCHEMAOWNER.set_security_context AFTER LOGON ON DATABASE BEGIN SCHEMAOWNER.context_package.set_context; END; / SHOW ERRORS
In order for the context package to have any effect on the users interaction with the database, we need to define a security_package for use with the security policy. This package will tell the database how to treat any interactions with the specified table.
CONNECT schemaowner/schemaowner@service; CREATE OR REPLACE PACKAGE security_package AS FUNCTION user_data_insert_security(owner VARCHAR2, objname VARCHAR2) RETURN VARCHAR2; FUNCTION user_data_select_security(owner VARCHAR2, objname VARCHAR2) RETURN VARCHAR2; END security_package; /
Next we create the security_package body.
CREATE OR REPLACE PACKAGE BODY Security_Package IS FUNCTION user_data_select_security(owner VARCHAR2, objname VARCHAR2) RETURN VARCHAR2 IS predicate VARCHAR2(2000); BEGIN predicate := '1=2'; IF (SYS_CONTEXT('USERENV','SESSION_USER') = 'SCHEMAOWNER') THEN predicate := NULL; ELSE predicate := 'USER_ID = SYS_CONTEXT(''SCHEMAOWNER'',''USER_ID'')'; END IF; RETURN predicate; END user_data_select_security; FUNCTION user_data_insert_security(owner VARCHAR2, objname VARCHAR2) RETURN VARCHAR2 IS predicate VARCHAR2(2000); BEGIN predicate := '1=2'; IF (SYS_CONTEXT('USERENV','SESSION_USER') = 'SCHEMAOWNER') THEN predicate := NULL; ELSE predicate := 'USER_ID = SYS_CONTEXT(''SCHEMAOWNER'',''USER_ID'')'; END IF; RETURN Predicate; END user_data_insert_security; END security_package; / SHOW ERRORS
Next we make sure that all users have access to the Security_Package.
GRANT EXECUTE ON SCHEMAOWNER.security_package TO PUBLIC; CREATE PUBLIC SYNONYM security_package FOR SCHEMAOWNER.security_package;
The DBMS_RlS
package is used to apply the security policay, implemented by security_package, to the the relevant tables.
BEGIN DBMS_RLS.add_policy('SCHEMAOWNER', 'USER_DATA', 'USER_DATA_INSERT_POLICY', 'SCHEMAOWNER', 'SECURITY_PACKAGE.USER_DATA_INSERT_SECURITY', 'INSERT', TRUE); DBMS_RLS.add_policy('SCHEMAOWNER', 'USER_DATA', 'USER_DATA_SELECT_POLICY', 'SCHEMAOWNER', 'SECURITY_PACKAGE.USER_DATA_SELECT_SECURITY', 'SELECT'); END; /
Finally, test that the VPD is working correctly.
CONNECT user1/user1@service; INSERT INTO schemaowner.user_data (column1, user_id) VALUES ('User 1', 1); INSERT INTO schemaowner.user_data (column1, user_id) VALUES ('User 2', 2); COMMIT; CONNECT user2/user2@service INSERT INTO schemaowner.user_data (column1, user_id) VALUES ('User 1', 1); INSERT INTO schemaowner.user_data (column1, user_id) VALUES ('User 2', 2); COMMIT; CONNECT schemaowner/schemaowner@service SELECT * FROM schemaowner.user_data; CONNECT user1/user1@Service; SELECT * FROM schemaowner.user_data; CONNECT user2/user2@Service SELECT * FROM schemaowner.user_data;
Notice that:
ORA-28115: policy with check option violation
Once you're happy with the basic mechanism you can extend the Security_Package to cover all tables where restricted access is neccessary, remembering to apply all security policies to the relevant tables.
For more information see:
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/8i/virtual-private-databases