-- Setup User SCOTT,BLAKE,SECUSR

clear screen
set echo on

CONNECT system/manager@PAR1;
DROP USER secusr CASCADE;
CREATE USER secusr IDENTIFIED by secusr
  DEFAULT TABLESPACE tab
  TEMPORARY TABLESPACE temp;
GRANT connect,resource,execute_catalog_role TO secusr;
GRANT drop any context TO secusr;
GRANT create any context TO secusr;
pause
DROP USER scott CASCADE;
CREATE USER scott IDENTIFIED BY tiger
  DEFAULT TABLESPACE tab
  TEMPORARY TABLESPACE temp;
GRANT connect,resource TO scott;
GRANT all privileges to scott;
pause
DROP USER blake CASCADE;
CREATE USER blake IDENTIFIED BY lion
  DEFAULT TABLESPACE tab
  TEMPORARY TABLESPACE temp;
GRANT connect TO blake;
DISCONNECT;
pause

-- Create DEMO tables

CONNECT scott/tiger@PAR1;
@demobld;
GRANT all ON emp TO PUBLIC;
DISCONNECT;
pause

-- Create an application context

CONNECT secusr/secusr@PAR1;
DROP CONTEXT order_entry_context;
CREATE CONTEXT order_entry_context USING order_entry;

-- Create PL/SQL package which sets the context for the application

CREATE OR REPLACE PACKAGE order_entry AS
  PROCEDURE set_emp;
END order_entry;
/
pause
CREATE OR REPLACE PACKAGE BODY order_entry AS
PROCEDURE set_emp IS
  myjob  scott.emp.job%type;
  mydept scott.emp.deptno%type;
 BEGIN
  SELECT job,deptno
    INTO myjob,mydept
    FROM scott.emp
    WHERE ename = sys_context('userenv','session_user');
  dbms_session.set_context('order_entry_context','myjob',myjob);
  dbms_session.set_context('order_entry_context','mydept',mydept);
 EXCEPTION
   WHEN no_data_found THEN NULL;
 END set_emp;
END order_entry;
/
GRANT EXECUTE ON order_entry TO PUBLIC;
pause

-- Test application context

CONNECT scott/tiger@PAR1
SET SERVEROUTPUT ON
EXEC secusr.order_entry.set_emp;
EXEC dbms_output.put_line(sys_context('order_entry_context','myjob'));
EXEC dbms_output.put_line(sys_context('order_entry_context','mydept'));
CONNECT blake/lion@PAR1
SET SERVEROUTPUT ON
EXEC secusr.order_entry.set_emp;
EXEC dbms_output.put_line(sys_context('order_entry_context','myjob'));
EXEC dbms_output.put_line(sys_context('order_entry_context','mydept'));
pause

-- Access the application context inside the package that
-- implements the security policy on the database object
-- Returns e.g. 'CLARK' = ename OR ('MANAGER' = 'MANAGER' AND 10 = deptno)

CONNECT secusr/secusr@PAR1
CREATE OR REPLACE FUNCTION emp_restrict(schema IN varchar2, tab IN varchar2)
 RETURN VARCHAR2
 AS
  BEGIN
   RETURN '''' || sys_context('userenv','session_user') || '''=ename' || ' OR ' ||
         '(''' || sys_context('order_entry_context','myjob')
               || '''=''MANAGER'' AND '
               || nvl(sys_context('order_entry_context','mydept'),'NULL')
               || '=deptno)';
END emp_restrict;
/

-- Create the new security policy

EXEC dbms_rls.add_policy('scott','emp','emp_policy','secusr','emp_restrict');
pause

-- Test the application

CONNECT scott/tiger@PAR1
EXEC secusr.order_entry.set_emp;
SELECT * from scott.emp;
pause
CONNECT blake/lion@PAR1
EXEC secusr.order_entry.set_emp;
SELECT * from scott.emp;
pause
connect system/manager@PAR1
pause
SELECT * from scott.emp;
connect  secusr/secusr@PAR1

-- Cleanup

EXEC dbms_rls.drop_policy('scott','emp','emp_policy');