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:
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:
There also two Runtime Execute Script IDs:
Finally there are two SQL Execute Script IDs:
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.
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.
PLEASE READ BEFORE POSTING
Please only post comments relating to the topic of this page.
If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.