Himanshu SharmaSoftware engineer
Let’s talk

MySQL · Lesson 47 of 50

Users and privileges

Create MySQL accounts, grant only the privileges they need, use roles, review access with SHOW GRANTS and change or remove users, using least privilege.

15 minAdvanced and practicalUpdated 22 October 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonCreate a limited MySQL user for an application, grant and revoke privileges, and explain least privilege.

Who can do what

A database often holds the most sensitive data in a business. MySQL protects it with accounts and privileges. An account answers "who are you?" (name, host and password), and privileges answer "what are you allowed to do?".

Only practise this lesson on your own learning server. Changing accounts on a shared or production server is an administrator's job and a mistake can lock people out.

Accounts are user@host

MySQL identifies an account by both a name and the host it connects from: 'app'@'localhost' is only valid from the same machine, while 'app'@'10.0.0.%' allows a private network and 'app'@'%' any host. The same name with a different host is a different account, with its own password and privileges.

Creating a user

Create a user for a web application that will work with the shop database:

sql
DROP USER IF EXISTS 'shop_app'@'localhost';
CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'Choose-A-Strong-Password-1!';

Use a long random password in real life, such as one from the Password Generator, and keep it in an environment variable or a secrets manager, never in source control. By default MySQL 8 uses the caching_sha2_password method to protect it. A brand-new account can connect but has almost no rights:

sql
SHOW GRANTS FOR 'shop_app'@'localhost';
result
+----------------------------------------------+
| Grants for shop_app@localhost                |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `shop_app`@`localhost` |
+----------------------------------------------+

Granting privileges

GRANT privilege_list ON scope TO account. Scope can be *.* (everything), shop.* (one database), shop.orders (one table), or even specific columns.

Give the application what it needs to read and write shop data, but not to change its structure:

sql
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'localhost';
SHOW GRANTS FOR 'shop_app'@'localhost';
result
+----------------------------------------------------------------------------+
| Grants for shop_app@localhost                                              |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `shop_app`@`localhost`                               |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `shop`.* TO `shop_app`@`localhost` |
+----------------------------------------------------------------------------+

Common privileges:

Privilege Allows
SELECT, INSERT, UPDATE, DELETE reading and changing rows
CREATE, ALTER, DROP, INDEX changing structure
EXECUTE calling stored routines
CREATE VIEW, TRIGGER creating views and triggers
ALL PRIVILEGES everything on that scope
GRANT OPTION passing privileges to others

You can restrict to table or column level, useful for hiding sensitive columns:

sql
DROP USER IF EXISTS 'support_agent'@'localhost';
CREATE USER 'support_agent'@'localhost' IDENTIFIED BY 'Another-Strong-Password-2!';
GRANT SELECT (id, first_name, last_name, city, country) ON shop.customers TO 'support_agent'@'localhost';
GRANT SELECT ON shop.orders TO 'support_agent'@'localhost';
SHOW GRANTS FOR 'support_agent'@'localhost';
result
+------------------------------------------------------------------------------------------------------------------------+
| Grants for support_agent@localhost                                                                                     |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `support_agent`@`localhost`                                                                      |
| GRANT SELECT (`city`, `country`, `first_name`, `id`, `last_name`) ON `shop`.`customers` TO `support_agent`@`localhost` |
| GRANT SELECT ON `shop`.`orders` TO `support_agent`@`localhost`                                                         |
+------------------------------------------------------------------------------------------------------------------------+

This support account can see customer names and cities, but not email addresses or phone numbers.

Least privilege

The guiding rule is least privilege: give each account only the rights it needs for its job.

  • The application gets SELECT, INSERT, UPDATE, DELETE (and EXECUTE if it calls routines) on its own database only.
  • Migrations and deployment use a separate account that can alter structure, used only when deploying.
  • Reports and analysts get SELECT only, ideally on a read replica.
  • Nobody uses root for routine work.

If an attacker finds an SQL injection hole in an application running as root, they own the server. If it runs as shop_app, they can damage only the shop data, and cannot drop tables or read other databases.

Revoking

REVOKE removes privileges with the same syntax:

sql
REVOKE DELETE ON shop.* FROM 'shop_app'@'localhost';
SHOW GRANTS FOR 'shop_app'@'localhost';
result
+--------------------------------------------------------------------+
| Grants for shop_app@localhost                                      |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `shop_app`@`localhost`                       |
| GRANT SELECT, INSERT, UPDATE ON `shop`.* TO `shop_app`@`localhost` |
+--------------------------------------------------------------------+

Privileges are checked each time a command is run, so a revoke applies at once to a connected user, for most privileges.

Roles

When many users need the same rights, grant them to a role and give people the role:

sql
DROP ROLE IF EXISTS 'shop_reader';
CREATE ROLE 'shop_reader';
GRANT SELECT ON shop.* TO 'shop_reader';
GRANT 'shop_reader' TO 'support_agent'@'localhost';
SHOW GRANTS FOR 'support_agent'@'localhost' USING 'shop_reader';
result
+------------------------------------------------------------------------------------------------------------------------+
| Grants for support_agent@localhost                                                                                     |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `support_agent`@`localhost`                                                                      |
| GRANT SELECT ON `shop`.* TO `support_agent`@`localhost`                                                                |
| GRANT SELECT (`city`, `country`, `first_name`, `id`, `last_name`) ON `shop`.`customers` TO `support_agent`@`localhost` |
| GRANT SELECT ON `shop`.`orders` TO `support_agent`@`localhost`                                                         |
| GRANT `shop_reader`@`%` TO `support_agent`@`localhost`                                                                 |
+------------------------------------------------------------------------------------------------------------------------+

A user must activate their roles (SET ROLE ALL; or by setting a default with SET DEFAULT ROLE). Roles keep permissions manageable as a team grows.

Passwords and removal

sql
ALTER USER 'shop_app'@'localhost' IDENTIFIED BY 'Rotated-Password-3!';
ALTER USER 'shop_app'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

List accounts from the system table, and finally remove the practice users:

sql
SELECT user, host FROM mysql.user WHERE user IN ('shop_app', 'support_agent', 'shop_reader') ORDER BY user;
result
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| shop_app      | localhost |
| shop_reader   | %         |
| support_agent | localhost |
+---------------+-----------+
sql
DROP USER IF EXISTS 'shop_app'@'localhost', 'support_agent'@'localhost', 'shop_reader'@'%';
DROP ROLE IF EXISTS 'shop_reader';

Other security basics

  • Network: bind the server to localhost or a private network, and firewall port 3306. Do not expose MySQL to the internet.
  • Encryption: require TLS for remote connections (REQUIRE SSL on the account).
  • Password hygiene: unique, long, rotated, and stored outside the code.
  • Auditing: know who has GRANT OPTION or ALL PRIVILEGES.
  • Updates: keep MySQL patched.

Common mistakes

  • Using root in application configuration.
  • Using 'user'@'%' for convenience when localhost would do.
  • Granting ALL PRIVILEGES ON *.* "to make it work".
  • Committing database passwords into a repository.
  • Forgetting that GRANT to a user that does not exist fails in MySQL 8, because users must be created first.

Exercise

Create a read-only reporting user reporter on localhost who can read all shop tables, but cannot read the phone and email columns of customers. (Hint: grant column-level privileges on customers, and table-level on the other tables you list.) Show the grants, then remove the user.

Show one possible answer
sql
DROP USER IF EXISTS 'reporter'@'localhost';
CREATE USER 'reporter'@'localhost' IDENTIFIED BY 'Report-Only-Password-4!';
GRANT SELECT ON shop.orders TO 'reporter'@'localhost';
GRANT SELECT ON shop.order_items TO 'reporter'@'localhost';
GRANT SELECT ON shop.products TO 'reporter'@'localhost';
GRANT SELECT ON shop.categories TO 'reporter'@'localhost';
GRANT SELECT (id, first_name, last_name, city, country, signup_date) ON shop.customers TO 'reporter'@'localhost';
SHOW GRANTS FOR 'reporter'@'localhost';
DROP USER 'reporter'@'localhost';
result
+----------------------------------------------------------------------------------------------------------------------------------+
| Grants for reporter@localhost                                                                                                    |
+----------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `reporter`@`localhost`                                                                                     |
| GRANT SELECT ON `shop`.`categories` TO `reporter`@`localhost`                                                                    |
| GRANT SELECT (`city`, `country`, `first_name`, `id`, `last_name`, `signup_date`) ON `shop`.`customers` TO `reporter`@`localhost` |
| GRANT SELECT ON `shop`.`order_items` TO `reporter`@`localhost`                                                                   |
| GRANT SELECT ON `shop`.`orders` TO `reporter`@`localhost`                                                                        |
| GRANT SELECT ON `shop`.`products` TO `reporter`@`localhost`                                                                      |
+----------------------------------------------------------------------------------------------------------------------------------+

Key points

  • A MySQL account is user@host. The same name from a different host is a different account.
  • GRANT gives privileges at a chosen level (server, database, table, column) and REVOKE removes them. SHOW GRANTS lists what an account can do.
  • Follow least privilege: applications get a dedicated user with only the rights they need, never root.
  • Roles bundle privileges so you can grant a group of rights at once. Use strong passwords and keep them out of source control.

Check yourself

1. Why should a web application not connect as root?
2. What does 'app'@'localhost' differ from 'app'@'%' in?
3. Which statement shows what an account is allowed to do?
4. Which privilege set suits a reporting user who only reads shop data?