CODEWITHSUNDEEP
sqlsql-serversql-server-2014
Chun Yin
Chun Yin

Reputation: 290

Select from table while masking a column

I'd like to export a table while masking a specific column, something like:

SELECT randomMask(userId), name, location FROM travel_location;

Requirements:

  1. Masked column must be random but consistent for that query. Eg 2345 will always be randomized as abwr for that query.
  2. Masked column must not be able to be decoded. abwr should not be able to be decoded into 2345 by me after running the query.
  3. Every query should randomly mask the id differently, 2345 may be masked as abwr this time, but it should be something else next time.

example result:

enter image description here

Upvotes: 2

Views: 4720

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

You can use checksum() to get a reasonable random value:

select checksum(userId)

Of course, there are collisions, but that is probably acceptable.

Alas, this will be the same each time you run the query. To fix that, you could add a random number or use the time:

select checksum(userid + rand())

or:

select checksum(convert(varchar(255), userid) + convert(varchar(255), getdate())

Both rand() and getdate() are guaranteed to be the same on all rows for for each expression they occur in. However, they will vary from query to query.

Upvotes: 1

Eray Balkanli
Eray Balkanli

Reputation: 7960

You can try (works for SQL Server 2016 or more):

ALTER Table travel_location
ALTER COLUMN userId ADD MASKED WITH (FUNCTION='Random(1,100000)')

The users who were not granted to unmask like below wouldn't see the real value after this change.

GRANT UNMASK TO TestUser;

Upvotes: 0

Related Questions