When working with MySQL in distributed environments, a common requirement is to allow database access from multiple hosts while maintaining security. The typical approach of creating separate user entries for each host (like 'bob'@'localhost' and 'bob'@'10.1.1.1') works but introduces maintenance overhead.

MySQL provides a wildcard option for host specifications. For internal networks, you can use:

CREATE USER 'bob'@'192.168.1.%' IDENTIFIED BY 'password123';
GRANT SELECT, INSERT, UPDATE, DELETE ON MyDatabase.* TO 'bob'@'192.168.1.%';

This allows connections from any IP in the 192.168.1.x range. For wider access (though less secure):

CREATE USER 'bob'@'%' IDENTIFIED BY 'password123';
GRANT SELECT ON MyDatabase.* TO 'bob'@'%';

When using wildcards:

• Combine with network-level firewalls
• Use specific IP ranges (10.0.0.0/8) rather than '%'
• Implement separate users for different security levels

To verify your setup:

SELECT User, Host FROM mysql.user WHERE User = 'bob';
SHOW GRANTS FOR 'bob'@'localhost';
SHOW GRANTS FOR 'bob'@'10.1.1.1';

For environments requiring multiple specific hosts, consider this script to keep permissions in sync:

#!/bin/bash
HOSTS=("localhost" "10.1.1.1" "10.1.1.2")
for host in "${HOSTS[@]}"; do
  mysql -e "CREATE USER IF NOT EXISTS 'bob'@'${host}' IDENTIFIED BY 'password123';"
  mysql -e "GRANT SELECT, INSERT, UPDATE, DELETE ON MyDatabase.* TO 'bob'@'${host}';"
done

When managing MySQL databases in distributed environments, administrators often need to create user accounts that can connect from multiple hosts. The standard approach involves creating separate user entries for each host, but this leads to maintenance challenges.

Here's the conventional method most DBAs use:

-- Creating separate user entries
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'securePass123!';
CREATE USER 'app_user'@'10.1.1.1' IDENTIFIED BY 'securePass123!';

-- Granting identical privileges
GRANT SELECT, INSERT, UPDATE ON inventory.* 
TO 'app_user'@'localhost', 'app_user'@'10.1.1.1';

The primary concerns with this approach are:

• Privilege synchronization between multiple user entries
• Password management complexity
• Audit trail fragmentation

Using Wildcard Hosts

For development environments, you might consider:

CREATE USER 'dev_user'@'%' IDENTIFIED BY 'tempPassword';
GRANT ALL PRIVILEGES ON sandbox.* TO 'dev_user'@'%';

Warning: Never use wildcards in production environments due to security risks.

Stored Procedures for User Management

Create a procedure to maintain multiple host entries:

DELIMITER //
CREATE PROCEDURE create_multi_host_user(
    IN username VARCHAR(50),
    IN password VARCHAR(100),
    IN hosts TEXT,
    IN db_privs TEXT
)
BEGIN
    DECLARE host_list TEXT DEFAULT hosts;
    DECLARE current_host VARCHAR(50);
    DECLARE pos INT DEFAULT 1;
    
    WHILE pos > 0 DO
        SET current_host = SUBSTRING_INDEX(host_list, ',', 1);
        SET host_list = SUBSTRING(host_list, LENGTH(current_host)+2);
        
        SET @create_user = CONCAT("CREATE USER '", username, "'@'", 
                               current_host, "' IDENTIFIED BY '", password, "'");
        PREPARE stmt FROM @create_user;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        SET @grant_privs = CONCAT("GRANT ", db_privs, " TO '", 
                               username, "'@'", current_host, "'");
        PREPARE stmt FROM @grant_privs;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        SET pos = LOCATE(',', host_list);
    END WHILE;
END //
DELIMITER ;

-- Usage example:
CALL create_multi_host_user('api_user', 'ComplexP@ss', 'localhost,192.168.1.5,10.0.0.8', 
                          'SELECT, INSERT, UPDATE ON api_db.*');

• Always use different passwords for production and development environments
• Implement regular privilege audits using SHOW GRANTS FOR 'user'@'host'
• Consider using MySQL roles (available in MySQL 8.0+) for privilege management

For large-scale deployments:

1. Implement centralized user management with MySQL Enterprise
2. Use proxy users with authentication plugins
3. Deploy configuration management tools (Ansible, Puppet, Chef) for consistent user provisioning

# Example Ansible playbook snippet for multi-host user creation
- name: Ensure MySQL users exist
  mysql_user:
    name: "{{ item.name }}"
    host: "{{ item.host }}"
    password: "{{ item.password }}"
    priv: "{{ item.priv }}"
    state: present
  with_items:
    - { name: 'cluster_user', host: '10.0.0.%', password: 'V3ryS3cret!', 
        priv: 'replication_client,replication_slave ON *.*' }
    - { name: 'cluster_user', host: 'localhost', password: 'V3ryS3cret!', 
        priv: 'replication_client,replication_slave ON *.*' }