?
Solved

Masking columns in SQL server 2005

Posted on 2010-01-11
9
Medium Priority
?
818 Views
Last Modified: 2012-06-21
I am looking for guidance in Masking 10 columns in the Database of SQL Server 2005.My criteria is to maintain the same lenght of the fields in the columns after masking. I would like to mask with the ###### and please let me know where to start.  
0
Comment
Question by:Govinda2020
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 26284538
why?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 26284541
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 26284595
That is not usually done in database management; the applications using the data is usually responsible for masking.  You can declare your table's fields to be specific datatypes of course and I guess if you wanted to store numeric data as strings you could restrict the length by only allowing 6 characters but that is a bit of a hack because let's face it...you want to store numeric data and you should treat the data for what it is.  Anyway, think about why you want to do this and I'm sure we can all come up with a better solution that masking in the database for sure.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:Govinda2020
ID: 26286722
We have to mask the database as it contains customer data and sensitive data. The main purpose we have to copy live data to test environment. So we want to mask the relevant data without losing the relationship of the database.  So we wan to mask customer name, customer address and so on , the only requirement is to mask the data and there is no need for any encryption. Can you provide any sample SQL that can be use to mask a column and i will work on from them.
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 26288188
This is a common need.  Basically, if you have users that should not see the sensative data, don't let them use SQL Server to access the information in the database directly.  Your applications should mask/hide and otherwise protect and/or verify data going-in and coming-out of the database.   I hope that does not sound condescending in anyway...apologies if I'm coming off patronizing, but protecting your data is very important and the database simply HOLDS it.  Accomodating non-IT type users in viewing or especially in manipulating data is for applications, not for database management tools.
0
 

Author Comment

by:Govinda2020
ID: 26291332
Yeah you are correct, I am IT guy having access to Database and they do'nt want me to look at the data and even the reports and viewing the data in the applicaiton by users (non-IT) should not see the data. So I think the best option is to mask those senstive columns. Hope you understand where I am coming for. Please let me know if i can start if with sample SQL masking code to be done on one column in the database.
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 26294259
0
 
LVL 15

Accepted Solution

by:
David L. Hansen earned 2000 total points
ID: 26294329
0
 

Author Comment

by:Govinda2020
ID: 26296986
Thanks sl8rz , I will try them and give you shout if I have anything
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question