[Home] [Help]
PACKAGE BODY: APPS.EDW_SEC_POLICY
Source
1 package body edw_sec_policy as
2 /* $Header: EDWSPLCB.pls 120.1 2006/03/28 01:47:43 rkumar noship $*/
3
4 -- This procedure associates a security policy to specified fact table
5
6 PROCEDURE attach_policy(Errbuf out NOCOPY varchar2, Retcode out NOCOPY varchar2,fact_table_name varchar2) IS
7
8 x_object_owner all_objects.owner%TYPE;
9
10 v_Errorcode number;
11 v_ErrorText varchar2(200);
12 fact_short_name varchar2(50);
13
14 g_conc_program_id number;
15
16 x_object_name varchar2(30) := 'EDW_SEC_POLICY.ATTACH_POLICY';
17 x_object_type varchar2(30) := 'Security Procedure';
18
19 x_count number :=0;
20
21 x_message varchar2(2000);
22
23 --code added for bug 3871867.. we can use fnd_installaton api to get apps schema name
24 l_schema varchar2(32);
25
26 BEGIN
27
28 Errbuf := NULL;
29 Retcode := 0;
30
31 --Get the schema name bug 3871867
32 OPEN cApps;
33 FETCH cApps INTO l_schema;
34 CLOSE cApps;
35
36 g_conc_program_id := FND_GLOBAL.conc_request_id;
37
38 -- Get fact physical name
39
40 select distinct fact_name into fact_short_name
41 from edw_sec_fact_info_t
42 where fact_long_name = fact_table_name;
43
44 -- Get object owner
45
46 -- The object will either be a view owned by APPS or a synonym pointing to a table
47 -- Check if the object is view
48
49 select count(*) into x_count from user_views
50 where view_name = UPPER(fact_short_name);
51
52
53 IF x_count = 1 THEN /* It is a view owned by APPS */
54 x_object_owner := l_schema; --bug 3871867
55
56 ELSE /* It is a synonym */
57
58 select table_owner into x_object_owner
59 from user_synonyms --bug#4905343
60 where synonym_name = UPPER(fact_short_name);
61
62 END IF;
63
64 -- Associate the policy with fact table
65
66 DBMS_RLS.ADD_POLICY(x_object_owner, fact_short_name, 'edw_sec_policy', 'apps', 'edw_sec_pkg.dim_sec', 'select', TRUE);
67
68 EXCEPTION
69
70 WHEN OTHERS THEN
71
72 v_ErrorCode := SQLCODE;
73 v_ErrorText := SUBSTR(SQLERRM, 1, 200);
74
75 -- Log error message
76
77 x_message := 'Oracle error occured. Fact name is : ' || fact_table_name ||
78 '. Errorcode is : ' || v_ErrorCode || ' and Errortext is : ' || v_ErrorText ;
79
80 edw_sec_util.log_error(x_object_name, x_object_type, null, g_conc_program_id, x_message);
81
82 Errbuf := v_ErrorText;
83 Retcode := SQLCODE;
84
85 END attach_policy;
86
87
88 -- This procedure removes a security policy from specified fact table
89
90 PROCEDURE detach_policy(Errbuf out NOCOPY varchar2, Retcode out NOCOPY varchar2,fact_table_name varchar2) IS
91
92 x_object_owner all_objects.owner%TYPE;
93
94 v_Errorcode number;
95 v_ErrorText varchar2(200);
96 fact_short_name varchar2(50);
97
98 g_conc_program_id number;
99
100 x_object_name varchar2(30) := 'EDW_SEC_POLICY.DETACH_POLICY';
101 x_object_type varchar2(30) := 'Security Procedure';
102
103 x_count number :=0;
104
105 x_message varchar2(2000);
106
107 --bug 3871867, we can not use fnd_installation api to get apps schema name
108 l_schema varchar2(32);
109
110
111 BEGIN
112
113 Errbuf := NULL;
114 Retcode := 0;
115
116 --Get the schema name bug 3871867
117 OPEN cApps;
118 FETCH cApps INTO l_schema;
119 CLOSE cApps;
120
121 g_conc_program_id := FND_GLOBAL.conc_request_id;
122
123 -- Get fact physical name
124
125 select distinct fact_name into fact_short_name
126 from edw_sec_fact_info_t
127 where fact_long_name = fact_table_name;
128
129 -- Get object owner
130
131 -- The object will either be a view owned by APPS or a synonym pointing to a table
132 -- Check if the object is view
133
134 select count(*) into x_count from user_views
135 where view_name = UPPER(fact_short_name);
136
137
138 IF x_count = 1 THEN /* It is a view owned by APPS */
139 x_object_owner := l_schema; --- bug 3871867
140
141 ELSE /* It is a synonym */
142
143 select table_owner into x_object_owner
144 from user_synonyms --bug#4905343
145 where synonym_name = UPPER(fact_short_name);
146
147 END IF;
148
149
150 -- Remove the policy from fact table
151
152 DBMS_RLS.DROP_POLICY(x_object_owner, fact_short_name, 'edw_sec_policy');
153
154 EXCEPTION
155
156 WHEN OTHERS THEN
157
158 v_ErrorCode := SQLCODE;
159 v_ErrorText := SUBSTR(SQLERRM, 1, 200);
160
161 -- Log error message
162
163 x_message := 'Oracle error occured. Fact name is : ' || fact_table_name ||
164 '. Errorcode is : ' || v_ErrorCode || ' and Errortext is : ' || v_ErrorText ;
165
166 edw_sec_util.log_error(x_object_name, x_object_type, null, g_conc_program_id, x_message);
167
168 Errbuf := v_ErrorText;
169 Retcode := SQLCODE;
170
171
172 END detach_policy;
173
174
175
176
177
178
179
180
181 -- This procedure associates a default security policy to specified fact table or view
182
183 PROCEDURE attach_default_policy(Errbuf out NOCOPY varchar2, Retcode out NOCOPY varchar2,fact_table_name varchar2) IS
184
185 x_object_owner all_objects.owner%TYPE;
186
187 v_Errorcode number;
188 v_ErrorText varchar2(200);
189 fact_short_name varchar2(50);
190
191 g_conc_program_id number;
192
193 x_object_name varchar2(50) := 'EDW_SEC_POLICY.ATTACH_DEFAULT_POLICY';
194 x_object_type varchar2(30) := 'Security Procedure';
195
196 x_count number :=0;
197
198 x_message varchar2(2000);
199 --bug 3871867, we can not use fnd_installation api to get apps schema name
200 l_schema varchar2(32);
201
202 BEGIN
203
204 Errbuf := NULL;
205 Retcode := 0;
206
207 --Get the schema name bug 3871867
208 OPEN cApps;
209 FETCH cApps INTO l_schema;
210 CLOSE cApps;
211
212 g_conc_program_id := FND_GLOBAL.conc_request_id;
213
214 -- Get fact physical name
215
216 select distinct fact_name into fact_short_name
217 from edw_sec_fact_info_t
218 where fact_long_name = fact_table_name;
219
220 -- Get object owner
221
222 -- The object will either be a view owned by APPS or a synonym pointing to a table
223 -- Check if the object is view
224
225 select count(*) into x_count from user_views
226 where view_name = UPPER(fact_short_name);
227
228
229 IF x_count = 1 THEN /* It is a view owned by APPS */
230
231 x_object_owner := l_schema; --bug 3871867
232
233 ELSE /* It is a synonym */
234
235 select table_owner into x_object_owner
236 from user_synonyms --bug#4905343
237 where synonym_name = UPPER(fact_short_name);
238
239 END IF;
240
241 -- Associate the policy with fact table
242
243 DBMS_RLS.ADD_POLICY(x_object_owner, fact_short_name, 'edw_sec_default_policy', 'apps', 'edw_sec_pkg.default_sec', 'select', TRUE);
244
245 EXCEPTION
246
247 WHEN OTHERS THEN
248
249 v_ErrorCode := SQLCODE;
250 v_ErrorText := SUBSTR(SQLERRM, 1, 200);
251
252 -- Log error message
253
254 x_message := 'Oracle error occured. Fact name is : ' || fact_table_name ||
255 '. Errorcode is : ' || v_ErrorCode || ' and Errortext is : ' || v_ErrorText ;
256
257 edw_sec_util.log_error(x_object_name, x_object_type, null, g_conc_program_id, x_message);
258
259 Errbuf := v_ErrorText;
260 Retcode := SQLCODE;
261
262 END attach_default_policy;
263
264
265 -- This procedure removes a default security policy from specified fact table
266
267 PROCEDURE detach_default_policy(Errbuf out NOCOPY varchar2, Retcode out NOCOPY varchar2,fact_table_name varchar2) IS
268
269 x_object_owner all_objects.owner%TYPE;
270
271 v_Errorcode number;
272 v_ErrorText varchar2(200);
273 fact_short_name varchar2(50);
274
275 g_conc_program_id number;
276
277 x_object_name varchar2(50) := 'EDW_SEC_POLICY.DETACH_DEFAULT_POLICY';
278 x_object_type varchar2(30) := 'Security Procedure';
279
280 x_count number :=0;
281
282 x_message varchar2(2000);
283
284 --bug 3871867, as we can not use fnd_installation api to get apps schema name
285 l_schema varchar2(32);
286
287 BEGIN
288
289 Errbuf := NULL;
290 Retcode := 0;
291
292 --Get the schema name bug 3871867
293 OPEN cApps;
294 FETCH cApps INTO l_schema;
295 CLOSE cApps;
296
297 g_conc_program_id := FND_GLOBAL.conc_request_id;
298
299 -- Get fact physical name
300
301 select distinct fact_name into fact_short_name
302 from edw_sec_fact_info_t
303 where fact_long_name = fact_table_name;
304
305
306 -- Get object owner
307
308 -- The object will either be a view owned by APPS or a synonym pointing to a table
309 -- Check if the object is view
310
311 select count(*) into x_count from user_views
312 where view_name = UPPER(fact_short_name);
313
314
315 IF x_count = 1 THEN /* It is a view owned by APPS */
316
317 x_object_owner := l_schema; --bug 3871867
318
319 ELSE /* It is a synonym */
320
321 select table_owner into x_object_owner
322 from user_synonyms --bug#4905343
323 where synonym_name = UPPER(fact_short_name);
324
325 END IF;
326
327
328 -- Remove the policy from fact table
329
330 DBMS_RLS.DROP_POLICY(x_object_owner, fact_short_name, 'edw_sec_default_policy');
331
332 EXCEPTION
333
334 WHEN OTHERS THEN
335
336 v_ErrorCode := SQLCODE;
337 v_ErrorText := SUBSTR(SQLERRM, 1, 200);
338
339 -- Log error message
340
341 x_message := 'Oracle error occured. Fact name is : ' || fact_table_name ||
342 '. Errorcode is : ' || v_ErrorCode || ' and Errortext is : ' || v_ErrorText ;
343
344 edw_sec_util.log_error(x_object_name, x_object_type, null, g_conc_program_id, x_message);
345
346 Errbuf := v_ErrorText;
347 Retcode := SQLCODE;
348
349
350 END detach_default_policy;
351
352
353 END edw_sec_policy;