Masking (Deidentification)

Question

Masking (Deidentification)

ramana3327

SSCoach

Points: 19722
More actions
October 21, 2014 at 1:11 pm

#294403

Hi,
I have a table. I have some sensitive data that I don't want to display that sensitive data. The columns I want to abstract are
Firstname varchar(50) not null
Latname varchar(50) not null
dob datetime not null
addressline1 varchar(50) null
adressline2 varchar(50) null
city varchar(50) null
postalcode varchar(20) null
I want to display null values as nulls empty as empty not null as numbers
How can I do that?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

With the detail provided, my suggestion would be to replace non-null values with a GUID. You may have some difficulty with the postal code column if you have any type of DRI to a postal code table or other type of postal code validation.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 2

You might take a look at this article[/url] on Simple-Talk. It could help.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 3

Quick example of a simple method, normally does the job but strictly speaking it is reversible although it would require a hash-table approach.

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @SAMPLE_DATA TABLE

(

SD_ID INT IDENTITY(1,1) NOT NULL

,Firstname varchar(50) not null

,Latname varchar(50) not null

,dob datetime not null

,addressline1 varchar(50) null

,adressline2 varchar(50) null

,city varchar(50) null

,postalcode varchar(20) null

);

INSERT INTO @SAMPLE_DATA

(

Firstname

,Latname

,dob

,addressline1

,adressline2

,city

,postalcode

)

VALUES

('Albert','Einstein','1879-03-14','Banhofstrasze',NULL,'ULM','')

,('Alexander Graham','Bell','1847-03-03',NULL,'','Edinburgh','EH1-17')

SELECT

SD.SD_ID

,ABS(CHECKSUM(REVERSE(Firstname )) * SIGN(DATALENGTH(Firstname ))) AS Firstname

,ABS(CHECKSUM(REVERSE(Latname )) * SIGN(DATALENGTH(Latname ))) AS Latname

,ABS(CHECKSUM(REVERSE(dob )) * SIGN(DATALENGTH(dob ))) AS dob

,ABS(CHECKSUM(REVERSE(addressline1)) * SIGN(DATALENGTH(addressline1))) AS addressline1

,ABS(CHECKSUM(REVERSE(adressline2 )) * SIGN(DATALENGTH(adressline2 ))) AS adressline2

,ABS(CHECKSUM(REVERSE(city )) * SIGN(DATALENGTH(city ))) AS city

,ABS(CHECKSUM(REVERSE(postalcode )) * SIGN(DATALENGTH(postalcode ))) AS postalcode

FROM @SAMPLE_DATA SD;

Results

SD_ID Firstname Latname dob addressline1 adressline2 city postalcode

----------- ----------- ----------- ----------- ------------ ----------- ----------- -----------

1 908524580 32315969 182191641 1453234408 NULL 60048253 0

2 1585822690 1232888188 1701236231 NULL 0 1631151168 447240984

ramana3327 SSCoach Points: 19722 More actions · Answer 4

Thanks All,

They change the mind. They just need to create view with same requirement. All sensitive data to be masked with creation of view.