When managing MySQL databases, we often encounter situations where a user initially configured with 'username'@'localhost' needs remote access capabilities. The standard approach of revoking and recreating privileges becomes problematic when the original password isn't known.

First, let's examine the existing grants as shown in the example:

mysql> SHOW GRANTS FOR 'username'@'localhost';
+---------------------------------------------------------------------------+
| Grants for username@localhost                                             |
+---------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'username'@'localhost' IDENTIFIED BY PASSWORD 'xxx' |
| GRANT ALL PRIVILEGES ON `userdb`.* TO 'username'@'localhost'              |
+---------------------------------------------------------------------------+

Here are three reliable approaches to modify the host without losing the password:

Method 1: Direct User Update

RENAME USER 'username'@'localhost' TO 'username'@'%';
FLUSH PRIVILEGES;

Method 2: Using mysql.user Table

UPDATE mysql.user SET host='%' WHERE user='username' AND host='localhost';
FLUSH PRIVILEGES;

Method 3: Create Duplicate User

CREATE USER 'username'@'%' IDENTIFIED BY PASSWORD 'xxx';
GRANT ALL PRIVILEGES ON `userdb`.* TO 'username'@'%';
FLUSH PRIVILEGES;

Remember that opening database access to % (all hosts) has security implications:

• Always configure firewall rules to restrict access
• Consider using specific IP ranges instead of % when possible
• Regularly audit your MySQL user privileges

To revert the change when remote access is no longer needed:

RENAME USER 'username'@'%' TO 'username'@'localhost';
-- OR --
UPDATE mysql.user SET host='localhost' WHERE user='username' AND host='%';
FLUSH PRIVILEGES;

If you encounter authentication issues after modification:

1. Verify the user exists in both hosts: SELECT user,host FROM mysql.user;
2. Check the authentication plugin: SELECT plugin FROM mysql.user WHERE user='username';
3. Ensure proper flush: Always run FLUSH PRIVILEGES after manual table updates

---

When working with MySQL user permissions, a common requirement is to modify the host restriction from 'localhost' to '%' (allowing remote connections) or vice versa. The standard approach would be:

REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'localhost';
DROP USER 'username'@'localhost';
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON `userdb`.* TO 'username'@'%';

However, this presents two significant problems:

• You need to know the existing password (which might be hashed in the system)
• There's a risk of privilege interruption during the transition

MySQL provides a direct way to rename users without losing privileges or needing the password:

RENAME USER 'username'@'localhost' TO 'username'@'%';

This single command:

• Preserves all existing privileges
• Maintains the same password
• Executes instantly without service interruption

Let's walk through a complete example with verification steps:

-- 1. Check current privileges
SHOW GRANTS FOR 'webapp'@'localhost';

-- 2. Perform the host change
RENAME USER 'webapp'@'localhost' TO 'webapp'@'%';

-- 3. Verify the change took effect
SHOW GRANTS FOR 'webapp'@'%';

-- 4. Test new connection (from remote client)
mysql -u webapp -p -h your_server_ip

If you need to restrict access back to localhost:

RENAME USER 'username'@'%' TO 'username'@'localhost';

This maintains all security credentials while changing the access restriction.

Before allowing remote connections:

• Ensure your MySQL server is properly firewalled
• Consider using SSH tunneling instead of direct remote access
• Review your bind-address in my.cnf/my.ini
• Implement additional security measures like connection limits

-- Example: Adding connection limits
ALTER USER 'username'@'%' WITH MAX_CONNECTIONS_PER_HOUR 100;

Always test changes in a staging environment before applying to production databases.