Storing SSNs in a Database

Hello Eveyone,

I was wondering if there is a secure way to store SSNs in a database. I read the link here but it's pretty much stops when I don't want it to! Here's the environment I will be using: I have a popular database within which I need to store the SSNs. I plan to access it through PHP, Java and ASP .NET over an internal network. Hashes won't do as I need the SSNs. Because of the multiple ways to access data - I would prefer the database itself to handle authentication, encryption and decryption. Here are some of my thoughts:
1. salting the SSN
2. performing encryption and decryption using user defined DB functions
3. A user will always be logged in to access this - session info can be used to add security
4. does the use of BLOBs provide any advantage?

Just wondering if there are any additional comments or thoughts on this subject. Thanks guys.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

1. If you are going to store an encrypted SSN in a database salting the value first is a very good idea.

2. If you have user-defined functions to store and retrieve the SSN then you really haven't gained much security by encrypting the values before storage. If someone steals your entire database they get the functions to decrypt the information too.

3. If the session information is evaluated by the application layer then you haven't gained any security by using it; if someone succeeds in a cross-site scripting attack against your database they only need to invoke the user-defined functions to retrieve an SSN.

4. How were you thinking about using BLOBs?
Please encrypt each SSN with a different salt. It it make it so you cannot do rainbow tables to attack the encryption.
By definition a salt is random for each instance.

Otherwise, you just have padded the SSN with known data.
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

paxITAuthor Commented:
Thanks for the comments guys.
I agree with using different salts - padding it with the same phrase doesn't really help.

Regarding #2:user defined func
When it comes to user defined functions within the database - the decryption key will be a required argument. The same holds true for encryption. If not the database, the encryption and decryption would have to be done in PHP/ Java/ ASP .NET pages which are just as secure as the database. If someone can steal the database, they can get to these pages too. Unless someone proves me wrong there!

Regarding #4:Blobs
based on what I've seen in most Content Management Systems like MediaWiki, the password field is always a BLOB. Read here for storing passwords and such using AES + blobs
I was planning to use AES or 3 DES to encrypt the salt and ssn combo, then storing them as BLOBs. I guess you could store them as text too - a long hexadecimal string. Any ideas why BLOBs are so popular for this application?

I'd love to hear ideas about where to implement the encryption and decryption. Thanks again for your thoughts. Appreciate it.

It's definitely a good idea to separate the key from the database. It would also be a good idea to separate the key from the code.

Aside: As a security analyst, there are a lot of red flags in the implementation you described. Before I would sign off on it within the company I work for I would need a LOT more information and I would want to ensure that there were appropriate safeguards in place before I signed off on the implementation.

Ideally, the encryption key would be stored in a hardware security module, but I suspect that you're not going to go that far. The next best bet is secure storage on the server. If this is a Linux/Unix system you should store it in a location that only the application can read while it is running (use setuid on the executable). If this is a Windows system, use EFS to protect the file containing the password.

The BLOB you're referencing is a hash of a password. You said in your requirements that you need to recover the raw data so a BLOB won't work for your implementation. A BLOB is popular because it allows you to store something related to a password (or any other value) in a way that you can verify that the user has the password without actually storing the password. You store HASH(password) and then when the user supplies the password, you hash it and compare to the stored hash; if they match you know the user supplied the correct password even though you didn't store the password in your database.
paxITAuthor Commented:
Thanks for your input.

I've looked at TruCrypt (not sure if you have used it) to encrypt key files and the database itself, but, once the encrypted file system is mounted there's little in anyone's way. It's a product with a different end all together, I guess.

I was also thinking about moving the relevant tables to another database which itself sits on EFS. The only problem with EFS is password changes. Any best practices advice when it comes to EFS? Backing up certificates and recovery agent is a given.

Appreciate the help
Password changes? Do you mean the password of the associated user account? Note that the EFS encryption key is kept in the user registry.

EFS and TrueCrypt both have a problem in that the file containing the password can be stolen by any attacker who has managed to penetrate the system and have access to the file system.

Nevertheless, I suspect your biggest risk is that an attacker can convince your application to reveal the data. In that case the hacker doesn't need to worry about the encryption key because the application will do the decryption as part of serving up a response. It's much easier to secure the OS than the application.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
paxITAuthor Commented:
Thanks for your thoughts. They've been very useful. Really appreciate it.
Since this is a database, I would look into Format Preserving Encryption.(FPE). This way you can encrypt only the SSNs without clobbering the structure of the data with blobs.
There are both commercial and public versions of FPE available and an FPE has been shown to be as secure as the underlying symmetric algorithm
paxITAuthor Commented:
Thank you for the idea - I haven't come across this before. I'll be sure to look into it
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.