Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

can't remote login to MySQL DB using Aqua tool

Posted on 2013-01-07
11
Medium Priority
?
390 Views
Last Modified: 2013-01-09
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
0
Comment
Question by:Jason Yu
  • 6
  • 5
11 Comments
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 38753741
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
 

Author Comment

by:Jason Yu
ID: 38755836
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
 

Author Comment

by:Jason Yu
ID: 38756170
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 84

Accepted Solution

by:
Dave Baldwin earned 2000 total points
ID: 38756394
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
 

Author Comment

by:Jason Yu
ID: 38756531
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
 
LVL 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 2000 total points
ID: 38756859
I have MySQL Community version 5.0.51a and the GRANT ALL command works on it.  Which version and OS are you using?
0
 

Author Comment

by:Jason Yu
ID: 38757278
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
 

Author Comment

by:Jason Yu
ID: 38757292
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
 
LVL 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 2000 total points
ID: 38757428
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
 

Author Comment

by:Jason Yu
ID: 38757904
DaveBaldwin, thank you for your advise, I will take it and follow the rule.
i will close this case.
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 38758069
Thanks for the points, glad to help.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question