stagedata.test.3.sas

Go to the documentation of this file.

/**
  @file
  @brief testing stagedata RLS / REPLACE loadtype incompatibility (#211)
  @details Backend validations should abort when:
    1. loading row level EDIT security against a table with LOADTYPE=REPLACE
    2. applying LOADTYPE=REPLACE to a table with row level EDIT security
 
  The staged loads are aborted by the post edit hooks inside the child
  service.  The abort is recorded as a FAILED load in MPE_LOADS (via
  mpe_loadfail), so the assertions query MPE_LOADS rather than the child
  response - a canceled child does not always return a webout or log.
 
  <h4> SAS Macros </h4>
  @li mx_execute.sas
  @li mp_assert.sas
 
 
**/
 
%let _program=&appLoc/services/editors/stagedata;
 
/**
  * Prep - register a DCTEST table with REPLACE loadtype (direct insert,
  * bypassing stagedata) and create the physical table
  */
data dctest.dc_replace;
  x=1;
run;
proc sql noprint;
delete from &dc_libref..mpe_tables where libref="DCTEST" and dsn='DC_REPLACE';
insert into &dc_libref..mpe_tables
  set tx_from=0
    ,tx_to='31DEC5999:23:59:59'dt
    ,libref="DCTEST"
    ,dsn='DC_REPLACE'
    ,buskey='X'
    ,loadtype='REPLACE'
    ,num_of_approvals_required=1;
/* ensure the RLS table's post edit hook is registered (matches the
  * mpe_makedata registration - some estates were initialised before the
  * hook existed, which would silently skip the test 1 validation) */
update &dc_libref..mpe_tables
  set post_edit_hook='services/hooks/mpe_row_level_security_postedit'
  where upcase(dsn)='MPE_ROW_LEVEL_SECURITY'
    and post_edit_hook='';
/* insert an active EDIT rule for DCTEST.WIDEBOY up front - the child
  * services run in separate (possibly pooled) compute sessions, so the
  * rule must be committed to the shared filesystem well before the test
  * 2 child reads it */
delete from &dc_libref..mpe_row_level_security
  where rls_libref="DCTEST" and rls_table='WIDEBOY';
select coalesce(max(rls_rk),0)+1 into: rlsrk
  from &dc_libref..mpe_row_level_security;
insert into &dc_libref..mpe_row_level_security
  set tx_from=0
    ,tx_to='31DEC5999:23:59:59'dt
    ,rls_rk=&rlsrk
    ,rls_scope='EDIT'
    ,rls_group='SASAdministrators'
    ,rls_libref='DCTEST'
    ,rls_table='WIDEBOY'
    ,rls_group_logic='AND'
    ,rls_subgroup_logic='OR'
    ,rls_subgroup_id=0
    ,rls_variable_nm='ROW_ID'
    ,rls_operator_nm='NE'
    ,rls_raw_value='1'
    ,rls_active=1;
quit;
 
/**
  * Test 1 - submitting an RLS EDIT rule against a REPLACE table should abort
  */
%let t1=%sysfunc(datetime());
data work.sascontroltable;
  action='LOAD';
  message='RLS EDIT rule on REPLACE table should abort';
  libds="&dc_libref..MPE_ROW_LEVEL_SECURITY";
  output;
  stop;
run;
 
data work.jsdata;
  length RLS_SCOPE $8 RLS_GROUP $128 RLS_LIBREF $8 RLS_TABLE $32
    RLS_GROUP_LOGIC $3 RLS_SUBGROUP_LOGIC $3 RLS_VARIABLE_NM $32
    RLS_OPERATOR_NM $12 RLS_RAW_VALUE $4000
    _____DELETE__THIS__RECORD_____ $3;
  RLS_SCOPE='EDIT';
  RLS_GROUP='SASAdministrators';
  RLS_LIBREF='DCTEST';
  RLS_TABLE='DC_REPLACE';
  RLS_GROUP_LOGIC='AND';
  RLS_SUBGROUP_LOGIC='OR';
  RLS_SUBGROUP_ID=0;
  RLS_VARIABLE_NM='X';
  RLS_OPERATOR_NM='NE';
  RLS_RAW_VALUE='1';
  RLS_ACTIVE=1;
  _____DELETE__THIS__RECORD_____='No';
  output;
run;
 
%mx_execute(&_program,
  viyacontext=&defaultcontext,
  inputdatasets=work.sascontroltable work.jsdata,
  outlib=web1,
  outref=wb1,
  mdebug=&sasjs_mdebug
)
 
/* a hook-aborted load is terminated before mpe_loadfail can record a
  * FAILED status, so the load row stays IN PROGRESS - the abort is
  * proved by the load NOT completing successfully.  Tests in this suite run
  * in parallel sessions, so match the row for THIS test by its unique
  * reason_txt (the submitted message) rather than any load in the time
  * window - an unrelated aborted/IN PROGRESS load would otherwise satisfy
  * the assertion even if this load completed.  The row id (csv_dir/mperef)
  * is generated inside the aborted child service, which returns no webout,
  * so reason_txt is the only stable identifier the test controls. */
%let abort1=0;
proc sql noprint;
select count(*) into: abort1
  from &dc_libref..mpe_loads
  where processed_dttm>&t1
    and reason_txt="RLS EDIT rule on REPLACE table should abort"
    and upcase(status) ne 'SUCCESS';
quit;
 
%mp_assert(
  iftrue=(&abort1>0),
  desc=Checking RLS EDIT rule is rejected for REPLACE loadtype table (#211),
  outds=work.test_results
)
 
/**
  * Test 2 - applying REPLACE loadtype to a table with RLS EDIT security
  * should abort.  The active EDIT rule was inserted in the prep section,
  * long before this child service reads it.
  */
%let t2=%sysfunc(datetime());
 
/* now stage a REPLACE loadtype for DCTEST.WIDEBOY */
data work.sascontroltable;
  action='LOAD';
  message='REPLACE loadtype on RLS-secured table should abort';
  libds="&dc_libref..MPE_TABLES";
  output;
  stop;
run;
 
data work.jsdata;
  set &dc_libref..mpe_tables(where=(libref='DCTEST' and dsn='WIDEBOY'));
  loadtype='REPLACE';
  length _____DELETE__THIS__RECORD_____ $3;
  _____DELETE__THIS__RECORD_____='No';
run;
 
%mx_execute(&_program,
  viyacontext=&defaultcontext,
  inputdatasets=work.sascontroltable work.jsdata,
  outlib=web2,
  outref=wb2,
  mdebug=&sasjs_mdebug
)
 
%let abort2=0;
proc sql noprint;
select count(*) into: abort2
  from &dc_libref..mpe_loads
  where processed_dttm>&t2
    and reason_txt="REPLACE loadtype on RLS-secured table should abort"
    and upcase(status) ne 'SUCCESS';
quit;
 
%mp_assert(
  iftrue=(&abort2>0),
  desc=Checking REPLACE loadtype is rejected for RLS EDIT-secured table (#211),
  outds=work.test_results
)
 
/**
  * Test 3 - sanity check: REPLACE loadtype on an unsecured table is fine
  */
data work.jsdata;
  set &dc_libref..mpe_tables(where=(libref='DCTEST' and dsn='WIDEBOY'));
  loadtype='UPDATE';
  length _____DELETE__THIS__RECORD_____ $3;
  _____DELETE__THIS__RECORD_____='No';
run;
 
/* remove the RLS rule first (cleanup of test 2) */
proc sql noprint;
delete from &dc_libref..mpe_row_level_security
  where rls_libref="DCTEST" and rls_table='WIDEBOY';
quit;
 
%mx_execute(&_program,
  viyacontext=&defaultcontext,
  inputdatasets=work.sascontroltable work.jsdata,
  outlib=web3,
  mdebug=&sasjs_mdebug
)
 
%let status3=0;
data _null_;
  set web3.sasparams;
  putlog (_all_)(=);
  if status='SUCCESS' then call symputx('status3',1);
run;
 
%mp_assert(
  iftrue=(&status3=1),
  desc=Checking non-REPLACE loadtype still stages successfully after cleanup,
  outds=work.test_results
)
 
/* final cleanup - remove the REPLACE table registration */
proc sql noprint;
delete from &dc_libref..mpe_tables where libref="DCTEST" and dsn='DC_REPLACE';
quit;