How to allow a MySQL user account to connect from any host?

It is quite possible to allow a user account to connect from any host. To do so we need to create the user with the help of ‘%’ wild card character after @ character. Its syntax would be as follows −

Use mysql;
CREATE USER user_name@’%’ IDENTIFIED BY password;

Here 

• user_name is the name of the user we wish to make an account for.
• Password is the password we wish to make for user_account. With the help of this password, MySQL server will identify this user.

Example

In the given example we are creating a user ‘Gaurav’ by using ‘%’ character so that it can be connected to any host.

mysql> use mysql
Database changed

mysql> CREATE USER Gaurav@'%' IDENTIFIED BY 'password123';
Query OK, 0 rows affected (0.00 sec)

The query below will give us the privileges for the new user account Gaurav@’%’.

mysql> SHOW GRANTS FOR Gaurav@'%';
+------------------------------------+
| Grants for Gaurav@%                |
+------------------------------------+
| GRANT USAGE ON *.* TO 'Gaurav'@'%' |
+------------------------------------+
1 row in set (0.00 sec)

Vanithasree

Published on 20-Feb-2018 17:46:23