How to check if the user and password works without making a connection to a database

Question

How do I check if a user/password pair works without actually making a connection to the database? The DBMS in question is MySQL.

That was my original question. Since most people get this question wrong, then I should rephrase it as: How do I check if a MySQL username/password is valid, without connecting to MySQL as that user? (thanks to derobert)

I actually found a solution, check my answer below.

Answer 1

If the username and password are stored in the database, then there's obviously no other way to check them other than to connect first.

The best you could do is perhaps only connect to the DB when they log in. Once they're authenticated, you could store some form of session information on disk, but it's not a great solution.

Answer 2

In short - not posssible if the userid/password are stored in the database.

Authentication basically means that you compare the response to a challenge with known values. If you do not have the values to compare with , you cannot authenticate.

Answer 3

Can I ask why you want to check the username and password without connecting?

Answer 4

One possible solution would be to devise some sort of scheme where the username/password are an encryption/decryption key pair. Obviously, this would be more feasible in an assigned username world, but such a policy would allow you not to hit the database if that is the primary objective.

Answer 5

I think this question is open to interpretation. Most people will jump in and say "You can't.", but if what you are actually asking is "How do I use MySQL to authenticate a user but not actually use the database?" then that's a whole different ball game. Take a look at mod_auth_mysql, an Apache module which does exactly that. If we had more details on what exactly you were trying to do, folks might be more forthcoming.

Answer 6

If you want to check if a MySQL username/password is valid, without connecting to MySQL as that user, then you should take a look at the the users table in the mysql database.

But I'd recommend not doing this; that is really an internal MySQL implementation detail, and you really shouldn't depend on it. (e.g., what if MySQL gets LDAP auth someday?)

Answer 7

Login as someone who has access to "mysql" database (schema), and do: SELECT COUNT(*) FROM MYSQL.USER WHERE USERNAME=? AND PASSWORD=PASSWORD(?)

If the count > 0 then the username/password is correct.