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 1:07 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:59 PM
Points: 13,007, Visits: 12,426
I agree with Steve that if this information is potentially sensitive then the system was designed poorly. This type of information should NOT be populated through a number of tables. Unfortunately that is what you have to deal with today.

I also agree with Steve that this is a potential issue for somebody new to the sql universe.

If however all you really need to is to mask the actual values in a test environment this really is not that difficult. I put together an example of how you could do something like this.

Please note that is only an example. Your real situation is likely to be a bit more complicated than this.

if OBJECT_ID('tempdb..#ClientInfo') is not null
drop table #ClientInfo

if OBJECT_ID('tempdb..#ClientAccount') is not null
drop table #ClientAccount

create table #ClientInfo
(
ExistingID int,
NewValue int
)

--This will generate 500 random integer values that will serve as "existing ID's"
--You would not do this in your system, this is just generating some data that is
--used to represent the table of data that you already have.
insert #ClientInfo(ExistingID)
select top 500 * from
(
select distinct ABS(CHECKSUM(NEWID())) % 10000 + 400 as NewVal
from sys.all_columns
) x

create table #ClientAccount
(
ExistingID int, --this is the column used in joins currently if I understand correctly
NewValue int
)

insert #ClientAccount (ExistingID)
select ExistingID
from #ClientInfo

--Now we have two tables that both have values that we want to "mask"
select *
from #ClientInfo
order by ExistingID

select *
from #ClientAccount
order by ExistingID



/*
Now that we have those two tables how can we go about mixing up the values?
Let's start looking at NewValue in each table to hold the new values.
In the real scenario we do not need these extra columns, I am including them for a visual reference and testing confirmation.
*/

update #ClientInfo
set NewValue = ExistingID

update #ClientAccount
set NewValue = ExistingID

select * from #ClientInfo --This will demonstrate that the values are the same
select * from #ClientAccount

--This is where you would start. Everything above here is just setting up the example.

update #ClientInfo
set ExistingID = floor(((ExistingID * 400) / 123.456789) * 100)

update #ClientAccount
set ExistingID = floor(((ExistingID * 400) / 123.456789) * 100)

--Cool we twisted the numbers around. But, did it work? Can we still join on our numbers?
select *
from #ClientAccount ca
join #ClientInfo ci on ca.ExistingID = ci.ExistingID



_______________________________________________________________

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 #1476223
Posted Monday, July 22, 2013 1:47 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
Thank you so much Steve and Sean.

As I was seraching for solution intially, when i got this task, i found single column encryption can be the best solution for this.
and I though I have some academic knowledge about encryption , so I can make it possible.

But now It seems that , it is alsmost near to impossible.


as far as I know from all our conversation is that , all our user has to work on the same server , so there is no point to do the single column encryption, because symmetric key and certificate will be there on the same server , so any of us(user) can decrypt that data.

can you please let me know more about , why encryption is not the proper solution for this issue..?

So that I can explain to my senior manager, as i told him earlier that encryption can be the solution, without understandig, the system and encryption thourghly.

Please.
Post #1476237
Posted Monday, July 22, 2013 1:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:59 PM
Points: 13,007, Visits: 12,426
can you please let me know more about , why encryption is not the proper solution for this issue..?


You answered this yourself...


as far as I know from all our conversation is that , all our user has to work on the same server , so there is no point to do the single column encryption, because symmetric key and certificate will be there on the same server , so any of us(user) can decrypt that data.


Encryption is pointless if you include the clear text values next to it. The point of encryption is to protect the actual values. If you have them side by side you didn't protect those values. And if you have several people all with the same access encrypting it doesn't do any good because they all have the key.

This is like buying a safe to protect your money. Then you want 1 employee to have access to the safe as a precaution. Then to allow this 1 person access you put the combination on a post-it note on the outside of the safe. You did accomplish locking up the money and the 1 person has access. However, so does everybody else.


_______________________________________________________________

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 #1476244
Posted Monday, July 22, 2013 2:19 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
Hi Sean AND Steve..

The following is totally not just to prove myself..but just got an idea in my mind, and want to discuss with you guys, before I waste my time on that.

Can I use single cloumn encryption from the following article

http://msdn.microsoft.com/en-us/library/ms179331.aspx

and then delete the clear text column from every table..

And then retrict the user to decrypt that data(I DON'T KNOW WHETHER THIS KIND OF FUNCTIONALITY IS THERE IN SQL SERVER OR NOT, THAT WE CAN RESTRICT USER TO PERFORM DECRYPT OPERATION)

and only admin can decrypt the column when he needs to refere encrypted data to clear text.

Is this possible?

thanks.
Post #1476263
Posted Monday, July 22, 2013 2:23 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
Sean Lange (7/22/2013)
I agree with Steve that if this information is potentially sensitive then the system was designed poorly. This type of information should NOT be populated through a number of tables. Unfortunately that is what you have to deal with today.

I also agree with Steve that this is a potential issue for somebody new to the sql universe.

If however all you really need to is to mask the actual values in a test environment this really is not that difficult. I put together an example of how you could do something like this.

Please note that is only an example. Your real situation is likely to be a bit more complicated than this.

if OBJECT_ID('tempdb..#ClientInfo') is not null
drop table #ClientInfo

if OBJECT_ID('tempdb..#ClientAccount') is not null
drop table #ClientAccount

create table #ClientInfo
(
ExistingID int,
NewValue int
)

--This will generate 500 random integer values that will serve as "existing ID's"
--You would not do this in your system, this is just generating some data that is
--used to represent the table of data that you already have.
insert #ClientInfo(ExistingID)
select top 500 * from
(
select distinct ABS(CHECKSUM(NEWID())) % 10000 + 400 as NewVal
from sys.all_columns
) x

create table #ClientAccount
(
ExistingID int, --this is the column used in joins currently if I understand correctly
NewValue int
)

insert #ClientAccount (ExistingID)
select ExistingID
from #ClientInfo

--Now we have two tables that both have values that we want to "mask"
select *
from #ClientInfo
order by ExistingID

select *
from #ClientAccount
order by ExistingID



/*
Now that we have those two tables how can we go about mixing up the values?
Let's start looking at NewValue in each table to hold the new values.
In the real scenario we do not need these extra columns, I am including them for a visual reference and testing confirmation.
*/

update #ClientInfo
set NewValue = ExistingID

update #ClientAccount
set NewValue = ExistingID

select * from #ClientInfo --This will demonstrate that the values are the same
select * from #ClientAccount

--This is where you would start. Everything above here is just setting up the example.

update #ClientInfo
set ExistingID = floor(((ExistingID * 400) / 123.456789) * 100)

update #ClientAccount
set ExistingID = floor(((ExistingID * 400) / 123.456789) * 100)

--Cool we twisted the numbers around. But, did it work? Can we still join on our numbers?
select *
from #ClientAccount ca
join #ClientInfo ci on ca.ExistingID = ci.ExistingID



Hi Sean this is WONDERFUL solution, i believe in my current situation.

Just wondering, once account number chages with update, it makes permantaly chages.

if admin want to refer back to the original account number how he can go for it??

I guess before we make an final update , introduce new column into table and populate it with actual account number .

and then apply the operation of masking them!!!!

Am I right?

thanks.
Post #1476266
Posted Monday, July 22, 2013 2:39 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:59 PM
Points: 13,007, Visits: 12,426
Learner44 (7/22/2013)
[quote]
Hi Sean this is WONDERFUL solution, i believe in my current situation.

Just wondering, once account number chages with update, it makes permantaly chages.

if admin want to refer back to the original account number how he can go for it??

I guess before we make an final update , introduce new column into table and populate it with actual account number .

and then apply the operation of masking them!!!!

Am I right?

thanks.


The intention of my code was to be used in a dev environment which I believe is the whole point of this exercise? If the data changes, just refresh from live whenever you need to do and rerun the tweaking code. Dev data will never be totally in synch with live data so this isn't something you should need to worry about realtime anyway.


_______________________________________________________________

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 #1476273
Posted Tuesday, July 23, 2013 8:47 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
Hi Sean..

I use the following approach ro reference masked value to original values.

-- **********************************************
-- **************** STEP 1 ********************
-- **********************************************
update #ClientInfo
set ExistingID = floor(((ExistingID * 400) / 123.456789) * 100)


-- **********************************************
-- **************** STEP 2 ********************
-- **********************************************
update #ClientAccount
set ExistingID = floor(((ExistingID * 400) / 123.456789) * 100)


-- **********************************************
-- **************** STEP 3 ********************
-- **********************************************
select *
from #ClientAccount ca
join #ClientInfo ci on ca.ExistingID = ci.ExistingID

-- **********************************************
-- ******************* STEP 4 *****************
-- **************** When You Want To ************
-- ***************Refer Original Value *********
-- **********************************************
update #ClientInfo
set ExistingID = floor(((ExistingID*123.456789)/100)/400)
select * from #ClientInfo


I want to make it more complex and secure..

like reading each Accountnumber and convert each character into some special character..

is it worth to do it ..from your point of view..?

thanks.
Post #1476622
Posted Tuesday, July 23, 2013 9:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:59 PM
Points: 13,007, Visits: 12,426
Learner44 (7/23/2013)
Hi Sean..
I want to make it more complex and secure..

like reading each Accountnumber and convert each character into some special character..

is it worth to do it ..from your point of view..?

thanks.


The problem here is that you will end up with a high risk of collisions. It is not super secure but it certainly does an adequate job of disguising the information. To be certain, it is only going to hide the actual value of that column. It does not protect any of the other information. I would maybe look into redesigning parts of your system so that the sensitive data is in only one table instead of all over the place.


_______________________________________________________________

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 #1476641
Posted Tuesday, July 23, 2013 9:20 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
You are 101% right..Sean

but, things here are ongoing..with millions of data across the 40+ tabels..

and account number is used as reference in almost 32+ tables.

Due to recent raise the quetions about security of sensitive information in our testing enviornment team, which mainly support the actual report generation team, by developing different SP and JOINS on small portion of original data...

They don't want us to see the actual account numbers of clients, and our scripts which already there , which we are runnig is mostly based on match of accoun number column between two tables.

It's very difficult to chage entire architecture..now.

is there any way that we can restrict our team member about only to perform/run decrypt operation/query?

thanks.
Post #1476652
Posted Tuesday, July 23, 2013 9:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:59 PM
Points: 13,007, Visits: 12,426
Learner44 (7/23/2013)
You are 101% right..Sean

but, things here are ongoing..with millions of data across the 40+ tabels..

and account number is used as reference in almost 32+ tables.

Due to recent raise the quetions about security of sensitive information in our testing enviornment team, which mainly support the actual report generation team, by developing different SP and JOINS on small portion of original data...

They don't want us to see the actual account numbers of clients, and our scripts which already there , which we are runnig is mostly based on match of accoun number column between two tables.

It's very difficult to chage entire architecture..now.

is there any way that we can restrict our team member about only to perform/run decrypt operation/query?

thanks.


You need to hire a consultant. I understand you are new but the kinds of things you are asking just don't make sense with regards to encryption and the arbitrary restraints on not changing permissions at a user level. There just is no magic pill here that will protect your data and prevent all users (except for one or two) from seeing it without any changes to permissions. It just isn't possible. Between Steve and myself we have given you 2 or 3 different approaches to accomplish the desired security but you can't do that because your boss doesn't want to change security.

The way I see it is you have 3 choices:
1) Fix the architecture. Spend the time it takes to rebuild the architecture to protect your sensitive information.
2) Encrypt the data and change permissions to prevent seeing the sensitive information.
3) Do some sort of obfuscation like the code I showed as an example.


_______________________________________________________________

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 #1476670
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse