How to create a user account using the CREATE USER statement in MySQL with examples
This tutorial explains how to create a user account in MySQL using the CREATE USER statement.
It is possible to create a user account that can connect to the MySQL database server from localhost, a specified host, or any host. For example, you can create a new user account named user_a and allow this user account to connect from a domain named somedomain.com.
Related tutorial: How to delete a user account in MySQL
CREATE USER Syntax
The syntax of the MySQL CREATE USER statement is as follows:
CREATE USER 'user_account'
IDENTIFIED BY 'password';
Note that this statement will create a new user account with no privileges and a default role of NONE.
Examples
Creating a user account that can connect from localhost
The following statement creates a new user account named admin and sets the password pass:
CREATE USER 'admin'@'localhost'
IDENTIFIED BY 'pass';
When the user account admin is first created, this user account has no privileges, and the default role is NONE. In the above SQL statement, localhost is specified, which means that this user account admin can only connect to the MySQL database server from localhost.
If you omit the hostname part of the user account, MySQL will allow that user account to connect from any host. For example, the following MySQL statement creates a new user account named remote_user that can connect from any host:
CREATE USER 'remote_user'
IDENTIFIED BY 'pass';
Creating a user account that can connect from any host
To make sure that the new user account you created can connect from any host, write the MySQL statement like this:
CREATE USER 'superadmin'@'%'
IDENTIFIED BY 'pass';
This statement creates a new user account named "superadmin" that can connect to the MySQL database server from any host.
Creating a user account that can connect from any subdomain
For example, suppose you have the domain mysqltutorial.com, and you want to create a new user account named "james" that can connect to the MySQL database server from any subdomain of this domain. You can write a MySQL statement as follows:
CREATE USER 'james'@'%.mysqltutorial.com'
IDENTIFIED BY 'pass';
Once the user james has been created, "james" can connect from any subdomain of mysqltutorial.com.
Summary
In this tutorial, you have learned how to create a user account in MySQL using the CREATE USER statement. You can create a user account that can connect to localhost, a specified host, or any host. User accounts have no privileges when they are first created, and the default role is NONE. If you want to drop a user account from MySQL, use MySQL DROP USER Statement.