MySQL Create / Drop / Show Users – Manage Database Access Accounts

Introduction – Why Manage MySQL Users?

In MySQL, users control who can access what within the database. Managing users correctly is crucial for:

• Securing your data
• Assigning appropriate roles and privileges
• Supporting multi-user access with custom permissions

Whether you’re running a production system, test environment, or development stack, managing users ensures only authorized people or applications can access your database.

In this guide, you’ll learn:

• How to create, show, and delete MySQL users
• Syntax and examples for secure user management
• Best practices for user-based security

CREATE USER – Add a New Account

Syntax

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

• 'username': The account name
• 'host': The origin of connection ('localhost', '%', or IP)
• 'password': Password for authentication

Example

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongP@ssw0rd!';

Creates a user app_user that can only connect from the local server.

Create a User for Remote Access

CREATE USER 'reporting'@'%' IDENTIFIED BY 'ViewOnly123!';

Allows login from any host (use cautiously in production environments).

👁️ SHOW USERS – List Existing Accounts

MySQL does not have a direct SHOW USERS command, but you can query the internal mysql.user table:

SELECT user, host FROM mysql.user;

Example Output

Use this to audit all created users and their connection scopes.

DROP USER – Remove an Account

Syntax

DROP USER 'username'@'host';

Example

DROP USER 'reporting'@'%';

Completely removes the user and all their privileges.

Additional Account Management

Rename User

RENAME USER 'old_user'@'localhost' TO 'new_user'@'localhost';

Change Password

ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'NewStrongerP@ss!';

Use this for password rotation or recovery.

Best Practices

Real-World Use Cases

Summary – Recap & Next Steps

Creating, listing, and deleting users is essential to secure, scalable, and maintainable MySQL operations. It allows for customized access control and clear accountability.

Key Takeaways

• Use CREATE USER to add new accounts with defined host and password
• Use DROP USER to delete unused or outdated accounts
• Use SELECT FROM mysql.user to list existing users
• Always apply the principle of least privilege

Real-World Relevance

User management is central in multi-team development, DevOps automation, cloud database security, and enterprise data governance.

FAQ – MySQL User Commands

How do I list all users in MySQL?

SELECT user, host FROM mysql.user;

What does '%' mean in 'username'@'%'?

It allows the user to connect from any host or IP address.

Can I reuse the same username for different hosts?

Yes. 'admin'@'localhost' and 'admin'@'%' are distinct users.

How do I change a user’s password?

ALTER USER 'username'@'host' IDENTIFIED BY 'NewPass123!';

How do I prevent remote root access?

UPDATE mysql.user SET host = 'localhost' WHERE user = 'root';
FLUSH PRIVILEGES;