viewtables.sas

Go to the documentation of this file.

/**
  @file viewtables.sas
  @brief List the tables and format catalogs the user can view
  @details Provide a library and get list of tables and catalogs.  Also return
  the libinfo details.
 
  <h4> Service Inputs </h4>
  <h5> SASControlTable </h5>
  Just one input - MPLIB (the libref to get tables and info for)
  |MPLIB:$char8.|
  |---|
  |SOMELIB|
 
  <h4> Service Outputs </h4>
  <h5> work.mptables </h5>
 
  |MEMNAME:$char32.|
  |---|
  |DS1|
  |DS2|
  |DS3|
 
  etc
 
  <h5> work.libinfo </h5>
  If attributes are empty, they don't need to be shown on screen.
 
  |engine $|libname $|paths $|perms $|owners $|schemas $ |libid $|libsize $|table_cnt |
  |---|---|---|---|---|---|---|---|---|
  |V9|SOMELIB|"some/path"|rwxrwxr-x|sassrv|` `|` `|636MB|33|
 
 
 
  <h4> SAS Macros </h4>
  @li dc_assignlib.sas
  @li mf_getuser.sas
  @li mpe_getgroups.sas
  @li mpe_getvars.sas
  @li mpeinit.sas
 
  @version 9.2
  @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.
**/
 
%mpeinit()
 
%global MPLIB;
 
/* load parameters */
%mpe_getvars(SASControlTable, SASControlTable)
 
/**
  * assign the Library
  */
%put &=MPLIB;
%dc_assignlib(READ,&MPLIB)
 
%mp_abort(iftrue= (&syscc ne 0 )
  ,mac=&_program..sas
  ,msg=%str(Unable to assign &mplib library)
)
 
/**
  * get the tables
  */
data members; /* empty table */
  name='';
  memtype='';
run;
ods output Members=Members;
proc datasets library=&mplib ;
quit;
 
/* cannot avoid the proc datasets warn!ng for an empty lib */
/* nolist means no output and nowarn has no effect */
%put &=syscc;
data _null_;
  if "&syscc" ne "0" then do;
    putlog "Library &mplib is empty, setting syscc to zero";
    call symputx('syscc',0);
  end;
run;
%put &=syscc;
 
proc sql;
create table work.mptables as
  select distinct case when memtype='CATALOG' then cats(name,'-FC')
    else name end as memname
  from members;
 
/* get security groups */
%mpe_getgroups(user=%mf_getuser(),outds=groups)
 
/* get security settings */
data sec;
  set &mpelib..mpe_security;
  where &dc_dttmtfmt. lt tx_to and ACCESS_LEVEL='VIEW';
  where also libref in ('*ALL*',"%upcase(&mplib)");
run;
 
/* check for any matching groups */
proc sql noprint;
create table matches as
  select * from sec
  where upcase(sas_group) in (select upcase(groupname) from groups);
select count(*) into: securitygroupscount from matches;
select count(*) into: ALL_CNT from matches
  where libref='*ALL*'
    or (libref="&mplib" and dsn='*ALL*');
 
%mp_abort(iftrue= (&syscc ne 0)
  ,mac=&_program..sas
  ,msg=%str(syscc=&syscc)
)
 
%macro mpestp_viewtables();
%if not %symexist(DC_RESTRICT_VIEWER) %then %let DC_RESTRICT_VIEWER=NO;
 
/* scenario 1 - user is in admin group, hence can view all libraries */
proc sql noprint;
select count(*) into: scenario1 from groups where groupname="&mpeadmins";
%if &scenario1>0 %then %return;
 
/* scenario 2 - viewer unrestricted and no groups listed */
%if &DC_RESTRICT_VIEWER=NO and &securitygroupscount=0 %then %return;
 
/* scenario 3 - an *ALL* libref or DSN is listed */
%if &all_cnt>0 %then %return;
 
/* scenario 4 - specific tables listed */
%if &securitygroupscount>0 %then %do;
  proc sql;
  delete from mptables
    where upcase(memname) not in (select upcase(dsn) from sec);
  %return;
%end;
 
/* viewer restricted and no groups listed */
%if &DC_RESTRICT_VIEWER=YES and &securitygroupscount=0 %then %do;
  data mptables;
    set mptables;
    stop;
  run;
  %return;
%end;
 
%mp_abort(iftrue= (1=1)
  ,mac=&_program..sas
  ,msg=%str(unhandled security logic error!)
)
 
%mend mpestp_viewtables;
%mpestp_viewtables()
 
 
/* get libinfo */
proc sql;
create table work.libinfo as
  select a.engine,
    a.libname,
    a.paths,
    a.perms,
    a.owners,
    a.schemas,
    a.libid,
    coalesce(b.libsize,0) as libsize,
    coalesce(b.table_cnt,0) as table_cnt,
    coalesce(b.catalog_cnt,0) as catalog_cnt
  from &mpelib..mpe_datacatalog_libs(where=(&dc_dttmtfmt. lt tx_to)) a
  left join &mpelib..mpe_datastatus_libs(where=(&dc_dttmtfmt. lt tx_to)) b
  on a.libref=b.libref
  where a.libref="&MPLIB";
 
 
%webout(OPEN)
%webout(OBJ,mptables)
%webout(OBJ,libinfo)
%webout(CLOSE)
 
 
%mpeterm()