Create a MySQL User with CREATE USER

Users is the fundamental element of MySQL authentication. You can only log in to the MySQL database with the correct user name and password, and grant users different privileges so that different users can perform different operations.

Creating users is the first step in precisely controlling privileges.

For an application connection, grant only the permissions its queries require. Keep schema changes in a separate migration or administrator account.

In MySQL, you can use the CREATE USER statement to create a new user in the database server.

MySQL `CREATE USER` syntax

The following is the basic syntax of the CREATE USER statement:

CREATE USER [IF NOT EXISTS] account_name
IDENTIFIED BY 'auth_string';

For MySQL 8.0.18 and later, MySQL can generate a random password with IDENTIFIED BY RANDOM PASSWORD; the server returns the generated value once. Store it securely. If you specify a password yourself, use a unique secret and do not copy a password from an example.

Here:

You should specify the account name after the CREATE USER keyword. The account name consists of two parts: username and hostname, separated by the @ symbol:
```
username@hostname
```
username is the user’s name. And hostname indicates where is the user from.

The hostname part is optional, but omitting it is equivalent to using the wildcard host '%', which matches any host. MySQL deprecates % and _ wildcards in host values. Prefer 'localhost' for a local application or specify the narrowest hostname, IP address, or network range the remote client needs. See MySQL’s account name and host matching rules.

An account name without a hostname is equivalent to:
```
'username'@'%'
```
If username and hostname contain special characters such as -, you need to quote the username and hostname respectively as follows:
```
'username'@'hostname'
```
Instead of single quotes ('), you can use backticks (``) or double quotes (").
Specify an authentication string after IDENTIFIED BY. To let MySQL generate one on MySQL 8.0.18 or later, use IDENTIFIED BY RANDOM PASSWORD.
IF NOT EXISTS option is used to conditionally create a new user only if the new user does not exist.

Note that the CREATE USER statement creates a new user without any privileges. To grant privileges to a user, use the GRANT statement.

MySQL `CREATE USER` Examples

Follow the steps below to execute a MySQL CREATE USER example:

Connect to the MySQL server using the mysql client tool:
```
mysql -u root -p
```
Enter the password for the root account and press Enter:
```
Enter password: ********
```

List all users from the current MySQL server :

SELECT user FROM mysql.user;

+------------------+
| user             |
+------------------+
| root             |
| test_role1       |
| test_role2       |
| testuser         |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+

Create a local user named sqliz. This account is limited to local connections and MySQL generates its password:
```
CREATE USER 'sqliz'@'localhost' IDENTIFIED BY RANDOM PASSWORD;
```
Save the generated password shown by MySQL in a secure secret store. If you use a MySQL version earlier than 8.0.18, replace RANDOM PASSWORD with a unique password supplied securely.

Show all users again:

SELECT user FROM mysql.user;

+------------------+
| user             |
+------------------+
| root             |
| test_role1       |
| test_role2       |
| testuser         |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
| sqliz            |
+------------------+

The user named sqliz was created.

Open a new session and log in to MySQL using the sqliz user :
```
mysql --user=sqliz --password
```
Enter the generated password saved in step 3 and press Enter. It is not necessarily the same as the account name.
```
Enter password: ********
```

Show the accessible databases of sqliz :

SHOW DATABASES;

The following is a list of databases that sqliz can be accessed:

+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+

Go to user root’s session and create a new database called sqlizdb:
```
CREATE DATABASE sqlizdb;
```
Grant only the SELECT and INSERT privileges used by the following example to sqliz:
```
GRANT SELECT, INSERT ON sqlizdb.* TO 'sqliz'@'localhost';
```

Switch to the session of sqliz and list the database:

SHOW DATABASES;

Now, sqliz can see sqlizdb:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| sqlizdb            |
+--------------------+

Select database sqlizdb:
```
USE sqlizdb;
```
Henceforth, sqlizdb is the default database in this session. All subsequent operations are performed in this database by default.

Create a new table named test_table:

CREATE TABLE test_table(
    id int AUTO_INCREMENT PRIMARY KEY,
    txt varchar(100) NOT NULL
);

Show all tables from the sqlizdb database :

SHOW TABLES;

Now, sqliz can see the test_table table:

+-------------------+
| Tables_in_sqlizdb |
+-------------------+
| test_table        |
+-------------------+

Insert a row into the test_table table :

INSERT INTO test_table(txt)
VALUES('Hello World.');

Fetch all rows from the test_table table :

SELECT * FROM test_table;

Here is the output:

+----+--------------+
| id | txt          |
+----+--------------+
|  1 | Hello World. |
+----+--------------+

The sqliz account can read rows and insert new rows in sqlizdb. It cannot create or alter tables, or update and delete rows. Use a separate migration account if the application needs to change the schema.

Conclusion

In this article, you learned how to create a new user in the MySQL server using MySQL CREATE USER:

Create a new user.
Grant the appropriate privileges to the new user.

After creating a user, you may also want to change a user’s password, rename a user or drop a user.

MySQL CREATE USER syntax

MySQL CREATE USER Examples

Conclusion

On this page

MySQL `CREATE USER` syntax

MySQL `CREATE USER` Examples