Setup Row Level Security or Virtual Private Database

April 23, 2004

>>Script Language and Platform: Oracle 8i/9i Enterprise Edition
This functionality to support fine-grained access control is based on dynamic predicates, where security rules are not embedded in views, but are acquired at the statement parse time, when the base table or view is referenced in a DML statement.

A dynamic predicate for a table or view is generated by a PL/SQL function, which is associated with a security policy through a PL/SQL interface.

Whenever Target_Table is referenced in a query or subquery (SELECT), the server calls the GetLabel function. This returns a predicate specific to the current user for the SECURITY_POLICY policy.

Author: Shahid Hafeez

-- TO SET UP ROW LEVEL SECURITY IN ORACLE 8I/9I ENTERPRISE EIDTION(S)
-- VIRTUAL PRIVATE DATABASE (VPD) OR ROW LEVEL SECURITY
-- Schema_Owner is owner user of your application schema
CONNECT SYS/S<<YSPASSWORD>>

GRANT EXECUTE ON dbms_rls TO SCHEMA_OWNER

GRANT EXECUTE ON dbms_session TO SCHEMA_OWNER 

CONNECT SCHEMA_OWNER/SCHEMA_OWNER

CREATE OR REPLACE PACKAGE Vpd
AS
	FUNCTION getlabel(owner IN VARCHAR2, objname IN VARCHAR2)
		RETURN VARCHAR2;
END Vpd;
/

CREATE OR REPLACE PACKAGE BODY Vpd
AS
	--
	FUNCTION getlabel(owner IN VARCHAR2, objname IN VARCHAR2)
		RETURN VARCHAR2
	IS
	BEGIN
		--
		IF sys_context('USERENV','SESSION_USER') IS NULL THEN
			RETURN '1 = 2';		/* deny access */
		END IF;
		IF sys_context('USERENV','SESSION_USER') = 'SCHEMA_OWNER' THEN
			RETURN ''; -- NO PREDICATE OR FILTER ENFORCED FOR OWNER USER
		END IF;
		IF sys_context('USERENV','SESSION_USER') <> 'SCHEMA_USER' THEN
			RETURN 'COMPANY_ID = 2'; 
                  -- RECORDS WILL BE FILTERED FOR SCHEMA_USER TO ACCESS ONLY COMPANY 2 REOCRDS
                  -- COMPANY ID SHOULD BE A COLUMN IN TARGET TABLE FOR WHICH YOU WANT TO ENFORCE ROW LEVEL SECURITY
		END IF;
		--
	END getlabel;
	--
END Vpd;
/

-- TO ADD/ENFORCE POLICY
BEGIN
DBMS_RLS.ADD_POLICY (
   NULL, 'TARGET_TABLE', 'POLICY_NAME', NULL, 'VPD.GETLabel', 'SELECT',TRUE,TRUE);
END;

-- TO DROP VPD POLICY
BEGIN
DBMS_RLS.DROP_POLICY('SCHEMA_OWNER', 'TARGET_TABLE', 'POLICY_NAME');
END; 
BEGIN
DBMS_RLS.ADD_POLICY (
   NULL, 'TARGET_TABLE', 'POLICY_NAME', NULL, 'VPD.GETLabel', 'SELECT',TRUE,TRUE);
END;

-- TO DROP VPD POLICY
BEGIN
DBMS_RLS.DROP_POLICY('SCHEMA_OWNER', 'TARGET_TABLE', 'POLICY_NAME');
END; 

Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, or fitness for a particular purpose... Disclaimer Continued

Back to Database Journal Home