/*****************************************************************************
 * File: vpdtest.sql
 * Type: SQL*Plus script
 * Author: Tim Gorman (Evergreen Database Technologies, Inc)
 * Date: 19may00
 *
 * Description:
 *
 *	An extremely simple example of the "virtual private databases" (VPD)
 *	feature, which is intended to use the standard demo SCOTT/TIGER
 *	schema (with the EMP table) as an example.
 ****************************************************************************/
whenever oserror exit failure
whenever sqlerror exit failure
set echo on feedback on timing on verify on
spool vpdtest

show user
show release

set termout off
/*
 * Package "header", which defines the public specification for any packaged
 * functions or procedures which are available to be called by users...
 */
create or replace package vpdtest
as
	procedure setlabel;
	function getlabel(owner in varchar2, objname in varchar2)
		return varchar2;
end vpdtest;
/
set termout on
show errors

set termout off
/*
 * Package "body", where the actual code (and any internal procedures)
 * are implemented...
 */
create or replace package body vpdtest
as
	--
	procedure setlabel
	is
	begin
		dbms_session.set_context('MYTEST', 'MYLABEL', 'X');
	end setlabel;
	--
	function getlabel(owner in varchar2, objname in varchar2)
		return varchar2
	is
	begin
		--
		insert into x values ('did it');
		commit;
		if sys_context('MYTEST','MYLABEL') is null then
			return '1 = 2';		/* deny access */
		else
			return '';
		end if;
		--
	end getlabel;
	--
end vpdtest;
/
set termout on
show errors

whenever sqlerror continue
drop context MYTEST;
whenever sqlerror exit failure

create context MYTEST using VPDTEST;
show errors

exec dbms_rls.drop_policy(null,'EMP','MYTEST');
exec dbms_rls.add_policy(null,'EMP','MYTEST',null,'VPDTEST.GETLABEL','SELECT',TRUE,TRUE)

spool off