 |
"Free testing tips and tools" |
| Products Templates Resources Support About Us |
| MSSQL.DataMask |
 2010 2010 2010 2010 2010  2010 |
|
 |
Download MSSQL.DataMask • Author: WinTestGear • Version Number: 1.0.0.7 - Release Notes - Wish List • Revision Date: April 28, 2010 • Byte Size: 2,431KB (251KB zipped) • License: Freeware, Warranty Disclaimer • Cost: Free • Requirements: .NET Framework 3.5 installed; Microsoft SQL Server 2005 or higher • Installation: No installation; simply copy & paste the single EXE file where you need it. • Screen Shots: Main Form, Select Mask Dialog, Generate Script, Open Connection Dialog, Execute Masks Status Dialog, Never Run Against Production Warning, Freeware License, Help About Dialog • Description: Do you need rich test data to develop, test, or outsource your project? If so, you've probably tried either generating from scratch, or cloning production data. Auto-generating the test data is difficult and error prone for all but the simplest databases due to complex variances, frequencies, and data interdependencies. Business and legal obligations such as HIPAA require that production data clones be thoroughly sanitized (masked) of personal indentifiable information (PII) and/or protected health information (PHI). Data masking is an effective test data generation approach. MSSQL.DataMask is a free, simple tool that quickly sanitizes a clone of your production database into a safe, secure test database. Once built, the process is easily repeatable to refresh your test data from production. You can either load and re-run a set of data masks from the application, or generate a fully doc- umented tSQL script to modify, run, or schedule as your needs dictate. • Why Mask Your Test Data? • Data Masking Beats Auto-Generating: According to Forrester Research Analyst Noel Yuhanna, "90 percent of organizations prefer to mask data...[because creating test data]...is more complex and doesn't always represent actual business scenarios." • But My Test Data is Safe Because Only Internally Accessed: According to the Ponemon Institute, "88% of all data breaches come from within;" thus, protecting your dev and test data is critically important. • Competitors: If MSSQL.DataMask does not fulfill your needs, then consider trying one of these more sophisticated products offered by other vendors: 1. The Data Masker 2. JumbleDB 3. DataMasking 4. Grid Tools 5. Dynamic Data Masking / ActiveBase Security 6. Dataguise dgMasker 7. CalSQL (tSQL script) 8. Hexaware Akiva 9. IBM Magen • Notes / Articles: • Good reminder on the risks of anonymizing data (be thorough when scrubbing) • Good overview article on data sanitization reasons and techniques • Keywords: Personal Identifiable Information, Protected Health Information, Scrub Data, Substitute Data, Transform Data, Data Masking, Data Sanitization, Data Obfuscation, Data Security, Data Cleansing, Data Hiding, Hide Data, Disguise Data, Sanitize Data, Data Privacy, Health Insurance Portability and Accountability Act (HIPAA) |
|
Available Mask Types: MSSQL.DataMask offers the sanitization techniques (mask types) listed below. Click the blue mask name link to view a sample of the auto-generated SQL script in a new window. • Scrub: Overwrite all rows of a column with the "same value" • Scrub.Null: Overwrite with null • Scrub.EmptyString: Overwrite with empty string ("") • Scrub.FixedString: Overwrite with a text you enter • Scrub.Zero: Overwrite with zero (0) • Scrub.FixedNumber: Overwrite with a number you enter • Scrub.FixedDate: Overwrite with a date/time you enter • Substitute: Overwrite all rows of a column with a "unique value" • Substitute.LoremGibberish: Overwrite with a random lorem ipsum gibberish text (retains original size) • Substitute.GUID: Overwrite with a random GUID (16-character text only) • Substitute.RandomNumber: Overwrite with a random number between min/max points you specify • Substitute.RowNumber: Overwrite with a row number (and text you specify if n/varchar) • Substitute.FromList: Overwrite with a random selection from value list you specify • Transform: Overwrite all rows of a column with an "obsfucated original value" • Transform.Scramble: Overwrite with scrambled original value (encrypt, then truncate at initial length) • Transform.Hash: Overwrite with hashed original value (MD5, always 16-bytes) • Transform.Prefix/Suffix: Prefix and suffix original value (with text you specify, can leave blank) • Transform.Find/Replace: Find & Replace text within original value (you enter find/replace text) • Transform.AsteriskMask: Overwrite first (n) characters with asterisks (like credit card statements) • Transform.ShuffleRecords: Randomly shuffle all rows within column (my favorite for most test data) • Transform.NumericVariance: Randomly de/increment original numeric value by percent you select • Transform.DateVariance: Randomly de/increment original date value by number days you select • Transform.DateTimeVariance: Randomly de/increment original date & time values by your selections • Transform.Truncate: Cutoff original value at character length you specify |
|
|
Screenshot #1 - Main Form: Using MSSQL.Datamask is simple. Run the executable. Click connect to open a database. Select a table in the tree control on the left. Click the column mask type drop down to open a mask selection dialog (shown below). As you assign data mask rules to table columns, they become bold in the grid.  |
|
Screenshot #2 - Select Mask Type Dialog: There are currently 21 types of data mask that can be applied to columns. The list of mask types is filtered based on the column's data type, length, whether or not it is nullable, whether or not it has constraints, etc. As you select different maskt types in the drop down list, the dialog re-paints itself with detailed instructions on using the mask type and reconfigures the parameter text boxes / drop down boxes. In the sample screenshot below, the Substitute value randomly from a list of possible values mask is depicted. Note that you can simply paste in a long list of names, addresses, zipcodes, phone numbers or whatever else you need (CRLF delimited). MSSQL.DataMask will then randomly substitute from this list to replace the actual values in your test database (cloned from production). In the example below, the [LastName] field is going to be randomly overwritten with values I keyed into the text box (John, Jack, Jim, etc.).  |
|
Screenshot #3 - Generate Scripts: MSSQL.Datamask automatically generates thoroughly commented tSQL for you if you'd rather not run your data masks from the tool's interface. Reasons you might choose to script include automating in a SQL Agent job, modifying the script yourself (to bypass limits MSSQL.DataMask enforces such as no masks on primary keys when you know it will be safe, or re-wiring to a large lookup table to bypass the 8,000 character limit on the "Substitute from List" mask, etc.).  |
|
Screenshot #4 - Execute Masks Status Dialog: A screenshot of the status dialog box results after executing a set of masks against a test database.  |
