Loading...
Searching...
No Matches
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
27proc sql;
28delete from &dc_libref..MPE_GROUPS where user_name="%mf_getuser()";
29
30%let tempgroup=%mf_getuniquename();
31insert 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 */
41delete from &dc_libref..MPE_ROW_LEVEL_SECURITY
42 where RLS_TABLE="MPE_X_TEST";
43select max(rls_rk) into: max_rk from &dc_libref..MPE_ROW_LEVEL_SECURITY;
44
45insert 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
76data _null_;
77 infile qref;
78 input;
79 if _n_=1 then put 'Test 1: filter query';
80 put _infile_;
81run;
82
83data work.test;
84 set &dc_libref..MPE_X_TEST;
85 where %inc qref;;
86run;
87proc sort data=work.test out=work.logme nodupkey;
88by some_dropdown;
89run;
90%put checking values;
91data _null_;
92 set work.logme;
93 put some_dropdown=;
94run;
95
96data work.check;
97 val='Option 1';output;
98 val='Option 2';output;
99run;
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 */
109proc sql;
110insert 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;