how to mask or hash data stored in a column

Question

how to mask or hash data stored in a column

RJ-476420

SSCarpal Tunnel

Points: 4806
More actions
March 12, 2009 at 5:55 am

#129692

My requirement would be to mask the data stored in a column in table.
For example:
Create Table1 (col1 int, col2 varchar(256))
I want col2 to be masked i.e. i dont want the column to be in clear text instead look like an hashed out column but when you perform a select or any action on the column it should be a clear text value. Basically, I dont want the column to be hashed out or encryped but for open eyes it should be masked out.
Is there a way to do it in SQL Server 2005.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

JamieX Hall of Fame Points: 3763 More actions · Answer 1

I want col2 to be masked i.e. i dont want the column to be in clear text instead look like an hashed out column but when you perform a select or any action on the column it should be a clear text value. Basically, I dont want the column to be hashed out or encryped but for open eyes it should be masked out.

To see the data you perform a select (open eyes and in applications 🙂 ). I assume you mean in SSMS you don't want to see it, but in your app you do. They both access the data in the same way. So you can setup encryption in your application, or you can restrict users from seeing the column.

Perhaps this is what you're looking for?

http://dotnetslackers.com/articles/sql/IntroductionToSQLServerEncryptionAndSymmetricKeyEncryptionTutorial.aspx

RJ-476420 SSCarpal Tunnel Points: 4806 More actions · Answer 2

well, you have an option to store stored procedure or function encrypted in the database but would be visible only to certain users. is there some functions that allows in an user table?

JamieX Hall of Fame Points: 3763 More actions · Answer 3

http://technet.microsoft.com/en-ca/library/cc966395.aspx

This is a pretty good explanation of how to implement security.

If I needed to do this, I would just restrict access to the table to all users but the application id (if the app works that way) and then create a view that does not show that column (or has a null value representing that column) and give them select access to that.

Richard M. SSCertifiable Points: 7401 More actions · Answer 4

There is a much easier way, without encryption.

You can define col2 as either a varbinary or image type and cast the string into it. To Select, from your app, you'd do the opposite.

To save the data

INSERT INTO Table1

VALUES (1,CAST('Your String' as varbinary(256)))

To extract the data

SELECT col1,CAST(col2 as nvarchar(256)) FROM Table1

To someone merely browsing the table, they would see only nonsense data.

Just substitute varbinary with image in case you use that one, but an image field would be mostly used if you have loads of text to save, which doesn't seem to be your case.

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

RJ-476420 SSCarpal Tunnel Points: 4806 More actions · Answer 5

Storing it in binary seems to be a good plan. but, i have to check with my project if this comply to what we are looking. anyway thanks y all. and, please keep posting any new ideas.

Thanks a lot.

RJ-476420 SSCarpal Tunnel Points: 4806 More actions · Answer 6

My application directly accesses the database tables (application creates insert, update, select statements & executes against the database.) i.e. there is no stored procedure in between application & tables so what encryption type should I use. Either it should be create master key & use encrypt by Key or passphrase.