1 package body BNE_SECURITY_UTILS_PKG as
2 /* $Header: bnesecutilsb.pls 120.2 2005/06/29 03:40:59 dvayro noship $ */
3
4 --------------------------------------------------
5 -- Check a comma separated list of form functions.
6 -- This routine will check that all form functions
7 -- exist in the FND_FORM_FUNCTIONS table
8 --------------------------------------------------
9 PROCEDURE CHECK_FUNCTION_EXISTANCE (
10 P_SECURITY_VALUE in VARCHAR2
11 )
12 IS
13 v_tab DBMS_UTILITY.UNCL_ARRAY;
14 v_count BINARY_INTEGER;
15 v_list VARCHAR2(32000);
16 v_funcCount INTEGER;
17 BEGIN
18 v_list := P_SECURITY_VALUE;
19 DBMS_UTILITY.COMMA_TO_TABLE(v_list, v_count, v_tab);
20 FOR i IN v_tab.FIRST .. v_tab.LAST -1
21 LOOP
22 select count(*)
23 into v_funcCount
24 from FND_FORM_FUNCTIONS
25 WHERE FUNCTION_NAME = trim(v_tab(i));
26
27 if v_funcCount = 0 then
28 RAISE_APPLICATION_ERROR( -20000,'The supplied function: ' || trim(v_tab(i))|| ' is invalid.');
29 end if;
30 END LOOP;
31 END CHECK_FUNCTION_EXISTANCE;
32
33 --------------------------------------------------
34 -- Check the existance of the specified object in
35 -- the Web ADI tables.
36 --------------------------------------------------
37 PROCEDURE CHECK_OBJECT_EXISTANCE (
38 P_APPLICATION_ID IN NUMBER,
39 P_OBJECT_CODE IN VARCHAR2,
40 P_OBJECT_TYPE IN VARCHAR2
41 )
42 IS
43 VV_DUMMY BNE_INTEGRATORS_B.INTEGRATOR_CODE%TYPE;
44 BEGIN
45 -- For different object types, check if object code exists
46 IF P_OBJECT_TYPE = 'INTEGRATOR' THEN
47 BEGIN
48 SELECT INTEGRATOR_CODE
49 INTO VV_DUMMY
50 FROM BNE_INTEGRATORS_B
51 WHERE INTEGRATOR_CODE = P_OBJECT_CODE
52 AND APPLICATION_ID = P_APPLICATION_ID;
53 EXCEPTION
54 WHEN NO_DATA_FOUND THEN
55 RAISE_APPLICATION_ERROR( -20000,'The supplied Object Code: ' || P_OBJECT_CODE|| ' is invalid. Integrator does not exist');
56 END;
57 ELSIF P_OBJECT_TYPE = 'COMPONENT' THEN
58 BEGIN
59 SELECT COMPONENT_CODE
60 INTO VV_DUMMY
61 FROM BNE_COMPONENTS_B
62 WHERE COMPONENT_CODE = P_OBJECT_CODE
63 AND APPLICATION_ID = P_APPLICATION_ID;
64 EXCEPTION
65 WHEN NO_DATA_FOUND THEN
66 RAISE_APPLICATION_ERROR( -20000,'The supplied Object Code: ' || P_OBJECT_CODE|| ' is invalid. Component does not exist');
67 END;
68 ELSIF P_OBJECT_TYPE = 'CONTENT' THEN
69 BEGIN
70 SELECT CONTENT_CODE
71 INTO VV_DUMMY
72 FROM BNE_CONTENTS_B
73 WHERE CONTENT_CODE = P_OBJECT_CODE
74 AND APPLICATION_ID = P_APPLICATION_ID;
75 EXCEPTION
76 WHEN NO_DATA_FOUND THEN
77 RAISE_APPLICATION_ERROR( -20000,'The supplied Object Code: ' || P_OBJECT_CODE|| ' is invalid. Content does not exist');
78 END;
79 END IF;
80 END CHECK_OBJECT_EXISTANCE;
81
82 --------------------------------------------------------------------------------
83 -- PROCEDURE: ADD_OBJECT_RULES --
84 -- --
85 -- DESCRIPTION: Adds security rules for a BNE object e.g. an Integrator
86 -- to BNE_SECURED_OBJECT and BNE_SECURITY_RULES --
87 -- --
88 -- PARAMETERS: P_APPLICATION_ID: Application ID of Object --
89 -- P_OBJECT_CODE: Name of object --
90 -- P_OBJECT_TYPE: Currently support Object types of 'INTEGRATOR',--
91 -- 'CONTENT' and 'COMPONENT'. It is recommended only 'INTEGRATOR'
92 -- objects are secured against initially. Please refer to the--
93 -- Web ADI team if you have a stand alone component that needs--
94 -- securing. --
95 -- The P_APPLICATION_ID:P_OBJECT_CODE should refer --
96 -- to a record in BNE_INTEGRATORS_B table --
97 -- P_SECURITY_CODE: UNIQUE KEY --
98 -- P_SECURITY_TYPE: Currently support Security type of 'FUNCTION'--
99 -- P_SECURITY_VALUE: When Security Type = 'FUNCTION', value is --
100 -- a comma separated list of functions --
101 -- P_USER_ID: User ID --
102 -- --
103 -- MODIFICATION HISTORY --
104 -- Date Username Description --
105 -- 16-Aug-2004 Kdobinso Created --
106 -- 25-Aug-2004 dagroves Added more parameter checks --
107 --------------------------------------------------------------------------------
108 PROCEDURE ADD_OBJECT_RULES (
109 P_APPLICATION_ID in NUMBER,
110 P_OBJECT_CODE in VARCHAR2,
111 P_OBJECT_TYPE in VARCHAR2,
112 P_SECURITY_CODE in VARCHAR2,
113 P_SECURITY_TYPE in VARCHAR2,
114 P_SECURITY_VALUE in VARCHAR2,
115 P_USER_ID in NUMBER)
116 IS
117 VV_OBJECT_TYPE BNE_SECURED_OBJECTS.OBJECT_TYPE%TYPE;
118 VV_SECURITY_TYPE BNE_SECURITY_RULES.SECURITY_TYPE%TYPE;
119 VV_SECURITY_VALUE BNE_SECURITY_RULES.SECURITY_VALUE%TYPE;
120 VN_OBJECT_VERSION_NUMBER CONSTANT NUMBER := 1;
121 VR_ROW_ID ROWID;
122 BEGIN
123
124 VV_OBJECT_TYPE := UPPER(P_OBJECT_TYPE);
125 VV_SECURITY_TYPE := UPPER(P_SECURITY_TYPE);
126 VV_SECURITY_VALUE := UPPER(TRIM(P_SECURITY_VALUE));
127
128 IF NOT BNE_INTEGRATOR_UTILS.IS_VALID_APPL_ID(P_APPLICATION_ID) THEN
129 RAISE_APPLICATION_ERROR(-20000,'The supplied application id: ' || P_APPLICATION_ID || ' is invalid.');
130 END IF;
131
132 IF NOT VV_OBJECT_TYPE IN ('INTEGRATOR','CONTENT','COMPONENT') THEN
133 RAISE_APPLICATION_ERROR( -20000,'The supplied Object type is invalid. We support the following object types: INTEGRATOR, CONTENT, COMPONENT');
134 END IF;
135
136 IF NOT VV_SECURITY_TYPE IN ('FUNCTION', 'SELF_SECURED') THEN
137 RAISE_APPLICATION_ERROR( -20000,'The supplied Security type is invalid. We support the following security types: FUNCTION');
138 END IF;
139
140 IF P_OBJECT_CODE IS NULL THEN
141 RAISE_APPLICATION_ERROR( -20000,'Value required for Object Code');
142 END IF;
143
144 IF P_SECURITY_CODE IS NULL THEN
145 RAISE_APPLICATION_ERROR( -20000,'Value required for Security Code');
146 END IF;
147
148
149 IF VV_SECURITY_VALUE IS NULL OR LENGTH(TRIM(VV_SECURITY_VALUE)) = 0 THEN
150 RAISE_APPLICATION_ERROR( -20000,'Value required for Security Value');
151 END IF;
152
153 CHECK_OBJECT_EXISTANCE (P_APPLICATION_ID, P_OBJECT_CODE, VV_OBJECT_TYPE);
154
155 -- Check security value.
156 IF P_SECURITY_TYPE = 'FUNCTION'
157 THEN
158 CHECK_FUNCTION_EXISTANCE(VV_SECURITY_VALUE);
159 END IF;
160
161 BNE_SECURITY_RULES_PKG.INSERT_ROW( VR_ROW_ID,
162 P_APPLICATION_ID,
163 P_SECURITY_CODE,
164 VN_OBJECT_VERSION_NUMBER,
165 VV_SECURITY_TYPE,
166 VV_SECURITY_VALUE,
167 SYSDATE,
168 P_USER_ID,
169 SYSDATE,
170 P_USER_ID,
171 0);
172
173 BNE_SECURED_OBJECTS_PKG.INSERT_ROW( VR_ROW_ID,
174 P_APPLICATION_ID,
175 P_OBJECT_CODE,
176 VV_OBJECT_TYPE,
177 VN_OBJECT_VERSION_NUMBER,
178 P_APPLICATION_ID,
179 P_SECURITY_CODE,
180 SYSDATE,
181 P_USER_ID,
182 SYSDATE,
183 P_USER_ID,
184 0 );
185 END ADD_OBJECT_RULES;
186
187 --------------------------------------------------------------------------------
188 -- PROCEDURE: UPDATE_OBJECT_RULES --
189 -- --
190 -- DESCRIPTION: Updates security rules for a BNE object e.g. an Integrator
191 -- from BNE_SECURED_OBJECT and BNE_SECURITY_RULES --
192 -- --
193 -- PARAMETERS: P_OBJECT_APP_ID: BNE_SECURED_OBJECT.APPLICATION_ID --
194 -- P_OBJECT_CODE: BNE_SECURED_OBJECT.OBJECT_CODE --
195 -- P_OBJECT_TYPE: BNE_SECURED_OBJECT.OBJECT_TYPE --
196 -- P_SECURITY_APP_ID: BNE_SECURITY_RULES.APPLICATION_ID --
197 -- P_SECURITY_CODE: BNE_SECURITY_RULES.SECURITY_CODE --
198 -- P_SECURITY_TYPE: BNE_SECURITY_RULES.SECURITY_TYPE --
199 -- P_SECURITY_VALUE: BNE_SECURITY_RULES.SECURITY_VALUE --
200 -- P_USER_ID: User ID --
201 -- --
202 -- MODIFICATION HISTORY --
203 -- Date Username Description --
204 -- 16-Aug-2004 Kdobinso Created --
205 -- 25-Aug-2004 dagroves Added more parameter checks --
206 --------------------------------------------------------------------------------
207 PROCEDURE UPDATE_OBJECT_RULES (
208 P_OBJECT_APP_ID in NUMBER,
209 P_OBJECT_CODE in VARCHAR2,
210 P_OBJECT_TYPE in VARCHAR2,
211 P_SECURITY_APP_ID in NUMBER,
212 P_SECURITY_CODE in VARCHAR2,
213 P_SECURITY_TYPE in VARCHAR2,
214 P_SECURITY_VALUE in VARCHAR2,
215 P_USER_ID in NUMBER)
216 IS
217 VN_OBJECT_VERSION_NUMBER CONSTANT NUMBER := 1;
218 BEGIN
219
220 IF NOT BNE_INTEGRATOR_UTILS.IS_VALID_APPL_ID(P_OBJECT_APP_ID) THEN
221 RAISE_APPLICATION_ERROR(-20000,'The supplied application id: ' || P_OBJECT_APP_ID || ' is invalid.');
222 END IF;
223
224 IF NOT P_SECURITY_TYPE IN ('FUNCTION', 'SELF_SECURED') THEN
225 RAISE_APPLICATION_ERROR( -20000,'The supplied Security type is invalid. We support the following security types: FUNCTION');
226 END IF;
227
228 IF P_OBJECT_CODE IS NULL THEN
229 RAISE_APPLICATION_ERROR( -20000,'Value required for Object Code');
230 END IF;
231
232 IF P_SECURITY_CODE IS NULL THEN
233 RAISE_APPLICATION_ERROR( -20000,'Value required for Security Code');
234 END IF;
235
236 IF NOT P_OBJECT_TYPE IN ('INTEGRATOR','CONTENT','COMPONENT') THEN
237 RAISE_APPLICATION_ERROR( -20000,'The supplied Object type is invalid. We support the following object types: INTEGRATOR, CONTENT, COMPONENT');
238 END IF;
239
240 IF P_SECURITY_VALUE IS NULL OR LENGTH(TRIM(P_SECURITY_VALUE)) = 0 THEN
241 RAISE_APPLICATION_ERROR( -20000,'Value required for Security Value');
242 END IF;
243
244 CHECK_OBJECT_EXISTANCE (P_OBJECT_APP_ID, P_OBJECT_CODE, P_OBJECT_TYPE);
245
246 -- Check security value.
247 IF P_SECURITY_TYPE = 'FUNCTION'
248 THEN
249 CHECK_FUNCTION_EXISTANCE(P_SECURITY_VALUE);
250 END IF;
251
252 BNE_SECURED_OBJECTS_PKG.UPDATE_ROW( P_OBJECT_APP_ID,
253 P_OBJECT_CODE,
254 P_OBJECT_TYPE,
255 VN_OBJECT_VERSION_NUMBER,
256 P_SECURITY_APP_ID,
257 P_SECURITY_CODE,
258 SYSDATE,
259 P_USER_ID,
260 0 );
261
262 BNE_SECURITY_RULES_PKG.UPDATE_ROW( P_SECURITY_APP_ID,
263 P_SECURITY_CODE,
264 VN_OBJECT_VERSION_NUMBER,
265 P_SECURITY_TYPE,
266 P_SECURITY_VALUE,
267 SYSDATE,
268 P_USER_ID ,
269 0 );
270
271 END UPDATE_OBJECT_RULES;
272
273
274 --------------------------------------------------------------------------------
275 -- PROCEDURE: DELETE_OBJECT_RULES --
276 -- --
277 -- DESCRIPTION: Deletes security rules for a BNE object e.g. an Integrator
278 -- from BNE_SECURED_OBJECT and BNE_SECURITY_RULES --
279 -- --
280 -- PARAMETERS: P_OBJECT_APP_ID: BNE_SECURED_OBJECT.APPLICATION_ID --
281 -- P_OBJECT_CODE: BNE_SECURED_OBJECT.OBJECT_CODE --
282 -- P_OBJECT_TYPE: BNE_SECURED_OBJECT.OBJECT_TYPE --
283 -- P_SECURITY_APP_ID: BNE_SECURITY_RULES.APPLICATION_ID --
284 -- P_SECURITY_CODE: BNE_SECURITY_RULES.SECURITY_CODE --
285 -- --
286 -- MODIFICATION HISTORY --
287 -- Date Username Description --
288 -- 16-Aug-2004 Kdobinso Created --
289 --------------------------------------------------------------------------------
290 PROCEDURE DELETE_OBJECT_RULES (
291 P_OBJECT_APP_ID in NUMBER,
292 P_OBJECT_CODE in VARCHAR2,
293 P_OBJECT_TYPE in VARCHAR2,
294 P_SECURITY_APP_ID in NUMBER,
295 P_SECURITY_CODE in VARCHAR2) IS
296 VV_SECURITY_CODE BNE_SECURITY_RULES.SECURITY_CODE%type;
297 BEGIN
298
299 BEGIN
300 SELECT SECURITY_RULE_CODE
301 INTO VV_SECURITY_CODE
302 FROM BNE_SECURED_OBJECTS
303 WHERE APPLICATION_ID = P_OBJECT_APP_ID
304 AND OBJECT_CODE = P_OBJECT_CODE
305 AND OBJECT_TYPE = P_OBJECT_TYPE
306 AND SECURITY_RULE_APP_ID = P_SECURITY_APP_ID;
307 EXCEPTION
308 WHEN NO_DATA_FOUND THEN
309 RAISE_APPLICATION_ERROR( -20000,'No record exists in BNE_SECURED_OBJECT with key ' || P_OBJECT_APP_ID|| ':' || P_OBJECT_CODE || ' WHERE OBJECT_TYPE = ' || P_OBJECT_TYPE);
310 END;
311
312 IF P_SECURITY_CODE <> VV_SECURITY_CODE THEN
313 RAISE_APPLICATION_ERROR( -20000,'The supplied security code ' || P_SECURITY_CODE || ' does not match security referenced by ' || P_OBJECT_APP_ID || ':' || P_OBJECT_CODE || ' in BNE_SECURED_OBJECT');
314 END IF;
315
316 BNE_SECURED_OBJECTS_PKG.DELETE_ROW( P_OBJECT_APP_ID, P_OBJECT_CODE, P_OBJECT_TYPE );
317 BNE_SECURITY_RULES_PKG.DELETE_ROW( P_SECURITY_APP_ID, P_SECURITY_CODE );
318
319 END DELETE_OBJECT_RULES;
320
321
322
323 END BNE_SECURITY_UTILS_PKG;