by
SQL Tutorial › Security › Users & Roles

MySQL Users and Roles

IntermediateLesson 66 of 6910 min read4 queries run on MySQL 8.4

Every connection to MySQL logs in as an account, and each account has its own privileges. Applications, testers, analysts and administrators should never share the root account. This lesson creates accounts, sets passwords, groups privileges into roles and shows how to inspect and remove accounts.

Advertisement
In this lesson
  • Create accounts with CREATE USER and understand user@host
  • Change passwords, lock and drop accounts
  • Bundle privileges into roles
  • Design accounts with least privilege

Accounts are user@host

A MySQL account is a user name plus the host it connects from: 'app'@'localhost' and 'app'@'%' are two different accounts.

Host partMeaning
'localhost'only from the database server itself
'10.0.5.%'any machine in that network
'%'from anywhere (use with care)

CREATE USER

MySQL
CREATE USER 'report_reader'@'localhost' IDENTIFIED BY 'Rep0rt#2026';CREATE USER 'shop_app'@'10.0.5.%' IDENTIFIED BY 'Sh0p#App2026';SELECT user, host, account_locked FROM mysql.userWHERE user IN ('report_reader', 'shop_app');
ResultMySQL 8.4
mysql> CREATE USER 'report_reader'@'localhost' IDENTIFIED BY 'Rep0rt
Query OK, 0 rows affected
mysql> CREATE USER 'shop_app'@'10.0.5.%' IDENTIFIED BY 'Sh0p
Query OK, 0 rows affected
mysql> SELECT user, host, account_locked FROM mysql.user
userhostaccount_locked
shop_app10.0.5.%N
report_readerlocalhostN
2 rows in set
How this query works, step by step
  1. Each CREATE USER creates an account with a password. New accounts can log in but have no privileges on any data yet.
  2. mysql.user is the system table that lists accounts.
Watch out

The passwords in this lesson are examples. Use long, unique passwords stored in a secrets manager, and never put real passwords in scripts or source code.

A new account sees only its own grant of USAGE (meaning “no privileges”):

MySQL
CREATE USER 'report_reader'@'localhost' IDENTIFIED BY 'Rep0rt#2026';SHOW GRANTS FOR 'report_reader'@'localhost';
ResultMySQL 8.4
mysql> CREATE USER 'report_reader'@'localhost' IDENTIFIED BY 'Rep0rt
Query OK, 0 rows affected
mysql> SHOW GRANTS FOR 'report_reader'@'localhost'
Grants for report_reader@localhost
GRANT USAGE ON *.* TO `report_reader`@`localhost`
1 row in set

Change, lock and drop

MySQL
CREATE USER 'intern'@'localhost' IDENTIFIED BY 'Int3rn#2026';ALTER USER 'intern'@'localhost' IDENTIFIED BY 'N3w#Passw0rd';ALTER USER 'intern'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;ALTER USER 'intern'@'localhost' ACCOUNT LOCK;SELECT user, host, account_locked, password_lifetime FROM mysql.user WHERE user = 'intern';DROP USER 'intern'@'localhost';
ResultMySQL 8.4
mysql> CREATE USER 'intern'@'localhost' IDENTIFIED BY 'Int3rn
Query OK, 0 rows affected
mysql> ALTER USER 'intern'@'localhost' IDENTIFIED BY 'N3w
Query OK, 0 rows affected
mysql> ALTER USER 'intern'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY
Query OK, 0 rows affected
mysql> ALTER USER 'intern'@'localhost' ACCOUNT LOCK
Query OK, 0 rows affected
mysql> SELECT user, host, account_locked, password_lifetime FROM mysql.user W…
userhostaccount_lockedpassword_lifetime
internlocalhostY90
1 row in set
mysql> DROP USER 'intern'@'localhost'
Query OK, 0 rows affected
How this query works, step by step
  1. ALTER USER ... IDENTIFIED BY changes the password.
  2. PASSWORD EXPIRE INTERVAL 90 DAY forces a change every 90 days.
  3. ACCOUNT LOCK blocks logins without deleting the account (use ACCOUNT UNLOCK to allow them again).
  4. DROP USER removes the account and its privileges.

Roles

A role is a named bundle of privileges. Grant privileges to the role once, then grant the role to people:

MySQL
CREATE ROLE 'analyst', 'developer';GRANT SELECT ON company.* TO 'analyst';GRANT SELECT, INSERT, UPDATE, DELETE ON company.* TO 'developer'; CREATE USER 'priya'@'localhost' IDENTIFIED BY 'Pr1ya#2026';GRANT 'analyst' TO 'priya'@'localhost';SET DEFAULT ROLE 'analyst' TO 'priya'@'localhost'; SHOW GRANTS FOR 'priya'@'localhost' USING 'analyst';
ResultMySQL 8.4
mysql> CREATE ROLE 'analyst', 'developer'
Query OK, 0 rows affected
mysql> GRANT SELECT ON company.* TO 'analyst'
Query OK, 0 rows affected
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON company.* TO 'developer'
Query OK, 0 rows affected
mysql> CREATE USER 'priya'@'localhost' IDENTIFIED BY 'Pr1ya
Query OK, 0 rows affected
mysql> GRANT 'analyst' TO 'priya'@'localhost'
Query OK, 0 rows affected
mysql> SET DEFAULT ROLE 'analyst' TO 'priya'@'localhost'
Query OK, 0 rows affected
mysql> SHOW GRANTS FOR 'priya'@'localhost' USING 'analyst'
Grants for priya@localhost
GRANT USAGE ON *.* TO `priya`@`localhost`
GRANT SELECT ON `company`.* TO `priya`@`localhost`
GRANT `analyst`@`%` TO `priya`@`localhost`
3 rows in set
How this query works, step by step
  1. CREATE ROLE creates two roles; they are locked accounts that nobody logs in as.
  2. Privileges are granted to the roles (see GRANT).
  3. Priya receives the analyst role. SET DEFAULT ROLE makes it active automatically when she logs in; otherwise she would have to run SET ROLE 'analyst';.
  4. SHOW GRANTS ... USING 'analyst' shows what she can actually do through the role.

When Priya joins the development team, one statement changes her access: GRANT 'developer' TO 'priya'@'localhost'; and later REVOKE 'analyst' FROM 'priya'@'localhost';.

Least privilege

AccountShould have
Web applicationSELECT, INSERT, UPDATE, DELETE on its own database only
Reporting / BI toolSELECT only, ideally on views
Test automationfull rights on a test database, nothing on production
Migrations / deploysCREATE, ALTER, INDEX, DROP on the app database
DBAadministrative privileges, with strong authentication

Never connect applications as root. If an application with only SELECT is attacked through SQL injection, the attacker cannot drop tables.

Quick quiz

1. Are ‘app’@’localhost’ and ‘app’@’%’ the same account?

2. What privileges does a new user have right after CREATE USER?

3. Why use roles?

Users and roles FAQ

How do I create a user in MySQL?

CREATE USER 'name'@'host' IDENTIFIED BY 'password'; then grant privileges with GRANT.

How do I see all users in MySQL?

SELECT user, host FROM mysql.user;

What is a role in MySQL?

A named collection of privileges (MySQL 8.0+). Grant privileges to the role and the role to users; activate it with SET DEFAULT ROLE or SET ROLE.

How do I change a MySQL user’s password?

ALTER USER 'name'@'host' IDENTIFIED BY 'new password';