Show and Tell

utPLSQL is an Open Source PL/SQL Unit Testing Framework for the Oracle RDBMS.

Real Application Security (RAS) is a 12c+ EE feature that's similar to “VPD on steroids”. It also allows for ease of management for stateless sessions (eg APEX page calls).

I've modified a fork of utPLSQL to support RAS Sessions (branch feature/RAS-support) so that testing of RAS Policy can be done easily and automated.

If anyone is interested, let me know.

REM Executor of test package needs XS_SESSION_ADMIN role

create or replace
package ut_ras_hr_demo
authid current_user
as
	/*
	* Runs example tests from RAS HR Demo in utPLSQL
	*
	* This assumes the Policy from the RAS HR Demo is enabled
	* (which requires the HR example schema installed)
	*/
	--%suite( RAS HR Demo )

	function count_employees return int;

	--%test( Count as DAUSTIN )
	--%xsuser( daustin )
	procedure count_daustin

	--%test( Count as SMAVRIS )
	--%xsuser( smavris )
	procedure count_smavris;

	--%test( Count as owner )
	procedure count_nobody;
end;
/

create or replace
package body as ut_ras_hr_demo
as
  function count_employees return int
  as
    ret_val int;
  begin
    select count(*) into ret_val
    from hr.employees;

    return ret_val;
  end;

  procedure count_daustin
  as
  begin
    ut.expect( count_employees ).to_equal( 5 );
  end;

  procedure count_smavris
  as
  begin
    ut.expect( count_employees ).to_equal( 107 );
  end;

  procedure count_nobody
  as
  begin
    -- this will fail if DB is not patched for CVE-2023-21829
    ut.expect( count_employees ).to_equal( 0 );
  end;
end;
/

REM allows DAUSTIN, SMAVRIS to execute the procedures
grant execute on ut_ras_hr_demo to db_emp;

select * from dba_xs_sessions should return 0 rows after. (run this as DBA or ADMIN)