How to show and manage MySQL users

MySQL user management is one of those tasks that is straightforward once you know the commands but confusing the first time because MySQL’s user model has a quirk that most other systems do not: a user is defined by both a username and a host. wp_user@localhost and wp_user@10.0.1.50 are two completely different users with separate passwords and separate privileges. Understanding this host component is the key to everything in this guide.

This is a practical reference for listing users, checking privileges, creating users, changing passwords, and managing access. Each section is self-contained so you can jump to the operation you need.

Listing all MySQL users#

Basic user list

SELECT User, Host FROM mysql.user;

Output:

+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| root             | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| wp_user          | localhost |
| wp_user          | 127.0.0.1 |
| shop_user        | localhost |
+------------------+-----------+

The mysql.user table is the authoritative source of all MySQL users. The system accounts ( mysql.infoschema , mysql.session , mysql.sys ) are internal to MySQL and should not be modified.

Notice that wp_user appears twice – once for localhost and once for 127.0.0.1 . These are separate users. On most systems, connecting via localhost uses a Unix socket, while 127.0.0.1 uses TCP. Some applications use one, some use the other, so it is common to create both.

User list with authentication details

SELECT User, Host, plugin, authentication_string
FROM mysql.user
WHERE User NOT LIKE 'mysql.%';

This excludes system accounts and shows which authentication plugin each user uses. On MySQL 8.0+, the default plugin is caching_sha2_password . Older installations may use mysql_native_password .

Count users

SELECT COUNT(*) AS total_users FROM mysql.user WHERE User NOT LIKE 'mysql.%';

Filter by pattern

-- All users starting with "wp"
SELECT User, Host FROM mysql.user WHERE User LIKE 'wp%';

-- All users that can connect from any host
SELECT User, Host FROM mysql.user WHERE Host = '%';

-- All non-root, non-system users
SELECT User, Host FROM mysql.user
WHERE User NOT IN ('root') AND User NOT LIKE 'mysql.%';

Checking user privileges#

SHOW GRANTS

View the privileges for a specific user:

SHOW GRANTS FOR 'wp_user'@'localhost';

Output:

+----------------------------------------------------------------+
| Grants for wp_user@localhost                                   |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `wp_user`@`localhost`                    |
| GRANT ALL PRIVILEGES ON `wordpress_db`.* TO `wp_user`@`localhost` |
+----------------------------------------------------------------+

Reading the output:

• GRANT USAGE ON *.*  means the user can connect to MySQL but has no global privileges. Every user has this.  USAGE  essentially means “no privileges.”
• GRANT ALL PRIVILEGES ON wordpress_db.*  means full access to every table in the  wordpress_db  database.

Check grants for the current user

SHOW GRANTS;

or:

SHOW GRANTS FOR CURRENT_USER();

List all users with their database access

SELECT grantee, table_schema, privilege_type
FROM information_schema.SCHEMA_PRIVILEGES
ORDER BY grantee, table_schema;

This shows which users have access to which databases and what type of access they have. Useful for auditing permissions across the server.

Check if a user has a specific privilege

SELECT *
FROM information_schema.USER_PRIVILEGES
WHERE GRANTEE = "'wp_user'@'localhost'";

Creating users#

Basic user creation

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'strong_password_here';

This creates a user who can connect from localhost only, with no privileges on any database. You must grant privileges separately.

Create user with specific authentication plugin

MySQL 8.0+ defaults to caching_sha2_password , which some older PHP versions and MySQL client libraries do not support. If you get authentication errors when connecting from WordPress or other PHP applications, create the user with the older plugin:

CREATE USER 'new_user'@'localhost'
  IDENTIFIED WITH mysql_native_password BY 'strong_password_here';

This is a compatibility measure, not a security recommendation. caching_sha2_password is more secure. Use mysql_native_password only if the connecting application does not support the newer plugin.

Create user if not exists

CREATE USER IF NOT EXISTS 'new_user'@'localhost' IDENTIFIED BY 'strong_password_here';

Prevents errors in scripts that may run multiple times.

Create user for remote access

CREATE USER 'remote_user'@'10.0.1.50' IDENTIFIED BY 'strong_password_here';

This user can only connect from IP 10.0.1.50 . For a complete guide to setting up remote MySQL access including firewall configuration and SSL requirements, see How to allow remote MySQL connections.

The host component explained

The @'host' part of a MySQL user determines where connections are accepted from:

MySQL evaluates host matches from most specific to least specific. If both wp_user@10.0.1.50 and wp_user@% exist, a connection from 10.0.1.50 matches the more specific entry.

Never use @'%' in production unless the user also requires SSL and you have firewall rules limiting access to port 3306. An @'%' user with a weak password is one of the most common vectors for MySQL compromise.

Granting privileges#

Grant all privileges on a database

GRANT ALL PRIVILEGES ON wordpress_db.* TO 'wp_user'@'localhost';
FLUSH PRIVILEGES;

ALL PRIVILEGES includes: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, REFERENCES, and more. For WordPress, this is the standard grant.

Grant specific privileges

For tighter security, grant only what the application needs:

GRANT SELECT, INSERT, UPDATE, DELETE ON wordpress_db.* TO 'wp_user'@'localhost';
FLUSH PRIVILEGES;

This is sufficient for WordPress during normal operation (reading and writing posts, comments, options). However, WordPress needs CREATE, ALTER, and DROP during plugin installations, theme installations, and core updates (which create and modify tables). If you restrict to only SELECT/INSERT/UPDATE/DELETE, plugin installations will fail.

A practical middle ground for WordPress:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX
  ON wordpress_db.* TO 'wp_user'@'localhost';
FLUSH PRIVILEGES;

This covers all WordPress operations without granting administrative privileges like GRANT OPTION, PROCESS, or FILE.

Grant on a specific table

GRANT SELECT ON wordpress_db.wp_posts TO 'readonly_user'@'localhost';

Useful for reporting tools or read-only API users that should only access specific tables.

Grant with GRANT OPTION

GRANT ALL PRIVILEGES ON wordpress_db.* TO 'admin_user'@'localhost' WITH GRANT OPTION;

WITH GRANT OPTION allows this user to grant their own privileges to other users. Do not give this to application users – only to administrative accounts.

Revoking privileges#

Revoke specific privileges

REVOKE DROP, ALTER ON wordpress_db.* FROM 'wp_user'@'localhost';
FLUSH PRIVILEGES;

Revoke all privileges

REVOKE ALL PRIVILEGES ON wordpress_db.* FROM 'wp_user'@'localhost';
FLUSH PRIVILEGES;

The user still exists and can connect, but has no access to any database. You would typically follow this with new, more appropriate grants.

Verify after revoking

SHOW GRANTS FOR 'wp_user'@'localhost';

Always verify that the revoke worked as expected. MySQL privilege inheritance can be subtle, and global grants take precedence over database-level grants.

Changing passwords#

ALTER USER (MySQL 8.0+ recommended method)

ALTER USER 'wp_user'@'localhost' IDENTIFIED BY 'new_strong_password';
FLUSH PRIVILEGES;

SET PASSWORD (older method, still works)

SET PASSWORD FOR 'wp_user'@'localhost' = 'new_strong_password';

In MySQL 8.0+, SET PASSWORD internally calls ALTER USER .

Change your own password

ALTER USER CURRENT_USER() IDENTIFIED BY 'new_strong_password';

Force password expiration

ALTER USER 'wp_user'@'localhost' PASSWORD EXPIRE;

The user can still connect but must change their password before running any queries. Useful for forcing a password rotation.

After changing a MySQL password

If you change the password for a user that WordPress connects with, update wp-config.php immediately:

define('DB_PASSWORD', 'new_strong_password');

Forgetting this step results in “Error establishing a database connection” – see Error establishing a database connection in WordPress: how to fix it.

Renaming users#

RENAME USER 'old_name'@'localhost' TO 'new_name'@'localhost';

This changes the username while preserving all privileges. You can also change the host:

RENAME USER 'wp_user'@'localhost' TO 'wp_user'@'10.0.1.50';

This effectively moves the user from local-only to remote access from a specific IP. Privileges are preserved.

Deleting users#

DROP USER

DROP USER 'wp_user'@'localhost';

This removes the user and all their privileges. The user can no longer connect. Databases and tables the user created are not affected – only the user account is removed.

Drop with IF EXISTS

DROP USER IF EXISTS 'wp_user'@'localhost';

Drop both localhost entries

If you created a user for both localhost and 127.0.0.1 (common practice), drop both:

DROP USER IF EXISTS 'wp_user'@'localhost';
DROP USER IF EXISTS 'wp_user'@'127.0.0.1';

Before dropping a user

Check if any application is using the credentials. Dropping a user that WordPress connects with immediately causes “Error establishing a database connection” for every visitor. Always update or remove the application configuration first.

Locking and unlocking accounts#

MySQL 8.0+ supports account locking, which prevents login without deleting the user:

-- Lock (prevent connections)
ALTER USER 'wp_user'@'localhost' ACCOUNT LOCK;

-- Unlock (allow connections again)
ALTER USER 'wp_user'@'localhost' ACCOUNT UNLOCK;

Locking is useful when you need to temporarily disable access without losing the user’s privilege configuration.

Checking connected users#

Currently connected users

SELECT User, Host, db, Command, Time
FROM information_schema.PROCESSLIST
WHERE User NOT IN ('system user', 'event_scheduler');

or the shorthand:

SHOW PROCESSLIST;

This shows who is connected right now, which database they are using, what command they are running, and how long they have been running it. Useful for identifying connection leaks or long-running queries.

Kill a connection

If a connection is stuck:

KILL connection_id;

The connection ID is in the Id column of SHOW PROCESSLIST .

Security best practices for MySQL users#

Principle of least privilege

Give each user only the privileges they need. A WordPress user does not need PROCESS (server status), FILE (read/write files on the server), or SUPER (administrative operations). These privileges can be exploited if the credentials are compromised.

One user per application

Do not share MySQL users between different WordPress installations or different applications. If one application is compromised, the attacker only has access to that application’s database, not every database on the server.

No anonymous users

Check for and remove anonymous users (users with an empty username):

SELECT User, Host FROM mysql.user WHERE User = '';

If any exist:

DROP USER ''@'localhost';

Anonymous users are created by some MySQL installation scripts and allow anyone to connect without credentials. The mysql_secure_installation script removes them.

Regular audits

Periodically check for users with excessive privileges:

-- Users with global ALL PRIVILEGES (dangerous)
SELECT User, Host FROM mysql.user WHERE Super_priv = 'Y' AND User != 'root';

-- Users who can connect from any host
SELECT User, Host FROM mysql.user WHERE Host = '%';

-- Users with GRANT OPTION
SELECT User, Host FROM mysql.user WHERE Grant_priv = 'Y' AND User != 'root';

How Hostney handles MySQL users#

On Hostney, MySQL users are managed through the control panel under Hosting > MySQL Users.

Local users. The system creates each user with access from both localhost and 127.0.0.1 , covering both Unix socket and TCP connections. Usernames are auto-generated with a unique pattern to prevent collisions across accounts on the same server.

Passwords. Passwords are auto-generated with a minimum of 16 characters using alphanumeric characters. You can change the password at any time through the control panel, and the new password takes effect immediately.

Access management. You assign users to databases through Hosting > MySQL Access. This is a many-to-many relationship: one user can access multiple databases, and one database can be accessed by multiple users. Each assignment creates the appropriate GRANT statements on the MySQL server.

External users. For remote database access, the control panel provides a separate section under Hosting > MySQL External Users. External users require SSL for all connections and are restricted to specific IP addresses that you configure. The system automatically manages firewall rules to allow MySQL connections from those IPs. See How to allow remote MySQL connections for the complete setup.

Per-account isolation. Each hosting account’s users are isolated from other accounts. You cannot see or manage users belonging to other accounts, even on the same MySQL server. This isolation is enforced at both the application level (the API scopes by customer ID) and the MySQL privilege level.

Size-based permission changes. When a database exceeds its size limit, the system automatically changes user privileges on that database from ALL PRIVILEGES to SELECT and DELETE only. This lets the site continue reading existing data and lets you delete data to free up space, but prevents new inserts until the database is back under its limit.

Summary#

MySQL users are defined by a username and host combination. List users with SELECT User, Host FROM mysql.user , check privileges with SHOW GRANTS FOR , create with CREATE USER , grant access with GRANT , and remove with DROP USER . Always specify the host when creating or managing users, and never use @'%' in production without additional security measures.

The most common mistake is creating a user and forgetting to grant privileges, or granting privileges on the wrong host. Always verify with SHOW GRANTS after any change. For creating the databases these users access, see How to create a database in MySQL. For listing and inspecting those databases, see How to show databases in MySQL.