# MySQL - can only connect as "root"



## bunner bob (Jul 23, 2004)

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 (Jul 23, 2004)

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 (Jul 27, 2004)

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 (Jul 27, 2004)

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


----------

