1 PACKAGE BODY edr_policy_function_pkg AS
2 /* $Header: EDRSECVB.pls 120.0.12000000.1 2007/01/18 05:55:19 appldev ship $ */
3
4 /* this function is used to control the update of the eRecords.
5 the security rule says that no update is allowed from any
6 interface other than Oracle applications.
7 */
8 FUNCTION psig_modify (owner VARCHAR2, objname VARCHAR2)
9 RETURN VARCHAR2 AS
10 l_predicate VARCHAR2(2000);
11 BEGIN
12 if (sys_context('userenv','client_info') is null) then
13 l_predicate := '1=2';
14 end if;
15 -- Bug 3411859 : Start
16 -- Verfication for the secure context is also required for the Update.
17 if (sys_context('edr_secure_ctx','secure') = 'Y') then
18 l_predicate := '1=1';
19 end if;
20 -- Bug 3411859 : End
21 return l_predicate;
22 END psig_modify;
23
24 /* this function is used to control the deletion of the eRecords.
25 the security rule says that no deletion is allowed from any
26 interface other than Oracle applications.
27 */
28
29 FUNCTION psig_delete (owner VARCHAR2, objname VARCHAR2)
30 RETURN VARCHAR2 AS
31 BEGIN
32 return '1=2';
33 END psig_delete;
34
35 /* this function is used to control the viewing of the eRecords.
36 the view access is controlled by the security rules in the
37 edr_psig_security table
38 */
39 FUNCTION psig_view (owner VARCHAR2, objname VARCHAR2)
40 RETURN VARCHAR2 AS
41 l_user varchar2(15);
42 l_resp varchar2(15);
43 l_high_security varchar2(1);
44
45 l_old_section varchar2(30);
46 l_old_event varchar2(240);
47
48 l_current_event varchar2(240);
49 l_current_section varchar2(30);
50 l_value varchar2(500);
51 l_rule_found boolean :=false;
52
53 l_temp_predicate varchar2(600);
54 l_wild_predicate varchar2(600);
55 l_predicate varchar2(3000);
56
57 --for low (default) security level- everyone is granted access by default
58 cursor c1 is
59 select a.event_name, b.index_section_name, upper(a.secure_value) secure_value
60 from edr_security_rules a, edr_secure_elements_v b
61 where ((a.user_id = l_user or a.responsibility_id = l_resp) and a.access_code = 'R')
62 and (a.start_date <= sysdate and (a.end_date >= sysdate or a.end_date is null))
63 and a.ELEMENT_ID = b.ELEMENT_ID
64 minus
65 select a.event_name, b.index_section_name, upper(a.secure_value) secure_value
66 from edr_security_rules a, edr_secure_elements_v b
67 where (a.user_id = l_user and a.access_code = 'G')
68 and (a.start_date <= sysdate and (a.end_date >= sysdate or a.end_date is null))
69 and a.ELEMENT_ID = b.ELEMENT_ID
70 order by event_name desc, index_section_name;
71
72 --for high securiy level- everyone has to be granted access
73 cursor c2 is
74 select a.event_name, b.index_section_name, upper(a.secure_value) secure_value
75 from edr_security_rules a, edr_secure_elements_v b
76 where ((a.user_id = l_user or a.responsibility_id = l_resp) and a.access_code = 'G')
77 and (a.start_date <= sysdate and (a.end_date >= sysdate or a.end_date is null))
78 and a.ELEMENT_ID = b.ELEMENT_ID
79 minus
80 select a.event_name, b.index_section_name, upper(a.secure_value) secure_value
81 from edr_security_rules a, edr_secure_elements_v b
82 where (a.user_id = l_user and a.access_code = 'R')
83 and (a.start_date <= sysdate and (a.end_date >= sysdate or a.end_date is null))
84 and a.ELEMENT_ID = b.ELEMENT_ID
85 order by event_name desc, index_section_name;
86
87 BEGIN
88 /* if the select is coming from a secure context in oracle apps, don't
89 get into rule deciphering at all.
90 */
91 if (sys_context('edr_secure_ctx','secure') = 'Y') then
92 l_predicate := '1=1';
93
94 /* if the select is coming from a direct source like SQL plus, simply
95 abort the attempt.
96 */
97 else
98
99 --if the query is coming in from direct source like sql plus abort
100 if (sys_context('userenv','client_info') is null) then
101 l_predicate := '1=2';
102
103 /* if the select is from generic query decipher the security rules */
104 else
105 l_user := fnd_global.user_id();
106
107 l_resp:=fnd_global.resp_id();
108
109 /* determine site's security level */
110 fnd_profile.get('EDR_SECURITY_HIGH',l_high_security);
111 l_old_section := 'x';
112 l_old_event:='x';
113
114 if (l_high_security <> 'Y') then
115 --this is the heart of the code for returning a predicate if the security
116 --level of the organization is low
117
118 --This loop would read each security rule for the user and create a predicate based
119 --on the security rules
120
121 for rule_rec in c1 loop
122
123 --set the boolean variable to indicate that at least one security rule found
124 l_rule_found :=true;
125
126 --obtain the value of event name and section name from cursor
127
128 l_current_event := rule_rec.event_name ;
129 l_current_event := nvl(l_current_event,'x');
130 l_current_section := rule_rec.index_section_name;
131
132 --if there are more than one security rules the control would enter this construct
133 --in that case creating the predicate is more complex
134
135 if (l_old_section <> 'x') then
136
137 --if the currently read section name is not the same as last read (section break)
138 --or the current event name is not same as last read (event break) then we have
139 --to add conditions to the predicate
140 --please note that the cursor is sorted on event name desc and section name
141
142 if (l_current_section <> l_old_section or l_current_event <> l_old_event) then
143
144 --this temp predicate is used to store the predicate created when breaks on section
145 --name occur in the cursor
146
147 l_temp_predicate := 'CONTAINS(PSIG_XML, '''||l_value ||' WITHIN '||l_old_section||''')+0=0 ';
148
149 --the value read for all the section names = the last section name is flushed
150 l_value := null;
151
152 --if a section break occurs but the event is null then add the temp predicate to the
153 --final predicate
154
155 if(l_old_event ='x') then
156
157 --if the final predicate is already populated then use AND
158
159 if (l_predicate is not null) then
160 l_predicate := l_predicate || 'AND ';
161 end if;
162 l_predicate := l_predicate || '(' || l_temp_predicate || ') ';
163
164 --flush out the temp predicate so that it can be used again
165 --this is doen whenever the temp predicate is used up to create the
166 --final predicate
167
168 l_temp_predicate :=null;
169
170 end if;
171
172 --if an event break has also occured then add the conditions using event name
173 --to the predicate
174
175 if(l_current_event <> 'x' AND l_old_event <> 'x' and l_current_event <> l_old_event) then
176
177 if (l_predicate is not null) then
178 l_predicate := l_predicate || 'AND ';
179 end if;
180 l_predicate := l_predicate || '((' || l_temp_predicate || 'AND event_name = ''' ||
181 l_old_event|| ''') OR event_name <> ''' || l_old_event||''')';
182
183 --flush out the temp predicate so that it can be used again
184 l_temp_predicate :=null;
185
186 end if;
187 end if;
188 end if;
189
190 --the secure value is OR'd till we get a section break or an event break
191
192 if (l_value is not null) then
193 l_value := l_value|| '|';
194 end if;
195 l_value := l_value || rule_rec.secure_value;
196
197 --the current section name and the event name are saved as old values before
198 --new ones are fetched from the cursor
199
200 l_old_event := l_current_event;
201 l_old_section := l_current_section;
202
203 end loop;
204
205 --this construction of the predicate outside the for loop is used to take into
206 --account the last read security rule and also if there is only security rule
207 --it essentially does the same processing as above
208
209 if(l_rule_found = true) then
210
211 if(l_old_event <> 'x') then
212
213 if (l_temp_predicate is not null) then
214 l_temp_predicate := l_temp_predicate || 'AND ';
215 end if;
216
217 l_temp_predicate := l_temp_predicate ||'CONTAINS(PSIG_XML, '''||l_value ||
218 ' WITHIN '||l_old_section||''')+0=0 ';
219 else
220 l_temp_predicate := 'CONTAINS(PSIG_XML, '''||l_value ||' WITHIN '||l_old_section||''')+0=0 ';
221 end if;
222
223 if (l_predicate is not null) then
224 l_predicate := l_predicate || 'AND ';
225 end if;
226
227 if(l_old_event <> 'x') then
228
229 l_predicate := l_predicate ||'((' || l_temp_predicate || 'AND event_name = '''|| l_old_event ||
230 ''') OR event_name <> ''' || l_old_event||''')';
231 else
232 l_predicate := l_predicate || '(' || l_temp_predicate || ')';
233 end if;
234 end if;
235
236 else
237 --this is the heart of the code for returning a predicate if the security
238 --level of the organization is high
239
240 --This loop would read each security rule for the user and create a predicate based
241 --on the security rules
242
243 for rule_rec in c2 loop
244
245 --set the boolean variable to indicate that at least one security rule found
246 l_rule_found :=true;
247
248 --obtain the value of event name and section name from cursor
249
250 l_current_event := rule_rec.event_name ;
251 l_current_event := nvl(l_current_event,'x');
252 l_current_section := rule_rec.index_section_name;
253
254 --if there are more than one security rules the control would enter this construct
255 --in that case creating the predicate is more complex
256
257 if (l_old_section <> 'x') then
258
259 --if the currently read section name is not the same as last read (section break)
260 --or the current event name is not same as last read (event break) then we have
261 --to add conditions to the predicate
262 --please note that the cursor is sorted on event name desc and section name
263
264 if (l_current_section <> l_old_section or l_current_event <> l_old_event) then
265
266 --this temp predicate is used to store the predicate created when breaks on section
267 --name occur in the cursor
268
269 l_temp_predicate := 'CONTAINS(PSIG_XML, '''||l_value ||' WITHIN '||l_old_section||''')>0 ';
270
271 --the value read for all the section names till the section break is flushed
272 l_value := null;
273
274 --if a section break occurs but the event is null then add the temp predicate to the
275 --final predicate
276
277 if(l_old_event ='x') then
278
279 --if the final predicate is already populated then use AND
280 if (l_predicate is not null) then
281 l_predicate := l_predicate || 'OR ';
282 end if;
283 l_predicate := l_predicate || '(' || l_temp_predicate || ') ';
284
285 --flush out the temp predicate so that it can be used again
286 --this is done whenever the temp predicate is used up to create the
287 --final predicate
288
289 l_temp_predicate :=null;
290
291 end if;
292
293 --if an event break has also occured then add the conditions using event name
294 --to the predicate
295
296 if(l_current_event <> 'x' AND l_old_event <> 'x' and l_current_event <> l_old_event) then
297
298 if (l_predicate is not null) then
299 l_predicate := l_predicate || 'OR ';
300 end if;
301
302 l_predicate := l_predicate ||'((' || l_temp_predicate || ') AND event_name = '''|| l_old_event||''') ';
303
304 --flush out the temp predicate so that it can be used again
305
306 l_temp_predicate :=null;
307
308 end if;
309 end if;
310 end if;
311
312 --the secure value is OR'd till we get a section break or an event break
313
314 if (l_value is not null) then
315 l_value := l_value|| '|';
316 end if;
317 l_value := l_value || rule_rec.secure_value;
318
319 --the current section name and the event name are saved as old values before
320 --new ones are fetched from the cursor
321
322 l_old_event := l_current_event;
323 l_old_section := l_current_section;
324 end loop;
325
326 --this construction of the predicate outside the for loop is used to take into
327 --account the last read security rule and also if there is only security rule
328 --it essentially does the same processing as above
329
330 if(l_rule_found = true) then
331 if(l_old_event <> 'x') then
332
333 if (l_temp_predicate is not null) then
334 l_temp_predicate := l_temp_predicate || 'OR ';
335 end if;
336
337 l_temp_predicate := l_temp_predicate || 'CONTAINS(PSIG_XML, '''||l_value ||
338 ' WITHIN ' || l_old_section || ''')>0 ';
339 else
340 l_temp_predicate := 'CONTAINS(PSIG_XML, '''||l_value ||' WITHIN '||l_old_section||''')>0 ';
341 end if;
342
343 if (l_predicate is not null) then
344 l_predicate := l_predicate || 'OR ';
345 end if;
346
347 if(l_old_event <> 'x') then
348
349 l_predicate := l_predicate || '((' || l_temp_predicate || ') AND event_name = '''|| l_old_event||''') ';
350 else
351 l_predicate := l_predicate || '(' || l_temp_predicate || ')';
352 end if;
353
354 end if;
355
356 end if;
357
358 if (l_high_security = 'Y' and l_rule_found = false) then
359 l_predicate := '1=2';
360 end if;
361
362 end if;
363 end if;
364
365 return l_predicate;
366 END psig_view;
367 end edr_policy_function_pkg;
[Home] [Help]