Solved

can't remote login to MySQL DB using Aqua tool

Posted on 2013-01-07
11
386 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 83

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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 83

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

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 83

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 83

Expert Comment

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

Featured Post

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

724 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