Masking sensitive employee data in the database

I really, really don't see that one working. What DB do you use? It would, however, be nice to be wrong on this one. Test system access should be part of routine maintenance of IDs. Could you set up a business process of requesting access to the test system for a limited time for testing?

thanks for your reply, Eric. Everything I've been reading agrees with your opinion. We are on windows 2008 R2, SQL server 2008 R2. We have tight (Lawson) security on our test environment and use the Kinsey & Kinsey security reports that show us what NTids are accessing which forms and reports. I think the network security folks are concerned about someone hacking directly into the database thru sql. They know we can't mask PROD but I guess they figure they should limit all the copies of it (i.e. our test and development environments). I found another thread in this forum that mentions DMSuite as a masking tool, but I could not find any other 3rd party products... Sounds like a business opportunity!

In an Oracle environment I might be able to make some logical suggestions on encrypting fields seamlessly. In SQL Server, not so much. Sorry, I just don't see this working. Yeah, the lack of connection between NTids and user name is maddening. It's one of my larger issues with Lawson implemented on a Windows environment. Of course I'm a self admitted UNIX snob. Ha!

Eric, say if we just wanted to scramble the socials or DOB, would doing a mass update of employees with HR511 be an option? As in - downloading employees to a file in HR511 format, updating the file with scrambled values, then running the HR511 to update the employees. Or is that just too weird?

Sorry, I'm a techie no idea what HR511 capabilities are. You could load the data, run a script to mangle the SSN, and leave is like that. I would think, however, that doing so would cause problems for HR doing testing. They'll be relying on say SSN to be useable as it would in PROD to verify what they are doing. You'd have to get a decision our of HR for that one, and (no offense to any HR folks out there! LOL) that's usually next to impossible.

While I understand your Security department's concerns but having scrambled data in your testing environment - at least the internal testing environment is a horrible practice.

So you've scrambled everyone's DOB in the system, what happens to all of your data that is in some way built off that field?

All the sudden your age reduction calculations for everyone are all over the place. Your dependent eligibilities are messed up.

It's even worse for pay rates. Now you can't verify your payroll functions, your salary based coverages are all wrong, your checks are wrong, etc. and etc.

It's one thing to scramble demographic info, names and SSN don't drive any business logic in the system. But actual data that processes rely onto work? You are going to spend more time hunting down the question of whether things are broken because the data or the change being tested is borked than you are actually testing. And while you might be able to do unit testing that way, the final step of any real test is using real data.

And speaking bluntly, if someone's managed to hack their way into your development database, the likeliyhood that they won't have also made their way to the production environment is slim to none.

That all being said, I would suggest sitting down with them and dividing the info they are concerned about into functional and demographic categories. Let them scramble the demographic info as much as their heart desires.

Either via straight up SQL or something like DMSuite.

However I would not try doing the masking using Lawson's programs. That just adds another layer of potential 'was the bug from the change we are testing or from the way we loaded the employees' that you don't want to deal with.

Many thanks to both you guys! I had the same concerns/thoughts. It is good to get this confirmation from other Lawson support folks.

In the past, I have used SQL scripts over test data to perform basic masking of personally identifiable information (PII) in accordance with established guidelines (see http://en.wikipedia.org/w...fiable_information). This covers names, addresses, phone numbers, drivers license numbers, email addresses, SSNs, I9 data, etc. for employee, spouse, dependents, and beneficiaries.Also ACH bank accounts.

Birth date is a tougher call, since some of the benefits, deductions, etc. are defined using it. One possibility is to set all of them to the first day of the month, and leave it at that.

As for pay rate, theoretically, you would have to scramble pay history and distributions to GL/AC, etc. as well as anything that is based on the pay rate and/or birthdate (including benefits and deductions, etc.).

John, you wouldn't happen to have samples of these said scripts would you? We are in an Oracle environment, however, if you have something in SQL we could easily mimic. Thanks in advance.