👤 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;