- #Red gate sql data generator other table column how to
- #Red gate sql data generator other table column upgrade
Any existing data in the table will be cleared out, and replaced with a mixture of masked and real data.įinally, inspect the contents of the MaskedReport table in SSMS to make sure it all worked correctly. Having obfuscated all personal or sensitive data, save the project and then hit the Generate Data button to run the generator, and populate the MaskedReport table. We can then go on to do as much data masking as we need.
#Red gate sql data generator other table column how to
We can use the Phone Number generator without modification, but in my previous article I’ve offered plenty of examples of how to customize SDG’s built-in generators, as required. Click on the dropdown list that specifies the current generator (which you’ll see is our view), expand the Personal category of generators and choose the Phone Number generator. I’ll just show how to mask one column, PhoneNumber, to make the point, since the tax people don’t require these customer details.Ĭlick on the column and the Column generation settings pane appears, above the data preview pane. Now we can overwrite the real information with the fake data, where necessary, column by column. Hit the Finish button, and SDG will populate the preview pane, for MaskedReport table, with the original data from the view.įigure 5 Masking the personal or sensitive data Hit the Browse button, specify the connection to the same Adventureworks database and select the view. Instead of generating the data, we want SDG to take the data from our ReportForTaxMan view, so set the table generation settings to use an existing data source. Therefore, we need to deselect every table and view (use the button at the bottom), except our masked table. No, we must first introduce the real data, and then fill the corresponding columns before we then mask the columns that reveal personal data. This might be OK for user training on the AdventureWorks applications, but it will irritate the people in the Tax office more than a little. Click on it and you’ll see, in the lower pane, that SDG is straining at the leash, and has figured out strategies for filling in this table with fake data. You’ll notice our empty MaskedReport table is listed. Now we start SDG, create a new project, and specify AdventureWorks as our database. Listing 2 Creative use of the Existing Data Source in SQL Data Generator SQL Data generator will fill this MaskedReport table Listing 1 just creates the single view we need, but you can create as many of these datasets as you want or need, within a single SDG project. We’ll create a view containing all the data we need for the report to the tax man.
#Red gate sql data generator other table column upgrade
Dust out that copy of SQL Data Generator (SDG) and upgrade it, if it tells you there are later versions, because with a little creativity it can do all the hard work for us. How? Your developers say they could modify the existing reports slightly but obfuscating various fields, using SQL, would take a long time. Instead, you want to generate a data-masked version of your standard report, which the Tax Men can pore over.
After all, imagine the lurid stories if AdventureWorks became one of those databases that was available ‘everywhere’, after being copied onto the Dark Web? No, before distributing the data, you must obfuscate all the identifiable details of your customers, whilst still giving the required information to people who have a right to inspect your books.Īfter a difficult conversation with the team leader of your ops team, you decide that because of the complications of identifying and masking out all the sensitive data, it is impractical to deliver a ‘pseudonymized’ copy of the relevant tables from the database. There was a time when you might have shrugged and just sent them a copy of the relevant data straight from your database, or even sent them the entire database.
They need the details of the purchases, without needing the details of the customers. They want to be able to check that the total tax you pass on to them is correct, and is the same as the tax charged to the customers.
Out of a blue sky comes an order from Taxman that you supply details of all your sales, along with the tax charged to your customers. Imagine that you are the CIO of AdventureWorks. He is a regular contributor to Simple Talk and SQLServerCentral. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.ĭespite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.
0 kommentar(er)
