Enforcing Client IP‑Based Access Control in MySQL InnoDB Cluster via Router

Background and Challenges

In a MySQL InnoDB Cluster, application servers connect to the backend through MySQL Router. This introduces three problems:

IP address loss : the server sees only the Router’s IP.

Missing Proxy Protocol : Router does not forward client information.

Access‑control dilemma : subnet‑based user restrictions cannot be enforced because the real client IP is unavailable.

Solution Overview

Core Idea

Enable MySQL Router’s connection‑attribute injection to add the client’s real IP as the attribute _client_ip at connection time, then run a stored procedure via the global init_connect variable to reject connections that violate subnet rules.

SSL Mode Attribute Injection Issue

Official documentation states that the default client_ssl_mode ( PREFERRED) should allow _client_ip injection. In practice the attribute is only injected when client_ssl_mode is set to DISABLED, indicating a bug that requires SSL to be disabled for the workaround.

Testing Process

1. Environment Information

# Test environment configuration
MySQL Server: 8.0.40+ (performance_schema enabled)
MySQL Router: 8.0.44
Network topology:
- Router IP: 192.168.1.101
- Router listening port: 6446
- Forbidden subnet: 10.0.0.0/8
- Restricted user: userA

2. MySQL Router Configuration Example

Edit mysqlrouter.conf on every Router node:

[routing:mgr_rw]
bind_address = 0.0.0.0
bind_port = 6446
destinations = 127.0.0.1:3306  # replace with actual DB address in production
routing_strategy = first-available
# SSL must be disabled for attribute injection (bug workaround)
client_ssl_mode = DISABLED

3. Database‑Side Access Control Implementation

3.1 Create Monitoring Database and Stored Procedure

-- Create a dedicated security monitoring database
CREATE DATABASE IF NOT EXISTS router_acl;

DELIMITER //
CREATE PROCEDURE router_acl.check_client_ip()
BEGIN
    DECLARE real_ip VARCHAR(50);
    DECLARE error_message VARCHAR(200);
    -- Extract the real client IP injected by the Router
    SELECT ATTR_VALUE INTO real_ip
    FROM performance_schema.session_connect_attrs
    WHERE PROCESSLIST_ID = CONNECTION_ID()
      AND ATTR_NAME = '_client_ip';
    -- Block logic: deny userA if coming from the restricted subnet
    IF (CURRENT_USER() LIKE 'userA@%') AND (real_ip LIKE '10.%') THEN
        SET error_message = CONCAT('Access Denied: userA is NOT allowed from IP ', real_ip, ' (10.0.0.0/8) via Router');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = error_message;
    END IF;
END //
DELIMITER ;

3.2 Grant Permissions and Configure init_connect

-- Grant EXECUTE on the procedure to the restricted user
GRANT EXECUTE ON PROCEDURE router_acl.check_client_ip TO 'userA'@'192.168.1.%';
-- Grant EXECUTE to other users to avoid connection failures
GRANT EXECUTE ON PROCEDURE router_acl.check_client_ip TO 'userB'@'192.168.1%';
-- Set the global connection initialization script (init_connect does not run for accounts with SUPER privilege)
SET GLOBAL init_connect = 'CALL router_acl.check_client_ip()';

4. Verification Tests

Test case 1: Subnet 10.x.x.x accessing userA (should be blocked)

# From a machine in the 10.* subnet
mysql -u userA -p -h 192.168.1.101 -P 6446
# Expected result:
# ERROR 45000 (45000): Access Denied: userA is NOT allowed from IP 10.x.x.x (10.0.0.0/8) via Router

Test case 2: Subnet 10.x.x.x accessing userB (should be allowed)

# From a machine in the 10.* subnet
mysql -u userB -p -h 192.168.1.101 -P 6446
# Expected result: login succeeds

Test case 3: Subnet 192.168.1.x accessing userA (should be allowed)

# From a machine in the 192.168.1.* subnet
mysql -u userA -p -h 192.168.1.101 -P 6446
# Expected result: login succeeds

5. Persistent Configuration

Add the following to each node’s my.cnf to survive restarts:

[mysqld]
init_connect='CALL router_acl.check_client_ip()'

6. Troubleshooting Checklist

Connection refused – likely insufficient privileges for init_connect. Grant EXECUTE on the procedure to the user.

Attribute value empty – Router SSL not disabled. Set client_ssl_mode = DISABLED.

Root user bypasses restriction – SUPER privilege exempts init_connect. Test with a non‑SUPER account.

Attribute empty without error – Router version may not support injection. Verify compatibility. init_connect execution fails – check stored‑procedure syntax.

All connections fail – global init_connect may be set incorrectly. Reset with SET GLOBAL init_connect = ''.