This article describes a dynamic data masking (DDM) method available to IRI FieldShield premium sites that uses a proxy-based system for intercepting application queries to JDBC-connected databases. It is one of several approaches to masking data in flight which FieldShield users can consider.

Other IRI DDM options include: API-callable FieldShield functions embedded into C/C++/C#, Java or .NET programs; real-time FieldShield functions embedded into SQL procedures that create masked views; and, dynamic unmasking of statically masked tables for authorized users.

The proxy-based system introduced here uses a fit-for-purpose, database-specific “JDBC SQL Trail” driver in conjunction with a configuration and management web application called SQL Sharp (SQL#). This diagram shows the system architecture before and after implementation:

This application currently supports the following relational database platforms:

• Oracle 11g, 12c, 18/19c
• PostgreSQL 9.5, 9.6, 10, 11
• MS SQL 2014, 2016
• SAP HANA 2.0

and requires the following third-party components:

• MS Windows 7,10, or Server 2012 and later (tested).
• Java JDK and JRE 1.8 or later.
• Tomcat 8.5 or above to run the SQL# web server.
• A modern web browser, such as:
  • Google Chrome
  • Mozilla Firefox
  • Apple Safari
  • Microsoft Edge
• Oracle or PostgreSQL as the repository database to store:
  • SQL# user and group configuration
  • DB access and activity controls
  • Dynamic data masking policies
  • SQL audit logs

How Does It Work?

Within the SQL# web application, you create data masking policies to redact column values in flight for all but authorized users connecting to the database through the JDBC SQL Trail driver. You need to install and configure that driver for each database instance you want to protect.

The DDM policies define which tables and columns to mask, and how the masked values will appear. Once the system is properly configured, all queries connected through the driver will be subject to the masking policy.

It is also possible to define policies to block users from logging in and certain SQL activities. A full login and SQL activity audit log is produced, and viewable in SQL#.

The driver does not differentiate between application users for blocking, masking, or auditing purposes. However, you can authorize specifically named users — making alternative application server connections to the DB through the same driver — to see data unmasked.

Creating a Masking Policy

To create a masking policy in SQL#, Use the Masking Policy tab of the SQL# Execute Management screen. Select the + (Add) icon to the right of the Masking Rule List label.

Give the masking rule a name, and an optional description. You can then choose the type of mask that will apply from the Masing Regex: drop-down in the Add Masking Rule dialog.

The first three options are predefined, while the Regex allows you to define a custom masking format. Click the + (Add) icon to the right of the TAB/COL label to add one or more table and column combinations, to specify which data values will be masked.

After each combination of table and columns have been made, Click the Add button in the middle of the dialog to put them into the list. When you are finished specifying table and column locations, Click the Add button at the bottom to add the locations to the Add Masking Rule dialog.

Finally, click Save at the bottom of the Add Masking Rule dialog when finished with the masking rule. At this point, all users who are configured for access to the data will see masked values when connecting through the JDBC SQL Trail proxy driver.

To allow a user to view unmasked data, you must add them to the Unmasked User List, as described below.

Granting Authorization to Users

Within the same Masking Policy tab of the SQL# Execute Management screen. Click the + (Add) icon to the right of the Unmasked User List label. This will display the Search User dialog where you can select one or more users for whom queries into the selected columns and tables will not be masked.

Click Save at the bottom of the dialog when you are finished selecting users.

Using SQL# and SQL Trail from DB Applications

In this example, our database application will be IRI Workbench, the Eclipse front-end job design environment for Voracity, FieldShield, and other IRI software products.

To enable your applications for SQL control and dynamic data masking using the SQL# proxy server and the JDBC SQL Trail driver, you will need to activate SQL# through Tomcat and its proxy server. You must also configure the JDBC SQL Trail driver in the IRI Workbench Data Source Explorer view, as well as the DDM policies in SQL# as described above.

Here is a view of the Oracle instance connected through the JDBC SQL Trail driver.

Note that all normal database operations and IRI job wizards will continue to work through this connection. That also means that any unauthorized activity from IRI Workbench will be blocked, and all SQL commands issued from here to the connected database will be recorded in the SQL# audit log.

This is a Workbench query on the ORDERS table that was policy-configured for DDM in SQL#:

vs. the same query ran by an authorized user, which displays the original unmasked data:

Meanwhile back in the SQL# application’s logging section, you can see our query record:

from the IRI Workbench IP address.

If you have any questions, or need help with dynamic data masking in this environment or via the FieldShield SDK, please email fieldshield@iri.com.