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
 
  <h4> SAS Macros </h4>
  @li mx_testservice.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;
quit;
 
/**
  * Test 1 - submitting an RLS EDIT rule against a REPLACE table should abort
  */
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_testservice(&_program,
  viyacontext=&defaultcontext,
  inputdatasets=work.sascontroltable work.jsdata,
  outlib=web1,
  outref=wb1,
  mdebug=&sasjs_mdebug
)
 
/* search terms may span multiple lines, so track matches across records */
%let abort1=0;
data _null_;
  retain foundabort foundmsg 0;
  infile wb1;
  input;
  putlog _infile_;
  if index(_infile_,'sasjsAbort') then foundabort=1;
  if index(_infile_,'REPLACE loadtype') then foundmsg=1;
  if foundabort=1 and foundmsg=1 then call symputx('abort1',1);
run;
 
%mp_assert(
  iftrue=(&abort1=1),
  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.  First insert an active EDIT rule directly.
  */
proc sql noprint;
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;
 
/* 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_testservice(&_program,
  viyacontext=&defaultcontext,
  inputdatasets=work.sascontroltable work.jsdata,
  outlib=web2,
  outref=wb2,
  mdebug=&sasjs_mdebug
)
 
%let abort2=0;
data _null_;
  retain foundabort foundmsg 0;
  infile wb2;
  input;
  putlog _infile_;
  if index(_infile_,'sasjsAbort') then foundabort=1;
  if index(_infile_,'EDIT security') then foundmsg=1;
  if foundabort=1 and foundmsg=1 then call symputx('abort2',1);
run;
 
%mp_assert(
  iftrue=(&abort2=1),
  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_testservice(&_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;