Introduction
The error Access denied for user 'root'@'localhost' when running GRANT statements means the root user lacks the GRANT OPTION privilege. This typically happens after running mysql_secure_installation or on managed database services that restrict root. The fix depends on the cause: re-grant root's privileges, use auth_socket authentication, or create a new admin user with full privileges.
The Error
GRANT ALL PRIVILEGES ON mydb.* TO 'appuser'@'localhost';
-- ERROR 1045 (28000): Access denied for user 'root'@'localhost'
Or sometimes:
-- ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'mydb'
Fix 1: Connect with sudo (Auth Socket)
On Ubuntu/Debian, MySQL root often uses auth_socket authentication — it matches the OS user, not a password:
1# WRONG — password auth may fail
2mysql -u root -p
3
4# CORRECT — auth_socket uses the OS user
5sudo mysql
6
7# Now GRANT works
8GRANT ALL PRIVILEGES ON mydb.* TO 'appuser'@'localhost';
9FLUSH PRIVILEGES;
Check which authentication plugin root uses:
SELECT user, host, plugin FROM mysql.user WHERE user = 'root';
-- If plugin is 'auth_socket' or 'unix_socket', use sudo mysql
-- If plugin is 'mysql_native_password' or 'caching_sha2_password', use password
Fix 2: Reset Root Privileges
If root lost the GRANT OPTION, stop MySQL and reset:
1# Stop MySQL
2sudo systemctl stop mysql
3
4# Start in safe mode (skip grant tables)
5sudo mysqld_safe --skip-grant-tables &
6
7# Connect without authentication
8mysql -u root
1-- Reset root privileges
2FLUSH PRIVILEGES;
3GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
4FLUSH PRIVILEGES;
5EXIT;
# Restart MySQL normally
sudo systemctl restart mysql
Fix 3: Create a New Admin User
If root is restricted (common on managed databases like AWS RDS), create a separate admin user:
1-- Connect as root (however you can)
2CREATE USER 'admin'@'localhost' IDENTIFIED BY 'strong_password';
3GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
4FLUSH PRIVILEGES;
Then use admin for granting privileges:
GRANT ALL PRIVILEGES ON mydb.* TO 'appuser'@'localhost';
Fix 4: Switch Root to Password Authentication
-- Switch from auth_socket to password authentication
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';
FLUSH PRIVILEGES;
After this, mysql -u root -p works with a password. But this is less secure than auth_socket on a single-user system.
Understanding GRANT Syntax
1-- Grant specific privileges on a specific database
2GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'appuser'@'localhost';
3
4-- Grant all privileges on a database
5GRANT ALL PRIVILEGES ON mydb.* TO 'appuser'@'localhost';
6
7-- Grant all privileges on all databases (admin-level)
8GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
9
10-- Grant specific table privileges
11GRANT SELECT ON mydb.users TO 'readonly'@'localhost';
12
13-- Always flush after granting
14FLUSH PRIVILEGES;
WITH GRANT OPTION
WITH GRANT OPTION lets the user grant their privileges to other users. Without it, the user can use the privileges but cannot share them:
1-- This user CAN grant privileges to others
2GRANT ALL ON mydb.* TO 'admin'@'localhost' WITH GRANT OPTION;
3
4-- This user CANNOT grant to others (most app users)
5GRANT SELECT, INSERT ON mydb.* TO 'appuser'@'localhost';
Creating Users and Granting
1-- Create user with password
2CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'app_password';
3
4-- Create user for remote access
5CREATE USER 'appuser'@'%' IDENTIFIED BY 'app_password';
6
7-- Create user for specific IP
8CREATE USER 'appuser'@'192.168.1.100' IDENTIFIED BY 'app_password';
9
10-- Grant after creation
11GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'appuser'@'localhost';
12FLUSH PRIVILEGES;
13
14-- Verify grants
15SHOW GRANTS FOR 'appuser'@'localhost';
Revoking Privileges
1-- Revoke specific privileges
2REVOKE INSERT, UPDATE ON mydb.* FROM 'appuser'@'localhost';
3
4-- Revoke all privileges
5REVOKE ALL PRIVILEGES ON mydb.* FROM 'appuser'@'localhost';
6
7-- Drop the user entirely
8DROP USER 'appuser'@'localhost';
MySQL 8.x Changes
1-- MySQL 8.x uses caching_sha2_password by default
2-- Some older clients can't connect with this plugin
3CREATE USER 'appuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
4
5-- MySQL 8.x requires CREATE USER before GRANT
6-- This no longer works (worked in MySQL 5.x):
7-- GRANT ALL ON mydb.* TO 'newuser'@'localhost' IDENTIFIED BY 'password';
8-- Must be two separate statements in 8.x
MariaDB Differences
1-- MariaDB uses unix_socket instead of auth_socket
2SELECT user, host, plugin FROM mysql.user WHERE user = 'root';
3-- plugin: unix_socket
4
5-- Connect via unix socket
6sudo mariadb
7
8-- Or switch to password auth
9ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('pw');
Common Pitfalls
Not using sudo mysql: On Ubuntu/Debian, MySQL root typically uses socket authentication. Running mysql -u root -p fails even with the correct password. Use sudo mysql instead.
Forgetting FLUSH PRIVILEGES: After GRANT or REVOKE, run FLUSH PRIVILEGES to reload the grant tables. Without it, changes may not take effect until MySQL restarts.
Host mismatch: 'user'@'localhost' and 'user'@'%' are different accounts in MySQL. A user created with @'%' cannot connect via localhost on some configurations. Create both if needed.
Missing WITH GRANT OPTION: Without this, a user cannot grant privileges to others, even if they have ALL PRIVILEGES. Root needs GRANT OPTION to run GRANT statements.
Managed database restrictions: AWS RDS, Google Cloud SQL, and Azure Database for MySQL do not give full SUPER or root privileges. Use the admin user provided by the service and check their documentation for privilege limitations.
Summary
Use sudo mysql on Ubuntu/Debian where root uses socket authentication
Root needs WITH GRANT OPTION to run GRANT statements for other users
Reset root privileges with --skip-grant-tables if locked out
MySQL 8.x requires CREATE USER before GRANT (no combined syntax)
Always FLUSH PRIVILEGES after granting or revoking
Create a dedicated admin user instead of relying on root for privilege management
