1 PACKAGE BODY IBY_SECURITY_PKG AS
2 /* $Header: ibysecb.pls 120.22.12010000.10 2008/11/20 02:12:50 jleybovi ship $ */
3
4 -- Number to hex format string
5 NUMTOX CONSTANT VARCHAR2(34) := 'FMXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';
6 -- Hex to number format string
7 XTONUM CONSTANT VARCHAR2(32) := 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';
8 -- DES3 mask to avoid "double encryption" errors
9 DES3_MASK CONSTANT RAW(8) := '0123456789ABCDEF';
10 -- PKCS 5 padding
11 PKCS5PAD CONSTANT RAW(36) := HEXTORAW('010202030303040404040505050505060606'
12 || '060606070707070707070808080808080808');
13
14 -- Forward Declarations
15
16 PROCEDURE print_debuginfo(
17 p_message IN VARCHAR2,
18 p_prefix IN VARCHAR2 DEFAULT 'DEBUG',
19 p_msg_level IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
20 p_module IN VARCHAR2 DEFAULT 'iby.plsql.IBY_SECURITY_PKG'
21 );
22
23 FUNCTION get_site_salt RETURN RAW
24 IS
25 BEGIN
26 RETURN fnd_vault.getr('IBY','IBY_SITE_SALT');
27 END get_site_salt;
28
29 FUNCTION get_key_hash(p_sys_key IN RAW) RETURN RAW
30 IS
31 l_site_salt RAW(128);
32 l_key_hash iby_sys_security_options.sys_key_hash%TYPE;
33 BEGIN
34 l_site_salt := get_site_salt();
35 l_key_hash := DBMS_OBFUSCATION_TOOLKIT.MD5( INPUT => p_sys_key );
36 RETURN
37 DBMS_OBFUSCATION_TOOLKIT.MD5
38 (INPUT => UTL_RAW.concat(l_key_hash,l_site_salt));
39 END get_key_hash;
40
41 FUNCTION Recipher_Key
42 ( p_data IN RAW, p_oldkey IN DES3_KEY_TYPE, p_newkey IN DES3_KEY_TYPE )
43 RETURN RAW
44 IS
45 BEGIN
46 RETURN
47 dbms_obfuscation_toolkit.des3encrypt
48 ( input =>
49 dbms_obfuscation_toolkit.des3decrypt
50 ( input => p_data , key => p_oldkey,
51 which => dbms_obfuscation_toolkit.ThreeKeyMode
52 ),
53 key => p_newkey,
54 which => dbms_obfuscation_toolkit.ThreeKeyMode
55 );
56 END Recipher_Key;
57
58 --
59 -- USE
60 -- Gets the hash value of the current system key
61 --
62 FUNCTION Get_SysKey_Hash
63 RETURN iby_sys_security_options.sys_key_hash%TYPE
64 IS
65 l_syskey_hash iby_sys_security_options.sys_key_hash%TYPE;
66
67 CURSOR c_sys_key
68 IS
69 SELECT sys_key_hash
70 FROM iby_sys_security_options;
71 BEGIN
72 IF (c_sys_key%ISOPEN) THEN CLOSE c_sys_key; END IF;
73
74 OPEN c_sys_key;
75 FETCH c_sys_key INTO l_syskey_hash;
76 CLOSE c_sys_key;
77
78 RETURN l_syskey_hash;
79 END Get_SysKey_Hash;
80
81 PROCEDURE Validate_Sys_Key
82 (p_sys_sec_key IN DES3_KEY_TYPE,
83 x_err_code OUT NOCOPY VARCHAR2
84 )
85 IS
86 l_sys_key_hash iby_sys_security_options.sys_key_hash%TYPE;
87 l_test_hash iby_sys_security_options.sys_key_hash%TYPE;
88 BEGIN
89 IF (p_sys_sec_key IS NULL) THEN
90 x_err_code := 'IBY_10006';
91 RETURN;
92 END IF;
93
94 l_sys_key_hash := Get_SysKey_Hash();
95 IF (l_sys_key_hash IS NULL) THEN
96 x_err_code := 'IBY_10007';
97 RETURN;
98 END IF;
99
100 IF (get_key_hash(p_sys_sec_key) <> l_sys_key_hash) THEN
101 x_err_code := 'IBY_10003';
102 RETURN;
103 END IF;
104
105 x_err_code := NULL;
106 END Validate_Sys_Key;
107
108 PROCEDURE Create_Sys_Key
109 (p_commit IN VARCHAR2,
110 p_sys_sec_key IN DES3_KEY_TYPE,
111 p_wallet_path IN VARCHAR2
112 )
113 IS
114 l_sys_secure_hash iby_sys_security_options.sys_key_hash%TYPE;
115 l_wallet_loc iby_sys_security_options.sys_key_file_location%TYPE;
116
117 CURSOR c_wallet_loc IS
118 SELECT sys_key_file_location FROM iby_sys_security_options;
119
120 BEGIN
121 IF (c_wallet_loc%ISOPEN) THEN CLOSE c_wallet_loc; END IF;
122
123 l_sys_secure_hash := Get_SysKey_Hash();
124 OPEN c_wallet_loc; FETCH c_wallet_loc INTO l_wallet_loc; CLOSE c_wallet_loc;
125 --
126 -- hex strings are twice as long as their byte string equivalents
127 --
128 IF (LENGTH(RAWTOHEX(p_sys_sec_key)) <> (G_DES3_MAX_KEY_LEN*2))
129 OR (iby_utility_pvt.is_trivial(p_wallet_path))
130 OR (p_sys_sec_key IS NULL)
131 THEN
132 raise_application_error(-20000,'IBY_10006', FALSE);
133 --
134 -- sys key not NULL or trivial at this point; if the same as the current
135 -- key and the wallet location is NULL, then is the first wallet set after
136 -- upgrade from 11i
137 --
138 ELSIF (NOT l_sys_secure_hash IS NULL) AND
139 ( (get_key_hash(p_sys_sec_key) <> l_sys_secure_hash) OR
140 (NOT l_wallet_loc IS NULL)
141 )
142 THEN
143 raise_application_error(-20000,'IBY_10005', FALSE);
144 ELSE
145 IF (NOT l_sys_secure_hash IS NULL) AND (l_wallet_loc IS NULL) THEN
146 FND_VAULT.DEL('IBY','IBY_SYS_SECURITY_KEY');
147 END IF;
148
149 UPDATE iby_sys_security_options
150 SET sys_key_hash = get_key_hash(p_sys_sec_key),
151 salt_version = get_salt_version,
152 sys_key_file_location = p_wallet_path,
153 object_version_number = object_version_number + 1,
154 last_update_date = sysdate,
155 last_updated_by = fnd_global.user_id,
156 last_update_login = fnd_global.login_id;
157 END IF;
158
159 IF FND_API.to_Boolean( p_commit ) THEN
160 COMMIT;
161 END IF;
162 END Create_Sys_Key;
163
164 PROCEDURE Change_Sys_Key
165 (p_commit IN VARCHAR2,
166 p_sys_key_old IN DES3_KEY_TYPE,
167 p_sys_key_new IN DES3_KEY_TYPE,
168 p_wallet_path_new IN VARCHAR2
169 )
170 IS
171 l_err_code VARCHAR2(50) := NULL;
172 BEGIN
173
174 -- validate the new key
175 IF (iby_utility_pvt.is_trivial(p_sys_key_new)) OR
176 (LENGTH(RAWTOHEX(p_sys_key_new)) <> (G_DES3_MAX_KEY_LEN*2))
177 THEN
178 raise_application_error(-20000,'IBY_10006', FALSE);
179 ELSIF (iby_utility_pvt.is_trivial(p_wallet_path_new)) THEN
180 raise_application_error(-20000,'INVALID_WALLET', FALSE);
181 END IF;
182
183 validate_sys_key(p_sys_key_old,l_err_code);
184 IF (NOT l_err_code IS NULL) THEN
185 raise_application_error(-20000,l_err_code, FALSE);
186 END IF;
187
188 -- recipher all subkeys
189 UPDATE iby_sys_security_subkeys
190 SET subkey_cipher_text =
191 Recipher_Key(subkey_cipher_text,p_sys_key_old,p_sys_key_new),
192 object_version_number = object_version_number + 1,
193 last_update_date = sysdate,
194 last_updated_by = fnd_global.user_id,
195 last_update_login = fnd_global.login_id;
196
197 -- update system key hash
198 UPDATE iby_sys_security_options
199 SET sys_key_hash = get_key_hash(p_sys_key_new),
200 salt_version = get_salt_version,
201 sys_key_file_location = p_wallet_path_new,
202 object_version_number = object_version_number + 1,
203 last_update_date = sysdate,
204 last_updated_by = fnd_global.user_id,
205 last_update_login = fnd_global.login_id;
206
207 IF FND_API.to_Boolean( p_commit ) THEN
208 COMMIT;
209 END IF;
210 END Change_Sys_Key;
211
212
213 PROCEDURE Get_Sys_Subkey
214 (p_commit IN VARCHAR2,
215 p_masterkey IN DES3_KEY_TYPE,
216 p_inc_use_flag IN VARCHAR2,
217 x_subkey_id OUT NOCOPY iby_sys_security_subkeys.sec_subkey_id%TYPE,
218 x_subkey OUT NOCOPY DES3_KEY_TYPE
219 )
220 IS
221 l_err_code VARCHAR2(100);
222 l_subkey_ciphertxt iby_sys_security_subkeys.subkey_cipher_text%TYPE;
223
224 CURSOR c_subkey
225 IS
226 SELECT k.sec_subkey_id, k.subkey_cipher_text
227 FROM iby_sys_security_subkeys k, iby_sys_security_options o
228 WHERE ( ( k.use_count < o.subkey_use_maximum) )
229 AND ( (sysdate - k.creation_date) < NVL(o.subkey_age_maximum,30) )
230 ORDER BY sec_subkey_id ASC;
231 BEGIN
232
233 IF (c_subkey%ISOPEN) THEN CLOSE c_subkey; END IF;
234
235 validate_sys_key(p_masterkey,l_err_code);
236 IF (NOT l_err_code IS NULL) THEN
237 raise_application_error(-20000,l_err_code, FALSE);
238 END IF;
239
240 OPEN c_subkey;
241 FETCH c_subkey INTO x_subkey_id, x_subkey;
242 CLOSE c_subkey;
243
244 IF (x_subkey_id IS NULL) THEN
245 --
246 -- subkey within use limit not found; create new one
247 --
248 x_subkey :=
249 dbms_obfuscation_toolkit.des3getkey
250 (seed => Fnd_Crypto.randombytes(G_DES3_MAX_KEY_LEN * 8),
251 which => dbms_obfuscation_toolkit.ThreeKeyMode
252 );
253 l_subkey_ciphertxt :=
254 dbms_obfuscation_toolkit.des3encrypt
255 ( input => x_subkey, key => p_masterkey,
256 which => dbms_obfuscation_toolkit.ThreeKeyMode
257 );
258
259 SELECT iby_sys_security_subkeys_s.NEXTVAL INTO x_subkey_id FROM dual;
260 INSERT INTO iby_sys_security_subkeys
261 (sec_subkey_id, subkey_cipher_text, use_count,
262 created_by, creation_date, last_updated_by, last_update_date,
263 last_update_login, object_version_number)
264 VALUES
265 (x_subkey_id, l_subkey_ciphertxt, 1,
266 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate,
267 fnd_global.login_id, 1);
268 ELSE
269 -- valid subkey existing found
270 x_subkey :=
271 dbms_obfuscation_toolkit.des3decrypt
272 ( input => x_subkey , key => p_masterkey,
273 which => dbms_obfuscation_toolkit.ThreeKeyMode
274 );
275 --
276 -- if subkey will be used, increment its use count
277 --
278 IF (p_inc_use_flag = 'Y') THEN
279 UPDATE iby_sys_security_subkeys
280 SET use_count = use_count + 1,
281 object_version_number = object_version_number + 1,
282 last_update_date = sysdate,
283 last_updated_by = fnd_global.user_id,
284 last_update_login = fnd_global.login_id
285 WHERE (sec_subkey_id = x_subkey_id);
286 END IF;
287 END IF;
288
289 IF FND_API.to_Boolean( p_commit ) THEN
290 COMMIT;
291 END IF;
292 END Get_Sys_Subkey;
293
294 FUNCTION Get_Sys_Subkey
295 (p_sys_key IN DES3_KEY_TYPE,
296 p_subkey_cipher IN iby_sys_security_subkeys.subkey_cipher_text%TYPE
297 )
298 RETURN DES3_KEY_TYPE
299 IS
300 BEGIN
301 -- no des3 padding required for either key
302 RETURN dbms_obfuscation_toolkit.des3decrypt
303 ( input => p_subkey_cipher , key => p_sys_key,
304 which => dbms_obfuscation_toolkit.ThreeKeyMode
305 );
306 END Get_Sys_Subkey;
307
308 PROCEDURE Get_Sys_Subkey_Hex
309 (p_commit IN VARCHAR2 := FND_API.G_FALSE,
310 p_sys_key IN DES3_KEY_TYPE,
311 p_inc_use_flag IN VARCHAR2,
312 x_subkey_id OUT NOCOPY iby_sys_security_subkeys.sec_subkey_id%TYPE,
313 x_subkey_Hex OUT NOCOPY VARCHAR2
314 ) IS
315 l_subkey_raw RAW(24);
316 BEGIN
317 Get_Sys_Subkey(p_commit, p_sys_key, p_inc_use_flag, x_subkey_id, l_subkey_raw);
318 x_subkey_Hex := RAWTOHEX(l_subkey_raw);
319 END Get_Sys_Subkey_Hex;
320
321 FUNCTION Get_Sys_Subkey_Hex
322 (p_subkey_id IN iby_sys_security_subkeys.sec_subkey_id%TYPE,
323 p_sys_key IN DES3_KEY_TYPE
324 )
325 RETURN VARCHAR2
326 IS
327 l_subkey_cipher RAW(24);
328 l_subkey_clear RAW(24);
329 l_subkey_Hex VARCHAR2(100);
330
331 BEGIN
332
333 SELECT subkey_cipher_text INTO l_subkey_cipher FROM iby_sys_security_subkeys
334 WHERE sec_subkey_id = p_subkey_id;
335
336 l_subkey_clear := Get_Sys_Subkey(p_sys_key, l_subkey_cipher);
337 l_subkey_Hex := RAWTOHEX(l_subkey_clear);
338 RETURN l_subkey_Hex;
339 END Get_Sys_Subkey_Hex;
340
341
342
343
344 FUNCTION Prepare_Cleartxt( p_cleartxt IN VARCHAR2, p_padchar IN VARCHAR2 )
345 RETURN VARCHAR2
346 IS
347 l_pad_by number;
348 l_total_padding VARCHAR2(4096) DEFAULT NULL;
349 BEGIN
350 IF (p_padchar IS NULL) THEN
351 RETURN p_cleartxt;
352 END IF;
353
354 -- the clear text for DES3 must be a mutliple of 8 bytes
355 -- could use the lengthb() function but as almost all
356 -- other character set encodings are multiples of 8 bytes
357 -- this should work
358 --
359 /* Bug 6313036: The RPAD function does not pad exact number of bytes
360 needed in multi-byte environment.
361 RETURN RPAD(p_cleartxt,CEIL(LENGTH(p_cleartxt)/8)*8,p_padchar);
362 */
363 l_pad_by := (8-MOD(LENGTHB(p_cleartxt),8));
364 IF l_pad_by = 0 THEN
365 RETURN p_cleartxt;
366 ELSE
367 FOR i in 1 .. l_pad_by
368 LOOP
369 l_total_padding := l_total_padding || p_padchar;
370 END LOOP;
371 RETURN p_cleartxt || l_total_padding;
372 END IF;
373 END Prepare_Cleartxt;
374
375
376 FUNCTION Unpack_Cleartxt( p_cleartxt IN VARCHAR2, p_padchar IN VARCHAR2 )
377 RETURN VARCHAR2
378 IS
379 BEGIN
380 RETURN RTRIM(p_cleartxt,p_padchar);
381 END Unpack_Cleartxt;
382
383 FUNCTION Cipher_Data
384 (p_data IN VARCHAR2,
385 p_sec_key IN IBY_SECURITY_PKG.DES3_KEY_TYPE,
386 p_pad IN VARCHAR2,
387 p_encrypt IN VARCHAR2
388 )
389 RETURN VARCHAR2
390 IS
391 BEGIN
392 IF ( p_encrypt = 'Y' ) THEN
393 RETURN dbms_obfuscation_toolkit.des3encrypt
394 ( input_string => prepare_cleartxt(p_data,p_pad),
395 key_string => p_sec_key,
396 which => dbms_obfuscation_toolkit.ThreeKeyMode
397 );
398 ELSE
399 RETURN p_data;
400 END IF;
401 END Cipher_Data;
402
403 FUNCTION Get_Unmasked_Data
404 ( p_data IN VARCHAR2, p_mask_option IN VARCHAR2, p_unmask_len IN NUMBER )
405 RETURN VARCHAR2
406 IS
407 l_mask_option VARCHAR2(30);
408 l_length NUMBER;
409 l_start_index NUMBER;
410 l_end_index NUMBER;
411 BEGIN
412 l_mask_option := p_mask_option;
413 l_length := LENGTH(p_data);
414
415 IF (l_length <= NVL(p_unmask_len,l_length)) AND
416 (l_mask_option <> G_MASK_ALL)
417 THEN
418 l_mask_option := G_MASK_NONE;
419 ELSIF (NVL(p_unmask_len,0) < 1) AND
420 (l_mask_option <> G_MASK_NONE)
421 THEN
422 l_mask_option := G_MASK_ALL;
423 END IF;
424
425 IF (l_mask_option = G_MASK_NONE) THEN
426 RETURN p_data;
427 ELSIF (l_mask_option = G_MASK_ALL) THEN
428 RETURN NULL;
429 ELSIF (l_mask_option = G_MASK_POSTFIX) THEN
430 RETURN SUBSTR(p_data,1,p_unmask_len);
431 ELSE
432 RETURN SUBSTR(p_data,l_length-(p_unmask_len-1));
433 END IF;
434 END Get_Unmasked_Data;
435
436 FUNCTION Mask_Data
437 (p_data IN VARCHAR2,
438 p_mask_option IN VARCHAR2,
439 p_unmask_len IN NUMBER,
440 p_mask_char IN VARCHAR2
441 )
442 RETURN VARCHAR2
443 IS
444 l_masked_number VARCHAR2(2000);
445 l_mask_option VARCHAR2(30);
446 l_unmask_len NUMBER;
447 l_mask_char VARCHAR2(01);
448 l_length NUMBER;
449 l_start_index NUMBER;
450 l_end_index NUMBER;
451 BEGIN
452 l_mask_option := nvl(p_mask_option, G_MASK_ALL);
453 l_unmask_len := nvl(p_unmask_len , 0);
454 l_mask_char := nvl(p_mask_char , '*');
455 l_length := LENGTH(p_data);
456 print_debuginfo('In Mask_Data, p_data :'||p_data);
457 print_debuginfo('The value of p_mask_option:' || l_mask_option);
458 print_debuginfo('The value of p_unmask_len :'|| l_unmask_len);
459 print_debuginfo('The value of l_length:' || l_length);
460 IF (l_length <= NVL(l_unmask_len,l_length)) AND
461 (l_mask_option <> G_MASK_ALL)
462 THEN
463 l_mask_option := G_MASK_NONE;
464 ELSIF (NVL(l_unmask_len,0) < 1) AND
465 (l_mask_option <> G_MASK_NONE)
466 THEN
467 l_mask_option := G_MASK_ALL;
468 END IF;
469
470 IF (l_mask_option = G_MASK_NONE) THEN
471 l_masked_number := p_data;
472 ELSIF (l_mask_option = G_MASK_ALL) THEN
473 l_masked_number := RPAD(l_mask_char,l_length,l_mask_char);
474 ELSIF (l_mask_option = G_MASK_POSTFIX) THEN
475 l_masked_number := RPAD(SUBSTR(p_data,1,l_unmask_len),
476 l_length,l_mask_char);
477 ELSE
478 l_masked_number:= LPAD(SUBSTR(p_data,l_length-(l_unmask_len-1)),
479 l_length,l_mask_char);
480 END IF;
481 print_debuginfo('The values after masking process.. ');
482 print_debuginfo('The value of p_data:'|| p_data);
483 print_debuginfo('The value of p_mask_option: '|| l_mask_option);
484 print_debuginfo('The value of p_unmask_len:'||l_unmask_len);
485 print_debuginfo('The value of l_length:'|| l_length);
486 print_debuginfo('The value of l_masked_number after masking process: '||
487 l_masked_number);
488 RETURN l_masked_number;
489
490 END Mask_Data;
491
492 FUNCTION Mask_Date_Field
493 (p_date IN DATE,
494 p_return_format IN VARCHAR2,
495 p_mask_char IN VARCHAR2
496 )
497 RETURN VARCHAR2
498 IS
499 l_date_format VARCHAR2(20);
500 l_mask_char VARCHAR2(1);
501 l_formatted_date VARCHAR2(20);
502 l_masked_date VARCHAR2(20);
503 l_date_len NUMBER;
504 BEGIN
505 l_date_format := NVL(p_return_format, 'MM/YY');
506 l_mask_char := NVL(p_mask_char, 'X');
507 l_formatted_date := TO_CHAR(p_date, l_date_format);
508 l_masked_date := regexp_replace(l_formatted_date, '[0-9]{1}', l_mask_char);
509 RETURN l_masked_date;
510 END Mask_Date_Field;
511
512 --
513 -- NOTES
514 -- !!! DO NOT MODIFY THE ESSENTIAL CHARACTERISTICS OF THIS FUNCTION !!!
515 -- !!! DOING SO COULD LEAD TO CREDIT CARD DATA CORRUPTION !!!
516 --
517 FUNCTION Encode_Number( p_number IN VARCHAR2, p_des3mask IN BOOLEAN )
518 RETURN VARCHAR2
519 IS
520 l_num VARCHAR2(256); -- number in hex (RAW) format
521 l_pad VARCHAR2(256); -- random pad
522 l_hex_len NUMBER; -- number hex string length
523 BEGIN
524 -- max length of the hex representation of the number is:
525 -- log(16,10) * (digit_length - 1 + 1) + 1
526 -- A length x digit string is < 10^x since the most signficant
527 -- digit is a multiple of 10^(x-1); thus the maximum value for a
528 -- length x string is 10^x-1 ~= 10^x
529 -- 10^x in hex representatin thus has floor(log(16,10)*x) + 1 digits
530 -- since the nth digit is a multiple of 16^(n-1)
531 --
532 l_hex_len := FLOOR(LOG(16,10)*(LENGTH(p_number)+1-1)) + 1;
533
534 -- must be of whole byte length; 1 byte = 2 hex characters
535 l_num := LTRIM(TO_CHAR(TO_NUMBER(p_number),NUMTOX));
536 l_num := LPAD(l_num,l_hex_len,'0');
537
538 -- must be of unit byte length; 2 hex characters = 1 byte
539 IF (MOD(l_hex_len,2) = 1) THEN
540 l_num := SUBSTR(fnd_crypto.randombytes(1),-1) || l_num;
541 l_hex_len := l_hex_len+1;
542 END IF;
543
544 -- data must be a multiple of 8 bytes long
545 l_pad := fnd_crypto.randombytes(8-MOD(l_hex_len/2,8));
546 l_num := l_pad || l_num;
547
548 IF (p_des3mask) THEN
549 l_num := RAWTOHEX(utl_raw.bit_xor(l_num, DES3_MASK));
550 END IF;
551
552 RETURN l_num;
553 END Encode_Number;
554
555 --
556 -- NOTES
557 -- !!! DO NOT MODIFY THE ESSENTIAL CHARACTERISTICS OF THIS FUNCTION !!!
558 -- !!! DOING SO COULD LEAD TO CREDIT CARD DATA CORRUPTION !!!
559 --
560 FUNCTION Decode_Number
561 ( p_number IN VARCHAR2, p_length IN NUMBER, p_des3mask IN BOOLEAN )
562 RETURN VARCHAR2
563 IS
564 l_num VARCHAR2(256); -- number in hex (RAW) format
565 l_hex_len NUMBER; -- number hex string length
566 BEGIN
567 IF (p_des3mask) THEN
568 l_num := RAWTOHEX(utl_raw.bit_xor(p_number,DES3_MASK));
569 ELSE
570 l_num := p_number;
571 END IF;
572
573 l_hex_len := FLOOR(LOG(16,10)*(p_length+1-1)) + 1;
574 -- ok if not of unit btye length; in fact, the filler hex
575 -- character to pad to even the length is random
576 --
577 l_num := TO_CHAR(TO_NUMBER(SUBSTR(l_num,-l_hex_len),XTONUM));
578 l_num := LPAD(l_num,p_length,'0');
579
580 RETURN l_num;
581 END Decode_Number;
582
583 --
584 -- NOTES
585 -- !!! DO NOT MODIFY THE ESSENTIAL CHARACTERISTICS OF THIS FUNCTION !!!
586 -- !!! DOING SO COULD LEAD TO CREDIT CARD DATA CORRUPTION !!!
587 --
588 FUNCTION Encode_CVV( p_number IN VARCHAR2, p_des3mask IN BOOLEAN )
589 RETURN VARCHAR2
590 IS
591 l_num VARCHAR2(256); -- number in hex (RAW) format
592 l_pad VARCHAR2(256); -- random pad
593 l_hex_len NUMBER; -- number hex string length
594 BEGIN
595 -- max length of the hex representation of the number is:
596 -- log(16,10) * (digit_length - 1 + 1) + 1
597 -- A length x digit string is < 10^x since the most signficant
598 -- digit is a multiple of 10^(x-1); thus the maximum value for a
599 -- length x string is 10^x-1 ~= 10^x
600 -- 10^x in hex representatin thus has floor(log(16,10)*x) + 1 digits
601 -- since the nth digit is a multiple of 16^(n-1)
602 --
603 l_hex_len := FLOOR(LOG(16,10)*(LENGTH(p_number)+1-1)) + 1;
604
605 -- must be of whole byte length; 1 byte = 2 hex characters
606 l_num := LTRIM(TO_CHAR(TO_NUMBER(p_number),NUMTOX));
607 l_num := LPAD(l_num,l_hex_len,'0');
608
609 -- must be of unit byte length; 2 hex characters = 1 byte
610 IF (MOD(l_hex_len,2) = 1) THEN
611 l_num := SUBSTR(fnd_crypto.randombytes(1),-1) || l_num;
612 l_hex_len := l_hex_len+1;
613 END IF;
614
615 -- data must be a multiple of 32 bytes long
616 l_pad := fnd_crypto.randombytes(32-MOD(l_hex_len/2,32));
617 l_num := l_pad || l_num;
618
619 IF (p_des3mask) THEN
620 l_num := RAWTOHEX(utl_raw.bit_xor(l_num, DES3_MASK));
621 END IF;
622
623 RETURN l_num;
624 END Encode_CVV;
625
626
627 PROCEDURE Create_Segment
628 (p_commit IN VARCHAR2,
629 p_segment IN RAW,
630 p_encoding IN VARCHAR2,
631 p_sys_key IN DES3_KEY_TYPE,
632 x_segment_id OUT NOCOPY iby_security_segments.sec_segment_id%TYPE
633 )
634 IS
635 l_segment_cipher iby_security_segments.segment_cipher_text%TYPE;
636 lx_subkey_id iby_sys_security_subkeys.sec_subkey_id%TYPE;
637 lx_subkey iby_sys_security_subkeys.subkey_cipher_text%TYPE;
638 BEGIN
639
640 IBY_SECURITY_PKG.Get_Sys_Subkey
641 (FND_API.G_FALSE,p_sys_key,'Y',lx_subkey_id,lx_subkey);
642
643 l_segment_cipher :=
644 DBMS_OBFUSCATION_TOOLKIT.des3encrypt
645 (input => p_segment, key => lx_subkey,
646 which => dbms_obfuscation_toolkit.ThreeKeyMode
647 );
648
649 SELECT iby_security_segments_s.NEXTVAL INTO x_segment_id FROM DUAL;
650 INSERT INTO iby_security_segments
651 (sec_segment_id, segment_cipher_text, sec_subkey_id, encoding_scheme,
652 created_by, creation_date, last_updated_by, last_update_date,
653 last_update_login, object_version_number
654 )
655 VALUES
656 (x_segment_id, l_segment_cipher, lx_subkey_id, p_encoding,
657 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate,
658 fnd_global.login_id, 1
659 );
660
661 IF FND_API.to_Boolean(p_commit) THEN
662 COMMIT;
663 END IF;
664 END Create_Segment;
665
666 PROCEDURE Update_Segment
667 (p_commit IN VARCHAR2,
668 p_segment_id IN iby_security_segments.sec_segment_id%TYPE,
669 p_segment IN RAW,
670 p_encoding IN VARCHAR2,
671 p_sys_key IN DES3_KEY_TYPE,
672 p_subkey_cipher IN DES3_KEY_TYPE
673 )
674 IS
675 l_segment_cipher iby_security_segments.segment_cipher_text%TYPE;
676 l_subkey DES3_KEY_TYPE;
677 BEGIN
678 l_subkey :=
679 DBMS_OBFUSCATION_TOOLKIT.des3decrypt
680 (input => p_subkey_cipher, key => p_sys_key,
681 which => dbms_obfuscation_toolkit.ThreeKeyMode
682 );
683 l_segment_cipher :=
684 DBMS_OBFUSCATION_TOOLKIT.des3encrypt
685 (input => p_segment, key => l_subkey,
686 which => dbms_obfuscation_toolkit.ThreeKeyMode
687 );
688
689 UPDATE iby_security_segments
690 SET
691 segment_cipher_text = l_segment_cipher,
692 encoding_scheme = NVL(p_encoding,encoding_scheme),
693 last_updated_by = fnd_global.user_id,
694 last_update_date = SYSDATE,
695 last_update_login = fnd_global.user_id,
696 object_version_number = object_version_number + 1
697 WHERE sec_segment_id=p_segment_id;
698 END Update_Segment;
699
700 PROCEDURE Store_Credential( p_key IN VARCHAR2, x_cred OUT NOCOPY NUMBER )
701 IS
702 BEGIN
703 x_cred := fnd_gfm.one_time_use_store(1);
704 END Store_Credential;
705
706 PROCEDURE Verify_Credential
707 ( p_key IN VARCHAR2, p_cred IN NUMBER, x_verify OUT NOCOPY VARCHAR2 )
708 IS
709 BEGIN
710 x_verify := FND_API.G_FALSE;
711 IF (fnd_gfm.one_time_use_retrieve(p_cred)=1) THEN
712 x_verify := FND_API.G_TRUE;
713 END IF;
714 EXCEPTION
715 WHEN OTHERS THEN
716 x_verify := FND_API.G_FALSE;
717 END Verify_Credential;
718
719
720
721 PROCEDURE print_debuginfo(
722 p_message IN VARCHAR2,
723 p_prefix IN VARCHAR2,
724 p_msg_level IN NUMBER,
725 p_module IN VARCHAR2
726 ) IS
727
728 l_message VARCHAR2(4000);
729 l_module VARCHAR2(255);
730 BEGIN
731
732 -- DBMS_OUTPUT.PUT_LINE(substr(RPAD(p_module,55)||' : '||p_message, 0, 150));
733 -- insert into ying_debug(log_time, text) values(sysdate, p_message);
734
735 -- Debug info.
736 l_module :=SUBSTRB(p_module,1,255);
737 IF p_prefix IS NOT NULL THEN
738 l_message :=SUBSTRB(p_prefix||'-'||p_message,1,4000);
739 ELSE
740 l_message :=SUBSTRB(p_message,1,4000);
741 END IF;
742 IF p_msg_level>=fnd_log.g_current_runtime_level THEN
743
744 FND_LOG.STRING(p_msg_level,l_module,l_message);
745
746 END IF;
747
748 END print_debuginfo;
749
750 FUNCTION get_salt_version RETURN NUMBER
751 IS
752 BEGIN
753 RETURN 1;
754 END get_salt_version;
755
756 FUNCTION Get_Hash( p_text IN VARCHAR2, p_salt IN VARCHAR2 )
757 RETURN VARCHAR2
758 IS
759 l_site_salt VARCHAR2(256);
760 l_hash VARCHAR2(1024);
761 BEGIN
762 l_site_salt := RAWTOHEX(get_site_salt());
763 -- !!! WARNING: DO NOT CHANGE THE SALTING FUNCTION !!!
764 -- !!! THIS WILL CORRUPT EVERY ENTITY THAT STORES !!!
765 -- !!! A SALTED HASH VALUE (HASH_VALUE_2) !!!
766 --
767 IF (FND_API.To_Boolean(p_salt)) THEN
768 l_hash := dbms_obfuscation_toolkit.md5
769 (input_string => p_text||SUBSTR(p_text,-1));
770 ELSE
771 l_hash := dbms_obfuscation_toolkit.md5(input_string => p_text);
772 END IF;
773
774 RETURN DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => (l_hash || l_site_salt));
775 END Get_Hash;
776
777 -- bug 7228583
778 FUNCTION Get_Hash( p_text IN VARCHAR2, p_salt IN VARCHAR2, p_site_salt IN VARCHAR2)
779 RETURN VARCHAR2
780 IS
781 l_hash VARCHAR2(1024);
782 BEGIN
783 -- !!! WARNING: DO NOT CHANGE THE SALTING FUNCTION !!!
784 -- !!! THIS WILL CORRUPT EVERY ENTITY THAT STORES !!!
785 -- !!! A SALTED HASH VALUE (HASH_VALUE_2) !!!
786 --
787 IF (FND_API.To_Boolean(p_salt)) THEN
788 l_hash := dbms_obfuscation_toolkit.md5
789 (input_string => p_text||SUBSTR(p_text,-1));
790 ELSE
791 l_hash := dbms_obfuscation_toolkit.md5(input_string => p_text);
792 END IF;
793
794 RETURN DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => (l_hash || p_site_salt));
795 END Get_Hash;
796
797 FUNCTION Get_Raw_Hash( p_data IN RAW ) RETURN RAW
798 IS
799 BEGIN
800 RETURN dbms_obfuscation_toolkit.md5(input => p_data);
801 END Get_Raw_Hash;
802
803 FUNCTION Gen_Des3_Key( p_random_seed IN RAW ) RETURN RAW
804 IS
805 BEGIN
806 RETURN
807 dbms_obfuscation_toolkit.des3getkey
808 (seed => p_random_seed,
809 which => dbms_obfuscation_toolkit.ThreeKeyMode
810 );
811 END;
812
813 FUNCTION PKCS5_Pad(p_data IN RAW) RETURN RAW
814 IS
815 pad NUMBER;
816 BEGIN
817 pad := 8 - MOD(utl_raw.LENGTH(p_data),8);
818 RETURN utl_raw.concat(p_data,utl_raw.substr(PKCS5PAD,pad*(pad-1)/2+1,pad));
819 END PKCS5_Pad;
820
821 FUNCTION PKCS5_Unpad(p_data raw) RETURN RAW
822 IS
823 pad NUMBER;
824 BEGIN
825 pad := TO_NUMBER(rawtohex(utl_raw.substr(p_data, -1)), 'XX');
826 IF (pad < 1 OR pad > 8) THEN
827 return null;
828 END IF;
829
830 IF (utl_raw.compare(utl_raw.substr(PKCS5PAD, pad*(pad-1)/2+1, pad),
831 utl_raw.substr(p_data, -pad)) <> 0)
832 THEN
833 return null;
834 END IF;
835
836 RETURN utl_raw.substr(p_data, 1, utl_raw.length(p_data) - pad);
837 END PKCS5_Unpad;
838
839 /* Bug 6018583: Implementation of the sceurity around Account Option Values
840 and Transmission Values
841 The entire code written below is added for the above purpose.
842 */
843
844
845 FUNCTION masked_value_passed
846 (p_data IN VARCHAR2
847 )
848 RETURN VARCHAR2
849 IS
850 BEGIN
851 IF translate(p_data,'#*','#') IS NULL THEN
852 RETURN 'Y';
853 ELSE
854 RETURN 'N';
855 END IF;
856 END masked_value_passed;
857
858
859
860 --
861 -- Compress_Value
862 --
863
864 PROCEDURE Compress_Value
865 (p_value IN VARCHAR2,
866 p_mask_setting IN VARCHAR2,
867 p_unmask_len IN NUMBER,
868 x_compress_val OUT NOCOPY VARCHAR2,
869 x_unmask_digits OUT NOCOPY VARCHAR2
870 )
871 IS
872 l_prefix_index NUMBER;
873 l_unmask_len NUMBER;
874 l_substr_start NUMBER;
875 l_substr_stop NUMBER;
876 BEGIN
877
878 x_unmask_digits := Get_Unmasked_Data( p_value
879 , p_mask_setting
880 , p_unmask_len
881 );
882 l_unmask_len := NVL(LENGTH(x_unmask_digits),0);
883
884 -- all digits exposed; compressed number is trivial
885 IF (l_unmask_len >= LENGTH(p_value)) THEN
886 x_compress_val := NULL;
887 RETURN;
888 END IF;
889
890 IF ( (p_mask_setting = iby_security_pkg.G_MASK_POSTFIX) )
891 THEN
892 l_substr_start := l_unmask_len + 1;
893 ELSE
894 l_substr_start := 1;
895 END IF;
896
897 IF (p_mask_setting = iby_security_pkg.G_MASK_PREFIX)
898 AND (p_unmask_len>0)
899 THEN
900 l_substr_stop := GREATEST(LENGTH(p_value)-p_unmask_len,0);
901 ELSE
902 l_substr_stop := LENGTH(p_value);
903 END IF;
904
905 IF (l_substr_start < (l_substr_stop +1)) THEN
906 x_compress_val := SUBSTR(p_value,l_substr_start,
907 l_substr_stop - l_substr_start + 1);
908 ELSE
909 x_compress_val := NULL;
910 END IF;
911 END Compress_Value;
912
913 --
914 -- encrypt_field_vals
915 --
916 FUNCTION encrypt_field_vals
917 (
918 p_value IN VARCHAR2,
919 master_key_in IN DES3_KEY_TYPE,
920 p_sec_segment_id IN NUMBER,
921 p_commit IN VARCHAR2 DEFAULT 'N'
922 ) RETURN NUMBER
923 IS
924
925 lx_key_error VARCHAR2(300);
926 lx_compress_val VARCHAR2(2000);
927 lx_unmask_digits VARCHAR2(2000);
928 l_sys_key RAW(24);
929 x_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
930 l_fv_segment iby_security_segments.segment_cipher_text%TYPE;
931 l_subkey_cipher iby_sys_security_subkeys.subkey_cipher_text%TYPE;
932
933 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.encrypt_field_vals';
934
935 BEGIN
936
937 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
938
939 IF masked_value_passed(p_value) = 'Y' THEN
940 return p_sec_segment_id;
941 END IF;
942
943 -- no more used. System key passed from middle-tier
944 -- l_sys_key := Get_Sys_Key_Raw();
945
946 iby_debug_pub.add('encrypting field value',
947 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
948
949 Compress_Value ( p_value => p_value
950 , p_mask_setting => iby_security_pkg.G_MASK_ALL
951 , p_unmask_len => 0
952 , x_compress_val => lx_compress_val
953 , x_unmask_digits => lx_unmask_digits);
954
955 IF (NOT lx_compress_val IS NULL) THEN
956 l_fv_segment := UTL_RAW.CAST_TO_RAW(CONVERT(lx_compress_val,'AL32UTF8'));
957 l_fv_segment := PKCS5_PAD(l_fv_segment);
958
959 IF (p_sec_segment_id IS NULL) THEN
960 Create_Segment ( FND_API.G_FALSE
961 , l_fv_segment
962 , iby_security_pkg.G_ENCODING_UTF8_AL32
963 , master_key_in
964 , x_sec_segment_id
965 );
966 ELSE
967 BEGIN
968 SELECT sk.subkey_cipher_text
969 INTO l_subkey_cipher
970 FROM iby_sys_security_subkeys sk
971 , iby_security_segments ss
972 WHERE sk.sec_subkey_id = ss.sec_subkey_id
973 AND ss.sec_segment_id = p_sec_segment_id;
974 END;
975 Update_Segment ( FND_API.G_FALSE
976 , p_sec_segment_id
977 , l_fv_segment
978 , iby_security_pkg.G_ENCODING_UTF8_AL32
979 , master_key_in
980 , l_subkey_cipher
981 );
982
983 x_sec_segment_id := p_sec_segment_id;
984 END IF;
985 ELSE
986 DELETE FROM iby_security_segments
987 WHERE sec_segment_id = p_sec_segment_id;
988 END IF;
989
990 IF ( p_commit = 'Y' ) THEN
991 COMMIT;
992 END IF;
993
994 RETURN x_sec_segment_id;
995
996 END encrypt_field_vals;
997
998 FUNCTION encrypt_num_field_vals
999 (
1000 p_value IN NUMBER,
1001 master_key_in IN DES3_KEY_TYPE,
1002 p_sec_segment_id IN NUMBER,
1003 p_commit IN VARCHAR2 DEFAULT 'N'
1004 ) RETURN NUMBER
1005 IS
1006 l_number VARCHAR2(4000);
1007 BEGIN
1008 IF p_value = -11111 THEN
1009 return p_sec_segment_id;
1010 END IF;
1011 l_number := to_char(p_value);
1012 return encrypt_field_vals
1013 ( l_number
1014 , master_key_in
1015 , p_sec_segment_id
1016 , p_commit
1017 );
1018 END encrypt_num_field_vals;
1019
1020 --
1021 -- encrypt_date_field_vals
1022 --
1023 FUNCTION encrypt_date_field
1024 (
1025 p_value IN DATE,
1026 master_key_in IN DES3_KEY_TYPE,
1027 p_sec_segment_id IN NUMBER,
1028 p_commit IN VARCHAR2 DEFAULT 'N'
1029 ) RETURN NUMBER
1030 IS
1031
1032 l_trunc_date VARCHAR2(12);
1033 l_pad VARCHAR2(4);
1034 l_padded_data VARCHAR2(16);
1035 l_segment_id NUMBER;
1036
1037 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.encrypt_field_vals';
1038
1039 BEGIN
1040
1041 IF ( p_value IS NULL ) THEN return null; END IF;
1042
1043 l_trunc_date := TO_CHAR(p_value, G_ENCRYPTED_EXPDATE_FORMAT);
1044
1045 -- Get 2 bytes of random data and pad the date value with this
1046 -- this will make the total length as 16 hex chars
1047 -- (similar to most credit cards). This will be a good camouflage
1048 -- and also since the range of date values could be quite small,
1049 -- with the random bytes, we would prevent cipher attacks.
1050
1051 l_pad := fnd_crypto.randombytes(2);
1052 l_padded_data := l_pad || l_trunc_date;
1053 l_segment_id := encrypt_field_vals(l_padded_data, master_key_in,
1054 p_sec_segment_id, p_commit);
1055
1056 RETURN l_segment_id;
1057
1058 END encrypt_date_field;
1059
1060 FUNCTION Uncipher_Field_Value
1061 (p_segment_id IN iby_security_segments.sec_segment_id%TYPE,
1062 p_sys_key IN DES3_KEY_TYPE,
1063 p_sub_key_cipher IN iby_sys_security_subkeys.subkey_cipher_text%TYPE,
1064 p_segment_cipher IN iby_security_segments.segment_cipher_text%TYPE,
1065 p_encoding IN iby_security_segments.encoding_scheme%TYPE
1066 )
1067 RETURN VARCHAR2
1068 IS
1069 l_sub_key RAW(24);
1070 l_fv_segment iby_security_segments.segment_cipher_text%TYPE;
1071 l_decrypted_value VARCHAR2(2000);
1072 l_db_characterset VARCHAR2(2000);
1073 BEGIN
1074
1075 -- uncipher the subkey
1076 l_sub_key := get_sys_subkey(p_sys_key,p_sub_key_cipher);
1077
1078 -- uncipher the segment
1079 l_fv_segment :=
1080 dbms_obfuscation_toolkit.des3decrypt
1081 ( input => p_segment_cipher, key => l_sub_key,
1082 which => dbms_obfuscation_toolkit.ThreeKeyMode
1083 );
1084 l_fv_segment := IBY_SECURITY_PKG.PKCS5_UNPAD(l_fv_segment);
1085 BEGIN
1086 SELECT value
1087 INTO l_db_characterset
1088 FROM v$nls_parameters
1089 WHERE parameter = 'NLS_CHARACTERSET';
1090 END;
1091 l_decrypted_value := UTL_RAW.CAST_TO_VARCHAR2(CONVERT(l_fv_segment,l_db_characterset));
1092
1093 RETURN l_decrypted_value;
1094
1095 END Uncipher_Field_Value;
1096
1097 --
1098 -- decrypt_field_vals
1099 --
1100 FUNCTION decrypt_field_vals
1101 ( p_sec_segment_id IN NUMBER,
1102 master_key_in IN DES3_KEY_TYPE
1103 ) RETURN VARCHAR2
1104 IS
1105
1106 lx_key_error VARCHAR2(300);
1107 l_sys_key RAW(24);
1108 l_subkey_ciphertxt iby_sys_security_subkeys.subkey_cipher_text%TYPE;
1109 l_fv_segment iby_security_segments.segment_cipher_text%TYPE;
1110 l_encoding iby_security_segments.encoding_scheme%TYPE;
1111
1112 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.decrypt_field_vals';
1113
1114 BEGIN
1115
1116 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
1117
1118 IF (p_sec_segment_id IS NOT NULL) THEN
1119 --l_sys_key :=
1120 -- iby_security_pkg.Pad_Raw_Key( UTL_RAW.CAST_TO_RAW( p_sys_sec_key ) );
1121
1122 iby_debug_pub.add('decrypting field value',
1123 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1124
1125 BEGIN
1126 SELECT bak.subkey_cipher_text
1127 , bas.segment_cipher_text
1128 , bas.encoding_scheme
1129 INTO l_subkey_ciphertxt
1130 , l_fv_segment
1131 , l_encoding
1132 FROM iby_sys_security_subkeys bak
1133 , iby_security_segments bas
1134 WHERE bas.sec_subkey_id = bak.sec_subkey_id
1135 AND bas.sec_segment_id = p_sec_segment_id;
1136 END;
1137
1138 RETURN Uncipher_Field_Value ( p_sec_segment_id
1139 , master_key_in
1140 , l_subkey_ciphertxt
1141 , l_fv_segment
1142 , l_encoding
1143 );
1144 ELSE
1145 return NULL;
1146 END IF;
1147
1148 END decrypt_field_vals;
1149
1150 FUNCTION decrypt_num_field_vals
1151 (
1152 p_sec_segment_id IN NUMBER,
1153 master_key_in IN DES3_KEY_TYPE
1154 ) RETURN NUMBER
1155 IS
1156 l_number VARCHAR2(4000);
1157 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.decrypt_num_field_vals';
1158
1159 BEGIN
1160 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
1161
1162 l_number := decrypt_field_vals
1163 ( p_sec_segment_id,
1164 master_key_in
1165 );
1166 --The decrypted data is sensitive. Don not log it.
1167
1168 -- iby_debug_pub.add('l_number : ' || l_number || ':'|| ':',
1169 -- iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1170
1171 return to_number(l_number);
1172 EXCEPTION
1173 WHEN OTHERS THEN
1174 return null;
1175 END decrypt_num_field_vals;
1176
1177 FUNCTION decrypt_date_field
1178 (
1179 p_sec_segment_id IN NUMBER,
1180 master_key_in IN DES3_KEY_TYPE
1181 ) RETURN DATE
1182 IS
1183 l_decrypted_val VARCHAR2(16);
1184 l_field_len NUMBER;
1185 BEGIN
1186 l_decrypted_val := decrypt_field_vals(p_sec_segment_id, master_key_in);
1187 l_field_len := LENGTH(G_ENCRYPTED_EXPDATE_FORMAT);
1188 l_decrypted_val := SUBSTR(l_decrypted_val, -l_field_len, l_field_len);
1189 -- Return the last day of the month since the oracle default is first day.
1190 -- Also, card expiry date is always the last day of month.
1191 RETURN LAST_DAY(TO_DATE(l_decrypted_val, G_ENCRYPTED_EXPDATE_FORMAT));
1192 END decrypt_date_field;
1193 /*
1194 * 6018583:FP: END
1195 */
1196
1197 END IBY_SECURITY_PKG;