Creating a MySQL User

-- Create a user that may connect from localhost only
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'StrongPassword123!';

-- For connections from any IP
CREATE USER 'myuser'@'%' IDENTIFIED BY 'StrongPassword123!';

-- For one specific IP
CREATE USER 'myuser'@'192.168.1.100' IDENTIFIED BY 'StrongPassword123!';

Granting Privileges (GRANT)

-- All privileges on a single database
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';

-- Read-only
GRANT SELECT ON mydb.* TO 'readonly_user'@'localhost';

-- Read + write
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'localhost';

-- On one specific table
GRANT SELECT, INSERT ON mydb.orders TO 'reports_user'@'localhost';

-- Superuser (all privileges on all databases)
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- Apply the changes
FLUSH PRIVILEGES;

Viewing Privileges

-- Privileges of a specific user
SHOW GRANTS FOR 'myuser'@'localhost';

-- List of all users
SELECT User, Host FROM mysql.user;

-- Current user
SHOW GRANTS;

Revoking Privileges (REVOKE)

-- Revoke specific privileges
REVOKE INSERT, UPDATE, DELETE ON mydb.* FROM 'myuser'@'localhost';

-- Revoke all privileges
REVOKE ALL PRIVILEGES ON mydb.* FROM 'myuser'@'localhost';
FLUSH PRIVILEGES;

Changing a User Password

-- MySQL 5.7 / MariaDB
SET PASSWORD FOR 'myuser'@'localhost' = PASSWORD('NewPassword456!');

-- MySQL 8.0+
ALTER USER 'myuser'@'localhost' IDENTIFIED BY 'NewPassword456!';
FLUSH PRIVILEGES;

Dropping a User

DROP USER 'myuser'@'localhost';
FLUSH PRIVILEGES;

Creating a User for a Web Application

-- Create a database and a user for a new site
CREATE DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'SecurePass789!';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,
    CREATE TEMPORARY TABLES, LOCK TABLES ON myapp_db.* TO 'myapp_user'@'localhost';
FLUSH PRIVILEGES;

Principle of least privilege: give users only the rights they really need. For web applications: SELECT, INSERT, UPDATE, DELETE. No CREATE, DROP or ALTER — unless the application requires them.

Common Access Privilege Errors

Error	Cause	Fix
ERROR 1045 (28000): Access denied for user 'app'@'10.0.0.5'	The user was created as `'app'@'localhost'`, but the connection comes from a different host or IP	Create a separate user entry for that host, or use `'app'@'%'` restricted at the firewall level
ERROR 1142 (42000): SELECT command denied to user	The user was not granted SELECT on a specific table or database	Check `SHOW GRANTS FOR 'user'@'host';` and grant the missing privilege
ERROR 1396 (HY000): Operation CREATE USER failed	A user with that name and host already exists	Use `CREATE USER IF NOT EXISTS` or DROP USER first

The '%' Host Mask — a Common Configuration Mistake

A privilege for 'user'@'%' allows connecting from any IP address if the MySQL server listens for external connections (bind-address not restricted to 127.0.0.1). For a web application user that always runs on the same server as the database, the only correct choice is 'user'@'localhost', not '%': this prevents a connection to the database even if the password leaks to an external IP, in case port 3306 is ever accidentally exposed. Confirming the port is closed externally does not replace a correctly configured user host mask — these are two independent layers of defense.

When GRANT ALL Is Not a Solution but a Problem

GRANT ALL PRIVILEGES ON *.* WITH GRANT OPTION only makes sense for an administrative account used by a human, not for an application's service account. An application that only reads and writes data does not need DROP, SHUTDOWN, or CREATE USER privileges — in case of a SQL injection or a leaked password from .env, the attacker gets exactly the privilege set granted to that user. A minimal set for a typical web application is SELECT, INSERT, UPDATE, DELETE, plus CREATE TEMPORARY TABLES and LOCK TABLES if an ORM needs them.

Verifying Actual Privileges After Setup

# Full list of the user's privileges as the server sees them
mysql -u root -p -e "SHOW GRANTS FOR 'app_user'@'localhost';"

# Verify the user genuinely cannot see other databases
mysql -u app_user -p -e "SHOW DATABASES;"

# Find users with dangerously broad privileges
mysql -u root -p -e "
SELECT User, Host, Super_priv, Grant_priv, Shutdown_priv
FROM mysql.user
WHERE Super_priv = 'Y' OR Grant_priv = 'Y';"

Run the last query periodically as an audit: if an application's service user shows up in the list instead of just the administrative account, that is a reason to review its privileges under the principle of least privilege. Recovering access after a lost password is covered in the article on resetting the root password.