Developing a production database requires extensive optimization. With the growing concerns about data privacy and new regulations such as the Global Data Protection Requirement (GDPR), these databases must be created with privacy safeguards in place. You need to use a few different data anonymization techniques to accomplish this with a standard SQL database.
SQL data generator tools are frequently used for data anonymization. Here is the process for using data anonymization with SQL generators.
Conduct a data audit and mark data sets that require masking
Anonymization tokens are often necessary, but they also take time to set up. Applying data masking to every data set would be an unnecessarily time intensive and cumbersome process.
You need to begin by taking an inventory of all current and future data sets. Mark data that will need to be anonymized with your SQL generator. Other data can be easily copied from your production database.
Benefits of using an SQL generator for producing sensitive data
The biggest mistake that Data administrators make when anonymizing SQL entries is copying sensitive data and trying to add the anonymization tokens after the fact. This creates a couple of security risks, which are listed below.
Data may be hijacked if the server is compromised
There is always a possibility that your data is vulnerable to hackers. You may not realize that they created a new database user and granted themselves administrative privileges. While the probability of such an event is low, the consequences could be disastrous if the data is intercepted before it could be anonymized.
Traces of non-anonymized data could be left on your server logs
Traces of data are not permanently destroyed after the logs are overwritten. The process is similar to writing over data on any other disc. The space the data resides on is marked for new allocations so that future data can be written in its place. This would create significant privacy and security concerns if it was sensitive data that was not properly anonymized.
Using an SQL generator is a much more secure option. You can create the data on a virtual machine and anonymize it. After it has been anonymized, you can use an SQL clone to replicate it to the database. This approach ensures copies of non-anonymized data are never left on your permanent servers.
Anonymization process with an SQL generator
Adding an anonymization token to datasets created with an SQL generator is fairly straightforward. Here are the steps that you will need to take.
Select the right SQL generator
There are a number of SQL generators to choose from. Vladimir Kulakov, a senior software engineer I spoke with from R Studio, says there are a number of great SQL generators to choose from.
Red Gate, Mockaroo and Devart dbForge Test Data Generator are some of the most popular. The functionality is fairly similar to all of them. The important thing is to make sure you are familiar with the data generator environment and use the GUI properly, Kulakov told me.
Make sure unmasked data is only available to users with the appropriate permissions
Even if you are using an SQL generator, you need to make sure that sensitive data can only be viewed with people with the appropriate permissions. Determine which users need the right privileges and set them accordingly.
Make sure sensitive data is assigned to the right array columns
Categorizing data is essential. Evaluate your arrays carefully and identify the variables that warrant anonymization. Anonymizing the wrong columns can leave sensitive data vulnerable.
Use the MASKED WITH command
The MASKED WITH method is used to create anonymization tokens. Microsoft has a document on using it. Here are some primers. You will need to specify the paraments of the data that will be masked. By default, the entire variable will be marked. You can also the random() method within MASKED WITH to limit the range of data that is masked. The syntax is as follows:
Variable name [variabletype] MASKED WITH (FUNCTION = ‘random([start range], [end range])’)
This method is fortunately very easy to use and is strongly encrypted.
Copy the data to your database
Once the data has been masked in your SQL generator, you will simply use your SQL clone to transfer it to your database. You will need to choose the right SQL clone. You just need the permissions to your SQL database and verify that the data fields are consistent between both environments to ensure all of your data is copied properly.
