Create a user with the CREATE USER statement in MySQL
This article describes how to use the CREATE USER
statement to create a new user in MySQL database server.
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
andhostname
, separated by the@
symbol:username@hostname
username
is the user’s name. Andhostname
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
andhostname
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 pressEnter
: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 pressEnter
: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 calledsqlizdb
:CREATE DATABASE sqlizdb;
-
Grant all privileges on the
sqlizdb
database tosqliz
using theGRANT
statement :GRANT ALL PRIVILEGES ON sqlizdb.* TO sqliz@'%';
-
Switch to the session of
sqliz
and list the database:SHOW DATABASES;
Now,
sqliz
can seesqlizdb
:+--------------------+ | 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 thetest_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.