How to hide sensitive data in SQL 2000 table
-
Is there any way of hiding/encypting a data in table. SQL is 2000. Server is win2003.
-
balasach82 (10/15/2013)
Is there any way of hiding/encypting a data in table. SQL is 2000. Server is win2003.You could encrypt your data before it hits sql.
_______________________________________________________________
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/ -
I have few columns like SSN, Creditcard details in the table which i dont want every one who has access to the table to see. Even if they see the value it should be unrecognisable..encrypted. Can it be done?
-
balasach82 (10/15/2013)
I have few columns like SSN, Creditcard details in the table which i dont want every one who has access to the table to see. Even if they see the value it should be unrecognisable..encrypted. Can it be done?Be VERY VERY VERY careful if you are storing credit card numbers. This is generally a very bad idea. Your company becomes liable for the security of this information.
Yes you can store encrypted data. With sql 2000 the best thing to do is to encrypt/decrypt the data outside of the database. That way the values stored are always the encrypted values and the data passing through the pipes is encrypted.
_______________________________________________________________
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/ -
That means sql cant be used to secure the data and i have to use .NET or any other programs to get what i need.
Thanks for the reply.
-
In the meantime, you can create a view for the table(s) with the non-sensitive columns, and lock down access to the base table for almost everybody.
-
balasach82 (10/15/2013)
I have few columns like SSN, Creditcard details in the table which i dont want every one who has access to the table to see. Even if they see the value it should be unrecognisable..encrypted. Can it be done?Are you PCI compliant?
http://www.pcicomplianceguide.org/
https://www.pcisecuritystandards.org/security_standards/index.php
-
balasach82 (10/15/2013)
I have few columns like SSN, Creditcard details in the table which i dont want every one who has access to the table to see. Even if they see the value it should be unrecognisable..encrypted. Can it be done?You can use something like XPCRYPT to encrypt the data. It works within SQL and is simple to implement. We used it at a previous place I worked without problems. The encryption keys can be stored in a separate database with very limited access.
Since you currently have unencrypted data, I would suggest implementing something immediately. Then you can make a business decision about the best solution for your environment.
-
Do you need to retrieve the numbers? If not, you could hash them, but again, as Sean suggested, do this in the application.
SQL 2000 has limited options. SQL 2005+ has more, but in most cases, storing credit card numbers isn't allowed by payment processors.
-
We were allowed to store CC numbers, but had to have various safeguards in place to be PCI compliant.
-
homebrew01 (10/20/2013)
We were allowed to store CC numbers, but had to have various safeguards in place to be PCI compliant.When you say you "were allowed to store CC numbers", do you mean in clear text? Also, what were those "various safeguards"?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Definately NOT clear text. The numbers were encrypted, and the database with the encryption key was in a separate database that was on an encrypted drive with only admin permissions. And all the other safegaurds imposed by PCI over the whole IT infrastructure.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply