Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL Scripts & Samples

Posted Apr 23, 2004

Setup Row Level Security or Virtual Private Database

By DatabaseJournal.com Staff



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



SQL Scripts & Samples Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL Scripts & Samples Forum
Topic By Replies Updated
sql help!! add 1 August 22nd, 11:58 AM
How To Combine These 2 Queries into 1 Query ? tarek_land 1 June 7th, 08:37 AM
solving query svibuk 1 February 3rd, 06:08 AM
converting from a character string to uniqueidentifier saturnius 4 January 4th, 05:56 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date