Easy Way To Disable Remote Access in MySQL Database – OrcaCore

By Posted on

Easy Way To Disable Remote Access in MySQL Database - OrcaCore

Easy Way To Disable Remote Access in MySQL Database – OrcaCore

This guide will show you how to Disable Remote Access in MySQL Database. Connecting remotely to a MySQL database, especially as the root user, poses significant security risks. Therefore, we’ve created this guide to show you how to Disable Remote Access in MySQL Database for both the root user and all other users. Follow the steps below on the OrcaCore website to Disable Remote Access in MySQL Database.

By default, remote access to your MySQL database should be disabled for security reasons. However, if you have modified this setting, you can use the following steps to Disable Remote Access in MySQL Database.

Step 1 – Prevent MySQL Database Remote Access for Root User

First, you need to connect to your MySQL shell and access the database you want to modify.

Then, execute the following MySQL command to remove all records from the mysql.user table where the user is root and the host is not localhost, 127.0.0.1, or ::1:

DELETE FROM `mysql.user` WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

Finally, apply the changes by flushing the privileges:

FLUSH PRIVILEGES;

Exit from your MySQL shell:

EXIT;

Step 2 – Prevent MySQL Database Remote Access for All Users

If you intend to disable all remote connections to your MySQL database, you’ll need to edit the my.ini or my.cnf configuration file. The specific file depends on your operating system and MySQL installation.

Note: For information on locating your MySQL configuration file, refer to this guide on OrcaCore: Find MySQL Configuration File Location on Linux.

Once you’ve located and opened your MySQL configuration file, use a text editor (such as the vi editor as described on OrcaCore How to work with the vi editor in Linux) to find the [mysqld] section. Add the following line under the [mysqld] section:

[mysqld]
...
skip-networking
...

Or, if the skip-networking option already exists, uncomment it by removing any preceding # symbol. This option disables MySQL remote connections for all users. After making the change, save the file and close it.

That’s it! You’re done.

Conclusion

At this point, you have successfully learned how to Disable Remote Access in MySQL Database for both the root user and all users. As mentioned earlier, this option is typically disabled by default for security reasons. If you have altered this setting previously, you can easily disable it again using the methods described in this guide.

We hope you found this guide helpful. You might also be interested in these related articles:

Alternative Methods to Disable Remote Access in MySQL

While the previous methods effectively disable remote access, there are alternative approaches that offer more granular control and security. Here are two additional methods:

Method 1: Limiting Access by Specific IP Addresses

Instead of completely disabling remote access, you can restrict access to your MySQL database to only specific IP addresses or IP address ranges. This allows authorized users from known locations to connect while blocking everyone else. This approach is especially useful when you need to grant access to specific servers or development environments.

Explanation:

This method leverages MySQL’s grant system. You can create users that are only allowed to connect from a specific host or IP address. Any connection attempt from a different IP address will be rejected. This offers a more refined control compared to completely disabling remote connections. You can create different users with different permission based on IP address.

Implementation:

  1. Connect to MySQL as a privileged user (e.g., root).

  2. Create a new user or modify an existing user with the desired IP address restriction.

    For example, to allow a user named webapp with password securepassword to connect only from the IP address 192.168.1.100, use the following SQL command:

    CREATE USER 'webapp'@'192.168.1.100' IDENTIFIED BY 'securepassword';
GRANT ALL PRIVILEGES ON database_name.* TO 'webapp'@'192.168.1.100';
FLUSH PRIVILEGES;

    Replace database_name with the name of the database you want the user to access. To allow access from any database, you can use *.*.
    To allow access from a range of ip addresses, you can do:

    CREATE USER 'webapp'@'192.168.1.%' IDENTIFIED BY 'securepassword';
GRANT ALL PRIVILEGES ON database_name.* TO 'webapp'@'192.168.1.%';
FLUSH PRIVILEGES;

    This creates a user who can access from IP address 192.168.1.1 to 192.168.1.254.

  3. Revoke remote access from the root user (if necessary): 
    REVOKE ALL PRIVILEGES ON *.* FROM 'root'@'%';
FLUSH PRIVILEGES;

    This will prevent the root user from connecting from any remote host.

  4. Ensure the user is not allowed to connect from any host:

    REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'%';
DROP USER 'user'@'%';
FLUSH PRIVILEGES;

    This script ensures that the remote connection for user is removed and will drop the user if it exists.

Benefits:

  • Provides granular control over who can access the database remotely.
  • Enhances security by limiting the attack surface.
  • Allows legitimate remote access for specific purposes.

Drawbacks:

  • Requires careful management of user accounts and IP address restrictions.
  • Can be more complex to set up than simply disabling remote access entirely.
  • Changes to network configuration may require updating user grants.

Method 2: Using a Secure Shell (SSH) Tunnel

Another secure way to enable remote access is through an SSH tunnel. This method creates an encrypted connection between your local machine and the server hosting the MySQL database. All MySQL traffic is then routed through this secure tunnel, protecting it from eavesdropping and unauthorized access.

Explanation:

An SSH tunnel creates a secure, encrypted channel between your local machine and the MySQL server. You forward a local port on your machine to the MySQL port on the server. When you connect to the local port, your traffic is automatically encrypted and securely transmitted to the MySQL server through the SSH tunnel. This method eliminates the need to expose the MySQL port directly to the internet, significantly improving security.

Implementation:

  1. Establish an SSH tunnel to the MySQL server.

    Using the SSH command in your terminal:

    ssh -L 3306:127.0.0.1:3306 user@your_server_ip
    • -L 3306:127.0.0.1:3306: This option forwards local port 3306 to port 3306 on the server. 127.0.0.1 ensures that the connection on the server side is only accepted from the localhost.
    • user@your_server_ip: Replace user with your SSH username on the server and your_server_ip with the server’s IP address or hostname.

  2. Connect to the MySQL database through the tunnel.

    In your MySQL client (e.g., MySQL Workbench, command-line client), connect to 127.0.0.1 (localhost) on port 3306. The connection will be tunneled through the SSH connection to the remote server.

  3. Configure MySQL to only listen on localhost (if not already configured):

    Edit your my.cnf or my.ini file and ensure the bind-address is set to 127.0.0.1:

    [mysqld]
bind-address = 127.0.0.1

    Restart the MySQL server after making this change.

Benefits:

  • Provides a secure, encrypted connection for remote access.
  • Eliminates the need to open the MySQL port directly to the internet.
  • Easy to set up and use with standard SSH tools.

Drawbacks:

  • Requires an active SSH connection for remote access.
  • Performance may be slightly affected by the overhead of encryption.
  • Relies on the security of the SSH server and user authentication.

These alternative methods provide more secure and granular control over remote access to your MySQL database. Choosing the best method depends on your specific needs and security requirements.

Related posts: