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 8:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 13,086, Visits: 12,553
Why do you need to have both encrypted and clear text columns? This defeats the whole point of column level encryption. You haven't protected the sensitive information at all this way.

The only way I know of to get around this would be to deny select permission on the table to any users that you don't want to view the clear text version. Then create a view that does not contain that column and grant them select permission on the view.


_______________________________________________________________

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 #1476030
Posted Monday, July 22, 2013 8:27 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
Sean Lange (7/22/2013)
Why do you need to have both encrypted and clear text columns? This defeats the whole point of column level encryption. You haven't protected the sensitive information at all this way.[/[quote]

You are right on the target..That's also I am wondering , after applying folowing steps..

I have used following as a reference ..and use my account number column, in bank table in my client database,so only column, table and databases name are changed otherwise evrything is same.

USE AdventureWorks;
GO

--If there is no master key, create one now.
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = ''
GO

CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO

CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE HumanResources037;
GO

USE [AdventureWorks];
GO

-- Create a column in which to store the encrypted data.
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO

-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;

-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
GO

-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data.
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO

-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.

SELECT NationalIDNumber, EncryptedNationalIDNumber
AS 'Encrypted ID Number',
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS 'Decrypted ID Number'
FROM HumanResources.Employee;
GO

Now , when I use [b]select * from table name

it gives me whole table plus one encrypted column...and I don't know...why it is comming..[/b]

even in the "AdventureWorks" when I run above exact query it gives both original(clear text) and encryted text, here is the out put.(Please find the attachment)

[quote]The only way I know of to get around this would be to deny select permission on the table to any users that you don't want to view the clear text version. Then create a view that does not contain that column and grant them select permission on the view.


Yes , we can surely do this but, my senior staff, ask me that once you have done encyption, why we have to create a view, to fillter restricted columns, which shows encryption is not done...properlly..

Now , what should I do...

Please let me know if need any further information, to help me.

thanks a million.



  Post Attachments 
output.docx (3 views, 13.57 KB)
Post #1476036
Posted Monday, July 22, 2013 8:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 13,086, Visits: 12,553
I would drop the column with clear text. You have two copies of this information now, 1 is encrypted and the other is not. If the only column you have is encrypted, then all you have to do to prevent people from viewing it is to not let them have the key.

_______________________________________________________________

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 #1476048
Posted Monday, July 22, 2013 8:54 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 4:00 PM
Points: 31,181, Visits: 15,627
I would agree with Sean. However if you need the plain text column for your application to continue to work (legacy code), I'd set it to blank.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1476051
Posted Monday, July 22, 2013 9:21 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..I got your point Sean and Steve...Thanks for that.

I just checked the encryption from one of my co-worker, under her login name and password , but on the same server.

she has the same view as I have, I mean clear text column(Account Number) and Encrypted coulmn(EncryptedAccountNumber)...

I found my certificate and symmetric key under the "Security-> Certificate" folder and "Security-> Symmetric Key" folder respectively.

So , as you are saying that I should not provide the key to other user, whom I don't want to see that clear text column..How I can do that.?

[i]The reson why I am asking this is that, it is there on the server and who ever logs on into that server have the certificate and symmetric key under the folder which I have metioned above.

Is it possible that I save them somewhere else , out of the server?

and YES , YOU BOTH ARE RIGHT, THAT I CAN DELETE THE COLUMN WITH CLEAR TEXT AS I HAVE NOW ENCRYPTED TEXT, BUT IT IS GOOD PRACTICE TO DO THAT..BECAUSE I HAVE 40+ TABLES....PLEASE LET ME KNOW SO THAT I CAN START FOLLOWING THIS OPTION..



THANKS TO BOTH OFF YOU FOR YOUR TIME AND SUPPORT.

REALLY..
Post #1476074
Posted Monday, July 22, 2013 9:35 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 4:00 PM
Points: 31,181, Visits: 15,627
It's not clear what you're asking, I think somewhat from the language barrier.

So , as you are saying that I should not provide the key to other user, whom I don't want to see that clear text column..How I can do that.?


what key, and regardless, what does the key have to do with the clear text column? It would help if you laid this out in a scenario with a real table, and what you want people to see or not see. Right now I think you don't understand encryption and how it's used, and that makes it hard to discuss.

There is no "have". Users who log into the server can see and access various objects. In the case of encryption, you have another key, or a password protecting that key. Without that password, accessing the key doesn't matter.

As far as a good practice, it's never a good practice to have encrypted data and encrypted data on the same system. It defeats the purpose of using encryption.

The retrofit of encryption into an existing application is complex and requires lots of work. Encryption also should be used sparingly.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1476085
Posted Monday, July 22, 2013 10:07 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
yes you are right..it's all about my language barrier..as english is not my first language..I always find my self in backfoot , while explaning the situation , what I really want

I am trying one more time as follows, what's my current situation is

I have working in SQL SERVER 2008 R2, which has 6 accounts(U01,U02,U03,U04,U05,U06) under one server name called "SQL SERVER MANAGMENT STUDIO\server23".

I am U02 user.

All our accounts have access on specific database and table (client)..
This table consists of columns list as clientID, clientName,clientSalary,clientAccountNumber.

I need to restrict the user U01,U03,U04,U05,U06 to see "clientAccountNumber" column in client table.

I mean, I don't want these users to see the real data inside the ""clientAccountNumber" column, instead of this I want them to see some emcrypted data.


Now can you suggest me the way to do this...Please...

My senior manager, asks me that he doesn;t want somthing like grant and revoke permisiion for all the users..

He needs either encryption or datamasking.

I hope this time I am more clear, and gave less hard time to you "Big Helpers" to understand my situation.

thanks.
Post #1476112
Posted Monday, July 22, 2013 10:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 13,086, Visits: 12,553
Learner44 (7/22/2013)
yes you are right..it's all about my language barrier..as english is not my first language..I always find my self in backfoot , while explaning the situation , what I really want


No worries about the language barrier. We can work through that easily enough.



I have working in SQL SERVER 2008 R2, which has 6 accounts(U01,U02,U03,U04,U05,U06) under one server name called "SQL SERVER MANAGMENT STUDIO\server23".

I am U02 user.

All our accounts have access on specific database and table (client)..
This table consists of columns list as clientID, clientName,clientSalary,clientAccountNumber.

I need to restrict the user U01,U03,U04,U05,U06 to see "clientAccountNumber" column in client table.

I mean, I don't want these users to see the real data inside the ""clientAccountNumber" column, instead of this I want them to see some emcrypted data.



Now can you suggest me the way to do this...Please...

My senior manager, asks me that he doesn;t want somthing like grant and revoke permisiion for all the users..

He needs either encryption or datamasking.

I hope this time I am more clear, and gave less hard time to you "Big Helpers" to understand my situation.

thanks.


Here is the problem. The type of encryption you have implemented is on the entire column, it has nothing to do with which user happens to be running a select statement currently.

It sounds like your boss doesn't understand encryption either.


_______________________________________________________________

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 #1476118
Posted Monday, July 22, 2013 10:27 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 4:00 PM
Points: 31,181, Visits: 15,627
I would agree that you and your boss don't understand encryption.

someone has to see the account number, who is it? And when? Can you change the code that allows them to query the account number?

If you just are trying to prevent them from seeing the data, you can use grant/revoke. Remove all rights to the table for all users.

Create a view, and grant rights to the view

CREATE VIEW ClientView
as

select ClientID, ClientName, '#$FW$FEDEED' as clientAccountID'
from Client









Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1476127
Posted Monday, July 22, 2013 10:31 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 ...

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.?

e.g.

Current table looks like

clientName ClientSalary clientAccountnumber
James 30000 123456
Panze 45000 923854
Jibar 40000 325658

I want them to see somthing like,

clientName ClientSalary clientAccountnumber
James 30000 (*mn>>
Panze 45000 (*&gdb
Jibar 40000 *(s^5s

according to my research on google, i found Data Making can be the solution, but I may be wrong too.

Please Help.
Post #1476128
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse