How to Manage Users in MySQL

Galaxy Glossary

How do I manage users in MySQL?

`CREATE USER`, `ALTER USER`, `GRANT`, `REVOKE`, and `DROP USER` let DBAs create, modify, secure, and remove MySQL accounts.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why do I need to manage users in MySQL?

Controlling who can read or change data prevents accidental loss, protects customer privacy, and meets compliance rules.

Which statements create and delete MySQL accounts?

Use CREATE USER to add accounts and DROP USER to remove them. Accounts are defined as 'user'@'host'.

How do I grant permissions on specific ecommerce tables?

Use GRANT. Example: GRANT SELECT, INSERT ON ecommerce.Orders TO 'sales_app'@'%'; lets the app read and add orders but not modify products.

How can I change a user's password or authentication method?

ALTER USER modifies credentials. Always combine with REQUIRE SSL or IDENTIFIED BY for stronger security.

What is the best way to remove unwanted privileges?

Run REVOKE followed by FLUSH PRIVILEGES (for older MySQL) to apply changes immediately.

Can I see which rights a user has?

Run SHOW GRANTS FOR 'user'@'host'; to list all active privileges, including database-level and table-level rules.

Best practices for MySQL user management?

Grant least privilege, isolate application roles, rotate passwords, enable SSL, and audit accounts periodically.

Key difference between `GRANT ALL` and `SUPER`?

GRANT ALL applies only to objects inside the specified scope, while SUPER is a global admin right. Avoid giving SUPER to apps.

FAQ

How do I force a password reset?

Run ALTER USER 'temp'@'%' PASSWORD EXPIRE;. The account must set a new password before the next login.

Is it safe to share one app account?

No. Create separate users per service to avoid cascading failures and simplify auditing.

What happens if I forget to specify the host?

MySQL defaults to 'user'@'localhost'. Remote connections will fail until you add a matching host.

Why How to Manage Users in MySQL is important

How to Manage Users in MySQL Example Usage


-- Grant an order-processing microservice the exact permissions it needs
CREATE USER 'order_svc'@'172.16.%' IDENTIFIED BY 'SvcPwd$2024';
GRANT SELECT, INSERT ON ecommerce.Orders TO 'order_svc'@'172.16.%';
GRANT SELECT ON ecommerce.OrderItems TO 'order_svc'@'172.16.%';
-- Now the service can create orders and view line items without touching customer data.

How to Manage Users in MySQL Syntax


-- Create a read-only analytics account
CREATE USER 'analytics'@'%' IDENTIFIED BY 'S3cretPwd!' REQUIRE SSL;
GRANT SELECT ON ecommerce.Customers TO 'analytics'@'%';
GRANT SELECT ON ecommerce.Orders TO 'analytics'@'%';

-- Change password and authentication plugin
ALTER USER 'analytics'@'%' IDENTIFIED WITH mysql_native_password BY 'NewPwd!@#2024';

-- Revoke accidental write rights
REVOKE INSERT, UPDATE, DELETE ON ecommerce.Orders FROM 'analytics'@'%';
FLUSH PRIVILEGES;  -- only needed on MySQL < 5.7

-- Delete an unused service account
DROP USER 'old_bot'@'10.1.2.%';

Common Mistakes

Granting ALL PRIVILEGES on *.* to application accounts. Fix by granting only SELECT, INSERT, or other required rights on specific databases or tables.
Forgetting to include the host part in user names, causing remote logins to fail. Always create 'user'@'%' or specific IPs, not just 'user'.

Frequently Asked Questions (FAQs)

How do I reset a locked account?

Run ALTER USER 'name'@'host' ACCOUNT UNLOCK; and consider adding stronger password rules.

Can I copy privileges from one user to another?

Yes. Use SHOW GRANTS FOR 'source'@'host' and paste the result after replacing the target user.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.