Frequently Asked Questions

Data Masker for Oracle & SQL Server

Q: What is data masking?

A: Data masking is the process of protecting sensitive information in non-production databases from inappropriate visibility. After sanitization, the database remains perfectly usable - the look-and-feel is preserved - but the information content is secure.

Q: What does Data Masker do?

A: Data Masker is a tool to replace sensitive data in test and development databases with realistic looking but false information. This process is sometimes called Data Sanitization, Data Masking or Data Protection. Data Masker provides a simple to use, repeatable method. If you would like an introduction to the art of data masking, please have a look at our Data Masking: What You Need To Know white paper.

Q: Why do you want Data Masker?

A: Masked data is a sensible precaution from a business security standpoint and in many cases, it is a legal obligation. It’s not just a matter of trusting your employees - scrambled test information can help prevent accidental data escapes as well.

Q: How does Data Masker do what it does?

A: Simple and understandable rules are defined to operate on the data. The collection of these rules performs a series of known, tested, and repeatable actions at the push of a button.

Each version of Data Masker has been custom written for the architecture of the target database and it operates only on that database. The database-specific design makes Data Masker simple to use on very complex data, and lightning fast on large tables.

Q: What platforms and databases does Data Masker support?

A: Data Masker supports both Oracle and SQL Server databases:

  • Oracle versions 9i, 10g, 11g, 12c and AWS RDS for Oracle.
  • SQL Server versions 2005, 2008, 2012, 2014 and 2016 as well as Azure SQL Database and AWS RDS for SQL Server.

Data Masker is installed on a Windows PC and operates on both local and remote databases. There are no server side components.

Q: Is my data too complex to be masked?

A: Not likely. Data Masker handles even the most intricate data structures. It can preserve data relationships between rows in tables, between rows in the same table or even internally between columns in the same row. Data synchronization issues of this type can be automatically handled by the addition of simple, easily configured masking rules. Don't worry about the size of the data either - Data Masker has successfully masked tables containing hundreds of millions of rows.

Q: Can I try Data Masker out?

A: Of course, download a 30-day, fully functional, free trial of Data Masker for evaluation now.

Q: Can I ask a question?

A: Got a challenging problem or just want to know where to start? Send an email to support@red-gate.com or call +44 (0) 1223 437909. We have scrambled a lot of test data and we are happy to provide advice.

Masking rules & how to... questions

Q: What types of masking rules does Data Masker have?

A: There are wide variety of rule types available.

Masking rules:

  • Substitution Rules – substitutes the data in the column of a table. As substitution data, this type of rule can use any of the supplied datasets or User Defined DataSets appropriate to the column type. This type of rule can also substitute based on a user supplied WHERE condition.
  • Shuffle Rules – shuffles the data in the column of a table (like a deck of cards) and leaves the other columns untouched. This type of rule can also shuffle based on a user supplied WHERE condition.
  • Command Rules – this type of rule is used to run user defined SQL or PL/SQL statements within the target database.

Synchronization rules:

Specialized rules:

  • Rule Controller – a Rule Controller contains login information. Rule Controllers tell their dependent masking rules which server and database they should connect to in order to perform their actions. All other types of masking rule must have a parent Rule Controller and every masking set must contain at least one Rule Controller.
  • Insertion Rules – inserts new rows into table columns. This type of rule can use as insertion data any of the available datasets appropriate to the column type.
  • Foreign Key Enable Rules – enables foreign keys in the target database. Each foreign key can be individually marked for enable. Usually used after a Foreign Key Disable Rule has run.
  • Foreign Key Disable Rules – disables foreign keys in the target database. Usually followed by a Foreign Key Enable Rule after the other masking rules have run.
  • Trigger Enable Rules – enables triggers in the target database. Each trigger can be individually marked for enable. Usually used after a Trigger Disable Rule has run.
  • Trigger Disable Rules – disables triggers in the target database. Usually followed by a Trigger Enable Rule after the other masking rules have run.

Q: How big a table can Data Masker work on?

A: There is no size limit really. The masking process is broken up into reasonably sized chunks with frequent commits so that you do not need to worry about rollback segments or UNDO tablespace resource. As an example, tables with hundreds of millions of rows have successfully been sanitized with Data Masker.

Q: Can I share my masking set with other users?

A: Yes, it is designed for this. However, be aware that masking sets are designed for a specific purpose and will operate against a designated database. If you share the masking set you should be sure that the recipient really knows what the set will do and wants the data in the target schema changed in that way.

Q: Can a table have more than one masking rule?

A: Absolutely, place as many masking rules on a table as you need to hide the data in the columns.

Q: Can a column in a table have more than one masking rule on it?

A: Yes you can. This is frequently done if WHERE Clause options are used.

Q: My database has foreign keys. Can Data Masker still hide the data?

A: Yes it can, simply use a Table-To-Table Synchronization rule to synchronize the updates.

Q: My database has primary keys. Can Data Masker still hide the data?

A: Yes it can, but you will have to ensure that you update with unique data that is not already in use in the primary or unique key. Many data sets have options for this purpose.

Q: Can I add multiple target databases to a masking set?

A: Yes, Data Masker software this function transparently.

For SQL Server, just create a new Rule Controller for each new database.

Q: If I have multiple target databases do they have to be in the same SQL Server instance?

A: No, a Rule Controller controls the database on which its dependent masking rules operate. It does not matter if they are in the same SQL Server instance or not, or if the SQL Server instances are of the same version.

Q: Can I define a substitution rule which uses the contents of a different table to determine which rows are updated in the primary table?

A: This situation often happens. Take the example of a CUSTOMER table in which you wish to substitute first names based on the contents of the GENDER column held in the CUSTOMER_DETAILS table. The join condition between the tables is the mutual CUSTOMER_ID column. In other words, you want to substitute Female first names in the CUSTOMER table everywhere the equivalent row in the CUSTOMER_DETAILS.GENDER field is 'F'.

This is quite possible to do using only a slightly more complex WHERE Clause. Notice the way the pseudo code of the first part of the WHERE CLAUSE is written (just above the bit you type in). It is written in the form:

select <rows> from DM_TEST.CUSTOMER DTB1

Well normally you just put WHERE <and then your condition>. However, it is quite possible to make a slightly more complex Select referencing the DTB1 alias. Using the WHERE Clause below only the rows from the CUSTOMER table based on an 'F' in the CUSTOMER_DETAILS table could be selected (and hence substituted). Note the leading comma - it is required to make the syntax valid - just as it would in any SQL statement.

, CUSTOMER_DETAILS DMG WHERE DMG.GENDER='F' and DMG.CUSTOMER_ID=DTB1.CUSTOMER_ID;

Anything valid in a normal SQL Where clause is valid in a Data Masker Where Clause rule: embedded subqueries, correlated subqueries, UNIONs, selects via dblink, views etc.

Q: If I update a column, can I update another column with the same value?

A: Yes. Data Masker for Oracle specifically supports this type of operation through a rule type called a Table-To-Table Synchronization rule. Using Table-To-Table Synchronization rules you can specify a join condition and easily synchronize columns in other tables with updates in a parent table.

Data Masker for SQL Server also supports Table-To-Table synchronization, as well as Row-Internal and Table-Internal rules.

Q: Can I synchronize the data in one column of a table using values from other columns in the same row?

A: Yes, with a Row-Internal Synchronization rule on the column. Row-Internal Synchronization rules can build up the contents of a field using simple SQL statements and the contents of the available datasets.

Q: Can I apply a Table-To-Table Synchronization rule to synchronize the data between tables in different schemas/databases?

A: Yes, as long as:

  • For Oracle databases: the schema is in the same database and the primary schema has the access rights to update the child table. If the target schema is not in the same database it is still possible to do this - just trick the Data Masker by making up a "pseudo" table using a synonym and a database link.
  • For SQL Server databases: the database is in the same SQL Server instance and the Rule Controller login has the access rights to update the target table. This is just a variation on a standard Table-To-Table Synchronization rule.

Q: Can I make one rule only execute after another rule completes?

A: Yes, just make it dependent on the parent rule. The rule will not execute until the parent successfully completes. The Rule Blocks and Dependencies help page contains a detailed discussion of this feature.

Q: Can I make a rule wait until a group of other rules execute?

A: Yes, just put it in a different rule block. Rule blocks execute in strict numeric order so if the rule is in a higher numbered rule block it will not execute until all of the lower rule blocks complete. The Rule Blocks and Dependencies help page contains a detailed discussion of this feature.

Q: What types of substitution data do you offer?

A: A wide variety. We offer pre-built datasets for just about every need. The current list can be found on the datasets help page.

If your requirement is extremely specialized, you can easily build your own datasets - or we will provide advice on how to generate the values you need from the existing datasets.

Q: Can Data Masker execute multiple masking rules at the same time?

A: Yes, up to eight rules can execute simultaneously. Rule Blocks and Dependencies can be used to control the execution order of the rules since it often happens that certain rules must complete before others are permitted to run.

Q: Can Data Masker mask multiple columns within the same masking rule?

A: Yes, an arbitrary number of columns can be masked within the same rule. For example, a single Substitution rule could mask the data in the FIRST_NAME, LAST_NAME, ADDRESS, BIRTH_DATE and PHONE_NUMBER columns. Each column can have an individual dataset configured and the rule only makes a single pass through the table.

System requirements & installation

Q: What are the PC system requirements for Data Masker?

A: You can read more about the hardware and software system requirements for Data Masker for SQL Server here, and Data Masker for Oracle here.

Q: What are the server side requirements for Data Masker?

A: Data Masker requires a small temporary table to be created in the target SQL Server database or Oracle schema. This table can be deleted after the masking operation completes and will never contain more than one row for each masking rule which executes. No other components are needed. Data Masker requires no installation of software of any sort on the server and requires no server side disk space.

Q: What server side operating systems does Data Masker support?

A: Data Masker can be used to mask data on any server that will run an Oracle or SQL Server database. Data Masker is server neutral.

For Oracle, it runs on a PC and connects via the Oracle supplied SQL*Net package or through a direct TCP/IP connection to the target database.

For SQL Server, it connects via the Microsoft supplied ADO software to the target database.

The server side operating system is largely irrelevant to it.

Q: Does Data Masker work against SQL Servers running on 64 bit platforms?

A: Yes. Data Masker is fully functional when masking data in SQL Servers installed on 64 bit Windows operating systems.

Q: Can I put my temporary directory on a Network Drive?

A: Yes, but it is a bad idea. Network drives are much slower than local drives and Data Masker will slow down. A better idea is to place it on your local drive.

Q: What version of Data Masker am I currently using?

A: Every copy of Data Masker contains version information in the "About Data Masker" dialog box. To see this dialog box start any Data Masker application and click on the logo in the upper right hand corner.

Q: How can I see or edit my license key?

A: To view the License Key dialog box press the Registration Key button in the "About Data Masker" dialog box.

A new evaluation or licensed activation key can be requested by pressing the Request New Key button on the About Data Masker form.

Q: How do I upgrade Data Masker?

A: Just get the latest version and re-install. You do not need to uninstall first, and you can skip as many versions as you wish. Your saved settings and license keys will be preserved.