Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need A Help in DATA MASKING in SQL SERVER 2008


Need A Help in DATA MASKING in SQL SERVER 2008

Author
Message
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16580 Visits: 17024
Learner44 (7/22/2013)
Hi Sean ...

can you please suggest me any way to achive the following objective.?

what should I do , in order to restrict other 5 users , having same access as me on the server, so that they can not get an idea about that sensitive information in "clientAccountNumber" column.?



This is the problem. If all 6 have the same access they ALL see the same thing. If you want to change access for certain individuals by definition they no longer have the same access. There just isn't any way around this. You MUST do some sort of user level permissions.

If you want to keep your legacy systems working maybe you can create a view to this table that does not include the encrypted data. Then you could rename the original table to something like OriginalTable_Encrypted. Your new view would be the original table name. Then you deny select permission to the table, except for the people you want to view the encrypted data. And you then grant select permission on the new view to the people who can currently select from the existing table.

There really just is no way around this. You are going to have to do some work with permissions in order to do this.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Learner44
Learner44
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 403
Ok "BigHelpers" here is the main point,

I guess finally I came out with proper quetion, Please forgive me for any spelling mistakes...I am trying my best to get command on english language.

so, my issue is ..

there are 4 tables called, 1) ClientInfo, 2) ClientAccount, 3) ClientDimension,4) ClientSecurity.

All of them have one common column called "AccountNumber", which PK in ClientAccount table and FK in all other tables.

There already script written by us, whch performs Joins, Stored Procedures and Cursors and retrive the data across these tables based on matching table1.AccountNumber = table2.AccountNumber.

Now, at moment we are in test enviornment, where we do the testing, but while doing that we can see the actual
"AccountNumber" of the clients.

Now , my task is to convert this "AccountNumber" column's data in all 4 tables into encrypted , masked or somthing other form , so that we don't have to make a change in our Joins, Stored Procedures and Cursors , which we are using right now and we can still run them via matching
table1.AccountNumber = table2.AccountNumber, but this time "AccountNumber" data will be in unidentified form.

and only DBA people , can go back or see with some techniques and make references of this unidentified data in "AccountNumber" to actual "AccountNumber" column data.

any suggestion on this , will be a huge huge help from you to me , as always..

thanks.
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36148 Visits: 18751
you can't do this. If this data is used in joins, you can't do it. you've designed things poorly.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Learner44
Learner44
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 403
Oh..no..Crying

I am pretty sure that there will not be any way, once you said.

Is there any way we can solve from the scratch, or somthing else., because data is loaded already in all 4 tables and they are like 3500+ records...!!!!!

Because in my team...our "Bigheads" don't want us to see the real accounnumber of clients , while we develop any storedprocedure or cursor or even monthly ETL in our test enviornment.

My condition is like , i have to change somthing at data level to make it unidentified in the table with some algorithm or technique, so that other guys can still run the same scripts as they run from several months, but now they want be able to identified individual clients.
Learner44
Learner44
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 403
I mean to say...

Can we add extra column in each table where "AccountNumber" column exists and apply somekind of algorithm, make modification in "AccountNumber" column data and make it unidentified and drop the original column or make that column unavailable for other user..

is it somthing like this possible?

thanks.
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36148 Visits: 18751
It's possible, but it's not simple. how do account numbers get populated, and then used in other tables? This entire process has to change if you are going to re-use the "account number" table you currently have for another purpose and move the actual values somewhere else.

Encryption can help, but you really have to think about who can access the information and how, and how you can change your code for the decryption.

Right now, you aren't going to solve this in a forum. It's a major change and an architectural change as well. It also sounds like you don't have enough knowledge to design this. It seems obvious to me what problems you have, but it's a lot of work to explain them and detail them. That's the type of work I would want to be paid to do. If you don't see the issues, then you aren't really ready to tackle this by yourself.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16580 Visits: 17024
So what you really want is a way to randomize the values in your test system? This is an entirely different situation than encrypting data in a column. Of course all you are really going to do here is to change the value of the account number. This would be really simple to do. Just create a quick and easy algorithm to take the existing value and turn it into something different. Then update all of your tables using the same algorithm.

Also, you mentioned twice about developing cursors and you throw that out there like it is something you do frequently. Cursors and other forms of looping in sql server is probably the absolute worst thing you can do for performance. There are times that they are needed but those are generally left to administrative tasks and NOT something that you need in every day sql programming. Next time you are about to create a cursor, instead start a new thread on SSC and we will help you figure out how to perform the same thing a LOT faster.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Learner44
Learner44
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 403
I completely agree with you Steve...As this is not the easy task.

I am fresh graduate...I know this very hard for me..as new to this orgranisation where , I am working .

Honestly I am not in a condition , to pay for this solution, I know that this task reuqires Experienced Guy.

Can you suggest me a steps for encryption to handle this situation, if it doesn't costs too much to you in terms of time.!!

Side by Side I am trying understand the encryption from this forum, and online, as I have only acedemic knowledge about it.

Thanks for all you help.
Learner44
Learner44
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 403
I am not sure whether or not I am gonna make this solution or not, but as per your explaination,

You want me to do somthing like following.

1) pick column "AccountNUmber" from each table.
2) take one by one character from eache AccountNumber
3) e.g.
if account number is 1234

4) then wirte an algorithm that convert

1 into ^
2 into *
3 into A
4 into #

somthing like this?

5) do this for all the tables (Parent and Child tables) where "AccountNumber" Column is acting as PK or FK.

this what you want me to do or somthing elese?
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36148 Visits: 18751
I don't have a simple, easy solution for you. Anything I give you quick is as likely to break things as help if you don't understand.

If you're a new graduate then tell your boss you can't do this. There's nothing wrong with ignorance, and admitting mistakes. You need help. Explain what you've done, but that you need someone there that can see the system to help.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search