Dynamic data masking with deterministic values

Question

Dynamic data masking with deterministic values

Aravindhan Ravichandran 20

I would like to check for data masking solution when reading data from a SQL Server database.

To elaborate further, the data elements should be encrypted/masked when a set of users querying the tables. The data stored at rest should be clear (i.e. unmasked/ not encrypted).

Also, the masking values should be consistent(deterministic) across the database. E.g. if a username john - masked/encrypted to xyz in schema 1 table a, schema 2 table b should have the same masked/encrypted value for the same user. So that we can join the cross-schema tables.

Key notes:

Data at rest should be in clear form i.e. unchanged from source.
Encryption/masking should be deterministic.

I have explored the SQL Server TDE & DDE options but doesn't suit my requirements.

Would like to hear from experts who handled such a requirement in a SQL Server database.

Thanks in advance!

Erland Sommarskog 134.3K Reputation points MVP Volunteer Moderator

2024-04-24T21:33:05.9333333+00:00

What is the underlying business requirement here? Masking and encryption are somewhat related, but still quite different. So it could help if you could clarify from a business perspective what the requirement is.
Aravindhan Ravichandran 20 Reputation points

2024-04-26T03:14:01.2133333+00:00

Hi @Erland Sommarskog ,

The need is to mask the data with deterministic value while reading the data from tables.

The masking values should be consistent across schemas for analytical use cases.
Erland Sommarskog 134.3K Reputation points MVP Volunteer Moderator

2024-04-26T21:17:08.2333333+00:00

You are not answering the question. You are only repeating what you said initially. I was asking for the underlying reason why you want to do this. With a better understand of the actual requirements, we may be able of thinking of different ways to go.

At a minimum, you could at least give an example of what you want to achieve.
Aravindhan Ravichandran 20 Reputation points

2024-04-29T04:59:15.77+00:00

Our requirement is to obfuscate the PII fields for specific set of users across the database with same masking values. The intention of deterministic masking is to facilitate the analytical needs across the databases or schemas

Answer accepted by question author

Erland Sommarskog 134.3K MVP Volunteer Moderator

You could also use the HASHBYTES function to produce your masked values.

Aravindhan Ravichandran 20 Reputation points

2024-04-30T01:50:05.3166667+00:00
Hi Erland, Yes , I have used the hash bytes function and it gives me the consistent masking values.

Now my concern is on managing the user access.

e.g. I have 100 users in total, out of 100 users, the data masking to be applied for 10 users. Initially my thought process was to use case statement to manage this.

---CASE WHEN SYSTEM_USER = 'specific_user' THEN

CONVERT(VARCHAR(MAX), HASHBYTES('SHA2_256', [PERSON_ID_EXTERNAL]), 2)

--ELSE [PERSON_ID_EXTERNAL] --END AS EncryptedStaffName

this will increase the workload and can get messy if we have more users comes in the future. Any alternate approach for managing the access control?
Erland Sommarskog 134.3K Reputation points MVP Volunteer Moderator

2024-04-30T20:48:19.8966667+00:00

I assume you have a user table. In this table you would have a flag to tell whether this users gets the masked or unmasked version.

1 additional answer

Your answer

Erland Sommarskog 134.3K Reputation points MVP Volunteer Moderator

2024-04-24T21:33:05.9333333+00:00

What is the underlying business requirement here? Masking and encryption are somewhat related, but still quite different. So it could help if you could clarify from a business perspective what the requirement is.
Aravindhan Ravichandran 20 Reputation points

2024-04-26T03:14:01.2133333+00:00

Hi @Erland Sommarskog ,

The need is to mask the data with deterministic value while reading the data from tables.

The masking values should be consistent across schemas for analytical use cases.
Erland Sommarskog 134.3K Reputation points MVP Volunteer Moderator

2024-04-26T21:17:08.2333333+00:00

You are not answering the question. You are only repeating what you said initially. I was asking for the underlying reason why you want to do this. With a better understand of the actual requirements, we may be able of thinking of different ways to go.

At a minimum, you could at least give an example of what you want to achieve.
Aravindhan Ravichandran 20 Reputation points

2024-04-29T04:59:15.77+00:00

Our requirement is to obfuscate the PII fields for specific set of users across the database with same masking values. The intention of deterministic masking is to facilitate the analytical needs across the databases or schemas
Aravindhan Ravichandran 20 Reputation points

2024-04-30T01:50:05.3166667+00:00

Hi Erland, Yes , I have used the hash bytes function and it gives me the consistent masking values.

Now my concern is on managing the user access.

e.g. I have 100 users in total, out of 100 users, the data masking to be applied for 10 users. Initially my thought process was to use case statement to manage this.

---CASE WHEN SYSTEM_USER = 'specific_user' THEN

CONVERT(VARCHAR(MAX), HASHBYTES('SHA2_256', [PERSON_ID_EXTERNAL]), 2)

--ELSE [PERSON_ID_EXTERNAL] --END AS EncryptedStaffName

this will increase the workload and can get messy if we have more users comes in the future. Any alternate approach for managing the access control?
Erland Sommarskog 134.3K Reputation points MVP Volunteer Moderator

2024-04-30T20:48:19.8966667+00:00

I assume you have a user table. In this table you would have a flag to tell whether this users gets the masked or unmasked version.

Answer 1

LiHongMSFT-4306 31,621

Hi @Aravindhan Ravichandran

How about encrypting data by using a symmetric key.

See these docs: ENCRYPTBYKEY (Transact-SQL); CREATE SYMMETRIC KEY (Transact-SQL)

Best regards,

Cosmog Hong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Aravindhan Ravichandran 20 Reputation points

2024-04-26T03:10:33.52+00:00

Hi @LiHongMSFT-4306 ,

Thanks for your time.

I have tried with that feature by creating symmetric with a password and created a view with the encrypted column.

By while running the select statement, the views show null values for the encrypted column.

Any issue with key created?
Erland Sommarskog 134.3K Reputation points MVP Volunteer Moderator

2024-04-26T21:15:16.58+00:00

If you go the encryption path, you would not try to decrypt the value as that would defeat the purpose. Rather you would display the binary string.
Aravindhan Ravichandran 20 Reputation points

2024-04-29T04:56:49.5933333+00:00

Hi Erland, I need not decrypt the values. Our intention is just to mask the critical fields at the same time masked values should be consistent across for cross schema analytical use cases.

Share via

Dynamic data masking with deterministic values

1 additional answer

Your answer