July 22, 2013 at 1:07 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 22, 2013 at 1:47 pm
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.
July 22, 2013 at 1:53 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 22, 2013 at 2:19 pm
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.
July 22, 2013 at 2:23 pm
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.
July 22, 2013 at 2:39 pm
Learner44 (7/22/2013)
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 23, 2013 at 8:47 am
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.
July 23, 2013 at 9:11 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 23, 2013 at 9:20 am
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.
July 23, 2013 at 9:36 am
Learner44 (7/23/2013)
You are 101% right..Seanbut, 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 23, 2013 at 10:09 am
Thanks Sean and Steve.
July 23, 2013 at 1:35 pm
Sean Lange (7/23/2013)
Learner44 (7/23/2013)
You are 101% right..Seanbut, 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.
Hi Sean, finally i researched on option 1 and 2 , and decided to go with option 2
1) I created one database called encrypt_Test1
2) I set owner of this database as myself(sqlserver\prb) instead of <default>
3) I followed the following steps.
USE encrypt_test1;
GO
-- Create Table
CREATE TABLE dbo.Customer_data
(Customer_id int constraint Pkey3 Primary Key NOT NULL,
Customer_Name varchar(100) NOT NULL,
Credit_card_number varchar(25) NOT NULL)
-- Populate Table
INSERT INTO dbo.Customer_data
VALUES (74112,'MSSQLTips2','2147-4574-8475')
GO
INSERT INTO dbo.Customer_data
VALUES (74113,'MSSQLTips3','4574-8475-2147')
GO
INSERT INTO dbo.Customer_data
VALUES (74114,'MSSQLTips4','2147-8475-4574')
GO
INSERT INTO dbo.Customer_data
VALUES (74115,'MSSQLTips5','2157-1544-8875')
GO
-- Verify data
SELECT *
FROM dbo.Customer_data
GO
--*********************************** STEP - 1 **************************
USE master;
GO
SELECT *
FROM sys.symmetric_keys
WHERE name = '##MS_ServiceMasterKey##';
GO
--*********************************** STEP - 2 **************************
USE encrypt_test1;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'HFUISDH894H329**9392IN3H2IN5664QWVE@@1';
GO
--*********************************** STEP - 3 **************************
USE encrypt_test1;
GO
CREATE CERTIFICATE Certificate1
WITH SUBJECT = 'Protect Data';
GO
--*********************************** STEP - 4 **************************
USE encrypt_test1;
GO
CREATE SYMMETRIC KEY SymmetricKey1
WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE Certificate1;
GO
--*********************************** STEP - 4 **************************
USE encrypt_test1;
GO
ALTER TABLE Customer_data
ADD Credit_card_number_encrypt varbinary(MAX) NULL
GO
--*********************************** STEP - 5 **************************
USE encrypt_test1;
GO
-- Opens the symmetric key for use
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
GO
UPDATE Customer_data
SET Credit_card_number_encrypt = EncryptByKey (Key_GUID('SymmetricKey1'),Credit_card_number)
FROM dbo.Customer_data;
GO
-- Closes the symmetric key
CLOSE SYMMETRIC KEY SymmetricKey1;
GO
--*********************************** STEP - 6 **************************
USE encrypt_test1;
GO
ALTER TABLE Customer_data
DROP COLUMN Credit_card_number;
GO
--*********************************** STEP - 7 **************************
SELECT Customer_id, Credit_card_number_encrypt AS 'Encrypted Credit Card Number',
CONVERT(varchar, DecryptByKey(Credit_card_number_encrypt)) AS 'Decrypted Credit Card Number'
FROM dbo.Customer_data;
--*********************************** STEP - 8 **************************
USE encrypt_test1;
GO
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
GO
-- Now list the original ID, the encrypted ID
SELECT Customer_id, Credit_card_number_encrypt AS 'Encrypted Credit Card Number',
CONVERT(varchar, DecryptByKey(Credit_card_number_encrypt)) AS 'Decrypted Credit Card Number'
FROM dbo.Customer_data;
-- Close the symmetric key
CLOSE SYMMETRIC KEY SymmetricKey1;
GO
Now as we have discuss earlier, my other co-workers can also perform step - 8 easily with their login account.
Now How can i remove everyone , I mean set up the permission from database properties , so that they won't be able to run step 8 and can not decrypt the data.
July 23, 2013 at 2:07 pm
I tried this one..
deny VIEW DEFINITION ON SYMMETRIC KEY::SymmetricKey1 TO [sqlserver\ackj];
deny VIEW DEFINITION ON Certificate::Certificate1 TO [sqlserver\ackj];
GO
but it didn't help.
still [sqlserver\ackj] user can see the data with above command.
July 23, 2013 at 2:15 pm
Learner44 (7/23/2013)
I tried this one..deny VIEW DEFINITION ON SYMMETRIC KEY::SymmetricKey1 TO [sqlserver\ackj];
deny VIEW DEFINITION ON Certificate::Certificate1 TO [sqlserver\ackj];
GO
but it didn't help.
still [sqlserver\ackj] user can see the data with above command.
Do it the easy way. DENY SELECT ON Object::dbo.YourTable to [sqlserver\ackj];
Then create a view that has all the other columns and GRANT select on YourView.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 23, 2013 at 2:40 pm
when I tried..following one..
use encrypt_test1
DENY SELECT ON Object::dbo.Customer_data to [sqlserver\ackj]
it says that
Command(s) completed successfully.
but, when I run the following from my co-worker station whose user name is [sqlserver\ackj]
USE encrypt_test1;
GO
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
GO
-- Now list the original ID, the encrypted ID
SELECT Customer_id, Credit_card_number_encrypt AS 'Encrypted Credit Card Number',
CONVERT(varchar, DecryptByKey(Credit_card_number_encrypt)) AS 'Decrypted Credit Card Number'
FROM dbo.Customer_data;
-- Close the symmetric key
CLOSE SYMMETRIC KEY SymmetricKey1;
GO
It also runs there...
I don't know but all weird things happen in my case in my sql server operations..
Viewing 15 posts - 31 through 45 (of 48 total)
You must be logged in to reply to this topic. Login to reply