Create a user with the CREATE USER statement in MySQL

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.

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 'password';

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 of the account name is optional. If it is omitted, users can connect from any host.

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 (").
You should pecify the user’s password after the IDENTIFIED BY keyword.
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 user named sqliz who can connect to MySQL server from any host :
```
CREATE user 'sqliz'@'%' IDENTIFIED by 'SqLiZ9879123!';
```

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 ways created.

Open a new session and log in to MySQL using the sqliz user :
```
mysql -u sqliz -p
```
Enter your password of sqliz and press Enter:
```
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 all privileges on the sqlizdb database to sqliz using the GRANT statement :
```
GRANT ALL PRIVILEGES ON sqlizdb.* TO sqliz@'%';
```

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. |
+----+--------------+

Now, the user sqliz can do anything in the sqlizdb database.

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