How To Create User Accounts Using MySQL CREATE USER Statement
Summary: in this tutorial, you will learn how to use the MySQL CREATE USER statement to create a new user in the database server.
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:
CREATE USER [IF NOT EXISTS] account_name
IDENTIFIED BY 'password';
Code language: SQL (Structured Query Language) (sql)
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:
username@hostname
Code language: SQL (Structured Query Language) (sql)
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:
username@%
Code language: SQL (Structured Query Language) (sql)
If the username and hostname contains special characters such as space or -, you need to quote the username and hostname separately as follows:
'username'@'hostname'
Code language: SQL (Structured Query Language) (sql)
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:
mysql -u root -p
Code language: SQL (Structured Query Language) (sql)
Enter the password for the root account and press Enter:
Enter password: ********
Code language: SQL (Structured Query Language) (sql)
Second, show users from the current MySQL Server:
mysql> select user from mysql.user;
Code language: SQL (Structured Query Language) (sql)
Here is the output:
+------------------+
| user |
+------------------+
| mysql.infoschema |
| mysql.saession |
| mysql.sys |
| root |
+------------------+ Third, create a new user called bob:
mysql> create user bob@localhost identified by 'Secure1pass!';
Code language: SQL (Structured Query Language) (sql)
Fourth, show all users again:
mysql> select user from mysql.user;
Code language: SQL (Structured Query Language) (sql)
The output will be:
+------------------+
| user |
+------------------+
| bob |
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| root |
+------------------+
5 rows in set (0.00 sec)Code language: JavaScript (javascript)
The user bob has been created successfully.
Fifth, open a second session and log in to the MySQL as bob:
mysql -u bob -p
Code language: SQL (Structured Query Language) (sql)
Input the password for bob and press Enter:
Enter password: ********
Code language: SQL (Structured Query Language) (sql)
Sixth, show the databases that bob has access:
mysql> show databases;
Code language: SQL (Structured Query Language) (sql)
Here is the list of databases that bob can access:
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)Code language: JavaScript (javascript)
Seventh, go to the session of the user root and create a new database called bobdb:
mysql> create database bobdb;
Code language: SQL (Structured Query Language) (sql)
Eight, select the database bobdb:
mysql> use bobdb;
Code language: SQL (Structured Query Language) (sql)
Ninth, create a new table called lists:
mysql> create table lists(
-> id int auto_increment primary key,
-> todo varchar(100) not null,
-> completed bool default false);
Code language: SQL (Structured Query Language) (sql)
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:
mysql> grant all privileges on bobdb.* to bob@localhost;
Code language: SQL (Structured Query Language) (sql)
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:
mysql> show databases;
Code language: SQL (Structured Query Language) (sql)
Now, bob can see the bobdb:
+--------------------+
| Database |
+--------------------+
| bobdb |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)Code language: JavaScript (javascript)
Twelfth, select the database bobdb:
mysql> use bobdb;
Code language: SQL (Structured Query Language) (sql)
Thirteenth, show the tables from the bobdb database:
mysql> show tables;
Code language: SQL (Structured Query Language) (sql)
The user bob can see the lists table:
+-----------------+
| Tables_in_bobdb |
+-----------------+
| lists |
+-----------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)
Fourteenth, insert a row into the lists table:
mysql> insert into lists(todo) values('Learn MySQL');
Code language: SQL (Structured Query Language) (sql)
Fifteenth, query data from the lists table:
mysql> select * from lists;
Code language: SQL (Structured Query Language) (sql)
This is the output:
+----+-------------+-----------+
| id | todo | completed |
+----+-------------+-----------+
| 1 | Learn MySQL | 0 |
+----+-------------+-----------+
1 row in set (0.00 sec) Code language: JavaScript (javascript)
So the user bob can do everything in the bobdb database.
Finally, disconnect from the MySQL Server from both sessions:
mysql> exitCode language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned how to use the MySQL CREATE USER to create a new user in the database server.