views:

234

answers:

1

This is the second time that I've noticed this...

I am running my Zend Framework application on my laptop, but connecting to my remote database. It works fine most of the time (from home, and other places). But this is the second time that I get an error message from my application:

SQLSTATE[28000] [1045] Access denied for user 'databaseuser'@'some.ip.address.here' (using password: YES)

The only thing that has changed is my wi-fi connection. Now, this message consistently pops up on this wi-fi connection, but once I am on a different one, I won't get this message. However, I noticed that this has also happened once when running the application on the production server and accessing it via wi-fi.

What does this mean for my application? How can I prevent this from happening?

+4  A: 

MySQL privileges are often granted per user and per IP address of the client host (client from the perspective of the database is the host where your PHP app is running).

Your wi-fi router is probably allocating the IP address for your laptop with DHCP, so there's no guarantee that it will grant the same address to your laptop each time it renews the connection.

I see two possible remedies:

  1. Some routers allow you to tie a specific IP address to a given MAC address so one PC will get the same IP address reliably (this is what I do on my home network).

  2. Or you can open privileges on your database server with a GRANT statement so that any IP address within the range of DHCP addresses served by your router can access the database. Example:

    GRANT ALL ON test.* TO 'databaseuser'@'192.168.1.%' IDENTIFIED BY 'databasepassword';
    

    You don't want to grant open access to any IP address, however, because then anyone on the broader network can potentially contact your database server.


update: Of course 192.168.1.% is an internal subnet that you'd use behind a firewall. That's just an example, showing that you can use wildcards for any octet in the address you give in your GRANT statement.

If you are connecting from home, you probably have a single external IP address that your internet provider assigns, but this may change frequently, subject to their network management policies. You might have an option to get a static IP address that is guaranteed not to change, but this generally costs more.

Of course if you are connecting from a coffee shop or an airport or whatever, your connection would appear as their external IP address.

It would be most flexible of course to open up your MySQL privileges so that any client from any IP address could connect (assuming they know the username & password). But the problem with that plan is that any client from any IP address could connect -- i.e. even when it's not you.

Bill Karwin
or GRANT ALL ON test.* TO 'databaseuser'@'%' IDENTIFIED BY 'databasepassword';
SM
@SM: I recommend *against* granting access to any client IP address.
Bill Karwin
@Bill Karwin: but 192.168.1.% is private ip. if he behind router with NAT or firewall - the outer IP will be other
SM