MySQL Create User CREATE USER, GRANT, Roles, and Host Examples
For someone whose application still connects to MySQL as root and who wants an account with only the privileges that application uses.
On this page
The application still logs in to MySQL as root, and you want it to stop. Two statements replace that account: one creates a user, the other gives it privileges on one database and nothing else.
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword!';
GRANT SELECT, INSERT, UPDATE, DELETE ON shopdb.* TO 'app_user'@'localhost';
Running them needs the global CREATE USER privilege, or the INSERT privilege on the mysql system schema. Neither statement needs FLUSH PRIVILEGES afterwards. Account management statements reload the grant tables themselves, and the flush is for grant tables somebody changed with INSERT or UPDATE directly. The examples are for MySQL 8.4.
MySQL create user syntax by version
The statement itself has been stable for years. What changed under it is how the password is stored: caching_sha2_password is the default authentication plugin in MySQL 8.4, where mysql_native_password was the earlier default and is now deprecated. An account created with a plain IDENTIFIED BY therefore gets caching_sha2_password, and an old client library that only speaks the native plugin is the usual reason a new account cannot connect while root still can. The native plugin is built into the 8.4 server and disabled by default, so the way through is to start the server with --mysql-native-password=ON, or to put mysql_native_password=ON in the [mysqld] section of its configuration file. Updating the library is the durable fix, because the plugin is removed as of MySQL 9.0.
IF NOT EXISTS makes a deployment script rerunnable: the clause turns an account that already exists into a warning rather than an error.
CREATE USER IF NOT EXISTS 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword!';
MySQL 8.4 can also pick the password for you, which keeps it out of the script and out of your shell history:
CREATE USER 'app_user'@'localhost' IDENTIFIED BY RANDOM PASSWORD;
The statement generates the password, hands it to the authentication plugin, and returns the cleartext value in a result set, so the one place it appears is the session that created the account. Roles, covered further down, arrived with MySQL 8.0.
Create a user for localhost
mysql -u root -p
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword!';
The host half of the name is not a comment. 'app_user'@'localhost' and 'app_user'@'%' are two different accounts, with their own passwords and their own privileges, and MySQL uses the one matching where the connection came from. localhost is the local host, 127.0.0.1 the IPv4 loopback interface, and ::1 the IPv6 one, so a client that resolves the server name to a loopback address may still miss an account written for localhost.
Write the host every time. An account name with no host at all is equivalent to 'user_name'@'%', which accepts connections from anywhere, and CREATE USER app_user IDENTIFIED BY '...' therefore creates a much wider account than it looks like.
For a web application or a development stack on the same machine as the server, localhost is the whole answer: the account cannot be used from another host even by someone holding the password.
Grant database privileges
GRANT takes the privileges, the object they apply to, and the account. The object is where most of the risk sits: shopdb.* is every table in one database you created, and *.* is every table on the server, including the mysql schema that holds the accounts themselves.
Read/write access to one database
GRANT SELECT, INSERT, UPDATE, DELETE
ON shopdb.*
TO 'app_user'@'localhost';
Four privileges cover what an ordinary application does with data. The account cannot create a table, drop one, or read another database, and none of that stops the application from working until somebody adds a migration to it.
Read-only reporting user
CREATE USER 'report_user'@'localhost' IDENTIFIED BY 'StrongPassword!';
GRANT SELECT ON analytics.* TO 'report_user'@'localhost';
A reporting account with SELECT alone is the one to hand out when someone asks for access to look at something. It is also the account to give DbSchema, which reads the schema over the connection to draw the diagram from it.
Full privileges on a single schema
GRANT ALL PRIVILEGES
ON shopdb.*
TO 'app_user'@'localhost';
ALL PRIVILEGES ON shopdb.* lets the account do anything inside that one database, migrations included, and nothing outside it. Use it for an application that manages its own schema, and stay away from ALL PRIVILEGES ON *.*, which is the root account with a different name.
Allow remote access safely
An application on another host needs an account whose host part matches where it connects from. The obvious way to write a subnet is a wildcard:
CREATE USER 'api_user'@'10.0.%' IDENTIFIED BY 'StrongPassword!';
GRANT SELECT, INSERT, UPDATE, DELETE ON shopdb.* TO 'api_user'@'10.0.%';
That form still works, and the 8.4 manual now says the % and _ wildcards in a host value are "deprecated and thus subject to removal in a future version of MySQL". The replacement is already there: an IPv4 host value takes a netmask or CIDR notation, which says the same thing without a pattern.
CREATE USER 'api_user'@'10.0.0.0/255.255.0.0' IDENTIFIED BY 'StrongPassword!';
GRANT SELECT, INSERT, UPDATE, DELETE ON shopdb.* TO 'api_user'@'10.0.0.0/255.255.0.0';
Netmask notation is for IPv4 only, so an IPv6 client still needs its address written out. Where you genuinely cannot name the network, as with an integration whose address changes:
CREATE USER 'integration_user'@'%' IDENTIFIED BY 'StrongPassword!';
GRANT SELECT ON integrationdb.* TO 'integration_user'@'%';
An account open to every host earns its privileges one at a time. SELECT on one database, as above, is a different proposition from write access on all of them.
Use roles in MySQL 8.0 and 8.4
MySQL gained roles in 8.0: a role is a named collection of privileges that can be granted to accounts, so the privileges live in one place instead of being repeated for every user.
CREATE ROLE 'reporting_readonly';
GRANT SELECT ON analytics.* TO 'reporting_readonly';
CREATE USER 'analyst'@'%' IDENTIFIED BY 'StrongPassword!';
GRANT 'reporting_readonly' TO 'analyst'@'%';
SET DEFAULT ROLE 'reporting_readonly' TO 'analyst'@'%';
The last statement is the one people leave out. A role granted to an account is not active in that account's sessions until something activates it, and SET DEFAULT ROLE makes it active at login; without it the analyst connects successfully and then finds no tables. Adding a second analyst is now one GRANT and one SET DEFAULT ROLE, and widening what analysts may read is one GRANT against the role, which every account holding it picks up.
What the account can reach through a role does not show up in a plain SHOW GRANTS, which lists the role as a grant rather than the privileges inside it. Name the role to see through it:
SHOW GRANTS FOR 'analyst'@'%' USING 'reporting_readonly';
The output then carries the role's SELECT on analytics alongside the account's own grants, which is the answer to "what can this person actually read" when the privileges arrived in a bundle.
Show grants, revoke access, and drop a user
SHOW GRANTS prints the account's privileges as the GRANT statements that would recreate them:
SHOW GRANTS FOR 'app_user'@'localhost';
GRANT USAGE ON *.* TO `app_user`@`localhost`
GRANT SELECT, INSERT, UPDATE, DELETE ON `shopdb`.* TO `app_user`@`localhost`
USAGE is what "no privileges" looks like: every account has it, and it means the account may connect and nothing more. The second line is the grant made earlier, and reading the two together is how you check that an account picked up nothing else along the way.
Taking privileges back names them the same way it gave them:
REVOKE INSERT, UPDATE, DELETE
ON shopdb.*
FROM 'app_user'@'localhost';
The account survives that with SELECT on shopdb, and SHOW GRANTS afterwards is worth one line of typing. Removing the account outright takes its privileges with it:
DROP USER 'app_user'@'localhost';
Do not run DROP USER against an account an application still uses. The application's next connection is refused, and MySQL does not warn you at drop time that anything was connected. Revoke first, watch what breaks, then drop.
Use the account in DbSchema
The reporting account above is the right shape for DbSchema. Click Connect to Database, pick MySql, and DbSchema downloads the JDBC driver and opens the Connection Dialog for it. Give it the Server Host and Port, the Database User and Password of that account, and tick Remember to keep the password on your computer. Test Connection checks that the server answers before you go further.
On the Settings tab, tick Read Only Connection. DbSchema then opens the connection in read-only mode and the database refuses every change made through it, which pairs with a SELECT grant rather than relying on it alone. Click Connect and DbSchema reverse-engineers the schema into a diagram.
What that reads is the structure: table names, columns, and keys. Table data is read when you open a table or run a query. Both land in a .dbs model file on your computer, so rearranging the diagram, grouping tables, or adding a comment changes the file and never the server.
If your next step is understanding the structure itself, continue with Create ER Diagrams for MySQL.
Download DbSchema at https://dbschema.com/download.html, create the reporting account above, and connect with it instead of with root. Connecting, reverse-engineering, and the diagrams are in the free Community Edition; saving the model file and generating schema documentation from it are in Pro.
FAQ
How do I create a MySQL user for localhost?
CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'StrongPassword!';. On Unix the host value has a second effect: MySQL programs connecting to localhost use a Unix socket file rather than TCP, and a client that needs TCP to the same machine passes --host=127.0.0.1 or --protocol=TCP instead.
How do I grant privileges to a MySQL user?
GRANT privileges ON database_name.* TO 'user_name'@'host'; grants at the database level. MySQL also grants at table and column level, so GRANT SELECT (col1), INSERT (col1, col2) ON mydb.mytbl TO 'someuser'@'somehost'; narrows an account to named columns of one table.
Do I need FLUSH PRIVILEGES after CREATE USER or GRANT?
FLUSH PRIVILEGES is for grant tables somebody edited directly, and running it needs the RELOAD or FLUSH_PRIVILEGES privilege of its own. CREATE USER and GRANT take effect without it, as the opening paragraph says.
Can I create roles in MySQL 5.7?
Roles arrived in MySQL 8.0, which the "What Is New in MySQL 8.0" page lists among the new features. On an older server, grant the privileges to each account separately.
Should I grant ALL PRIVILEGES ON *.* to application users?
An account holding it can rewrite its own privileges, for the reason the section on granting database privileges gives. Grant on the one database the application uses, and use ALL PRIVILEGES ON shopdb.* where it manages its own schema.
How do I see what privileges a user already has?
SHOW GRANTS FOR 'user_name'@'host'; prints them as GRANT statements, and SHOW GRANTS with no FOR clause prints the ones the connected account holds. Reading another account's grants needs the SELECT privilege for the mysql system schema, so the application account cannot look up anyone else.
