Many MySQL administrators encounter a puzzling situation where the root user - which should have full privileges - suddenly can't grant permissions. The error typically manifests like this:

mysql> GRANT ALL PRIVILEGES ON testdb.* TO 'newuser'@'%';
ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)

First, let's verify what's actually happening with your root account:

-- Check current user
SELECT CURRENT_USER(), USER();

-- Examine root privileges
SHOW GRANTS FOR 'root'@'%';

-- Check global privileges
SELECT * FROM mysql.user WHERE User='root' AND Host='%'\G

Several scenarios can lead to this behavior:

• The root user might be missing the GRANT OPTION privilege
• MySQL privilege tables might be corrupted
• There could be multiple root accounts with different host specifications
• The server might be running with --skip-grant-tables

Here's a step-by-step method to resolve the issue:

-- 1. First, ensure you're connecting as root properly
mysql -u root -p --protocol=TCP

-- 2. Check for multiple root accounts
SELECT User, Host FROM mysql.user WHERE User = 'root';

-- 3. If needed, update all root privileges (dangerous - be careful!)
UPDATE mysql.user SET 
  Grant_priv='Y', 
  Super_priv='Y' 
WHERE User='root';

-- 4. Flush privileges
FLUSH PRIVILEGES;

-- 5. Verify changes
SHOW GRANTS FOR 'root'@'%';

When GRANT commands fail, you can directly modify the privilege tables:

-- Create user directly (alternative to GRANT)
INSERT INTO mysql.user 
  (Host, User, Password, 
   Select_priv, Insert_priv, Update_priv, 
   Delete_priv, Create_priv, Drop_priv)
VALUES
  ('%', 'newuser', PASSWORD('secret'),
   'Y', 'Y', 'Y', 'Y', 'Y', 'Y');

-- Then grant specific database privileges
INSERT INTO mysql.db 
  (Host, Db, User, 
   Select_priv, Insert_priv, Update_priv, 
   Delete_priv, Create_priv, Drop_priv)
VALUES
  ('%', 'testdb', 'newuser',
   'Y', 'Y', 'Y', 'Y', 'Y', 'Y');

FLUSH PRIVILEGES;

To avoid similar issues in the future:

• Regularly backup your mysql database
• Consider creating secondary admin accounts with specific privileges
• Document all privilege changes
• Periodically verify privileges with SHOW GRANTS

Enable general query logging to see the exact privilege checks:

-- Enable logging
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/mysql-general.log';

-- After reproducing the issue, check the log
-- Disable when done
SET GLOBAL general_log = 'OFF';

---

Recently I encountered a baffling situation where my MySQL root user couldn't grant privileges despite having ALL PRIVILEGES on *.*. The error message appeared simple:

ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)

This is particularly confusing because:

• I could successfully authenticate as root
• SHOW GRANTS confirmed root has ALL PRIVILEGES
• The issue occurred when trying to create users or grant privileges

After digging through MySQL documentation and forums, I discovered several potential causes:

-- Check if root has the GRANT OPTION privilege
SELECT Grant_priv FROM mysql.user WHERE User='root' AND Host='%';

-- Verify if there are anonymous users causing conflicts
SELECT User, Host FROM mysql.user WHERE User='';

The most likely scenarios are:

1. The root user lacks the GRANT OPTION privilege despite showing ALL PRIVILEGES
2. MySQL upgrade artifacts in the permission tables
3. Conflicts with anonymous users
4. mysql.user table corruption

Here's how I fixed the issue:

First, restart MySQL in safe mode with privilege checking disabled:

sudo systemctl stop mysql
sudo mysqld_safe --skip-grant-tables &

Then connect without authentication:

mysql -u root

Now flush privileges and verify:

FLUSH PRIVILEGES;
SELECT * FROM mysql.user WHERE User='root'\G

If you see Grant_priv='N', fix it with:

UPDATE mysql.user SET Grant_priv='Y' WHERE User='root';
FLUSH PRIVILEGES;
exit;

Finally restart MySQL normally:

sudo systemctl start mysql

If the above doesn't work, try these approaches:

1. Recreate root privileges completely:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

2. Check for conflicting permissions:

SHOW GRANTS FOR 'root'@'localhost';
SHOW GRANTS FOR 'root'@'127.0.0.1';

3. Repair permission tables:

mysql_upgrade -u root -p --force

To avoid similar issues:

• Regularly back up your mysql database
• After upgrades, always run mysql_upgrade
• Consider using more specific privileges rather than ALL PRIVILEGES
• Audit user permissions periodically

Remember that MySQL's privilege system can be complex, and sometimes the solution requires multiple approaches. The key is to methodically eliminate potential causes until you find the root of the problem.