Data Masking Techniques and Issues with Creating Tables

[weilin0323]

Hello Databricks Team,

I understand that the mask function can be used to mask columns, but I have a few questions:

1. When users with access use a masked TABLE to create a downstream TABLE, the downstream TABLE does not inherit the mask function directly, so the data remains unmasked. In this case, do we need to apply the mask function again to the columns that require masking when creating the downstream TABLE?

2. If users without access to the original data use a masked TABLE to create a downstream TABLE, the masked columns in the downstream TABLE will display the masked values, but it seems that this masking is hard-coded rather than applied through the mask function. How can this be addressed during querying? For example, if a user wants to query data for id=1234, but the id appears as '1**4' after masking, will it be impossible to use id=1234 as a query condition?

We would greatly appreciate any guidance on how to resolve these issues. Thank you for your assistance.

[Brahmareddy]

Hi @weilin0323, How are you doing today?

As per my understanding, When creating a downstream table from a masked table, you’ll need to reapply the mask function to the necessary columns in the new table if you want to maintain the same level of data protection. For users without access to the original data, the masked values in the downstream table are indeed hard-coded. This means if they try to query using an original value like id=1234;, it won't work because the data is already masked. To address this, consider applying the mask function during the query process, or provide a separate mechanism for those users to query based on the masked values.

Give a try and let me know.

Regards,

Brahma

[weilin0323]

Thank you for your response.

I tried to create a function that can be used in a query:

CREATE OR REPLACE FUNCTION idno_filter_test(idno STRING, filter_value STRING)
RETURNS BOOLEAN
RETURN IF(current_user() = "admin@test.com", idno = filter_value,
    concat(left(idno, 2), "**", right(idno, 2)) = concat(left(filter_value, 2), "**", right(filter_value, 2)) )

However, this may result in errors in the query. For example, with the following condition:

SELECT * 
FROM working.idno_masked_downstream
WHERE idno_filter_test(memidno, "123456")

Both "123456" and "124356" match the condition id=12**56, so it may return two results.

Additionally, if I want to join two tables using idno as the key, how should I configure the masked table to ensure it can be matched with other tables based on the key value?

SELECT aa.order_number, aa.IDNO, bb.MEMIDNO
FROM processed.order_integrate AS aa
INNER JOIN working.idno_masked_downstream AS bb
ON bb.MEMIDNO = aa.IDNO

I would greatly appreciate any guidance on how to resolve these issues. Thank you for your assistance.

[Brahmareddy · Accepted solution]

Hi @weilin0323,

Good morning.

To avoid multiple results with masking, try using a hash function like sha2 in your function instead of partial masking. This ensures each value is unique, even when masked. When joining tables, apply the same hash to both columns so you can match them without revealing sensitive data. For filters, using hashing or encryption will give you more accurate results compared to partial matching. Also, consider using views with dynamic masking to consistently apply masks across queries. This approach will help maintain security and integrity in your data.

Regrads,

Brahma

[weilin0323]

Hi @Brahmareddy ,

Thank you so much. I'll try it.