1 / 46

Moving From Social Security Numbers Rickey Creel and Phillip Brooks

Moving From Social Security Numbers Rickey Creel and Phillip Brooks. The following code is provided "as-is" with no warranty, guarantee or any other promise. Why When What How Questions. Why.

maja
Download Presentation

Moving From Social Security Numbers Rickey Creel and Phillip Brooks

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Moving From Social Security Numbers Rickey Creel and Phillip Brooks The following code is provided "as-is" with no warranty, guarantee or any other promise.

  2. Why • When • What • How • Questions

  3. Why

  4. The Federal Trade Commission (FTC) requires that all financial institutions establish policies and procedures for safeguarding customer financial information. This mandate requires that Educational Entities, as financial institutions, must comply with the Gramm-Leach-Bliley Act (GLBA). This legislation is composed of several parts, including: the Privacy Rule and the Safeguards Rule. The FTC has ruled that being in compliance with the Family Educational Rights and Privacy Act (FERPA) satisfies the privacy requirement of the GLBA. However, there is no similar exception for the Safeguards Rule. Meridian Community College must comply with the Safeguards Rule. Why

  5. Ensure the security and confidentiality of customer records and information Protect against any anticipated threats to the security or integrity of such records Protect against unauthorized access to, or use of, such records or information that could result in substantial harm or inconvenience to any customer Why

  6. When

  7. When • Institutions must implement an information security program no later than May 23, 2003

  8. What • Social Security Numbers • Employee applications, transcripts, copies of driver’s licenses and social security cards • Employee contracts • Credit card forms • Class rolls, attendance sheets

  9. What • Test results • Discipline records • Housing records • Transcripts and applications • Student statements • Credit card requests • Income and tax documents

  10. What • Birth certificates, marriage licenses • Criminal background check data • Drug screen results • Child abuse registry checks and finger print cards

  11. Problems • Physical security – many areas which have access to critical information are open to the public and not secured by doors • Student workers • Shredding access is limited • Open verbal exchange of restricted information

  12. Problems • PC’s with access to Banner left unattended • Social Security numbers are used for ID

  13. How

  14. How • Employee training • Provide adequate shredders in all areas • Change ID to generated ID

  15. Problems

  16. Problems • Admissions

  17. Problems • ID System

  18. Problems • Payroll

  19. Problems • Meal Plans

  20. Problems • Library System

  21. Problems • Records

  22. Problems • When to make the change

  23. Spriden ID Change

  24. Spriden ID Change • Change Current Social Security Number ID’s to Portal ID’s

  25. Spriden ID Change • Change Current Social Security Number ID’s to Portal ID’s • Change non Social security ID’s to a generated ID’s

  26. Spriden ID Change • Change Current Social Security Number ID’s to Portal ID’s • Change non Social security ID’s to a generated ID’s • Convert Current Generated ID’s from “@” ID’s to “M” ID’s

  27. Spriden ID Change • Change Current Social Security Number ID’s to Portal ID’s • Change non Social security ID’s to a generated ID’s • Convert Current Generated ID’s from “@” ID’s to “M” ID’s • Update Banner to use a “M” for generated ID’s in the Future

  28. Spriden ID Change • Change Current Social Security Number ID’s to Portal ID’s • Change non Social security ID’s to a generated ID’s • Convert Current Generated ID’s from “@” ID’s to “M” ID’s • Update Banner to use a “M” for generated ID’s in the Future • Create Triggers

  29. Spriden ID Change • Change Current Social Security Number ID’s to Portal ID’s • Change non Social security ID’s to a generated ID’s • Convert Current Generated ID’s from “@” ID’s to “M” ID’s • Update Banner to use a “M” for generated ID’s in the Future • Create Triggers • Banner Examples

  30. Spriden ID Change • Change Current Social Security Number ID’s to Portal ID’s • Change non Social security ID’s to a generated ID’s • Convert Current Generated ID’s from “@” ID’s to “M” ID’s • Update Banner to use a “M” for generated ID’s in the Future • Create Triggers • Banner Examples • Change form for persons not receiving a Portal ID

  31. Change Current Social Security Number ID’s to Portal ID’s

  32. The following code is provided "as-is" with no warranty, guarantee or any other promise. • DECLAREv_stu_pidm gobtpac.gobtpac_pidm%TYPE;v_stu_ext_id gobtpac.gobtpac_external_user%TYPE;v_stu_spr_id spriden.spriden_id%TYPE;cursor W_stuid ISSELECT A.GOBTPAC_PIDM, UPPER(substr(a.gobtpac_external_user,1,9)), spriden_idFROM gobtpac A, spridenWHERE A.GOBTPAC_PIDM = spriden_pidmAND spriden_id <> UPPER(substr(a.gobtpac_external_user,1,9))AND spriden_change_ind ISNULLAND (spriden_id = &upd_spriden_idOR &upd_spriden_id = 'ALL')ORDERBY spriden_id;BEGINOPEN W_stuid;LOOPFETCH W_stuid INTO v_stu_pidm, v_stu_ext_id, v_stu_spr_id;INSERTINTO spriden (SELECT cpy.spriden_pidm, v_stu_ext_id, cpy.spriden_last_name, cpy.spriden_first_name, cpy.spriden_mi,NULL, cpy.spriden_entity_ind,SYSDATE, 'IDCONVCP', 'SPAIDEN', cpy.spriden_search_last_name, cpy.spriden_search_first_name, cpy.spriden_search_mi, cpy.spriden_soundex_last_name, cpy.spriden_soundex_first_name, cpy.spriden_ntyp_code, 'idconvcp',SYSDATE, cpy.spriden_data_origin FROM spriden cpywhere cpy.spriden_pidm = v_stu_pidmAND cpy.spriden_change_ind ISNULL);COMMIT;update spriden a set a.spriden_change_ind = 'I'where a.spriden_pidm = v_stu_pidmAND a.spriden_id = v_stu_spr_idAND spriden_change_ind ISNULL;COMMIT;EXITWHEN W_stuid%NOTFOUND;ENDLOOP; -- wstuidCLOSE W_stuid;COMMIT;END;

  33. Change non Social security ID’s to a generated ID’s

  34. The following code is provided "as-is" with no warranty, guarantee or any other promise. • DECLAREv_stu_pidm spriden.spriden_pidm%TYPE;v_stu_ext_id spriden.spriden_id%TYPE;v_stu_spr_id spriden.spriden_id%TYPE;cursor W_stuid ISSELECT a.spriden_pidm, a.spriden_idFROM spriden aWHERE substr(a.spriden_id,1,1) IN ('1','2','3','4','5','6','7','8','9','0',' ')AND spriden_change_ind ISNULLAND (spriden_id = &upd_spriden_idOR &upd_spriden_id = 'ALL')ORDERBY a.spriden_id;cursor W_gen_id ISSELECT 'M'||lpad(seqt.sobseqn_maxseqno + 1,8,0) INTO v_stu_ext_idFROM sobseqn seqtWHERE seqt.sobseqn_function = 'ID';BEGINLOOPOPEN W_stuid; FETCH W_stuid INTO v_stu_pidm, v_stu_spr_id;EXITWHEN W_stuid%NOTFOUND;OPEN w_gen_id;FETCH W_gen_id INTO v_stu_ext_id;CLOSE w_gen_id;UPDATE sobseqn seqt2SET seqt2.sobseqn_maxseqno = seqt2.sobseqn_maxseqno + 1WHERE seqt2.sobseqn_function = 'ID';COMMIT;INSERTINTO spriden (SELECT cpy.spriden_pidm, v_stu_ext_id, cpy.spriden_last_name, cpy.spriden_first_name, cpy.spriden_mi,NULL, cpy.spriden_entity_ind,SYSDATE, 'IDCONVCP', 'SPAIDEN', cpy.spriden_search_last_name, cpy.spriden_search_first_name, cpy.spriden_search_mi, cpy.spriden_soundex_last_name, cpy.spriden_soundex_first_name, cpy.spriden_ntyp_code, 'idconvcp',SYSDATE, cpy.spriden_data_origin FROM spriden cpywhere cpy.spriden_pidm = v_stu_pidmAND cpy.spriden_change_ind ISNULL);COMMIT;update spriden a set a.spriden_change_ind = 'I'where a.spriden_pidm = v_stu_pidmAND a.spriden_id = v_stu_spr_idAND spriden_change_ind ISNULL;COMMIT;EXITWHEN W_stuid%NOTFOUND;CLOSE W_stuid;ENDLOOP; -- wstuidEND;

  35. Convert Current Generated ID’s from “@” ID’s to “M” ID’s

  36. The following code is provided "as-is" with no warranty, guarantee or any other promise. • DECLAREv_stu_pidm gobtpac.gobtpac_pidm%TYPE;v_stu_ext_id spriden.spriden_id%TYPE;v_stu_spr_id spriden.spriden_id%TYPE;cursor W_stuid ISSELECT a.spriden_pidm, a.spriden_id, 'M'||SUBSTR(A.SPRIDEN_ID,2,8)FROM spriden aWHERE substr(a.spriden_id,1,1) = '@'AND spriden_change_ind ISNULLAND (spriden_id = &upd_spriden_idOR &upd_spriden_id = 'ALL');BEGINOPEN W_stuid; LOOPFETCH W_stuid INTO v_stu_pidm, v_stu_spr_id, v_stu_ext_id;EXITWHEN W_stuid%NOTFOUND;INSERTINTO spriden (SELECT cpy.spriden_pidm, 'M'||substr(v_stu_spr_id,2,8), cpy.spriden_last_name, cpy.spriden_first_name, cpy.spriden_mi,NULL, cpy.spriden_entity_ind,SYSDATE, 'IDCONVCP', 'SPAIDEN', cpy.spriden_search_last_name, cpy.spriden_search_first_name, cpy.spriden_search_mi, cpy.spriden_soundex_last_name, cpy.spriden_soundex_first_name, cpy.spriden_ntyp_code, 'idconvcp',SYSDATE, cpy.spriden_data_origin FROM spriden cpywhere cpy.spriden_pidm = v_stu_pidmAND cpy.spriden_change_ind ISNULL);COMMIT;update spriden a set a.spriden_change_ind = 'I'where a.spriden_pidm = v_stu_pidmAND a.spriden_id = v_stu_spr_idAND spriden_change_ind ISNULL;COMMIT;EXITWHEN W_stuid%NOTFOUND;ENDLOOP; -- wstuidCLOSE W_stuid;END;

  37. Update Banner to use a “M” for generated ID’s in the Future The following code is provided "as-is" with no warranty, guarantee or any other promise. • UPDATE sobseqn seqtSET seqt.sobseqn_seqno_prefix = 'M'WHERE seqt.sobseqn_function = 'ID'

  38. Create Triggers • Trigger to process a newly created Portal ID’s • Trigger to process updates to a Portal ID’s

  39. Trigger to process a newly created Portal ID’s The following code is provided "as-is" with no warranty, guarantee or any other promise. • Createorreplacetrigger "GENERAL".GOBTPAC_UPDATE_SPRIDEN_IDafterinserton GOBTPAC foreachrowdeclare-- local variables hereBEGINIF :new.gobtpac_external_user ISNOTNULLTHENINSERTINTO SATURN.spriden (SELECT cpy.spriden_pidm, SUBSTR(upper(:new.gobtpac_external_user),1,9), cpy.spriden_last_name, cpy.spriden_first_name, cpy.spriden_mi,NULL, cpy.spriden_entity_ind,SYSDATE, 'ID_GOBTPAC', 'GOATPAD', cpy.spriden_search_last_name, cpy.spriden_search_first_name, cpy.spriden_search_mi, cpy.spriden_soundex_last_name, cpy.spriden_soundex_first_name, cpy.spriden_ntyp_code, 'ID_GOBTPAC',SYSDATE, cpy.spriden_data_origin FROM SATURN.spriden cpywhere cpy.spriden_pidm = :NEW.GOBTPAC_pidmAND cpy.spriden_change_ind ISNULL);update SATURN.spriden a set a.spriden_change_ind = 'I'where a.spriden_pidm = :NEW.GOBTPAC_pidmAND a.spriden_id <> SUBSTR(upper(:new.gobtpac_external_user),1,9)AND spriden_change_ind ISNULL;ENDIF;end GOBTPAC_insert_to_goremal;

  40. Trigger to process updates to a Portal ID’s The following code is provided "as-is" with no warranty, guarantee or any other promise. • createorreplacetrigger "GENERAL".GOBTPAC_UPDATE_SPRIDEN_ID2afterUPDATEon GOBTPAC foreachrowdeclare-- local variables hereBEGINIF :new.gobtpac_external_user ISNOTNULLAND :new.gobtpac_external_user <> :old.gobtpac_external_user THENINSERTINTO spriden (SELECT cpy.spriden_pidm, SUBSTR(upper(:new.gobtpac_external_user),1,9), cpy.spriden_last_name, cpy.spriden_first_name, cpy.spriden_mi,NULL, cpy.spriden_entity_ind,SYSDATE, 'ID_GOBTPAC', 'GOATPAD', cpy.spriden_search_last_name, cpy.spriden_search_first_name, cpy.spriden_search_mi, cpy.spriden_soundex_last_name, cpy.spriden_soundex_first_name, cpy.spriden_ntyp_code, 'ID_GOBTPAC',SYSDATE, cpy.spriden_data_origin FROM spriden cpywhere cpy.spriden_pidm = :NEW.GOBTPAC_pidmAND cpy.spriden_change_ind ISNULL);update spriden a set a.spriden_change_ind = 'I'where a.spriden_pidm = :NEW.GOBTPAC_pidmAND a.spriden_id <> SUBSTR(upper(:new.gobtpac_external_user),1,9)AND spriden_change_ind ISNULL;ENDIF;end GOBTPAC_insert_to_goremal;

  41. Spriden Rows

  42. Spaiden form with social entered

  43. Spaiden automatically converts to new id, by using a alternate id search

  44. Goatpad form for BBunny

  45. Change form for persons not receiving a Portal ID

  46. Questions

More Related