Skip to content
Tutorials Center
My learning

Topics

Lesson 59 of 75 ยท Security and Administration

Users, Privileges and Roles

Learn MySQL account management: CREATE USER, host patterns, GRANT and REVOKE, privilege levels, roles, caching_sha2_password, password policies and least privilege.

Every connection to MySQL is made by an account, and every account has a list of things it is allowed to do. Getting this right is the cheapest security work you can do: an application that can only read and write its own tables cannot drop the database if someone finds a bug in it. Getting it wrong, for example by running a web app as root, turns every small flaw into a total compromise.

This lesson covers how MySQL names accounts (user plus host), CREATE USER and authentication, GRANT and REVOKE at each level, roles, password policies and the principle of least privilege. Account management is a server feature, so the SQL here is read-only; two small SQL models on a grants table let you practice the logic of "who may do what" in the in-browser engine.

An account is user plus host

User name The name given at login.
Host pattern Where the connection may come from.
One identity 'app'@'10.0.%' and 'app'@'localhost' differ.
1 / 3

Accounts: user and host

A MySQL account is written 'user'@'host'. The host part limits where the connection may come from, and it is a real part of the identity: 'app'@'localhost' and 'app'@'%' are two different accounts, with separate passwords and separate privileges.

Host value Matches
'localhost' connections through the local socket or loopback
'10.0.1.25' exactly that IP address
'10.0.1.%' any address that starts with 10.0.1. (wildcards % and _)
'192.168.0.0/255.255.0.0' an address range given as a netmask
'%' any host (avoid in production when you can name the network)

When several accounts could match, MySQL picks the most specific host pattern, so a named host beats a wildcard. If you create only 'app'@'localhost' and your application connects from another machine, you will get an access-denied error even with the right password, because that account does not match. List the accounts with:

SQL
SELECT user, host, plugin, account_locked, password_expired
FROM mysql.user
ORDER BY user, host;

CREATE USER and authentication

SQL
CREATE USER 'shop_app'@'10.0.1.%'
  IDENTIFIED BY 'a-long-random-secret'
  PASSWORD EXPIRE NEVER
  FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1
  COMMENT 'Web application, reads and writes the shop schema';

MySQL 8 uses the caching_sha2_password authentication plugin by default. It stores a salted SHA-256 based hash and caches successful verifications in memory, so repeated logins are fast. Older clients and drivers that only know the previous default, mysql_native_password, fail with errors such as "authentication plugin cannot be loaded". The right fix is to upgrade the driver. Only if you cannot, you can create the account with IDENTIFIED WITH mysql_native_password BY '...'; that plugin is deprecated and is disabled by default in MySQL 8.4 and removed from 9.0.

Connections using caching_sha2_password need either TLS or an RSA key exchange for the first login, which is another reason to enable TLS (Securing MySQL).

Other useful options:

  • ACCOUNT LOCK creates the account locked, and ALTER USER ... ACCOUNT UNLOCK opens it later.
  • REQUIRE SSL (or REQUIRE X509) insists that the account connects over TLS.
  • WITH MAX_USER_CONNECTIONS 20 caps parallel connections for the account.
  • COMMENT or ATTRIBUTE record who owns the account, so you know later why it exists.

Change a password, rename or drop an account with:

SQL
ALTER USER 'shop_app'@'10.0.1.%' IDENTIFIED BY 'a-new-long-random-secret';
RENAME USER 'shop_app'@'10.0.1.%' TO 'shop_web'@'10.0.1.%';
DROP USER 'old_report'@'localhost';

SET PASSWORD FOR 'shop_app'@'10.0.1.%' = 'a-new-long-random-secret'; does the same job in older syntax, and SET PASSWORD = 'new-secret'; changes the password of the account you are logged in as. ALTER USER is the form the MySQL manual recommends. To see who you are, run SELECT USER(), CURRENT_USER();: USER() is the account name and host the client connected as, CURRENT_USER() is the account the server matched for privilege checks (they differ when a wildcard or anonymous account matched), and DATABASE() returns the default database.

Passwords sent with IDENTIFIED BY appear in the statement text, so avoid pasting real secrets into scripts that end up in shell history or logs. Where the client supports it, pass them through a protected option file or a secrets manager.

GRANT and REVOKE

GRANT gives privileges, and REVOKE takes them back. A privilege can be given at several levels:

SQL
-- global: the whole server (administrative accounts only)
GRANT PROCESS, RELOAD ON *.* TO 'ops'@'localhost';

-- one database
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'10.0.1.%';

-- one table
GRANT SELECT ON shop.orders TO 'report'@'%';

-- selected columns
GRANT SELECT (id, name, department), UPDATE (department) ON hr.employees TO 'helpdesk'@'%';

-- a stored procedure
GRANT EXECUTE ON PROCEDURE shop.refund_order TO 'support'@'%';

Because MySQL 8 no longer creates an account implicitly, you must run CREATE USER before GRANT. After a GRANT, the privilege change takes effect for new statements without any reload; only direct edits of the mysql.* tables would need FLUSH PRIVILEGES, and you should not edit those tables by hand.

Privileges are cumulative: an account holds the union of all its grants, and there is no "deny" rule. To remove something you REVOKE the matching grant, with the same level:

SQL
REVOKE DELETE ON shop.* FROM 'shop_app'@'10.0.1.%';

Revoking at a lower level does not carve out an exception from a grant at a higher level. If you granted SELECT ON *.*, revoking SELECT ON shop.secrets is an error, because that privilege was never granted at the table level. Grant narrowly from the start.

Some privileges deserve a warning:

Privilege Why be careful
ALL PRIVILEGES everything at that level, including DROP and ALTER
GRANT OPTION lets the account pass its privileges to others
SUPER (deprecated, split into dynamic privileges) can bypass many safeguards
FILE read and write files on the server host
PROCESS see every connection's running query text
CREATE USER create further accounts

To inspect what an account can do:

SQL
SHOW GRANTS FOR 'shop_app'@'10.0.1.%';
SHOW GRANTS;                       -- for the current account
SELECT CURRENT_USER(), USER();     -- which account matched, and who you said you were

CURRENT_USER() shows the account that actually matched your login (after host-pattern resolution), while USER() shows the name and host you connected with. They differ whenever a wildcard account was matched.

The next model stores grants in a plain table and answers "may this account do this?" with a query. It is a teaching stand-in: real MySQL checks happen inside the server, in the order global, database, table, column.

SQL Model: a grants table and the 'is it allowed?' check

Press Run (or Ctrl + Enter) to see the result here.

Four rows show the pattern: shop_app may SELECT anywhere in the shop database but may not DELETE; report can read shop.orders but not shop.customers; and the global PROCESS grant for ops matches every target. Anything not granted is denied, which is MySQL's default posture.

Roles

Granting the same ten privileges to forty accounts is tedious and error-prone. A role is a named bundle of privileges that you grant to accounts, available since MySQL 8.0:

SQL
CREATE ROLE 'shop_read', 'shop_write';

GRANT SELECT ON shop.* TO 'shop_read';
GRANT INSERT, UPDATE, DELETE ON shop.* TO 'shop_write';

GRANT 'shop_read' TO 'anna'@'%', 'ben'@'%';
GRANT 'shop_read', 'shop_write' TO 'shop_app'@'10.0.1.%';

A role is itself an account without login ability (written 'shop_read'@'%' internally). One important detail: granting a role does not activate it automatically. Each session must have the role turned on, or you set a default:

SQL
SET DEFAULT ROLE ALL TO 'anna'@'%', 'ben'@'%', 'shop_app'@'10.0.1.%';

-- or for the current session only
SET ROLE 'shop_read';
SELECT CURRENT_ROLE();

You can also enable activate_all_roles_on_login = ON to switch on every granted role at login. A frequent confusion is a user who has a role but "no privileges": the role was never activated. Changing a role's privileges affects everybody who holds it immediately, which is the point of roles. Use SHOW GRANTS FOR 'anna'@'%' USING 'shop_read' to see the effective privileges.

Here is a small model of roles as a join: users point to roles, roles point to privileges, and the effective privilege list is the union.

SQL Model: effective privileges through roles

Press Run (or Ctrl + Enter) to see the result here.

anna ends up with only SELECT, while shop_app collects all four privileges from its two roles. (The order inside each list may differ in MySQL's own GROUP_CONCAT; here it simply follows the join.) Add a privilege to shop_read and both users gain it at once.

Password policies

MySQL can enforce password quality and lifecycle:

  • The validate_password component (install it with INSTALL COMPONENT 'file://component_validate_password') checks length, mixed case, digits and special characters. Its policy levels are LOW, MEDIUM and STRONG (the last also checks a dictionary file).
  • default_password_lifetime or PASSWORD EXPIRE INTERVAL 90 DAY forces periodic changes. An expired password lets the user log in but only to change it.
  • PASSWORD HISTORY 5 and PASSWORD REUSE INTERVAL 365 DAY stop password recycling.
  • FAILED_LOGIN_ATTEMPTS with PASSWORD_LOCK_TIME slows guessing attacks by temporarily locking the account.
  • PASSWORD REQUIRE CURRENT asks for the old password when changing it.

Service accounts that applications use usually get PASSWORD EXPIRE NEVER, because an expired application password causes an outage, and instead rely on strong random secrets and rotation through your deployment process.

Least privilege in practice

  • One account per application and environment, with a name that says what it is. Never share an account between services; you could not tell their traffic apart or revoke one safely.
  • Grant on the schema, not on *.*. A web application usually needs SELECT, INSERT, UPDATE, DELETE only. Migrations run under a separate account that also has CREATE, ALTER, DROP and INDEX, and only during deploys.
  • Separate read-only accounts for reporting and read replicas.
  • Name the host. Use the narrowest host pattern that works, and block direct internet access to the database port (Securing MySQL).
  • No anonymous or empty-password accounts, and a locked or tightly restricted root.
  • Review regularly. Query mysql.user and SHOW GRANTS for every account, drop what nobody uses, and revoke excess privileges. Views and stored routines using SQL SECURITY DEFINER run with the definer's rights, so check those too (Views).
  • Audit changes. Account changes show in the binary log and can be captured by audit tools.

What ran live here

Only two SQL models ran in the in-browser engine (a grants table with an "allowed?" query, and a role-to-privilege join). CREATE USER, GRANT, REVOKE, roles, authentication plugins and password policies are MySQL server features and appear only as read-only code.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why can the account 'app'@'localhost' fail to log in from a web server on another machine?

    An account is user plus host. 'app'@'localhost' matches only local connections, so a remote connection needs an account whose host pattern includes it.
  2. 2. You granted a role to a user, but the user's session shows no privileges. What is the most likely cause?

    Granting a role does not switch it on. Use SET DEFAULT ROLE (or activate_all_roles_on_login, or SET ROLE) so the session has the role's privileges.
  3. 3. Which grant best follows least privilege for a typical web application?

    The application needs data access in its own schema from its own network, nothing more. The others hand out far more power than the app needs.
  4. 4. What is the default authentication plugin in MySQL 8?

    caching_sha2_password is the default since 8.0. Old drivers that only support mysql_native_password should be upgraded rather than the plugin downgraded.

Summary

  • An account is 'user'@'host'; the host pattern is part of its identity.
  • CREATE USER sets the password, plugin (caching_sha2_password by default), TLS requirement, locking and limits.
  • GRANT and REVOKE work at global, database, table, column and routine levels; privileges only add up, and there is no deny.
  • Roles bundle privileges; grant them, then activate them with SET DEFAULT ROLE.
  • Password policies cover quality, expiry, history and lockout after failed logins.
  • Give each application its own account with only the privileges and hosts it needs, and review grants regularly.

Next: Securing MySQL.