Written by Kali Kiran Lakhinana on June 19, 2014 in How to > SQL

How to Enable and Disable Remote Access to users in MySQL

GET ALERTS:

Get our Latest updates delivered to your mailbox!

When you try to connect to a Database using a tool like MySQL Workbench then you may run into an error like below, as the user is not allowed to access the Database from a remote location.

Access denied for user ‘username’@’192.168.2.2’ (using password: YES)

Note: You’ll require Administrator privileges.

How to Enable Remote Access to Users in MySQL

In order to allow remote access we need to perform two steps. The first is to edit the my.cnf (MySQL Configuration file) and the second is to Grant the specific user to login remotely.

First : Change MySQL Configuration

#1. Navigate in to the path : /etc/mysql.
#2. Open the File : my.cnf using vim or vi or nano nano my.cnf.
(or)

#1. You can directly open the file using the command :
sudo nano /etc/mysql/my.cnf
#2. Search for the line: bind-address.
#3. After finding the line, Comment out that line by placing a hash (#) and add #skip-networking in the line below after #bind-address.
It should look something similar to:

#bind-address = 127.0.0.1
#skip-networking

Second : GRANT Privileges to user in MySQL

You may sometimes get access to your database by just performing the First step, if you are not able to access the Database remotely after performing the first step then update user Privileges by using GRANT.

For instance, you would like to allow a user: sandeep to access any Database from any IP, then the GRANT command should be:

mysql > GRANT ALL PRIVILEGES ON *.* TO 'sandeep'@'%' IDENTIFIED BY 'PASSWORD-HERE';

If you would like to allow a user to access any Database from a specific IP, then the GRANT command should be:

mysql > GRANT ALL PRIVILEGES ON *.* TO 'sandeep'@'175.100.44.72' IDENTIFIED BY 'PASSWORD-HERE';

If you would like to allow a user to access specific Database from a specific IP or any IP, then GRANT command should be:

//Any IP
mysql > GRANT ALL PRIVILEGES ON mydb.* TO 'sandeep'@'%' IDENTIFIED BY 'PASSWORD-HERE';

//Specific IP

mysql > GRANT ALL PRIVILEGES ON mydb.* TO 'sandeep'@'175.100.44.72' IDENTIFIED BY 'PASSWORD-HERE';

Finally you need to update the privileges by using:

mysql > FLUSH PRIVILEGES;

Now exit MySQL and restart the MySQL server using the command :

service mysql restart

How to Disable Remote Access in MySQL

If you want to revoke remote login privileges to a certain user, then you need to use the REVOKE command.

For instance, You would like to revoke remote login privileges of user: sandeep FROM any IP then :

mysql > REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'sandeep'@'%';

If you would like to revoke a specific privilege :

mysql > REVOKE INSERT ON mydb.* FROM 'sandeep'@'%';

The above will disable the user:sandeep from Inserting any records in the Database mydb from any IP.

You can read more about GRANT and REVOKE permissions here.

{ 0 comments… add one now }

Leave a Comment

Previous post:

Next post: