MySQL - can only connect as "root"

[bunner bob]

Since upgrading to Panther (clean install) I'm only able to connect to my databases as "root".

I've granted access permissions to all my tables, using the (presumed correct) syntax:

PHP:

grant*select,*insert,*update,*delete*on*mydb.table*to*dbadmin*identified*by*'adminpassword';


and so on for each table in my database. The sql 'grant' commands are accepted and when I check, the proper username and table are in the mysql database, "tables_priv" table.

But, I still can only connect to my database using the "root" user and root password. I.E. - this works:

PHP:

$db*=*mysql_connect("localhost",*"root",*"rootpassword");
mysql_select_db('mydb');


but this doesn't:

PHP:

$db*=*mysql_connect("localhost",*"dbadmin",*"adminpassword");
mysql_select_db('mydb');


This all used to work fine, but since I upgraded to Panther it doesn't, and I must connect as the root user.

Any ideas why this might be, what I should check, etc.?

Thanks!

- Bob


(crossposted in phpbuilder.com)

 

[LordCoven]

Out of interest - are you able to connect using the mysql program:
mysql -u <user name> -p <database name>
You'll be prompted for your password, and should then end up in the mysql CLI.

I'd guess that it may have something to do with the domain your user is coming from. I can't remember the exact syntax off hand, but you normally specify grants for a user in a particular domain, e.g. <my user>@localhost, or <my user>@my.domain.net. It could be that Panther is now sending the user information with a different domain than it was before. AFAIK you can also specify a the domain with a wildcard. So you could grant access for the user <user name>@*
But please check the MySQL manual for the exact syntax - its a long time since I did this on my machine ...
Cheers,
C

 

[didde]

I can imagine that you're having some problems with from which machine or host the user in question can connect from. It's a part of MySQL's excellent security model.

If you wanted to grant / create a user who can connect from any host and insert, delete, update and update any table in the database db, this could work:

grant select, insert, delete, update on db.* to user@'%' identified by 'password';

Remember to flush the privileges after a call like the one above, otherwise the changes won't get through.

flush privileges;

 

[bunner bob]

Yup - that's it. I wasn't specifying the hostname in my grants.I should have granted to "dbadmin@localhost", not just "dbadmin".

All fixed - thanks!

- Bob