Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

Need A Help in DATA MASKING in SQL SERVER 2008 Expand / Collapse
Author
Message
Posted Monday, July 22, 2013 10:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
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)
Post #1476142
Posted Monday, July 22, 2013 10:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:34 AM
Points: 134, Visits: 259
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.
Post #1476148
Posted Monday, July 22, 2013 11:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:11 PM
Points: 31,368, Visits: 15,837
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
Post #1476162
Posted Monday, July 22, 2013 11:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:34 AM
Points: 134, Visits: 259
Oh..no..

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.



Post #1476170
Posted Monday, July 22, 2013 11:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:34 AM
Points: 134, Visits: 259
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.
Post #1476175
Posted Monday, July 22, 2013 12:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:11 PM
Points: 31,368, Visits: 15,837
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
Post #1476177
Posted Monday, July 22, 2013 12:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
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)
Post #1476181
Posted Monday, July 22, 2013 12:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:34 AM
Points: 134, Visits: 259
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.
Post #1476192
Posted Monday, July 22, 2013 12:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:34 AM
Points: 134, Visits: 259
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?
Post #1476202
Posted Monday, July 22, 2013 12:34 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:11 PM
Points: 31,368, Visits: 15,837
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
Post #1476208
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse