Allow all remote connections, MySQL

by

As pointed out by Ryan above, the command you need is 

GRANT ALL ON *.* to user@'%' IDENTIFIED BY 'password';

However, note that the documentation indicates that in order for this to work, another user account from localhost must be created for the same user; otherwise, the anonymous account created automatically by mysql_install_db takes precedence because it has a more specific host column.

In other words; in order for user user to be able to connect from any server; 2 accounts need to be created as follows: 

GRANT ALL ON *.* to user@localhost IDENTIFIED BY 'password'; 
GRANT ALL ON *.* to user@'%' IDENTIFIED BY 'password';

Read the full documentation here.

And here’s the relevant piece for reference:

After connecting to the server as root, you can add new accounts. The
following statements use GRANT to set up four new accounts:

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'admin'@'localhost';
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> CREATE USER 'dummy'@'localhost';

The accounts created by these statements have the following
properties:

Two of the accounts have a user name of monty and a password of
some_pass. Both accounts are superuser accounts with full privileges
to do anything. The ‘monty’@’localhost’ account can be used only when
connecting from the local host. The ‘monty’@’%’ account uses the ‘%’
wildcard for the host part, so it can be used to connect from any
host.

It is necessary to have both accounts for monty to be able to connect
from anywhere as monty. Without the localhost account, the
anonymous-user account for localhost that is created by
mysql_install_db would take precedence when monty connects from the
local host. As a result, monty would be treated as an anonymous user.
The reason for this is that the anonymous-user account has a more
specific Host column value than the ‘monty’@’%’ account and thus comes
earlier in the user table sort order. (user table sorting is discussed
in Section 6.2.4, “Access Control, Stage 1: Connection Verification”.)

More Related Contents:

  1. SQL select only rows with max value on a column [duplicate]
  2. What is the error “Every derived table must have its own alias” in MySQL?
  3. Generating Depth based tree from Hierarchical Data in MySQL (no CTEs)
  4. Authentication plugin ‘caching_sha2_password’ cannot be loaded
  5. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  6. How to grant remote access permissions to mysql server for user?
  7. mySQL convert varchar to date
  8. Generating a series of dates [duplicate]
  9. How to escape single quotes in MySQL
  10. MySQL comparison with null value
  11. Why is the estimated rows count very different in phpmyadmin results?
  12. Slow query when using ORDER BY
  13. How to import XML file into MySQL database table using XML_LOAD(); function
  14. Unique constraint that allows empty values in MySQL
  15. Matching all values in IN clause
  16. INTERSECT in MySQL
  17. Having both a Created and Last Updated timestamp columns in MySQL 4.0
  18. What does SQL clause “GROUP BY 1” mean?
  19. Bogus foreign key constraint fail
  20. MySQL SELECT LIKE or REGEXP to match multiple words in one record
  21. Syntax error when using except in a query
  22. Easiest way to copy a table from one database to another?
  23. sql like operator on integer
  24. How to make a primary key start from 1000?
  25. The best way to remove value from SET field?
  26. How do you force mysql LIKE to be case sensitive? [duplicate]
  27. MySQL Alter table causes Error: Invalid use of NULL value
  28. Delete all rows with timestamp older than x days
  29. In what order are MySQL JOINs evaluated?
  30. uninitialized constant MysqlCompat::MysqlRes (using mms2r gem)

Leave a Comment