mpe_filtermaster.test.1.sas
Go to the documentation of this file.
1 /**
2  @file
3  @brief setup mpe_filtermaster RLS test
4  @brief testing row level security
5 
6 
7  <h4> SAS Macros </h4>
8  @li mf_getuniquename.sas
9  @li mf_getuser.sas
10  @li mp_assertcolvals.sas
11  @li mpe_filtermaster.sas
12 
13  @author 4GL Apps Ltd
14  @copyright 4GL Apps Ltd. This code may only be used within Data Controller
15  and may not be re-distributed or re-sold without the express permission of
16  4GL Apps Ltd.
17 
18 **/
19 
20 
21 /**
22  During tests, a DC group is made admin. By removing this account from the
23  MPE_GROUPS table and replacing with a SAS system group (that this account is
24  a member of) we can become a "regular" member.
25  */
26 
27 proc sql;
28 delete from &dc_libref..MPE_GROUPS where user_name="%mf_getuser()";
29 
30 %let tempgroup=%mf_getuniquename();
31 insert into &dc_libref..MPE_GROUPS
32  set user_name="%mf_getuser()"
33  ,group_name="&tempgroup"
34  ,group_desc="temp group"
35  ,tx_from='01Jan1960:00:00:00'dt
36  ,tx_to='31Dec9999:23:59:59'dt;
37 
38 /**
39  Prep table
40  */
41 delete from &dc_libref..MPE_ROW_LEVEL_SECURITY
42  where RLS_TABLE="MPE_X_TEST";
43 select max(rls_rk) into: max_rk from &dc_libref..MPE_ROW_LEVEL_SECURITY;
44 
45 insert into &dc_libref..mpe_row_level_security set
46  tx_from=0
47  ,tx_to='31DEC5999:23:59:59'dt
48  ,RLS_RK=&max_rk+1
49  ,RLS_SCOPE='ALL'
50  ,RLS_GROUP="&tempgroup"
51  ,RLS_LIBREF="&dc_libref"
52  ,RLS_TABLE="MPE_X_TEST"
53  ,RLS_GROUP_LOGIC='AND'
54  ,RLS_SUBGROUP_LOGIC='OR'
55  ,RLS_SUBGROUP_ID=0
56  ,RLS_VARIABLE_NM='SOME_DROPDOWN'
57  ,RLS_OPERATOR_NM='IN'
58  ,RLS_RAW_VALUE="('Option 1','Option 2')"
59  ,RLS_ACTIVE=1;
60 
61 /*
62  Test 1 - Expect 5 records:
63 
64  ((
65  LIBREF CONTAINS 'DC'
66  ) AND (
67  DSN = 'MPE_LOCK_ANYTABLE'
68  ))
69 
70 */
71 %mpe_filtermaster(VIEW,&dc_libref..MPE_X_TEST,
72  dclib=&dc_libref,
73  outref=qref
74 )
75 
76 data _null_;
77  infile qref;
78  input;
79  if _n_=1 then put 'Test 1: filter query';
80  put _infile_;
81 run;
82 
83 data work.test;
84  set &dc_libref..MPE_X_TEST;
85  where %inc qref;;
86 run;
87 proc sort data=work.test out=work.logme nodupkey;
88 by some_dropdown;
89 run;
90 %put checking values;
91 data _null_;
92  set work.logme;
93  put some_dropdown=;
94 run;
95 
96 data work.check;
97  val='Option 1';output;
98  val='Option 2';output;
99 run;
100 %mp_assertcolvals(work.test.SOME_DROPDOWN,
101  checkvals=work.check.val,
102  desc=Testing for RLS filtered rows in MPE_X_TEST,
103  test=ALLVALS
104 )
105 
106 /**
107  * put record back
108  */
109 proc sql;
110 insert into &dc_libref..mpe_groups set
111  tx_from=&dc_dttmtfmt.
112  ,group_name="DC Demo Group"
113  ,group_desc="Custom Group for Data Controller Purposes"
114  ,user_name="%mf_getuser()"
115  ,tx_to='31DEC5999:23:59:59'dt;