MySQL users and privileges: CREATE USER, GRANT and roles

A MySQL account is not a user name. It is a user name and a host pattern together - 'app'@'%' and 'app'@'localhost' are two different accounts with their own passwords and their own privileges. Once that clicks, most of MySQL's permission system is straightforward: you CREATE USER an account, GRANT it privileges at the global, database, table or column level, group common sets of privileges into roles, and check the result with SHOW GRANTS. The habit worth building is giving each application its own account with rights on its own database and nothing else, and keeping root for administration.

This guide covers MySQL 8.4 LTS. Most of it is identical in 8.0; the differences are flagged where they matter. If you came from MySQL 5.7, the two changes that break old scripts are that GRANT no longer creates users and that the default password plugin is caching_sha2_password.

Accounts are user plus host#

Every account is stored in mysql.user with a User and a Host column. When a client connects, MySQL looks at the address it came from and the name it gave, and picks the single most specific matching row. Privileges come from that row and nothing else.

SELECT user, host, plugin, account_locked, password_last_changedFROM mysql.userORDER BY user, host;

Matching sorts rows by host specificity first - literal addresses and names before patterns, patterns before bare % - and only then by user name. This produces the classic trap: some old installations ship an anonymous account ''@'localhost'. A user connecting as app from localhost matches the anonymous row before 'app'@'%', because the host is more specific, and then has none of the privileges they were granted. SELECT CURRENT_USER(); after logging in tells you which row you actually matched. If it shows @localhost with an empty user, drop the anonymous account.

When the client's address changes - home broadband, a laptop, an app platform with no fixed outbound address - % is the practical choice, and the password and TLS are what protect the account. When you have a fixed address, using it is a cheap extra layer.

Creating users#

CREATE USER 'app'@'%'  IDENTIFIED BY 'a-long-generated-password'  REQUIRE SSL  PASSWORD EXPIRE NEVER;

The account is created with the default authentication plugin, caching_sha2_password. In MySQL 8.4 the default is set by authentication_policy; the old default_authentication_plugin variable was removed. mysql_native_password exists but is disabled unless the server starts with mysql_native_password=ON, so IDENTIFIED WITH mysql_native_password fails on a stock 8.4 server.

MySQL can generate the password for you and print it once:

CREATE USER 'report'@'%' IDENTIFIED BY RANDOM PASSWORD;

+--------+------+----------------------+-------------+| user   | host | generated password   | auth_factor |+--------+------+----------------------+-------------+| report | %    | 7Ba(Xr;lWk.Vx4Tq,Z%3 |           1 |+--------+------+----------------------+-------------+

The length comes from generated_random_password_length (20 by default). Copy it then; it is not stored anywhere you can read back.

Other clauses worth knowing:

• IF NOT EXISTS makes the statement safe to rerun in a provisioning script.
• REQUIRE SSL refuses unencrypted connections for this account. REQUIRE X509 demands a client certificate.
• WITH MAX_USER_CONNECTIONS 20 caps simultaneous sessions for the account, which stops one runaway service from taking every slot under max_connections.
• ACCOUNT LOCK creates the account disabled, useful for a definer account that owns views and routines but should never log in.
• COMMENT 'billing service' stores a note in mysql.user (8.0.21 and later), which you will be glad of in two years.

To change an existing account use ALTER USER; to rename it, RENAME USER 'app'@'%' TO 'shop'@'%'; to remove it, DROP USER IF EXISTS 'app'@'%'. Dropping a user does not drop objects it created, but views and routines whose DEFINER was that user stop working until the definer exists again.

GRANT and REVOKE#

Privileges are granted at a level, and the level is the ON clause:

GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app'@'%';          -- databaseGRANT SELECT ON appdb.orders TO 'report'@'%';                          -- tableGRANT SELECT (id, email, created_at) ON appdb.users TO 'support'@'%';  -- columnsGRANT EXECUTE ON PROCEDURE appdb.close_month TO 'cron'@'%';            -- routineGRANT PROCESS ON *.* TO 'monitor'@'%';                                 -- global

Since MySQL 8.0, GRANT does exactly one thing. The 5.7 habit of GRANT ALL ON db.* TO 'u'@'%' IDENTIFIED BY 'pw' - creating the user and setting a password in one go - is a syntax error now. Create the user first, then grant.

REVOKE mirrors GRANT and must name the same level. Revoking SELECT ON appdb.orders from someone who has SELECT ON appdb.* does nothing, because they never had a table-level grant to revoke. If you need "everything in this database except one table", grant per table, or turn on partial_revokes (off by default), which lets you subtract a database from a global grant - and only a global one.

The privileges an application actually needs

Most frameworks run migrations with the same credentials as the app, so the app ends up with ALL PRIVILEGES ON appdb.*. That is acceptable for a small project - it is still confined to one database - but the stronger setup is two accounts: app with the four data privileges, and app_migrate with the schema privileges, used only by the deploy step. A SQL injection through the app then cannot DROP TABLE.

What an application should never have: anything ON *.*, GRANT OPTION, FILE (reads and writes files on the server's disk), SUPER or its dynamic replacements, PROCESS (sees every session's queries, including other apps'), CREATE USER, or SHUTDOWN.

Dynamic privileges

MySQL 8 split the old catch-all SUPER into dynamic privileges with names like SYSTEM_VARIABLES_ADMIN (change global variables), CONNECTION_ADMIN (kill other users' sessions, connect beyond max_connections), BINLOG_ADMIN, BACKUP_ADMIN and REPLICATION_SLAVE_ADMIN. MySQL 8.4 added FLUSH_PRIVILEGES, OPTIMIZE_LOCAL_TABLE, TRANSACTION_GTID_TAG, and SET_ANY_DEFINER with ALLOW_NONEXISTENT_DEFINER in place of the removed SET_USER_ID. They are granted like any other privilege, always at the global level:

GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO 'dba'@'%';

SUPER still exists but is deprecated. If an old tool insists on it, find out which dynamic privilege it really needs.

Checking what an account can do#

SHOW GRANTS FOR 'app'@'%';

+------------------------------------------------------------------------+| Grants for app@%                                                       |+------------------------------------------------------------------------+| GRANT USAGE ON *.* TO `app`@`%`                                        || GRANT SELECT, INSERT, UPDATE, DELETE ON `appdb`.* TO `app`@`%`         |+------------------------------------------------------------------------+

USAGE means "no privileges" - it is the line every account has, and it is how an account with nothing granted still appears. SHOW GRANTS with no FOR shows your own current session, including roles that are active. SHOW CREATE USER 'app'@'%' prints the account's definition (plugin, TLS requirement, limits, lock state) with the password as a hash, which is how you copy accounts between servers - the subject of migrating MySQL to a new host.

For a wider view, the information_schema tables USER_PRIVILEGES, SCHEMA_PRIVILEGES, TABLE_PRIVILEGES and COLUMN_PRIVILEGES list grants by level, and mysql.db holds the database-level ones directly.

Roles#

A role is a named bundle of privileges that can be granted to accounts. Roles arrived in MySQL 8.0 and are ordinary accounts underneath (they show up in mysql.user, locked and with no password), which is why their names take a host part too; it defaults to %.

CREATE ROLE 'appdb_read', 'appdb_write', 'appdb_ddl';GRANT SELECT ON appdb.* TO 'appdb_read';GRANT INSERT, UPDATE, DELETE ON appdb.* TO 'appdb_write';GRANT CREATE, ALTER, DROP, INDEX, REFERENCES ON appdb.* TO 'appdb_ddl';GRANT 'appdb_read', 'appdb_write' TO 'app'@'%';GRANT 'appdb_read' TO 'report'@'%';GRANT 'appdb_read', 'appdb_write', 'appdb_ddl' TO 'app_migrate'@'%';SET DEFAULT ROLE ALL TO 'app'@'%', 'report'@'%', 'app_migrate'@'%';

The last line is the one everyone forgets. A granted role is not active in a session until it is activated, either by SET ROLE inside the session or by being a default role. Skip SET DEFAULT ROLE and the account logs in with only USAGE, every query fails with "command denied", and SHOW GRANTS FOR 'app'@'%' misleadingly lists the role as granted. The alternative is the server-wide activate_all_roles_on_login=ON, which is off by default.

-- What would this account be able to do with its roles active?SHOW GRANTS FOR 'app'@'%' USING 'appdb_read', 'appdb_write';-- Which roles are active in my session right now?SELECT CURRENT_ROLE();

Roles pay off when there are several accounts with the same needs, or several databases with the same pattern. For one app and one database, plain grants are fine.

Passwords, rotation and lockout#

MySQL 8 has the policies you would expect, set per account or as server defaults:

ALTER USER 'support'@'%'  PASSWORD EXPIRE INTERVAL 180 DAY  PASSWORD HISTORY 5  FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

FAILED_LOGIN_ATTEMPTS with PASSWORD_LOCK_TIME (in days, or UNBOUNDED) locks an account temporarily after consecutive failures, available since 8.0.19. It is a reasonable guard on human accounts. Think twice before putting it on an application account: a misconfigured deploy hammering a wrong password would lock out the correctly configured instances too.

Expiry suits people and is a nuisance for services. An expired password lets a client log in only in a sandbox mode where it can do nothing except change the password, and most drivers surface that as a baffling error at three in the morning. For service accounts, rotate on your own schedule with PASSWORD EXPIRE NEVER.

Rotation without downtime uses dual passwords, available since 8.0.14:

-- 1. Add a new password; the old one keeps workingALTER USER 'app'@'%' IDENTIFIED BY 'new-password' RETAIN CURRENT PASSWORD;-- 2. Roll the new password out to every instance of the app-- 3. Retire the old oneALTER USER 'app'@'%' DISCARD OLD PASSWORD;

Password strength rules come from the validate_password component, which some packages install and others do not. SHOW VARIABLES LIKE 'validate_password%'; returns nothing if it is absent. Generated passwords make it mostly irrelevant.

A least-privilege layout for a typical app#

Putting it together for a web application with a nightly backup job and a read-only analytics tool:

The backup account's list is what mysqldump needs for a consistent dump of one database with --single-transaction --no-tablespaces; dumping tablespace information requires the global PROCESS privilege, which --no-tablespaces avoids.

On RE:NODE, a MySQL server comes with three credentials generated: a root password, an application database, and an application user for it. That covers the first two rows of the table out of the box. With root you can create the rest in a minute with the statements above, and anyone you add to the server in the panel as a subuser gets panel permissions, which are separate from database accounts - subusers and least privilege covers the panel side.

Definers, views and stored routines#

Views, stored procedures, functions, triggers and events run with the privileges of their DEFINER by default (SQL SECURITY DEFINER). Two consequences:

• A view lets someone read through it even if they have no rights on the underlying table, which is useful for exposing a safe subset of columns to a reporting account.
• When you move a database to another server, every object still names its original definer, often root@localhost. If that account does not exist on the new server, the objects fail with "The user specified as a definer does not exist".

Creating an object with a definer other than yourself needs SET_ANY_DEFINER in 8.4 (SET_USER_ID or SUPER in 8.0), and creating one whose definer does not exist also needs ALLOW_NONEXISTENT_DEFINER. A hosted application user will not have those, which is why dumps restored as an ordinary user often fail on definer clauses - the fix is to strip or rewrite them before importing, or to import as root. SQL SECURITY INVOKER sidesteps the problem for routines that should run with the caller's rights.

Permission errors and what they mean#

The error codes are specific, and each one points at a different fix.

ERROR 1142 (42000): SELECT command denied to user 'app'@'198.51.100.7' for table 'invoices'

The account matched and logged in, but has no SELECT on that table. Check SHOW GRANTS for the account named in the message - not the one you think you are using - and check that a role holding the privilege is active.

ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER,SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation

The statement needs a global or dynamic privilege. Common triggers are a dump that sets @@GLOBAL.GTID_PURGED, a SET GLOBAL in an import script, or a DEFINER clause naming someone else. The message lists the privileges that would satisfy it; usually the right fix is to remove the statement from the script rather than grant the privilege.

ERROR 1396 (HY000): Operation CREATE USER failed for 'app'@'%'

The account already exists (or, for DROP USER, does not). Use IF NOT EXISTS and IF EXISTS in scripts that may run twice.

ERROR 1410 (42000): You are not allowed to create a user with GRANT

A 5.7-style GRANT ... IDENTIFIED BY against an 8.x server, or a GRANT to an account that does not exist yet. Run CREATE USER first.

ERROR 1045 (28000): Access denied for user 'app'@'203.0.113.9' (using password: YES)

Authentication failed: wrong password, or no account whose host pattern matches that address. If the account uses mysql_native_password on an 8.4 server where that plugin is disabled, the login fails here too. MySQL remote connections covers the connection-side causes in detail.

FAQ#

Do I need FLUSH PRIVILEGES after GRANT?

No. CREATE USER, GRANT, REVOKE, ALTER USER and DROP USER update the in-memory privilege cache immediately. FLUSH PRIVILEGES is only needed after editing the grant tables directly, which you should not do. It does no harm, but its presence in a script is a sign the script was copied from somewhere old.

Why does my user have access from one machine but not another?

Because the account is a user and a host pattern, and the other machine's address does not match it. The error message shows the address MySQL saw - 'app'@'198.51.100.7' - so compare it with SELECT user, host FROM mysql.user. Create an account for that host, or use %.

How do I give a user access to every database whose name starts with a prefix?

Historically with a wildcard database grant, quoting the database name client\_% in backticks and escaping the underscore so it is not itself a wildcard. MySQL 8.2 deprecated wildcards in database grants and they are expected to become literals in a future release. Grant per database instead; it is more explicit anyway.

Should my application use root?

No. Root can read every database, change server settings, create users and drop anything. If the application is compromised, root turns a bad day into a total loss. Use an account with rights on the application's own database, and keep root for maintenance.

What is the difference between a role and a user in MySQL?

Underneath, very little: a role is stored as a locked account with no password. The difference is in use. Roles are granted to accounts and must be activated (by default role or SET ROLE) before their privileges apply. Accounts log in.

Can I see which privileges a role grants?

Yes: SHOW GRANTS FOR 'appdb_read'; lists the role's own privileges, and SHOW GRANTS FOR 'app'@'%' USING 'appdb_read'; shows what an account gets with that role active. More on hardening accounts is in the database security checklist.