[Home] [Help]
PACKAGE BODY: APPS.WF_DIGITAL_SECURITY_PRIVATE
Source
1 PACKAGE Body WF_Digital_Security_Private AS
2 /* $Header: WFDSPVTB.pls 120.2 2005/09/01 09:13:04 mputhiya ship $ */
3 -----------------------------------------------------------------------------
4 -- Constants
5 -----------------------------------------------------------------------------
6
7 /* Bug 3110339: Remove constants from body
8 STAT_ERROR CONSTANT NUMBER := -1;
9 STAT_REQUESTED CONSTANT NUMBER := 100;
10 STAT_SIGNED CONSTANT NUMBER := 200;
11 STAT_VERIFIED CONSTANT NUMBER := 300;
12 STAT_AUTHORIZED CONSTANT NUMBER := 400;
13 STAT_VAL_ATTEMPTED CONSTANT NUMBER := 500;
14 STAT_VALIDATED CONSTANT NUMBER := 600;
15 STAT_REQUEST_FAILED CONSTANT NUMBER := -100;
16 STAT_SIGN_FAILED CONSTANT NUMBER := -200;
17 STAT_SIGN_CANCELLED CONSTANT NUMBER := -201;
18 STAT_VERIFY_FAILED CONSTANT NUMBER := -300;
19 STAT_AUTHORIZE_FAILED CONSTANT NUMBER := -400;
20 STAT_VALIDATE_FAILED CONSTANT NUMBER := -600;
21 */
22
23 -----------------------------------------------------------------------------
24 -- Routines
25 -------------------------------------------------------------------------------
26 -- Procedure
27 -- Create_Signature_Entry
28 --
29 -- Purpose
30 -- Creates a new row in WF_DIG_SIGS
31 --
32 -- Returns: 0 for success; -1 if not successful.
33 --
34 --
35
36 Procedure Create_Signature_Entry(
37 P_SIG_ID NUMBER,
38 P_SIG_OBJ_TYPE VARCHAR2,
39 P_SIG_OBJ_ID Varchar2,
40 P_PLAINTEXT CLOB,
41 P_REQUESTED_SIGNER_TYPE Varchar2,
42 P_REQUESTED_SIGNER_ID Varchar2,
43 P_Sig_Flavor Varchar2,
44 P_Sig_Policy Varchar2,
45 P_STATUS NUMBER,
46 P_returncode out nocopy number) is
47
48 PRAGMA AUTONOMOUS_TRANSACTION;
49
50 begin
51 insert into WF_DIG_SIGS
52 (SIG_ID, SIG_OBJ_TYPE, SIG_OBJ_ID, PLAINTEXT,
53 Requested_Signer_Type, Requested_Signer_ID,
54 Sig_Flavor, Sig_Policy,
55 STATUS, CREATION_DATE)
56 select
57 P_SIG_ID, P_SIG_OBJ_TYPE, P_SIG_OBJ_ID, P_PLAINTEXT,
58 P_Requested_Signer_Type, P_Requested_Signer_ID,
59 P_Sig_Flavor, P_Sig_Policy,
60 P_STATUS, sysdate from dual;
61
62 commit;
63
64 P_returncode := 0;
65 exception when others then P_returncode := -1;
66 rollback;
67 end;
68
69 --
70 -- Procedure
71 -- Update_Signed_Sig
72 --
73 -- Purpose
74 -- Updates row in WF_DIG_SIGS
75 --
76 -- Returns: 0 for success; -1 if not successful.
77 --
78 --
79
80 Procedure Update_Signed_Sig(
81 P_SIG_ID NUMBER,
82 P_SIGNATURE CLOB,
83 P_STATUS NUMBER,
84 P_Returncode out nocopy number) is
85
86 PRAGMA AUTONOMOUS_TRANSACTION;
87
88 begin
89 UPDATE WF_DIG_SIGS
90 SET SIGNATURE = P_SIGNATURE,
91 STATUS = P_STATUS,
92 SIGNED_DATE = sysdate
93 where SIG_ID = P_SIG_ID;
94
95 commit;
96
97 P_returncode := 0;
98 exception when others then P_returncode := -1;
99 rollback;
100 end;
101
102 --
103 -- Procedure
104 -- Update_Verified_Sig
105 --
106 -- Purpose
107 -- Updates row in WF_DIG_SIGS
108 --
109 -- Returns: 0 for success; -1 if not successful.
110 --
111 --
112
113 Procedure Update_Verified_Sig(
114 P_SIG_ID NUMBER,
115 P_CERT_ID NUMBER,
116 P_STATUS NUMBER,
117 P_Returncode out nocopy number) is
118
119 PRAGMA AUTONOMOUS_TRANSACTION;
120
121 begin
122 UPDATE WF_DIG_SIGS
123 SET CERT_ID = P_CERT_ID,
124 STATUS = P_STATUS,
125 VERIFIED_DATE = sysdate
126 where SIG_ID = P_SIG_ID;
127
128 commit;
129
130 P_returncode := 0;
131 exception when others then P_returncode := -1;
132 rollback;
133 end;
134
135 --
136 -- Procedure
137 -- Update_Validated_Sig
138 --
139 -- Purpose
140 -- Updates a row in WF_DIG_SIGS
141 --
142 -- Returns: 0 for success; -1 if not successful.
143 --
144 --
145
146 Procedure Update_Validated_Sig(
147 P_SIG_ID NUMBER,
148 P_STATUS NUMBER,
149 P_Returncode out nocopy number) is
150
151 PRAGMA AUTONOMOUS_TRANSACTION;
152
153 begin
154 UPDATE WF_DIG_SIGS
155 SET STATUS = P_STATUS,
156 Last_Validation_Attempt = sysdate,
157 Validated_complete_date = Decode(P_STATUS,
158 WF_DIGITAL_SECURITY_PRIVATE.STAT_VALIDATED, sysdate, null)
159 where SIG_ID = P_SIG_ID;
160
161 commit;
162
163 P_returncode := 0;
164 exception when others then P_returncode := -1;
165 rollback;
166 end;
167
168 --
169 -- Procedure
170 -- Update_Sig_Error
171 --
172 -- Purpose
173 -- Updates a row in WF_DIG_SIGS
174 --
175 -- Returns: 0 for success; -1 if not successful.
176 --
177 --
178
179 Procedure Update_Sig_Error(
180 P_SIG_ID NUMBER,
181 P_STATUS NUMBER,
182 P_ERRBUF VARCHAR2,
183 P_Returncode out nocopy number) is
184
185 PRAGMA AUTONOMOUS_TRANSACTION;
186
187 begin
188 UPDATE WF_DIG_SIGS
189 SET STATUS = P_STATUS,
190 ERRBUF = to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') || ': ' || P_ERRBUF,
191 ERRSTACK = DECODE(ERRSTACK, NULL, ERRBUF,
192 ERRBUF || WF_CORE.NEWLINE || ERRSTACK)
193 where SIG_ID = P_SIG_ID;
194
195 commit;
196
197 P_returncode := 0;
198 exception when others then P_returncode := -1;
199 rollback;
200 end;
201
202 --
203 -- Function
204 -- PSIG_Cert_to_ID
205 --
206 -- Purpose
207 -- Registers a PSIG cert if it isn't already there.
208 --
209 -- Returns: cert ID or -1 if not successful.
210 --
211 --
212
213 Function PSIG_Cert_To_ID(P_USER VARCHAR2) return number is
214
215 PRAGMA AUTONOMOUS_TRANSACTION;
216
217 MyCert_ID Number;
218
219 begin
220 begin
221 select cert_id
222 into mycert_id
223 from wf_dig_certs
224 where cert_type = 'PSIG'
225 and fingerprint = P_User;
226 exception
227 when no_data_found then mycert_id := -1;
228 when others then raise;
229 end;
230
231 if (mycert_id = -1) then
232
233 insert into wf_dig_certs (cert,cert_id,cert_type,parent_cert_id,
234 owner_id, owner_domain, valid, sot_flag, intermediate_flag,
235 fingerprint, expire)
236 (select P_User, wf_dig_certs_s.nextval,'PSIG', wf_dig_certs_s.currval,
237 U.User_ID, 'U', 'Y', 'Y', 'N', P_User, null
238 from fnd_user U
239 where user_name = P_User
240 and not exists
241 (select cert_id
242 from wf_dig_certs
243 where cert_type = 'PSIG'
244 and fingerprint = P_User)
245 );
246
247 select cert_id
248 into mycert_id
249 from wf_dig_certs
250 where cert_type = 'PSIG'
251 and fingerprint = P_User;
252 end if;
253
254 commit;
255
256 return MyCert_ID;
257
258 exception when others then
259 rollback;
260 return -1;
261 end;
262
263 --
264 -- Procedure
265 -- Get_Requested_Signer
266 --
267 -- Purpose
268 -- Gets Requested Signer info for a sig.
269 --
270 -- Returns: -1 (for ID) if not successful.
271 --
272 --
273
274 Procedure Get_Requested_Signer( P_SIGNATURE_ID in Number,
275 P_reqSignerType out nocopy Varchar2,
276 P_reqSignerID out nocopy Varchar2) is
277
278 begin
279 Select Requested_Signer_Type, Requested_Signer_ID
280 into P_reqSignerType, P_reqSignerID
281 From WF_Dig_Sigs
282 Where P_Signature_ID = Sig_ID;
283 Exception
284 when others then
285 P_reqSignerType := ' ';
286 P_reqSignerID := '-1';
287 End;
288
289 --
290 -- Procedure
291 -- Authorize_Signature
292 --
293 -- Purpose
294 -- Determines if actual signer is authorized to sign for requested
295 -- signer. E.g. Does the user have the desired responsibility. Updates
296 -- the WF_DIG_SIGS table.
297 --
298 -- Returns: .
299 -- Outcome = either "AUTHORIZED" or "FAILED".
300 --
301 --
302
303 Procedure Authorize_Signature(P_SIGNATURE_ID In Number,
304 P_OUTCOME out nocopy Varchar2) is
305
306 PRAGMA AUTONOMOUS_TRANSACTION;
307
308 MyStatus number := WF_DIGITAL_SECURITY_PRIVATE.STAT_AUTHORIZED;
309 kount number := -1;
310 act_cert_id number;
311 act_type varchar2(30);
312 act_id varchar2(320);
313 act_emp_id varchar2(320);
314 req_type varchar2(30);
315 req_id varchar2(320);
316 sig_date date;
317
318 sec_grp_id number;
319 resp_app_id number;
320 resp_id number;
321 str_buf varchar2(30);
322 idx number;
323
324 begin
325 /* get info from sig table */
326 Select Cert_ID, Requested_Signer_Type, Requested_Signer_ID, Signed_Date
327 into act_cert_id, req_type, req_id, sig_date
328 From WF_Dig_Sigs S
329 where SIG_ID = P_SIGNATURE_ID;
330
331 /* get info from cert table */
332 Select Owner_domain, Owner_ID
333 into act_type, act_id
334 From WF_Dig_Certs
335 where Cert_ID = act_cert_id;
336
337 /* messy code to determine authorization. */
338 if (req_type = 'W') then
339 if (act_type = 'U') then
340 Select EMPLOYEE_ID
341 into act_emp_id
342 from fnd_user
343 where USER_ID = act_id;
344
345 Select count(*)
346 into kount
347 From WF_User_Roles R
348 where R.USER_ORIG_SYSTEM in ('FND_USR', 'PER')
349 and R.USER_ORIG_SYSTEM_ID = to_number(
350 decode(R.User_Orig_System, 'PER', act_emp_id,
351 'FND_USR', act_id,
352 '-999'))
353 and R.ROLE_NAME = req_id;
354
355 if (kount > 0) then
356 mystatus := WF_DIGITAL_SECURITY_PRIVATE.STAT_AUTHORIZED;
357 else
358 mystatus := WF_DIGITAL_SECURITY_PRIVATE.STAT_AUTHORIZE_FAILED;
359 end if;
360 else
361 mystatus := WF_DIGITAL_SECURITY_PRIVATE.STAT_AUTHORIZE_FAILED;
362 end if;
363 elsif (req_type = 'U') then
364 if ((act_type = 'U') and (act_id = req_id)) then
365 mystatus := WF_DIGITAL_SECURITY_PRIVATE.STAT_AUTHORIZED;
366 else
367 mystatus := WF_DIGITAL_SECURITY_PRIVATE.STAT_AUTHORIZE_FAILED;
368 end if;
369 elsif (req_type = 'R') then
370 if (act_type = 'U') then
371 /* Breakdown Req_id into secgrpid,respappid,respid */
372 idx := instr(req_id,':');
373 sec_grp_id := to_number(substr(req_id, 1, idx - 1));
374 str_buf := substr(req_id, idx + 1);
375 idx := instr(str_buf, ':');
376 resp_app_id := to_number(substr(str_buf, 1, idx - 1));
377 resp_id := to_number(substr(str_buf, idx + 1));
378
379 Select count(*)
380 into kount
381 From Fnd_User_Resp_Groups R
382 where R.USER_ID = resp_id
383 and R.RESPONSIBILITY_APPLICATION_ID = resp_app_id
384 and R.SECURITY_GROUP_ID = sec_grp_id
385 and R.Responsibility_ID = to_number(req_id)
386 and NVL(R.Start_date, sig_date) <= sig_date
387 and NVL(R.End_date, sig_date) >= sig_date;
388
389 if (kount > 0) then
390 mystatus := WF_DIGITAL_SECURITY_PRIVATE.STAT_AUTHORIZED;
391 else
392 mystatus := WF_DIGITAL_SECURITY_PRIVATE.STAT_AUTHORIZE_FAILED;
393 end if;
394 else
395 mystatus := WF_DIGITAL_SECURITY_PRIVATE.STAT_AUTHORIZE_FAILED;
396 end if;
397 else
398 mystatus := WF_DIGITAL_SECURITY_PRIVATE.STAT_AUTHORIZE_FAILED;
399 end if;
400
401 /* update the table */
402 UPDATE WF_DIG_SIGS
403 SET STATUS = MyStatus
404 where SIG_ID = P_SIGNATURE_ID;
405
406 /* let calling code know what happened */
407 if (MyStatus = WF_DIGITAL_SECURITY_PRIVATE.STAT_AUTHORIZED) then
408 P_Outcome := 'AUTHORIZED';
409 else
410 P_Outcome := 'FAILED';
411 end if;
412
413 commit;
414
415 exception when others then P_Outcome := 'FAILED';
416
417 UPDATE WF_DIG_SIGS
418 SET STATUS = WF_DIGITAL_SECURITY_PRIVATE.STAT_AUTHORIZE_FAILED
419 where SIG_ID = P_SIGNATURE_ID;
420 commit;
421 end;
422
423 --
424 -- Procedure
425 -- Get_SPI_Info
426 --
427 -- Purpose
428 -- Gets SPI info for a sig ID.
429 --
430 -- Returns: nulls if not successful.
431 --
432 --
433
434 Procedure Get_SPI_Info( P_SIGNATURE_ID in Number,
435 P_Flavor out nocopy Varchar2,
436 P_BSR out nocopy Varchar2,
437 P_Verifier out nocopy Varchar2,
438 P_Validator out nocopy Varchar2,
439 P_CertMapper out nocopy Varchar2,
440 P_Validator_Store out nocopy Varchar2,
441 P_Validation_Mode out nocopy Varchar2,
442 P_Signature_Format out nocopy Varchar2,
443 P_Signature_Mode out nocopy Varchar2) is
444 begin
445 Select Sig_Flavor
446 into P_Flavor
447 From WF_Dig_Sigs
448 Where P_Signature_ID = Sig_ID;
449
450 Get_SPI_Info(P_Flavor, P_BSR, P_Verifier, P_Validator, P_CertMapper,
451 P_Validator_Store, P_Validation_Mode,
452 P_Signature_Format, P_Signature_Mode);
453
454 Exception
455 when others then
456 P_Flavor := null;
457 P_BSR := null;
458 P_Verifier := null;
459 P_Validator := null;
460 P_CertMapper := null;
461 P_Validator_Store := null;
462 P_Validation_Mode := null;
463 P_Signature_Format := null;
464 P_Signature_Mode := null;
465 End;
466
467 --
468 -- Procedure
469 -- Get_SPI_Info
470 --
471 -- Purpose
472 -- Gets SPI info for a Flavor.
473 --
474 -- Returns: nulls if not successful.
475 --
476 --
477
478 Procedure Get_SPI_Info( P_Flavor In Varchar2,
479 P_BSR out nocopy Varchar2,
480 P_Verifier out nocopy Varchar2,
481 P_Validator out nocopy Varchar2,
482 P_CertMapper out nocopy Varchar2,
483 P_Validator_Store out nocopy Varchar2,
484 P_Validation_Mode out nocopy Varchar2,
485 P_Signature_Format out nocopy Varchar2,
486 P_Signature_Mode out nocopy Varchar2) is
487 begin
488 Select BSR_SPI, Verify_SPI, Validate_SPI, Cert_Mapper, Validator_Store,
489 Validation_Mode, Signature_Format, Signature_Mode
490 into P_BSR, P_Verifier, P_Validator, P_CertMapper, P_Validator_Store,
491 P_Validation_Mode, P_Signature_Format, P_Signature_Mode
492 From WF_Dig_Sig_SPI_Flavors
493 Where P_Flavor = Flavor;
494
495 Exception
496 when others then
497 P_BSR := null;
498 P_Verifier := null;
499 P_Validator := null;
500 P_CertMapper := null;
501 P_Validator_Store := null;
502 P_Validation_Mode := null;
503 P_Signature_Format := null;
507
504 P_Signature_Mode := null;
505 End;
506
508 --
509 -- Function
510 -- Get_Next_Sig_ID
511 --
512 -- Purpose
513 -- Yanks an ID off of the sequence WF_DIG_SIGS_S
514 --
515 -- Returns: ID or -1 if not successful.
516 --
517 --
518
519 Function Get_Next_Sig_ID return number is
520 PRAGMA AUTONOMOUS_TRANSACTION;
521
522 nextID number := -1;
523
524 begin
525 begin
526 SELECT WF_DIG_SIGS_S.nextval
527 into nextID
528 from dual;
529 exception when others then null;
530 end;
531
532 return nextID;
533
534 end;
535
536 -- Procedure
537 -- UPDATE_CRL_URL
538 --
539 -- Purpose
540 -- update the crl url of the ca. if the same url is found then no updates
541 -- are made
542 --
543 --
544 Procedure UPDATE_CRL_URL(p_ca_name varchar2,p_crl_url varchar2) as
545
546 v_ca_name varchar2(255);
547 v_crl_url varchar2(255);
548 BEGIN
549
550 --if the crl url is not null then do the operations
551 if(p_crl_url is not null) then
552 --check whether the corresponding CA name and CA_URL exists
553 SELECT ca_name, ca_url
554 INTO v_ca_name, v_crl_url
555 FROM wf_dig_cas
556 WHERE ca_name = p_ca_name
557 and ca_url = p_crl_url;
558 END if;
559
560 --if url exists do nothing. else insert an entry
561 EXCEPTION
562 WHEN NO_DATA_FOUND THEN
563 INSERT INTO wf_dig_cas (ca_name,ca_url)
564 VALUES(p_ca_name,p_crl_url);
565 commit;
566 end;
567
568 --
569 --Bug No#3062359
570 --Function
571 -- Upload_Certificate
572 --
573 -- Purpose
574 -- Upload the given certificate data to the table
575 --
576 --Returns : ID or -1 if not successful
577 --
578 Function Upload_X509_Certificate(
579 p_cert clob,
580 p_cert_type varchar2,
581 p_parent_cert_id number,
582 p_owner_id varchar2,
583 p_owner_domain varchar2,
584 p_valid varchar2,
585 p_sot_flag varchar2,
586 p_intermediate_flag varchar2,
587 p_fingerprint varchar2,
588 p_expire date,
589 p_security_group_id varchar2,
590 p_subjectdn varchar2,
591 p_issuer varchar2,
592 p_crl_url varchar2
593 )
594 return number
595 as
596
597 seqVal number;
598 v_fingerprint varchar2(100);
599 existing_certid number;
600 v_user_id varchar2(255);
601 existing_userid varchar2(255);
602
603 BEGIN
604
605 --If the owner_id is 'CA' then set the user_id as CA itself since
606 --this is a CA certificate and may not contain the corresponding user
607
608 if (p_owner_id = 'CA') then
609 v_user_id := p_issuer;
610 UPDATE_CRL_URL(p_issuer,p_crl_url);
611 else
612 -- get the user_id corresponding to the fnd_user. If the user_name
613 -- does not exist, then it throws a no_data_found exception, which
614 -- is captured and -2 is returned
615 select user_id into v_user_id
616 from fnd_user
617 where user_name = p_owner_id;
618 end if;
619
620 -- check whether the certificate exists
621 existing_certid := X509_Cert_To_ID(P_CERT,P_FINGERPRINT);
622
623 -- if certificate already exists then check whether the certificate
624 -- is associated with the same user .if it is , then return back the
625 -- existing cert_id else return -1
626 if (existing_certid <> -1) then
627 SELECT owner_id
628 INTO existing_userid
629 FROM wf_dig_certs
630 WHERE cert_id = existing_certid;
631
632 if (existing_userid = v_user_id) then
633 return existing_certid;
634 else
635 return -1;
636 end if;
637 end if;
638
639 --get the next value from the sequence for the cert_id
640 SELECT wf_dig_certs_s.nextval
641 INTO seqVal
642 FROM DUAL;
643
644 --insert the certificate into the table
645 insert into WF_DIG_CERTS(CERT, CERT_ID, CERT_TYPE, PARENT_CERT_ID,
646 OWNER_ID, OWNER_DOMAIN, VALID, SOT_FLAG,
647 INTERMEDIATE_FLAG, FINGERPRINT, EXPIRE,
648 SECURITY_GROUP_ID, SUBJECTDN)
649 values( p_cert, seqVal, p_cert_type, p_parent_cert_id,
650 v_user_id, p_owner_domain, p_valid, p_sot_flag,
651 p_intermediate_flag, p_fingerprint, p_expire,
652 p_security_group_id, p_subjectdn);
653 return seqVal;
654
655 exception
656 when NO_DATA_FOUND then
657 return -2;
658 when others then
659 raise;
660
661 END;
662
663 --
664 --Bug No#3062359
665 -- Function
669 -- get a certificate from the given id
666 -- X509_ID_To_Cert
667 --
668 -- Purpose
670 --
671 -- Returns: certificate if certificate exists for the id
672 --
673 --
674 Function X509_ID_To_Cert(
675 p_cert_id number)
676 return CLOB
677 as
678 v_certificate CLOB;
679 begin
680
681 select
682 CERT into v_certificate
683 from
684 WF_DIG_CERTS
685 where CERT_TYPE='X509' and CERT_ID=p_cert_id;
686
687 return v_certificate;
688
689 end;
690 --
691 --Bug No#3062359
692 -- Function
693 -- X509_Cert_To_ID
694 --
695 -- Purpose
696 -- Gets the ID of the certificate of given fingerprint and certificate data
697 --
698 -- Returns:
699 -- ID if matching certificate found, -1 otherwise
700 --
701 Function X509_Cert_To_ID(P_Certificate clob, P_Fingerprint varchar2)
702 return number as
703
704 --cursor for getting all the certificates with the given fingerprint
705 cursor cert_cursor is
706 select CERT_ID, CERT
707 from WF_DIG_CERTS
708 where CERT_TYPE = 'X509'
709 and FINGERPRINT = P_Fingerprint;
710
711 V_Certificate CLOB;
712 V_Cert_ID Number;
713 I Number;
714
715 Begin
716 open cert_cursor;
717
718 --loop until matching certificate found or the cursor is exhausted
719 loop
720 fetch cert_cursor into V_Cert_ID, V_Certificate;
721 exit when Cert_Cursor%NOTFOUND;
722
723 -- Compare the obtained certificate with the given certificate
724 I := DBMS_LOB.Compare(V_Certificate, P_Certificate);
725
726 --if comparison is success return the certificate id
727 if (i = 0) then
728 return V_Cert_ID;
729 end if;
730 end loop;
731
732 close cert_cursor;
733
734 --if no certificate found return -1
735 return -1;
736 end;
737
738 -- Function
739 -- Store_CRL
740 --
741 -- Purpose
742 -- Stores the given CRL into WF_Dig_Crls. If the CRL already exists
743 -- then the crl_id of the existing CRL is returned. Otherwise, the new
744 -- CRL is stored and the CRL_ID is returned
745 --
746 -- Returns
747 -- CRL_ID
748 --
749
750 Function Store_CRL (P_validation_Mode In Number,
751 P_Issuer In Varchar2,
752 P_Toi In Date,
753 P_Ton In Date,
754 P_CRL In CLOB) return number AS
755
756 /* Cursor for getting the values from CRL table */
757 CURSOR CRL_Cursor IS
758 SELECT CRL_ID, CRL_Data
759 FROM WF_Dig_CRLS
760 WHERE Issue_Date = P_Toi
761 and Issuer = P_Issuer
762 and Next_Issue_Date = P_Ton;
763
764 V_SeqVal Number;
765 V_CRL Clob;
766 V_CRLID Number;
767
768 BEGIN
769
770 open crl_cursor;
771 loop
772 /* get the crlid and crl value, exit when no value found. */
773 fetch crl_cursor into v_crlid,v_crl;
774 exit when crl_cursor%NOTFOUND;
775
776 /* compare the value of the input crl and crl obtained from table
777 if they are the same return the existing CRL_ID */
778 if (dbms_lob.compare(V_CRL, P_CRL) = 0) then
779 return V_CRLID;
780 end if;
781 end loop;
782 close crl_cursor;
783
784 /* No match found: Get the next sequence number */
785 SELECT WF_DIG_SIGS_S.nextval into v_seqVal from dual;
786
787 /* insert the data into the table */
788 Insert Into WF_DIG_CRLS
789 (CRL_ID, ISSUE_DATE, CRL_DATA, ISSUER, NEXT_ISSUE_DATE)
790 Values (V_SeqVal, P_Toi, P_Crl, P_Issuer, P_Ton);
791
792 Return V_SeqVal;
793
794 EXCEPTION
795 when others then
796 raise;
797 end;
798
799 --
800 -- Procedure
801 -- GetVerifyData
802 --
803 -- Purpose
804 -- Finds plaintext, Signature for a given Sig ID
805 --
806
807 Procedure GetVerifyData (SigID NUMBER,
808 PText out nocopy Clob,
809 Sig out nocopy Clob) is
810
811 begin
812 Select Plaintext, Signature
813 into PText, Sig
814 from wf_dig_sigs
815 where Sig_Id = SigID;
816
817 exception
818 when others then
819 Sig := Null;
820 PText := Null;
821 end;
822
823 --
824 -- Procedure
825 -- Purge_Signature_By_Sig_ID
826 --
827 -- Purpose
828 -- Removes Signature for a given Signature ID
829 --
830
831 Procedure Purge_Signature_By_Sig_ID(SigID NUMBER) is
832
833 begin
834 delete from wf_dig_sigs
835 where Sig_ID = SigID;
836 end;
837
838 --
839 -- Procedure
840 -- Purge_Signature_By_Obj_ID
841 --
842 -- Purpose
843 -- Removes Signature for a given Object ID
844 --
845
846 Procedure Purge_Signature_By_Obj_ID(Obj_Type varchar2, Obj_ID varchar2) is
847
848 begin
849 delete from wf_dig_sigs
850 where SIG_OBJ_TYPE = Obj_Type
851 and SIG_OBJ_ID =Obj_ID;
852 end;
853
854 --
855 -- Procedure
856 -- Purge_Signature_By_Obj_ID
857 --
858 -- Purpose
859 -- Removes Signature for a given set of object ids
860 --
861
862 Procedure Purge_Signature_By_Obj_ID(Obj_Type varchar2,
863 Obj_IDs objid_tab_type) is
864
865 begin
866 IF (Obj_IDs.count >0) then
867 FORALL j IN Obj_IDs.FIRST..Obj_IDs.LAST
868 delete from wf_dig_sigs
869 where SIG_OBJ_TYPE = Obj_Type
870 and SIG_OBJ_ID =Obj_IDs(j);
871 END IF;
872 end;
873
874
875 END WF_Digital_Security_Private;