#GPPT Obscuring Bank Account Numbers in Microsoft Dynamics GP

David Meego - Click for blog homepageThis article covers the latest “Must Have” free sample project for the GP Power Tools – Developer Tools module (requires Build 28 or later).

Back in 2012, I created code to obscure customer credit card details (see more information below). Using the same concepts, I have now created a project to obscure Bank Account Numbers for Checkbooks, Customers, Vendors and Employees.

Whether you have a requirement (see NACHA Security Requirements article) or not, best practice would be to make sure that Bank Account Numbers in your system are not human readable while stored in the database.

Disclaimer: The Bank Account Numbers are obscured by this custom code which makes it unreadable to a human, but it is not using industry standard encryption. That is why we are using the terminology of obscure and unobscure instead of encrypt and decrypt.

Introduction

The BANK_OBSCURE GP Power Tools project contains two primary features:

  1. The obscuring of Bank Account Numbers when stored in SQL tables.
  2. The hiding of Bank Account Numbers on the windows until the field has focus.

The obscuring functionality can be enabled for the following fields and tables:

  • Checkbooks: ‘EFT Bank Account’ of table cmCheckbookEFT (CM00101.EFTBankAcct)
  • Checkbooks: ‘Bank Account Number’ of table CM_Checkbook_MSTR (CM00100.BNKACTNM)
  • Checkbooks: ‘DD Account Number’ of table CM_Checkbook_MSTR (CM00100.DDACTNUM)
  • Customers: ‘EFT Bank Account’ of table AddressEFT (SY06000.EFTBankAcct), Series = 3
  • Vendors: ‘EFT Bank Account’ of table AddressEFT (SY06000.EFTBankAcct), Series = 4
  • Employees: ‘DD Account Number of table ddEmpAcctMstr (DD00200.DDACTNUM)

The obscuring algorithm used is one that does not change the length of the data stored to avoid the risk of data being truncated and lost if the field size is not long enough.

The hiding of the Bank Account Number until the field gains focus can be enabled for the following windows:

  • Checkbooks: Checkbook EFT Bank Maintenance
  • Checkbooks: Checkbook Maintenance
  • Checkbooks: Direct Deposit Checkbook Maintenance
  • Customers: Customer EFT Bank Maintenance
  • Vendors: Vendor EFT Bank Maintenance
  • Employees: Employee Direct Deposit Maintenance

Note: This is a sample project that can be customized as desired once installed on your system. If you need the functionality extended to other tables or windows, additional scripts and triggers can be added.

Configuring

After downloading and importing the project (instructions at the end of article), you can configure the project by opening the BANK_CONFIG Runtime Execute script and clicking the Execute Button.

Select the options you would like enabled and click OK. The code will obscure/unobscure data in the tables to match your selections based on whether they were previously obscured or not. It also will store your settings in the DUOS (Dynamics User Object Store, SY90000) table. If you want to check the stored settings execute the BANK_SETTINGS Runtime Execute script.

Checking the Data

If you want to check the state of the data in the SQL Server tables, you can use the BANK_OBSCURE* and BANK_UNOBSCURE* SQL Execute scripts. Just open the desired scripts and execute them. The scripts have SQL Gotos (BANK_GOTO* Runtime Execute scripts), so double clicking on a row will open the window where the data is stored.

Changing the Data

If you want to manually change the state of the data in the the SQL Server tables, you can use the BANK_OBSCURE* and BANK_UNOBSCURE* Runtime Execute scripts. Just open the desired scripts and execute them. They will show a progress window and a dialog to show how many changes they have made.

Automatically Obscuring Data

Once the BANK_OBSCURE project has been enabled using the BANK_CONFIG Runtime Execute script, the BANK_OBSCURE* and BANK_UNOBSCURE* Triggers will ensure that whenever data is saved to SQL, the record will be updated to obscure the data and unobscured when read from SQL. This means that the obscuring of the data is transparent to all the Microsoft Dynamics GP (and addon products) Dexterity code.

Note: If data is read directly from SQL it will not be unobscured, and additional code might be required.

Manually Obscuring Data

If you import records into your system from external sources, the Bank Account Numbers for those records will not be obscured initially. To make it easier for users, a menu option has been added with the BANK_MENU* Triggers to obscure data has been added as an Additional menu to the Maintenance windows for each type of record. The menu items just run the BANK_OBSCURE* Runtime Execute scripts for you.

Note: Manually saving an imported record via the GP user interface will apply the obscuring.

Scheduled Obscuring Data

To also ensure that the obscuring of data for imported records is not missed, there is a BANK_SCHEDULE Trigger which is scheduled to run once per day on the first login of the day. It will run the BANK_SCHEDULE Runtime Execute script as a background process. This script will call the BANK_OBSCURE* Runtime Execute scripts but hide the progress window and completion dialogs.

Working with Reports

When Reports read the data from the SQL tables, they don’t cause the BANK_UNOBSCURE* triggers to run. This means the data on a report will be shown as obscured. If this is desired, no further action is required.

If the data needs to be unobscured, this can be achieved by modifying the report to use a calculated field which calls a Report Writer function. The Report Writer function can also mask characters. The instructions are below:

Modify the report and create a calculated field which calls the User Defined function rw_TableHeaderString in the system series.

Below are parameters you need to pass through to the rw_TableHeaderString function:

  1. Product ID: For GP Power Tools 5261
  2. Script ID from Runtime Execute: RW_BNKUNOBSCURE
  3. Field to be unobscured: For example: AddressEFT.EFT Bank Account
  4. Number of left hand characters to display
  5. Number of right hand characters to display

Then drag the calculated field on to the report and hide or remove the original field.

Notes:

  • The Report Writer function will work for both obscured and unobscured data in the table, it only unobscures when required.
  • If the Number of left hand characters and Number of right hand characters are both zero (0), the entire credit card number will be displayed.
  • If one or both of the Number of characters to display parameters are used, the credit card number will be masked except of the numbers of characters specified.
  • There is RW_BNKOBSCURE Runtime Execute script which can be used to obscure data, if needed.

For example:

(c) Bank Account Number  = FUNCTION_SCRIPT( rw_TableHeaderString  5261  “RW_BNKUNOBSCURE”  AddressEFT.EFT Bank Account 1  4 )

For a bank account number of 1234567890, the calculated field above will show 1XXXXX7890.

Hiding the Data on Windows

The BANK_HIDE* series of Triggers implement the hiding of the Bank Account Number until the focus is in the actual field. This means that a casual glance over a shoulder will not be able to see a Bank Account Number.

Note: The field will look empty until it is tabbed into or click in by the mouse.

[Edit] The hiding of the Bank Account Number on the Employee Direct Deposit window might sometimes show the first line of the scrolling window even when not selected. This is a quirk of Dexterity’s behavior and not an issue with the code in this sample. The obscuring of table data works correctly.

More Information

For more information on the previous example and on the upcoming NACHA requirements, see the articles below:

Downloading and Installing

Download the example code, import using the Project Setup window (now possible for first time imports with Build 28.8 onwards), or use Configuration Export/Import window (for older builds):

The code will be active on next login or after switching companies, or you can start the triggers manually from the Project Setup window.

More free customizations on the GP Power Tools Samples page:

Hope you find this free sample project useful. The testers have been really happy with it.

David

21-Jun-2022: Added note about hiding Bank Account Number quirk on Employee Direct Deposit window.

This article was originally posted on http://www.winthropdc.com/blog.

One thought on “#GPPT Obscuring Bank Account Numbers in Microsoft Dynamics GP

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.