Why Do I Get 'Access Denied' Errors When Granting MySQL Privileges Despite Having Necessary Permissions?

Granting Privileges without Access Denied Errors

When attempting to grant privileges, MySQL users may encounter the error "Access denied for user 'root'@'localhost' (using password: YES)." This can be puzzling, as it may seem like the user has the necessary permissions.

Verifying Privileges

To confirm that the user has appropriate privileges, run the following commands:

SELECT user();
SELECT current_user();
SHOW GRANTS FOR 'root'@'localhost';
SELECT * FROM mysql.user WHERE User='root';

These commands should verify that the user is indeed root@localhost and has the necessary permissions, including:

• SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER
• Create and manipulate tables, views, and routines
• Grant privileges to other users

Issue: Granting Privileges on Specific Tables

Despite having these permissions, an error may occur when attempting to grant privileges on specific tables:

GRANT ALL PRIVILEGES ON *.* TO 'steves'@'[hostname].com' IDENTIFIED BY '[OBSCURED]' WITH GRANT OPTION;

This is because the mysql.users table is considered off-limits for all users except root.

Solution: Granting Privileges on All Databases

To work around this, use the following command, which grants privileges on all databases except mysql.users:

GRANT ALL PRIVILEGES ON `%`.* TO '[user]'@'[hostname]' IDENTIFIED BY '[password]' WITH GRANT OPTION;

Using %. instead of .* ensures that all databases are included except the mysql.users table. This should successfully grant privileges without triggering the access denied error.

The above is the detailed content of Why Do I Get 'Access Denied' Errors When Granting MySQL Privileges Despite Having Necessary Permissions?. For more information, please follow other related articles on the PHP Chinese website!