Let’s discuss a few variations of Dynamic Data Masking (DDM) and explain the differences from its static sibling. We’ll cover different implementation approaches, which apply in what situations, and the preferred alternatives.

Static vs. Dynamic: The “Permanent” vs. “On-the-Fly” Debate

Before diving into the technical implementation, we must distinguish between the two primary philosophies of masking: Static and Dynamic.

Static Data Masking (SDM)

Think of Static Masking as a “permanent makeover”. In this process, we physically change the data stored in the database. The original sensitive values are overwritten with realistic but fake data.

• How it works: It uses UPDATE operations to desensitize the data.
• When to use it: This is the gold standard for non-production environments (Dev, Test, Analytics). It is simpler, cheaper, and safer than any other method. Once masked, the database is inherently “safe”, meaning you don’t need complex security protocols because the real data simply isn’t there anymore.
• When NOT to use it: When you need the data, you cannot remove it. Static masking is, therefore, inappropriate for Production databases. An uncommon exception is when masked data is purposefully used for specific production activities. For example, for public reporting, 3rd party access, and more.

Dynamic Data Masking (DDM)

Dynamic Masking is a “smoke and mirrors” approach. The real data remains untouched in the database, but it is disguised when it is sent to some of the users.

• How it works: It intercepts the SQL query and rewrites it on the fly.
• When to use it: This is designed for production environments where the data must remain real for the system to function, but certain users should only see a redacted version.
• When NOT to use it: While you could use dynamic masking in non-production environments, the cost and complexity make it inappropriate. Additionally, a database with dynamic masking still contains sensitive information and must be fully protected. That further increases the costs and complexities. Static masking is the preferred approach in those cases.

How it Works: The Art of the SQL Rewrite

Dynamic masking is essentially a translation exercise. When a user requests data, the masking engine intercepts the request and modifies the SQL statement. There are two primary ways to achieve this:

The Column Rewrite

This method replaces the requested column with a masking function.

• Original: SELECT col1 FROM table1
• Masked: SELECT mask_func(col1) FROM table1
• The Challenge: SQL parsing is technically challenging in general and, even more so, because it relies on database metadata. Here are some examples:
  • “SELECT *” statements are challenging because the column name doesn’t appear, and the * needs to be replaced with the current list of columns (requires database metadata).
  • Column and table aliases are challenging because column and table names can appear differently in different parts of the SQL. The alias may also contain the column name, and the alias must not be masked.
  • WHERE clauses must also be masked to avoid data leakage, but these can potentially break the SQL logic and impact SQL performance.
  • In a join, column names can refer to either table. They can explicitly target a table based on its name (or alias), or implicitly refer to the table that contains them (parsing relies on database metadata).
  • Minor challenges include dealing with subqueries or aggregates, like SELECT SUM(col). These are usually handled easily by parsers.

All these can be partially addressed, but they cannot be addressed as perfectly as the database view solution below. A view leverages the database SQL parser to embed the masking everywhere.

The Masked View

This method involves creating a “shadow” version of the table. Usually, it uses a view, but it can also leverage a materialized view or a statically masked table.

• Original: SELECT col1 FROM table1
• Masked: SELECT col1 FROM table1_masked
• The Challenge: This is safer and much more robust in terms of SQL rewrite. However, it can be easily bypassed by personnel with high-level database-level access, such as DBAs. You cannot, for example, dynamically mask DBA activity using a view since a DBA can examine the underlying table or modify the view to reveal the truth.

Other Methods

It’s worthwhile discussing a couple of other alternatives:

Replacing the data: Some solutions attempt to replace the data returned by the query rather than rewriting the query. That is extremely fragile and easy to bypass since the data is “flavorless”. It’s impossible to know if 92000 is a salary, a zip code, a customer ID, or something else. The column names returned can be easily modified, and so can the data (e.g., by using the translate function).

DIY: Some approach the problem as an application coding challenge. Masking application data is akin to an application feature or enhancement. However, such enhancements are not possible in off-the-shelf applications or applications not in active development. Even for applications in active development, it can be a monumental task that is better solved by a dynamic masking solution.

Location Matters: Application-Side vs. Database-Side

Once you decide to use Dynamic Masking, you must decide where to place the “masking brain.”

Application-Side (The Client Side)

This is often the most robust solution that answers the customer’s needs. Tools like Core Audit for Java can plug into the Java application at runtime and add the required dynamic masking logic to the JDBC client driver.

The Benefit: The application knows who the end-user is. In modern systems, the application and all its users share a single database user. They often also share the same database connections (session pooling). The database can’t distinguish between the different end-users, but the application can easily do that. By rewriting the SQL inside the application, you can apply dynamic masking based on the specific user without touching a single line of source code.

Additionally, application-side masking is safer since it doesn’t interfere with the database operation and how the database services other users.

Database-Side (The Server Side)

When aiming to mask data sent to different database users or to multiple applications, masking at the database level is the right solution. Solutions like Core Audit for Databases connect to the database’s SQL engine at runtime and add the masking logic to the database. Some database engines have this functionality built in, though, often, with severe limitations.

However, masking at the database level has a “blind spot”. Usually, it cannot see the original application end-user, especially when session pooling is involved. It treats all traffic coming from the application as one “user” and one “application”, rendering more granular security nearly impossible.

Dynamic Masking vs. SQL Blocking

Sometimes, masking is actually the wrong tool for the job. This is where SQL Blocking comes in.

You have two options to prevent an administrator or analyst from accessing sensitive data: rewrite their query to show them stars (Masking) or stop the query entirely (Blocking).

Security Pro-Tip: Blocking is inherently safer than masking. Rewriting a “dangerous” query into a “safe” one is error-prone. That can be dangerous when the person submitting the query can modify it until they find a variation that the masking engine fails to rewrite correctly. If a query shouldn’t run, blocking it is a definitive, un-circumventable security measure.

Additional Considerations

• Security Strength: There’s a big difference between these two types of requirements:
  • Masking hard-coded application SQLs where the dynamic masking functionality can be tested. In this case, robustness and “zero-holes” requirements are less critical. The user cannot change the SQLs they submit, and the implementor can ensure the application data is properly masked.
  • Masking SQLs written by individuals (like DBAs or analysts) who can change those SQLs in an attempt to bypass security. In this case, the “full proof” requirements are far stricter. Being able to “usually” mask “most cases” isn’t good enough.

The more the requirements shift towards impregnability, the better it is to move from dynamic masking to blocking. In such cases, you should also audit the activity to compensate for potential weaknesses that may still exist.

• Performance: There are three performance aspects to dynamic data masking:
  • The overhead on applications or sessions that don’t require masking. This is only for database-side masking (not application-side). This performance impact should be minuscule and immeasurable.
  • The impact on SQLs that don’t require masking, but in sessions that may require it. This overhead should be extremely small and represent the evaluation of every SQL against all the relevant masking and blocking rules.
  • SQL rewrite speed. This is the additional delay when dynamic masking is applied. It is usually infrequent and small compared to the database response time.

Application-side dynamic masking tends to scale better because it runs in parallel on each of the application servers and scales with the application. Database-side dynamic masking relies on limited database-server resources. However, in nearly all cases, neither is a problem.

• Manageability: If you have multiple application servers or database servers that must be synchronized to follow the same masking rules, coordinating them all may be challenging. Central management can be vital in those cases.

The Bottom Line: Your 4-Step Strategy

Protecting your data isn’t about picking one tool; it’s about using the right tool for the specific threat. Here is how to organize your defense:

1. For Dev/Test Environments: Use Static Data Masking. Make the data “safe” by replacing it entirely as soon as it leaves production.
2. To Protect Production from DBAs: Use SQL Blocking. Since DBAs have “God-mode” permissions, traditional access controls won’t work. Blocking unauthorized queries is the best way to mitigate threats from these accounts.
3. To Protect Production from App Users: Use Application-Side Dynamic Masking. This provides the necessary context to show “partial” data (masked) based on the end-user.
4. For General Database Users: For other users (not privileged or the application), use standard Database Permissions (GRANT/REVOKE). Only move to masking or blocking if these granular controls prove insufficient for your needs.

Beyond these preventive steps, a proper security posture must include the rest of Activity Control: monitoring, alerting, reporting, forensics, and anomaly analysis. These will help you catch the attacks you failed to prevent or didn’t see coming.