Obscuring Table Data Fields using the Support Debugging Tool

My colleague, Allan, had a case recently where the customer had noticed that the Credit Card Number field stored on the Customer Master table at the SQL Server level was not encrypted or obscured and could be seen by anyone who could access the data.

Before, you say that this is a huge security risk, I should mention that due to the way in which Microsoft Dynamics GP encrypts passwords, users which access to the Microsoft Dynamics GP application CANNOT use those credentials to access SQL Server directly or from any other application.

Within the application, access to this information on the Customer Maintenance Options window can be controlled using a number of methods, including:

• Standard Security Tasks and Roles to control access to the Customer Maintenance form.
• Field Level Security to deny access to the Customer Maintenance Options window.
• Field Level Security to hide the Credit Card related fields.
• VBA to hide the Credit Card related fields.
• Use Modifier to hide the Credit Card related fields and control access to the original window.

So you can see there are many ways to control access to this information (highlighted on the screenshot below). 

Customer Maintenance Options window

To see the data we are talking about you can execute the following SQL Query against your company database: 

select CRCRDNUM, * from RM00101 where CRCRDNUM <> ''

---

However, regardless of the options mentioned above, the customer on this case still wanted a way to have this data obscured at the SQL Server level.

So this is where the Support Debugging Tool can help.... See the article Using Support Debugging Tool Non-Logging Triggers for more information.

Using Non-Logging Automatic Debugger Mode triggers on the save and read events for the RM_Customer_MSTR (RM00101) table we can use standard Dexterity functions to obscure the field when table is saved and unobscure the field when the table read. Once these triggers are in place, the manipulation of the field becomes transparent to the rest of the system.

This example uses two Trigger IDs:

1. RM_OBSCURE
   This trigger fires every time the RM_Customer_MSTR table is saved. If there is a Credit Card Number value entered into the field and it is currently not obscured, the field will be obscured and the table will be saved again with the now obscured value.
    
2. RM_UNOBSCURE
   This trigger fires every time the RM_Customer_MSTR table is read. If there is a Credit Card Number value in the table and it is currently obscured, the field will be unobscured in the table buffer. This allows all existing code to work using the field as normal.

 

There also two Runtime Execute Script IDs:

1. RM_OBSCURE
   This Dexterity script can be used to obscure the Credit Card Numbers in the existing data in the RM_Customer_MSTR table. It looks for Customer records with a Credit Card Number entered which has not been obscured. It will then update the field and saves the obscured data. It should only be needed when first installing or after importing data from an external source.
    
   Note: The Non-Logging Automatic Debugger Mode Triggers (above) should be disabled when you use this script. Check and if necessary unregister them using the Automatic Debugger Mode Status window.
    
2. RM_UNOBSCURE
   This Dexterity script can be used to unobscure the Credit Card Numbers in the RM_Customer_MSTR table. It looks for Customer records with a Credit Card Number entered which has been unobscured. it will then update the field and saves the unoscured the data. It should only be needed if you wish to stop using and remove the triggers.
    
   Note: The Non-Logging Automatic Debugger Mode Triggers (above) should be disabled when you use this script. Check and if necessary unregister them using the Automatic Debugger Mode Status window. This script will fail to unobscure any fields if the triggers are still running.

 

Finally there are two SQL Execute Script IDs:

1. RM_OBSCURE
   This SQL script will display any records from the RM_Customer_MSTR (RM00101) table which have an obscured Credit Card Number.
    
2. RM_UNOBSCURE
   This SQL script will display any records from the RM_Customer_MSTR (RM00101) table which have a Credit Card Number which is not obscured.

 

Note:  The code in this example uses a check to see if the first character of the Credit Card Number is a number (0-9) to decide if the field has already been obscured. If you want to use a similar method for a field that could contain alphabetic values, you will need to use the isalpha() function or change the comparison strings in the code.

The Support Debugging Tool Debugger Settings file with these triggers and scripts is attached as an archive to the bottom of this post.

Note: The function used for this example ito obscure the data is a very simple algorithm built into Dexterity and is only meant to stop the data from being easily readable. It is NOT secure and does not meet any compliance standards for encryption of personal data. Use at your risk.

If you want proper encryption of Credit Card details, there are solutions from Independent Software Vendors (ISVs) for Microsoft Dynamics GP.

 

Please leave a comment if you find this example useful.

David

20-Aug-2012: Added RW_UNOBSCURE Runtime Execute script for use with reports, see Update: Obscuring Table Data Fields using the Support Debugging Tool for more info.

Debugger Settings Obscure and Unobscure Credit Card Number on Customer Master.dbg.zip

Comments

• Anonymous
  August 04, 2012
  Good article...