Friday, July 29, 2022

How to grant privileges Mysql user for remote host machine

In this tutorial, we will learn how to grant privileges MySQL users for remote host/ip_address of the machine

SSH into remote server:

First, let's enter the remote server where the MySQL server is allocated.

ssh server_user_name@server_ip_address

Use your own server_user_name and server_ip_address.

ssh ubuntu@

Now, logged in as MySQL's root user

mysql -u root_username -p

Enter your root username and hit enter. After that, it will ask for MySQL password use your MySQL password

Grant All Privileges for all host:

GRANT ALL PRIVILEGES ON *.* TO 'mysql_username'@'%' IDENTIFIED BY 'mysql_password';

Here, we are giving the MySQL user access to it from any host or IP address by specifying %. Don't forget to flush privileges after granting privileges to the user.

Grant All Privileges for a host:

If we want to restrict MySQL user from all the hosts and give access to the specific IP address or host machine then we can use the following command

GRANT ALL PRIVILEGES ON *.* TO 'mysql_username'@'ip_address' IDENTIFIED BY 'mysql_password';

Make sure to use your own mysql_username and ip_address to allow access from that host.

Revoke privileges:

REVOKE ALL PRIVILEGES ON *.* FROM 'mysql_username'@'ip_address';

List host users:

SELECT user, host FROM mysql.user;

This will list all the users associated with the hosts.

List all the privileges given to users:

SELECT * FROM information_schema.user_privileges;

This will show all the users with hosts and privileges available for that user and host.

Delete user associated with host:

DROP USER 'mysql_username'@'ip_address';

This will drop the user associated with that IP address and revoke all the privileges.