Solved

can't remote login to MySQL DB using Aqua tool

Posted on 2013-01-07
11
377 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 82

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
 
LVL 82

Accepted Solution

by:
Dave Baldwin earned 500 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 82

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 500 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 82

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 500 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 82

Expert Comment

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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now