Managing MySQL user accounts
MySQL users are the named accounts stored in the mysql.user system table that control who can connect to a MySQL server and what each account is permitted to do.
On a fresh MySQL 8.0 install there is typically 1 root account and 0 application accounts, and the command that lists every account on the server is SHOW USERS (MySQL 8.0.15 and later) or the query SELECT user, host FROM mysql.user on earlier versions. Everything that follows on this page is the working procedure for listing those accounts, creating new ones, assigning and removing privileges, and verifying the result without ever locking yourself out.
For a broader look at how these accounts sit inside a running production server, see MySQL in practice. If you prefer a graphical interface over the command line, a MySQL Workbench walkthrough covers the same operations through the Schema Browser. When you are deciding which database engine to run in the first place, Choosing between MySQL and PostgreSQL is the comparison to read first. And because the server process runs under a dedicated system account, the OS-level setup described in Linux User Accounts is a prerequisite you should finish before touching any SQL here.
Listing the accounts that already exist
To list the users, connect with an account that has the SELECT privilege on the mysql schema and run one of the following statements. The output below shows a 3-account instance (root, app, and readonly) on a MySQL 8.0.37 server.
SHOW USERS;returns a single column namedUser@Hostwith one row per account.SELECT user, host, plugin FROM mysql.user;returns the same rows plus the authentication plugin (typicallycaching_sha2_passwordon MySQL 8.0 ormysql_native_passwordon MySQL 5.7).SELECT user, host, password FROM mysql.user WHERE host = '%';surfaces every account that is reachable from any network, which is the first query to run during a hardening pass.
The host column matters: '%' means any source address, a specific IP such as 10.0.1.4 restricts the account to that host, and 'localhost' binds it to the local socket only. An account with the same user value but different host values is a distinct grant entry, so a server can hold 2 entries for the name "app" and grant them different privileges.
Creating a new user account
To create a user, issue CREATE USER with the account name, the host scope, and an authentication method. The syntax below is exact for MySQL 8.0 and 5.7.
CREATE USER 'app'@'10.0.1.%' IDENTIFIED BY 'Str0ng!P@ss' REQUIRE SSL; This creates 1 account limited to the 10.0.1.0/24 subnet and forces a TLS connection. If the password policy is MEDIUM (the default), the password must be at least 8 characters and contain 1 uppercase letter, 1 lowercase letter, 1 digit, and 1 special character. MySQL 5.7 defaults the plugin to mysql_native_password; MySQL 8.0 defaults to caching_sha2_password, which stores the hash differently and requires a client library that supports it.
After the account exists but before it can do anything, the server denies every request with error 1045 (Access denied for user). That is by design: creation and privilege assignment are separate steps.
Granting and revoking privileges
To grant or revoke privileges, use GRANT and REVOKE scoped to a specific database and table. The 6 most common privilege groups and what each one permits are shown in the table below.
| Privilege group | What it permits | Typical scope |
|---|---|---|
SELECT, INSERT, UPDATE, DELETE | Read and write rows | Database or table |
CREATE, ALTER, DROP | Define and remove schema objects | Database |
EXECUTE | Run stored procedures and functions | Database |
INDEX | Create and drop indexes | Table |
REFERENCES | Create foreign-key constraints | Database |
RELOAD | Run FLUSH statements | Global |
A typical application grant on a 4-table schema looks like this: GRANT SELECT, INSERT, UPDATE, DELETE ON shopdb.* TO 'app'@'10.0.1.%'; To narrow it later, REVOKE INSERT ON shopdb.audit_log FROM 'app'@'10.0.1.%'; removes write access to that single table while leaving the other 3 tables unaffected. Always follow a REVOKE with FLUSH PRIVILEGES; if the session cache has not yet expired, though on MySQL 8.0 the privilege cache refreshes automatically after the statement commits.
Viewing exactly what an account can do
To view the effective privilege set of a user, run SHOW GRANTS FOR 'app'@'10.0.1.%';. The output lists every GRANT row that applies, from the global level down to individual columns. If the output is empty, the account can authenticate but perform no operation other than SLEEP(). This check is the fastest way to confirm that a REVOKE took effect and that no residual GRANT ALL from an earlier migration is still in force.
On a server with 12 or more application accounts, exporting the full grant map to a file with mysqldump --no-data mysql gives you a 1-file audit trail you can diff between deployments.
Hardening the default accounts
To harden the default accounts, start with the 2 that ship on every install: root@'localhost' and the anonymous entry (an empty user string) that some older 5.7 packages create. The procedure is 3 steps, performed in order:
- Remove the anonymous account:
DROP USER ''@'localhost'; - Lock or delete any account whose host is
'%'that you do not recognise:SELECT user, host FROM mysql.user WHERE host = '%';thenDROP USERfor each unknown row. - Set a 20-character minimum password on the remaining accounts by confirming
validate_password.policy = MEDIUMinmy.cnf(ormy.inion Windows) and restarting the 60-second grace period withSET PERSIST validate_password.policy = 'MEDIUM';
After those 3 steps, the mysql.user table should show no more than the accounts you intentionally created, each bound to a specific IP range rather than a wildcard. Re-run the listing query from the first section and compare the row count to your deployment inventory; a count mismatch is the signal that an account was created outside your change-management process.