can't remote login to MySQL DB using Aqua tool

After I set up a new LAMP server, i can log in locally using root account on the linux box. but when i install Aqua tool and tried to log in from my desktop. It gives me error and doesn't let me log in.

I tried to add the records to user table on MySQL. I added two users accounts and my desktop IP into the User table, and restart the service. But still doesn't work.

please advise.

thank you.
login-error.jpg
user-table.jpg
Jason YuAsked:
Who is Participating?
 
Dave BaldwinFixer of ProblemsCommented:
Your last message implies that you typed the password for 'root' wrong but yet you're already logged in.  That's confusing.  Did you do a 'CREATE USER...' first?  You can't grant anything to a user until after you have 'created' the user and password.

CREATE USER 'minkaweb'@'localhost' IDENTIFIED BY 'some_pass';
0
 
Dave BaldwinFixer of ProblemsCommented:
You're trying to login as 'root' and 'root' can only connect from '127.0.0.1' and 'localhost'.  In addition, you should not being using 'root' remotely, that is considered a security problem.  Also, the account you added does not show the complete hostname that you are showing in your error page.  Everything has to match up, the username, the password, and the hostname that you are connecting from.  Though if it is just a user account, 'anyhost' is often used as the hostname.

From http://dev.mysql.com/doc/refman/5.1/en/adding-users.html ...

The 'monty'@'%' account uses the '%' wildcard for the host part, so it can be used to connect from any host.
0
 
Jason YuAuthor Commented:
I run the following command successfully but still couldn't remote access with mis account.

mysql> GRANT ALL PRIVILEGES ON User.* TO 'mis'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)



The user table is as below;

mysql> select Host, User, Password, Select_priv, Grant_priv from user;
+-----------+----------+-------------------------------------------+-------------+------------+
| Host      | User     | Password                                  | Select_priv | Grant_priv |
+-----------+----------+-------------------------------------------+-------------+------------+
| localhost | root     | *13E093B3B83A3A99FDFE993DA3F0A46E0F9F74BE | Y           | Y          |
| 127.0.0.1 | root     | *13E093B3B83A3A99FDFE993DA3F0A46E0F9F74BE | Y           | Y          |
| BFDMIS07  | username | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | N           | N          |
| 10.10.6.9 | username | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | N           | N          |
| BFDMIS07  | mis      | *107F6D76F0CF44552CA76D43F45FFDFB40A36900 | N           | N          |
| BFDMIS07  | root     | *13E093B3B83A3A99FDFE993DA3F0A46E0F9F74BE | N           | N          |
| localhost | mis      | *BF06A06D69EC935E85659FCDED1F6A80426ABD3B | N           | N          |
| %         | mis      | *BF06A06D69EC935E85659FCDED1F6A80426ABD3B | N           | N          |
+-----------+----------+-------------------------------------------+-------------+------------+
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jason YuAuthor Commented:
got this error:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'minkaweb'@'localhost' WITH GRANT OPTION;
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
mysql>
0
 
Jason YuAuthor Commented:
Yes, i indeed followed that article and created that user, but i couldn't assign full privileges to that user. Could you help?

if I change *.* to User.*, then it works. That means I granted full privileges to this user for user DB.
0
 
Dave BaldwinFixer of ProblemsCommented:
I have MySQL Community version 5.0.51a and the GRANT ALL command works on it.  Which version and OS are you using?
0
 
Jason YuAuthor Commented:
Mysql:

Client API version      5.5.29

OS: Cent OS newest one.


mysql

MySQL Support      enabled
Active Persistent Links      0
Active Links      0
Client API version      5.5.29
MYSQL_MODULE_TYPE      external
MYSQL_SOCKET      /var/lib/mysql/mysql.sock
MYSQL_INCLUDE      -I/usr/include/mysql
MYSQL_LIBS      -L/usr/lib64/mysql -lmysqlclient
0
 
Jason YuAuthor Commented:
I got it partially solved. If I use mis account to connect to the information_shema database, the server let me connect. but if I try to connect to User db, it failed.

I guess User database is part of the meta db and doesn't permit ordinary user connect remotely. If I use root and login locally, I can modify this user database.

My two questions:

Is it possible to permit mis user to access user db remotely?

How can i use " GRANT ALL PRIVILEGES ON *.* TO 'minkaweb'@'localhost' WITH GRANT OPTION;" to enable full access for other users?





thank you.
0
 
Dave BaldwinFixer of ProblemsCommented:
I personally hope you can't do that.  "GRANT ALL PRIVILEGES ON *.*" just opens your database server up to anything anyone wants to do including deleting all databases.  It's a security nightmare to allow remote access that way.

Standard procedure is to allow someone privileges only on the database they are using.  That prevents them from interfering with someone else's data.
0
 
Jason YuAuthor Commented:
DaveBaldwin, thank you for your advise, I will take it and follow the rule.
i will close this case.
0
 
Dave BaldwinFixer of ProblemsCommented:
Thanks for the points, glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.