KFS Application Database Refresh

DB Copy Procedure

 

An official request will be sent to the DBA team by the Kuali Tech. Manager or Finance System Manager, initiating the DB copy process. The following are high-level steps entailed.

  • CM stops Tomcat instances in the target environment(s).
  • DBA's copy from Production to target environment. They exclude KFS_PRD.KRNS_ATT_T, KR_PRD.KRNs_ATT_T and KR_PRD.KREW_ATT_T tables.
  • They run the encryption process when the copy is complete. Tables excluded by those script runs are re-created.
  • Assigned developers check the database copy integrity and on confirmation the CM or SA restarts the Tomcat instance on Manager's request to begin post processing steps.

KFS-1700 - Getting issue details... STATUS

 

 Fields to Encrypt (technical details)

 

make sure log4j.properties has debug on for the re-encrypt step

make sure that that kfs-build.properties is pointing to the right location for security properties

ant -logfile encrypt.out -Duser.home=/home/tomcat dist filter-local re-encrypt-step

--SELECT distinct (length(CC_NBR)) FROM FP_PRCRMNT_CARD_HLDR_DTL_T

--SELECT count(*) FROM FP_PRCRMNT_CARD_HLDR_DTL_T; -- 1366
--SELECT count(*) FROM FP_PRCRMNT_CARD_HLDR_DTL_T where length(CC_NBR)=32; --683
--SELECT count(*) FROM FP_PRCRMNT_CARD_HLDR_DTL_T where length(CC_NBR)=64; --683

 

SELECT owner, table_name FROM all_tables where owner='KFS_UAT' and table_name like '%_BAK'

update 
  FP_PRCRMNT_CARD_HLDR_T t1
set
  (
    t1.trn_cc_nbr
      ) = (
    select
      t2.trn_cc_nbr 
    from
      FP_PRCRMNT_CARD_HLDR_T_bak  t2
    where
      t2.fdoc_nbr = t1.fdoc_nbr
     );  

 

com.rsmart.kuali.kfs.fp.businessobject.ProcurementCardHolderDetail=creditCardNumber

You should know the column and table name for the field that needs to be encrypted. KFS uses ojb for database mappings so all you would need to do is search the code for an ojb file containing the table name. In the case of the fp_prcrmnt_card_hldr_dtl_t table it is found in ojb-fp.xml in the com.rsmart.kuali.kfs.fp package. There in the class-descriptor is the java class name you need, in this case com.rsmart.kuali.kfs.fp.businessobject.ProcurementCardHolderDetail. Then all that is left is to get the name for the field in the class which you can find in the field-descriptor for the specific column (cc_nbr), in this case creditCardNumber. Put that together and you got all the info you need for the fieldsToEncrypt file (com.rsmart.kuali.kfs.fp.businessobject.ProcurementCardHolderDetail=creditCardNumber).

rSmart created a page in their confluence that lists all the different fields that need to be encrypted in KFS, there aren't many so I would recommend doing a similar thing here in the UConn confluence just so you have them handy.

 

tablecolumnfieldtoencrypt
pur_vndr_hdr_tvndr_tax_nbrorg.kuali.kfs.vnd.businessobject.VendorHeader=vendorTaxNumber
tax_payee_thdr_vndr_tax_nbrcom.rsmart.kuali.kfs.tax.businessobject.Payee=headerTaxNumber
fp_bank_tbnk_acct_nbrorg.kuali.kfs.sys.businessobject.Bank=bankAccountNumber
pdp_ach_acct_nbr_tach_bnk_acct_nbrorg.kuali.kfs.pdp.businessobject.AchAccountNumber=achBankAccountNbr
fp_prcrmnt_card_hldr_ttrn_cc_nbr

com.rsmart.kuali.kfs.fp.businessobject.ProcurementCardHolder=transactionCreditCardNumber

org.kuali.kfs.fp.businessobject.ProcurementCardHolder=transactionCreditCardNumber

   
fp_prcrmnt_card_trn_ttrn_cc_nbr

org.kuali.kfs.fp.businessobject.ProcurementCardTransaction=transactionCreditCardNumber

**** NOT ENCRYPTED!

fp_prcrmnt_card_hldr_dtl_tcc_nbr

com.rsmart.kuali.kfs.fp.businessobject.ProcurementCardHolderDetail=creditCardNumber

edu.uconn.kuali.kfs.fp.businessobject.ProcurementCardHolderDetail=creditCardNumber

**POST ENCRYPTION there will be duplicates, one encrypted one not, because the cc_nbr is the primary key.

check_reconciliation_tbank_account_nbrcom.rsmart.kuali.kfs.cr.businessobject.CheckReconciliation=bankAccountNumber
ar_cust_tcust_tax_nbrorg.kuali.kfs.module.ar.businessobject.Customer=customerTaxNbr
pur_vndr_tax_chg_tvendr_prev_tax_nbrorg.kuali.kfs.vnd.businessobject.VendorTaxChange=vendorPreviousTaxNumber

pdp_payee_ach_acct_t

bnk_acct_nbr

org.kuali.kfs.pdp.businessobject.PayeeACHAccount=bankAccountNumber
fp_dv_wire_trnfr_tdv_payee_acct_nbrorg.kuali.kfs.fp.businessobject.DisbursementVoucherWireTransfer=disbVchrPayeeAccountNumber

*** missing conversion="org.kuali.rice.kns.util.OjbKualiEncryptDecryptFieldConversion" ?? (work/src/org/kuali/kfs/fp/ojb-fp.xml)

org.kuali.kfs.vnd.businessobject.VendorHeader=vendorTaxNumber
com.rsmart.kuali.kfs.tax.businessobject.Payee=headerTaxNumber
org.kuali.kfs.sys.businessobject.Bank=bankAccountNumber
org.kuali.kfs.pdp.businessobject.AchAccountNumber=achBankAccountNbr
com.rsmart.kuali.kfs.fp.businessobject.ProcurementCardHolder=transactionCreditCardNumber
com.rsmart.kuali.kfs.fp.businessobject.ProcurementCardHolderDetail=creditCardNumber
com.rsmart.kuali.kfs.cr.businessobject.CheckReconciliation=bankAccountNumber
org.kuali.kfs.module.ar.businessobject.Customer=customerTaxNbr
org.kuali.kfs.vnd.businessobject.VendorTaxChange=vendorPreviousTaxNumber
org.kuali.kfs.fp.businessobject.DisbursementVoucherWireTransfer=disbVchrPayeeAccountNumber
#pdp_payee_ach_acct_t
org.kuali.kfs.pdp.businessobject.PayeeACHAccount=bankAccountNumber

Validate from front end:

also 

Jim's reference (unrelated to the above):

<target name="post-load-encrypt" depends="make-source">
                <java classname="org.kuali.kfs.sys.context.BatchStepRunner" fork="true">
                        <jvmarg value="-Xms${run.postloadencrypt.min.memory}" />
                        <jvmarg value="-Xmx${run.postloadencrypt.max.memory}" />
                        <jvmarg value="-XX:MaxPermSize=${run.postloadencrypt.max.permsize}" />
                        <arg value="postDataLoadEncryptionStep" />
                        <classpath>
                                <pathelement location="${war.classes.directory}" />
                                <path refid="run.server.classpath" />
                        </classpath>
                </java>
        </target>
./classes/configuration.properties:encrypt.attributes.properties.file=work/db/fieldsToEncrypt.properties

./classes/org/kuali/kfs/sys/spring-sys.xml: &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <property name="attributesToEncryptProperties" value="${encrypt.attributes.properties.file}" />

 

 

for new version of encryption:

 

at bottom of page: policy

http://www.oracle.com/technetwork/java/javase/downloads/index.html

download the one that matches your java version.

Extract,

copy jars to:

<java-home>/lib/security/

 

Full Process (including Post-Processing steps)

  1. Shut down the tomcat application servers on each of the nodes in the environment that will be copied to (kfs, kr, krapps, batch)
  2. Allow the DBA's to complete the copy from the desired prd backup/export.
  3. Start up the tomcat application servers on each of the nodes in the environment that was copied to (kfs, kr, krapps)

1. Cleanse the KFS data (kfs db only)  (DEV, UAT, TRN, YEN)

The CleansePII.sql file generates values and updates each KFS table that contains PII data. The new
data is encrypted before updating the tables. The following data tables/fields are cleansed:

  • fp_bank_t.bnk_acct_nbr
  • check_reconciliation_t.bank_account_nbr
  • fp_dv_wire_trnfr_t.dv_payee_acct_nbr
  • pdp_ach_acct_nbr_t.ach_bnk_acct_nbr
  • pdp_payee_ach_acct_t.bnk_acct_nbr
  • ar_cust_t.cust_tax_nbr
  • tax_payee_t.hdr_vndr_tax_nbr
  • pur_vndr_tax_chg_t.vndr_prev_tax_nbr
  • pur_vndr_hdr_t.vndr_tax_nbr
  • fp_prcrmnt_card_hldr_t.trn_cc_nbr
  • fp_prcrmnt_card_hldr_dtl_t.cc_nbr
  • fp_prcrmnt_card_trn_t.trn_cc_nbr

2. Clear the service registry table (rice db only) (ALL)

The service registry table contains endpoints for the published web services. It is populated upon startup of the servers. After refreshing from production the production values need to be removed.

The clearServiceRegistry.sql file runs the following command to remove all existing entries from the table:
truncate table KRSB_SVC_DEF_T;

3. Fix the docHandler URLs for the KR Apps documents (rice only) (ALL) ***Future

The doc_typ_t table contains a document handler URL (doc_hdlr_url) for each document type. For all of the
document types that belong to the KR Apps applications, the document handler URL is hardcoded in the table.
These URLs must be corrected so they point to the correct environment and not the production environment.
The URLs for the KFS document types are all parameterized, so there is no need to change the value in the
database. This is only needed for the UCONN* document types that are hardcoded.

There are 2 ways we could fix this.
Solution A: Parameterize the UCONN* document types. This would be the preferred solution because it would mean
nothing would need to be changed when refreshing the databases from production data. The drawback is that currently
we don't know what that will take. Can the parameter replacement happen in rice services or does it have to be
changed in each application?

Solution B: Update the rows in the doc_typ_t table that apply to UCONN* document types. The problem with this solution
is that there are different values that are required for DEV and UAT environments. Plus environments that aren't used
by KRApps applications do not need to be updated (or maybe the rows should be removed completely). Therefore, each
environment would require a unique update process.

4. Remove the workflow data for KR apps documents (rice db only) (DEV, UAT, TRN, YEN)  ***Future

The RemoveKrAppsWorkflowData.sql script finds all of the document ids where the document type starts with 'UCONN'
and deletes all records for those document ids in the following rice tables:

  • krew_actn_itm_t
  • krew_actn_rqst_t
  • krew_actn_tkn_t
  • krew_app_doc_stat_tran_t
  • krew_doc_hdr_cntnt_t
  • krew_doc_hdr_ext_dt_t
  • krew_doc_hdr_ext_flt_t
  • krew_doc_hdr_ext_long_t
  • krew_doc_hdr_ext_t
  • krew_doc_hdr_t
  • krew_doc_nte_t
  • krew_edl_dmp_t
  • krew_edl_fld_dmp_t
  • krew_init_rte_node_instn_t
  • krew_out_box_itm_t
  • krew_rmv_rplc_doc_t
  • krew_rmv_rplc_grp_t
  • krew_rmv_rplc_rule_t
  • krew_rte_node_instn_t
  • krns_adhoc_rte_actn_recip_t
  • krns_maint_doc_att_t
  • krns_maint_doc_t
  • krns_maint_lock_t
  • krns_pessimistic_lock_t
  • krns_sesn_doc_t
  • krns_doc_hdr_t

5. Re-encrypt the CLOB fields in both KFS and Rice databases.  (DEV, UAT, TRN, YEN)

6. Remove attachments from the document content XML stored as CLOB types  (DEV, UAT, TRN, YEN)  ***Future

The XML documents that are stored in the krns__maint_doc_t table in KFS contain all of the data for a
document, including any attachments attached to the document. As the attachments can be extremely large,
and we already remove the data from the attachment table when we refresh, we could gain a significant
amount of space and time by removing the attachments from the document content XML, also.

 

7. Adjusting DocHandler URL's for UAT workflow applications

 Full process for DocHandler config...

 

FOR WORKFLOW APPLICATIONS, go one by one and change the dochandler url's on the travel doc types

 

    1. Select the Administration tab and then choose Document Type



    2. In the Search criteria select "UCONN" and the Workflow Doc Types should appear.

    3. Select edit under Actions for each Doc type, and then alter the Document Handler URL on the right side. Make sure you also fill in "Document Header Change" or another note in the change description, and click Submit.

      Do NOT click Save or Blanket Approve. This will not change the Document Handler URL properly.

      Environmental Configurations:

      Depending on the target environment that has been refreshed, you will need to change the Document Handler URL to a specific path to point it at the correct KRAPPS server.

      They are as follows:

      UAT:

      tst.krapps.uconn.edu (or testworkflow.apps.uconn.edu)

      DEV:

      dev.krapps.uconn.edu

    4. Once the change has been made, you can validate that it worked by clicking on the Document in another Search, and it should appear with the updated Document Handler URL.


    5. If there are any further issues, make sure that the KSB registry has been cleaned, and that no PII exists once the nodes are brought back online.

 

Known Issues: The refresh procedure also brings in all of the production parameters, such as email notification and Workflow application dochandler's. Some parameters will have to be changed by hand, and workflow application xml files may need to be re-ingested in order to fix the dochandler issues.

 

The following documents all of the processes that need to happen when we copy production data to
non-production environments. Some of these steps have not been implemented yet, or need decisions
made about implementation.

 

 

 

Scripts for Post-Processing to stop batch jobs from failing in UAT

For the receivingpaymentrequestjob :

UPDATE PUR_REQS_T SET REQS_STAT_CD='CANC' WHERE REQS_STAT_CD='ACMR';
commit;

 

For the PurchasingPreDisbusementExtractJob : 

UPDATE AP_PMT_RQST_T SET PMT_EXTRT_DT='01-JAN-14' WHERE PMT_EXTRT_DT IS NULL;
UPDATE AP_CRDT_MEMO_T SET CRDT_MEMO_EXTRT_DT='01-JAN-14' WHERE CRDT_MEMO_EXTRT_DT IS NULL;
UPDATE FP_DV_DOC_T SET DV_EXTRT_DT='01-JAN-14' WHERE DV_EXTRT_DT IS NULL;
commit;