MySQL Secure Random String Generator – Best Practices

encryptionMySQLmysql-5Security

I am looking to generate a cryptographically secure string for password reset in MySQL. I know I could do this at the application level in PHP; but that requires the OpenSSL extension which many of my customers might not have.

If I can do it in MySQL 5 in a secure way that is widely available that would be ideal. Is this possible?

Note: This is for generating a secure token for password reset. It doesn't have anything to do with a secure connection, so using https is not a solution.

Best Answer

There are many encryption methods available in mySQL.

If you need two way encryption you could use aes_encrypt which has the accompanying aes_decrypt

If if you only need a secure hash then you could use sha2

The following statement could get you a similar result to openssl_random_pseudo_bytes

SELECT HEX(SHA2(CONCAT(NOW(), RAND(), UUID()), 512));

The statement above takes NOW() and concatenates it with RAND() and a UUID(), then performs a 512 bit SHA2() encryption on the result, and then converts that to HEX()

Related Solutions

MySQL – Securing Database Passwords

Not a direct answer about storage of the passwords, but I generally use at least two database connections when building webapps -- one's used 99% of the time for user-related activities, with restricted privileges, and the other's used for 'admin' functionality (delete users, etc.).

In a few cases, where I'm installing someone else's package, I'll install two instances ... a public facing instance which only has database access to do the general user-type stuff, and a second instance that's IP-restricted to my local subnet (possibly even on a different machine) that has to be used for any 'admin' type activities. Neither one has access to modify tables, etc, though ... I'd rather go in via the native database tools than allow the webapp to run its own update tasks that haven't been vetted.

You can take it even further though, and add more connections specifically for given tasks ... so the user creation & password management tasks go through a user that has extra privileges on the user tables, login has database privs to authenticate and not much else, etc.

In this way, if there's a sql injection attack, on most webpages it can't really do anything significant -- can't see the password hashes, can't add a new admin user (not that they'd be able to do anything anyway), etc. It still won't help if they manage to get a shell on your machine, but it'll slow them down.

MySQL Permissions Necessary To Reset Password

A user should be able to change his/her own password strictly using SET PASSWORD.

For a connected user to set the new password to mynewpass, just run the following:

mysql> SET PASSWORD = PASSWORD('mynewpass');

According to the MySQL Documentation on SET PASSWORD, if the server is a read-only enabled server, you need SUPER privilege to do this. Otherwise, you can do this any time. There is no need for another user to set someone else's password. If you need a super user to set it you can still use SET PASSWORD.

To set the password of 'someuser'@'10.1.2.30' to hisnewpass, run this:

mysql> SET PASSWORD FOR 'someuser'@'10.1.2.30' = PASSWORD('hisnewpass');

According to the MySQL Documentation on SET PASSWORD, this is the equivalent of:

UPDATE mysql.user SET Password=PASSWORD('hisnewpass')
WHERE User='someuser' AND Host='10.1.2.30';
FLUSH PRIVILEGES;

Using SET PASSWORD does not warrant manipulating mysql.user.

Best Answer

Related Solutions

MySQL – Securing Database Passwords

MySQL Permissions Necessary To Reset Password

Related Question