11

I'm trying to securely store user credentials in a MySQL database which is hosted on a different server than the application (the communication between the two is secure).

I have initially assumed that without a knowledge of username/password for MySQL, nobody can access and read the data. However, I have realized, that there could be a scenario where an attacker could gain access to this SQL server and simply make a copy of the database files which are located on the disk.

Are MySQL database files that are stored on disk encrypted?

Could I directly read the data from these files without knowing my MySQL username/password? I am considering to encrypt and decrypt the credentials in the application and store only the encrypted version in the DB, but is this necessary?

Anders
  • 64,406
  • 24
  • 178
  • 215
leopik
  • 233
  • 1
  • 2
  • 5
  • 2
    Alternatively, you can encrypt your drives with eg LUKS, if that fits your requirement. –  Oct 30 '16 at 14:18
  • Thanks for the idea! Although I think it'd be easier to simply save the encrypted credentials since (at the moment) nothing else that is sensitive besides the creds is being stored there. – leopik Oct 30 '16 at 14:34
  • If you are storing passwords or similar, you should definitely hash + salt them if you are not (!) doing so already: http://security.blogoverflow.com/2013/09/about-secure-password-hashing/ – Jonas Czech Oct 30 '16 at 17:33
  • 1
    If someone has root access to your server, they do not need the MySQL password to log in to MySQL. In fact you can assume once root access is gained, the attacker can see and do anything you can – Darren H Oct 30 '16 at 17:33

4 Answers4

14

Are MySQL database files that are stored on disk encrypted?

No, they are not.

You can (relatively) easily test this by moving .ibd or .myd files to a different system, where you can still read them. Or you can just open them and you will likely see at least some of the content of the tables in plaintext.

Some MySQL engines do provide optional encryption, such as innodb. MySQL enterprise edition also provides optional encryption.

tim
  • 29,018
  • 7
  • 95
  • 119
  • 3
    What does it mean for the database engine to provide encryption, that their exists encryption between the client and server? – Celeritas Oct 30 '16 at 21:36
  • @Celeritas In this case, I believe we're talking about encryption between the server and the actual disk that the data is stored on. Another way to ask the question is... "Does MySQL store its data in plain text on disk?". The answer is "Yes, with some exceptions". The implication of this is that one doesn't *need* MySQL privileges to read/write to the database, they can directly manipulate MySQL's data files. – Aaron Cicali Jan 23 '20 at 16:11
6

Could I directly read the data from these files without knowing my MySQL username/password?

You bet.

I am considering to encrypt and decrypt the credentials in the application and store only the encrypted version in the DB, but is this necessary?

Don't store plaintext passwords in your database, and don't use symmetric encryption, either (if you do, then when your key gets stolen along with the encrypted passwords, you lost).

The way to store passwords (anywhere - it doesn't matter whether you store them in flat files, databases or somewhere else...) is to salt and hash them using a secure hash function. A hash function takes an input string and returns a fixed-length random-looking string as an output. You store this random-looking string in your database.

When you want to check whether someone entered the right password, you run the password through the hash function and check the resulting string against the string in your database.

This way, passwords aren't exposed (hash functions are one-way functions, which means that you can't inverse the hash function without tremendous effort).

Note that simply hashing the password isn't enough. You have to concatenate a random salt value to the password before hashing it and then store both the hash function output AND the salt value in your database. You need do to this for several reasons: 1. If you don't, then identical passwords will yield an identical hash value 2. Someone with some time on his hands could precalculate a table of hashed passwords (rainbow tables). Using a salt value makes this operation much more expensive.

There are hash functions available for hashing passwords, bcrypt for example. Use one of them instead of rolling your own. DON'T use a standard hash function such as md5 or sha1. They are all designed to be fast, which is the opposite of what you want in a password hash function (to make brute force attacks on your password file harder when it gets stolen).

Out of Band
  • 9,150
  • 1
  • 21
  • 30
  • 1
    I didn't mention it in my original question since I didn't consider it to be relevant, but I am salting and hashing the passwords already. However, I am using SHA1 for this purpose - is it truly that bad to use it (even when salting the passwords)? Either way, I will look at other hashing functions to see what the possibilities are. And thanks for your post! – leopik Oct 30 '16 at 19:41
  • 2
    @leopik [yes, it is](https://security.stackexchange.com/questions/211/). Before considering gaining some slight amount of additional security by encryption, you definitely need to use a proper hashing function. – tim Oct 30 '16 at 20:46
  • 2
    "*don't use symmetric encryption, either*" - Microsoft do it for Outlook for iOS to hold your email credentials in the cloud ( http://www.pcworld.com/article/2881632/eu-parliament-blocks-new-outlook-apps-over-privacy-concerns.html ). It also claims to require part of the key from the mobile device, so "*when your key gets stolen along with the encrypted passwords, you lost*" is less likely. Yes this is an argument by authority, which is logically weak, but it's also demonstration of a real world system that people use because it can be good enough. – TessellatingHeckler Oct 30 '16 at 23:34
  • 1
    That's a good point, so I'll rephrase: Always prefer hashing to symmetric encryption if you don't absolutely need access to the plaintext. (But I'd still be uncomfortable with this kind of real-world solution. It's often just a matter of time until such real-world solutions fail in some very public, bad-press sort of way...) – Out of Band Oct 31 '16 at 14:22
1

where an attacker could gain access to this SQL

At this point they have already bypassed most of your security controls and have access to the data, which is usually the objective of the attack (stealing credentials is usually done with the objective of getting access to the data). User passwords should be securely hashed (native mysql passwords are held thus in the database but will probably be held unencrypted in the application).

Any sort of transparent encryption, such as LUKS or FUSE will also be transparent to an attacker with access to the running server.

There may be things you might consider, such as hashing usernames to make them more anonymous (consider the Ashley Maddison attack) but without understanding what you are still trying to protect at this stage of the game, it is difficult to advise. I suggest you would get more benefit from focusing your attention on preventing an attacker getting this far / detecting a potential compromise of the server.

symcbean
  • 18,278
  • 39
  • 73
  • I agree with everything but the last sentence. Security comes in layers. By protecting your passwords (and maybe usernames), you protect the security of others even if your own system's security is largely compromised. Some of your users will probably use their usernames (especially if they're e-mail addresses) and passwords on other sites, too. Don't make it trivial to attack these other systems by not securing your password database. – Out of Band Oct 30 '16 at 16:41
  • The only option for "securing" usernames is to hash them - usernames are not secrets. Agreed, email addresses are slightly more sensitive, but still not secrets, and hashing all identifiers breaks auditability. Sorry, but unless you have a specific suggestion which will improve security against a defined attack, I still say this discussion is futile. – symcbean Oct 30 '16 at 21:42
1
Are MySQL database files that are stored on disk encrypted?

No. MySQL does not, by default encrypt its files. You are not clear about which kind of data you are encrypting, but it looks like the best solution in your case would be a column-based encryption solution. This will allow you to encrypt selected columns containing sensitive information and define policies/encryption keys per each column.

A good solution should also allow you to have control over the life cycle of the enc/dec keys.

All this can be very helpful especially for compliance to any serious private data protection regulation.

This solution "MyDiamo" is a viable option for column-based encryption and the pricing is pretty reasonable.

If you want file-level encryption, then I'd recommend to go for MySQL Enterprise encryption as suggested above.

NA AE
  • 188
  • 3