Summary: in this tutorial, you will learn how to create user in MySQL by using the CREATE USER and INSERT statements.
In MySQL, you can specify not only who can connect to a database server but also from where. Therefore an account in MySQL consists of username and host name where user connects from, that separated by
@. For example, if the
root user connects from the
mysqltutorial.org host to the database server the account name would be
This allows you to setup multiple accounts with the same name but connects from different hosts and have different privileges. In addition, you have control to setup account to connect from either specific or broad a set of hosts. The username and host are stored in the grant table named
Create new users using CREATE USER statement
MySQL provides the
CREATE USER statement to allow you to create a new user in a database server. The syntax of the
CREATE USER statement is as follows:
CREATE USER user IDENTIFIED BY password
User account in the format
'username'@'hostname' is followed by the
The password is specified after the
IDENTIFIED BY clause. The password must be in plain text. MySQL will encrypt the password for you when it saves the user into the
For example, to create a new user
dbadmin that connects from
localhost with the password
CrEate-User , you use the
CREATE USER statement as follows:
CREATE USER dbadmin@localhost IDENTIFIED BY 'CrEate-User'
To allow user to connect from any host you use the
% wildcard, which means any host.
CREATE USER superadmin@'%' IDENTIFIED BY 'Secured'
The percentage wildcard
% has same effect as it is used in the LIKE operator e.g., to allow
mysqladmin user to connect from any host to the
mysqltutorial.org you use percentage wildcard
% as follows:
CREATE USER mysqladmin@'%.mysqltutorial.org' IDENTIFIED by 'SecurePass704';
Notice that you can also use the underscrore wildcard _ in the
CREATE USER statement.
If you omit the
hostname part of the account, MySQL still accepts it and allows user to connect from any host. The quote is very important especially when the account contains special characters such as
If you accidentally quote the account name like
'username@hostname', MySQL will create a user with
username@hostname username and allow the user to connect from any host, which may not be what you expected.
CREATE USER statement only creates a new user and does not grant any privilege to that user. If you want to grant privileges to the user you use the
MySQL GRANT statement.
Create new user by using INSERT statement
A less common way to create user is using INSERT statement to insert a new record into the grant table named
user. By doing this way, you need to use the
PASSWORD function to encrypt the password before inserting the user record into the table. See the following example:
INSERT INTO user (host,user,password) VALUES('localhost','dbadmin',PASSWORD('CrEate-User'));
In this tutorial, you have learned various ways to create user in MySQL using the
CREATE USER statement and