mpe_columnlevelsecurity.sas

Go to the documentation of this file.

/**
  @file
  @brief Filters a table with CLS rules
  @details Implements CLS as per the rules described here:
 
  https://docs.datacontroller.io/column-level-security/
 
  Usage:
 
      %mpe_columnlevelsecurity(TGTLIB,TGTDS,work.inds
        ,mode=VIEW
        ,clsds=dc.mpe_cls
        ,groupds=work.groups
        ,outds=work.final
        ,outmeta=work.cls_rules
      )
 
 
  @param [in] tgtlib The libref of the target table
  @param [in] tgtds The dataset reference of the target table
  @param [in] inds A two-level (lib.ds) reference to the WORK table to which to
    apply the column filter rules
  @param [in] mode= (VIEW) Either VIEW or EDIT to indicate whether the
    resulting table is intended to be viewable or editable.
  @param [in] groupds= (work.groups)A two-level (lib.ds) reference to a dataset
    containing the list of groups of which the current user is a member.  The
    column containing the group name should be called `groupname`.
  @param [in] clsds= (work.clsds) A two-level (lib.ds) reference to the
    configuration table containing the CLS rules to apply.
  @param [out] outds= (WORK.CLSVIEW) A two-level (lib.ds) reference to the WORK
    dataset to create
  @param [out] outmeta= (WORK.CLS_RULES)The rule metadata, with the following
    structure:
  |CLS_VARIABLE_NM:$32.|CLS_HIDE:best.|
  |---|---|---|---|---|
  |`SOME_VARIABLE`|`0 `|
  |`VAR3`|`1 `|
 
 
  <h4> SAS Macros </h4>
 
  @author 4GL Apps Ltd
  @copyright 4GL Apps Ltd.  This code may only be used within Data Controller
    and may not be re-distributed or re-sold without the express permission of
    4GL Apps Ltd.
**/
 
%macro mpe_columnlevelsecurity(tgtlib,tgtds,inds
  ,mode=VIEW
  ,groupds=work.groups
  ,clsds=work.clsview
  ,outds=CLSVIEW
  ,outmeta=work.cls_rules
);
 
%local col_list is_admin;
/* filter for the appropriate rules */
proc sql;
create table &outmeta as
  select CLS_VARIABLE_NM,
    min(case when CLS_HIDE=1 then 1 else 0 end) as CLS_HIDE
  from &clsds
  where &dc_dttmtfmt. lt tx_to
    and CLS_SCOPE in ("&mode",'ALL')
    and CLS_ACTIVE=1
  %if &mode=VIEW %then %do;
    and CLS_HIDE ne 1
  %end;
    and upcase(CLS_GROUP) in (select upcase(groupname) from &groupds)
    and CLS_LIBREF="%upcase(&tgtlib)"
    and CLS_TABLE="%upcase(&tgtds)"
  group by CLS_VARIABLE_NM;
 
%let is_admin=0;
proc sql;
select count(*) into: is_admin from &groupds where groupname="&MPEADMINS";
%put &sysmacroname: &=is_admin;
%if %mf_nobs(work.cls_rules) = 0 or &is_admin>0 %then %do;
  %put &sysmacroname: no CLS rules to apply;
  %put &=is_admin;
  /* copy using append for speed */
  data &outds;
    set &inds;
    stop;
  run;
  proc append base=&outds data=&inds;
  run;
  /* ensure CLS_RULES is empty in case of admin */
  data &outmeta;
    set &outmeta;
    stop;
  run;
  %return;
%end;
%else %if &mode=VIEW %then %do;
  /* just send back the relevant columns */
  %let col_list=0;
  proc sql noprint;
  select CLS_VARIABLE_NM into: col_list separated by ' ' from &outmeta
    where CLS_HIDE=0;
 
  %if &col_list=0 %then %do;
    /*
      We have columns that are set to CLS_HIDE=1 but we do not have any to
      explicitly show.  Therefore we assume all columns are to be shown except
      those that are explicitly hidden.
    */
    proc sql noprint;
    select CLS_VARIABLE_NM into: col_list separated by ' ' from &outmeta
      where CLS_HIDE=1;
 
    data &outds;
      set &inds;
      drop &col_list;
    run;
  %end;
  %else %do;
    data &outds;
      set &inds;
      keep &col_list;
    run;
  %end;
%end;
%else %if &mode=EDIT %then %do;
  /*
    In this case we pass all columns and the frontend will filter out the
    ones that are not allowed to be edited.
  */
  data &outds;
    set &inds;
    stop;
  run;
  proc append base=&outds data=&inds;
  run;
%end;
%else %do;
  %put &sysmacroname: invalid mode - &mode!;
  %abort;
%end;
 
%mend mpe_columnlevelsecurity;