Every application that talks to MySQL should log in with its own user, limited to its own database. This guide covers the statements you need — CREATE USER, GRANT, REVOKE, SHOW GRANTS — and the user@host detail that causes most “Access denied” errors. Everything here works on MySQL 8 and MariaDB 10.5+.

Log in as an administrator first:

sudo mysql

Users are user@host pairs

In MySQL, an account is a user name plus the host it connects from. These are three different accounts:

'app'@'localhost'      -- connects via the local socket or 127.0.0.1*
'app'@'10.0.0.5'       -- connects from that IP only
'app'@'%'              -- connects from any host

* Strictly, localhost means the Unix socket; a TCP connection to 127.0.0.1 matches 'app'@'127.0.0.1' or a wildcard — but MySQL tries localhost too when name resolution is enabled. If an app connects over TCP and gets “Access denied” while the CLI works, create the account for 127.0.0.1 as well.

Create a user

CREATE USER 'app'@'localhost' IDENTIFIED BY 'Long-Random-Password-Here';

Generate passwords instead of inventing them:

openssl rand -base64 24

CREATE USER IF NOT EXISTS avoids an error in scripts if the user exists.

Create a database and grant access

CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GRANT ALL PRIVILEGES ON shop.* TO 'app'@'localhost';

shop.* means every table in shop. With MySQL 8 you no longer need FLUSH PRIVILEGES after GRANT — it’s only required if you edit the grant tables directly.

Grant only what the app needs

ALL PRIVILEGES on its own database is common and acceptable for most web apps (they create tables during installation and upgrades). For tighter control:

-- A read-only reporting user
CREATE USER 'report'@'10.0.0.%' IDENTIFIED BY '…';
GRANT SELECT ON shop.* TO 'report'@'10.0.0.%';

-- An app that never changes schema
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app'@'localhost';

-- A backup user for mysqldump
CREATE USER 'backup'@'localhost' IDENTIFIED BY '…';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, PROCESS ON *.* TO 'backup'@'localhost';

Common privileges:

Privilege Allows
SELECT, INSERT, UPDATE, DELETE Read and write rows
CREATE, ALTER, DROP, INDEX Change schema
CREATE TEMPORARY TABLES Temp tables (some apps need this)
EXECUTE Run stored procedures
LOCK TABLES, SHOW VIEW, TRIGGER, EVENT Needed by mysqldump
PROCESS See all running queries; needed for some dumps
RELOAD, SUPER Admin-level — avoid for apps

A full admin account

If you want a password-based admin for tools like phpMyAdmin (Ubuntu’s root uses socket authentication):

CREATE USER 'dba'@'localhost' IDENTIFIED BY '…';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'localhost' WITH GRANT OPTION;

Keep it bound to localhost and reach it through an SSH tunnel.

List users and their privileges

SELECT user, host, plugin FROM mysql.user ORDER BY user;
SHOW GRANTS FOR 'app'@'localhost';
SHOW GRANTS;                         -- for the current user
SELECT CURRENT_USER();               -- which account you matched

CURRENT_USER() is invaluable when debugging: it shows which user@host entry MySQL actually used.

Revoke privileges

REVOKE INSERT, UPDATE, DELETE ON shop.* FROM 'report'@'10.0.0.%';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'olduser'@'%';

Change a password

ALTER USER 'app'@'localhost' IDENTIFIED BY 'New-Long-Password';

Update the app’s config file at the same time. To force a change at next login: ALTER USER … PASSWORD EXPIRE;.

Rename and drop users

RENAME USER 'app'@'localhost' TO 'shop_app'@'localhost';
DROP USER 'olduser'@'%';

Dropping a user doesn’t drop their databases.

Lock an account temporarily

ALTER USER 'app'@'localhost' ACCOUNT LOCK;
ALTER USER 'app'@'localhost' ACCOUNT UNLOCK;

Useful during maintenance or after a suspected leak.

Authentication plugins (MySQL 8)

MySQL 8 defaults to caching_sha2_password. Very old client libraries (old PHP mysqlnd, legacy connectors) may fail with “authentication method unknown”. Upgrade the client first; as a last resort for one account:

ALTER USER 'legacy'@'localhost' IDENTIFIED WITH mysql_native_password BY '…';

Note that mysql_native_password is deprecated and disabled by default in MySQL 8.4 and later.

Least-privilege checklist

  • One database and one user per application.
  • Host restricted to localhost or specific IPs — avoid '%'.
  • No app user with *.* privileges.
  • Separate read-only users for reporting and backups.
  • Passwords generated, stored in a secrets file with chmod 600, rotated after staff changes.

Troubleshooting “Access denied”

  1. SELECT user, host FROM mysql.user WHERE user='app'; — does an entry exist for the host you connect from?
  2. Socket vs TCP: try mysql -u app -p -h 127.0.0.1 and mysql -u app -p (socket).
  3. SHOW GRANTS FOR … — does the account have privileges on that database?
  4. Anonymous users (''@'localhost') can shadow your account — remove them with mysql_secure_installation.

More fixes in MySQL error 1045. New to MySQL? Start with installing MySQL on Ubuntu, and plan for remote access only if another server needs it.

  • MySQL
  • MariaDB
  • Databases