Summary: in this tutorial, you will learn how to use the mysql CREATE user, DROP USER statement to create a new user or remove one or more user accounts from the database server.

1. How To Create User Accounts Using MySQL CREATE USER Statement

MySQL CREATE USER syntax

The CREATE USER statement creates a new user in the database server.

Here is the basic syntax of the CREATE USER statement:

In this syntax:

First, specify the account name after the CREATE USER keywords. The account name has two parts: username and hostname, separated by the @ sign:

The username is the name of the user. And hostname is the name of the host from which the user connects to the MySQL Server.

The hostname part of the account name is optional. If you omit it, the user can connect from any host.

An account name without a hostname is equivalent to:

If the username and hostname contains special characters such as space or -, you need to quote the username and hostname separately as follows:

Besides the single quote ('), you can use backticks ( `) or double quotation mark ( ").

Second, specify the password for the user after the IDENTIFIED BY keywords.

The IF NOT EXISTS option conditionally create a new user only if it does not exist.

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

MySQL CREATE USER example

First, connect to the MySQL Server using the mysql client tool:

Enter the password for the root account and press Enter:

Second, show users from the current MySQL Server:

Here is the output:

Third, create a new user called bob:

Fourth, show all users again:

The output will be:

The user bob has been created successfully.

Fifth, open a second session and log in to the MySQL as bob:

Input the password for bob and press Enter:

Sixth, show the databases that bob has access:

Here is the list of databases that bob can access:

Seventh, go to the session of the user root and create a new database called bobdb:

Eight, select the database bobdb:

Ninth, create a new table called lists:

Notice that when you press Enter, instead of showing the mysql> command, the mysql tool shows the -> that accepts new clause of the statement.

Tenth, grant all privileges on the bobdb to bob:

Note that you will learn how to grant privileges to a user in the GRANT tutorial.

Eleventh, go to the bob’s session and show databases:

Now, bob can see the bobdb:

Twelfth, select the database bobdb:

Thirteenth, show the tables from the bobdb database:

The user bob can see the lists table:

Fourteenth, insert a row into the lists table:

Fifteenth, query data from the lists table:

This is the output:

So the user bob can do everything in the bobdb database.

Finally, disconnect from the MySQL Server from both sessions:

2. Introduction to MySQL DROP USER statement

To remove a user account from the MySQL Server, you use the DROP USER statement as follows:

In this syntax, you specify the name of the user account that you want to remove after the DROP USER keywords.

If you want to remove multiple user accounts at once, you specify a list of comma-separated user accounts in the DROP USER clause:

If you remove a user account that doesn’t exist, MySQL will issue an error.

In MySQL 5.7.8+, you can use the IF EXISTS clause to conditionally drop a user only if it exists:

Besides removing the user account, the DROP USER statement also removes all privileges of the user from all grant tables.

MySQL DROP USER examples

Let’s take some examples of dropping users.

A) Using MySQL DROP USER statement to drop a user example

First, connect to the MySQL Server using the root account:

Type the password for the root user and press Enter:

Second, create four account users  accounts api@localhost, remote, dbadmin@localhost and alice@localhost:

Third, show users from the MySQL Server:

Here is the current user list:

Fourth, drop the user dbadmin@localhost by using the DROP USER statement:

Fifth, show all users again:

The user account dbadmin@localhosthas been removed successfully.

B) Using MySQL DROP USER to drop multiple user accounts at once

First, remove two user accounts api@localhost and remote using the following statement:

Second, show users from the current database:

Here is the output:

C) Using MySQL DROP USER to drop a connected user

First, create a new database called people:

Second, select the people database:

Third, create a new table persons in the people database:

Fourth, grant all privileges on the people database to the account user alice:

Fifth, launch another session and connect to the database using the user alice@localhost:

Type the password for the user account alice and press the Enter key:

Sixth, select the people database:

Seventh, insert a row into the persons table:

Suppose that you want to drop the user alice@localhost.

However, the user account alice@localhost is still connected to MySQL Server. If you drop a currently connected user, the user can operate as normal until the next login. In this case, you should inform the user first.

If you cannot do so, you can kill user sessions first before dropping the user account.

Eighth, use the SHOW PROCESSLIST statement from the root’s session to find the id of the connection:

As you see, the user account alice@localhost has the connection id id 21.

Ninth, terminate process 21 by using the KILL statement:

The user account alicereceived an error message if he/she issue any query:

Finally, execute the DROP USER statement to remove the user account alice@localhost.

In this tutorial, you have learned how to use the MySQL CREATE USER, DROP USER to create a new user and remove one or more user accounts in the database server.