User Management

Introduction

Every MySQL server starts with an all-powerful root user, capable of doing absolutely anything — creating and dropping databases, changing other users' passwords, even shutting the server down. Using this account for everyday application work is one of the most common and dangerous MySQL mistakes.

This lesson introduces MySQL's user management: creating dedicated accounts with CREATE USER, understanding host restrictions, inspecting a user's access with SHOW GRANTS, and removing accounts with DROP USER.

Why Not Just Use Root?

The root account has unrestricted access to everything on the server: every database, every table, and every administrative command. If an application connects as root and has any kind of security flaw — like a SQL injection vulnerability — an attacker inherits full control of the entire server, not just that one application's data.

The fix is straightforward: create a separate, dedicated MySQL user for each application (or person), and grant it only the specific permissions it actually requires.

Creating a New User

A new user account is created with the CREATE USER statement, which specifies a username, a host, and a password.

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'S3cur3P@ssword!';

At this point, app_user exists and can log in, but it has no permissions on any database yet — it cannot even SELECT from a single table. Permissions are granted separately, which you will cover in detail in the next lesson.

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

+----------+-----------+
| User     | Host      |
+----------+-----------+
| app_user | localhost |
+----------+-----------+

Understanding the Host Part

Every MySQL user is really identified by a username AND a host combined — 'user'@'host'. The host restricts which machines that account is even allowed to connect from, before any permissions are checked.

-- Only usable by an application running on this same server
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'S3cur3P@ssword!';

-- Usable from any machine that can reach this server over the network
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'AnotherP@ssword!';

Because the username and host together form the account's identity, 'app_user'@'localhost' and 'app_user'@'%' are two entirely separate accounts, even though they share the same username.

Viewing a User's Permissions

To see exactly what an existing user is allowed to do, use SHOW GRANTS FOR, specifying the exact user and host.

SHOW GRANTS FOR 'app_user'@'localhost';

+---------------------------------------------------------------+
| Grants for app_user@localhost                                 |
+---------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_user`@`localhost`                  |
+---------------------------------------------------------------+

GRANT USAGE ON *.* essentially means "this user can log in, but has no meaningful privileges yet." Once privileges are granted (covered in the next lesson), they will appear here as additional GRANT lines.

Removing a User

When an account is no longer needed — an employee leaves, or an application is retired — remove it with DROP USER so it can no longer be used to connect.

DROP USER 'app_user'@'localhost';

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

Empty set (0.00 sec)

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

Creating dedicated, appropriately restricted user accounts, instead of relying on root, is one of the most important security habits in working with MySQL.

In this lesson, you learned why root should be avoided for everyday use, how to create and remove users, and how host restrictions control where a user can connect from. Next, you will learn how to grant those users precisely the privileges they need.