How to grant remote access to MySQL for a whole subnet?

Understanding MySQL database security and access control is crucial for technical interviews and practical implementation.

First, to grant remote access permissions for an entire subnet to the MySQL database, modify the MySQL server's user table to allow connections from any IP within the subnet. This process involves the following steps:

1. Ensure MySQL Server Configuration Allows Remote Connections:

   • Edit the MySQL server configuration file (typically my.cnf or my.ini) to set bind-address to 0.0.0.0 or comment out this line, enabling the MySQL server to accept connections from any IP.
   • Restart the MySQL service to apply these changes.

2. Create or Modify User Permissions to Allow Subnet Access:

   • Log in to the MySQL server: mysql -u root -p
   • Use the following SQL commands to update user permissions. For example, with the subnet 192.168.1.0/24, if you want to allow user example_user to connect from any IP within this subnet:

     sql
     CREATE USER 'example_user'@'192.168.1.%' IDENTIFIED BY 'password';
     GRANT ALL PRIVILEGES ON *.* TO 'example_user'@'192.168.1.%' WITH GRANT OPTION;
     FLUSH PRIVILEGES;

   • Here, 192.168.1.% represents any IP address from 192.168.1.1 to 192.168.1.254 that can use this account to connect to the MySQL server.

3. Ensure Network Security:

   • Configure firewall rules to allow traffic on the specific port (MySQL defaults to 3306) from the designated subnet.
   • Use security groups (if on a cloud platform) to ensure inbound rules permit access from the subnet.

4. Test the Connection:

   • Attempt to connect to the MySQL server from one or more different IP addresses within the subnet to verify the configuration is effective.

For example, when I configured the project database at my previous company, we needed to allow the entire development team's subnet to access the test database. I followed the above steps to configure the MySQL user and firewall, ensuring only our subnet could access the database, thus providing both convenience and security.

This concludes the main steps for granting remote access permissions for an entire subnet to the MySQL database. I hope this is helpful for you!