Create a New User in MySQL with Password

This tutorial explains how to create a user with a password in MySQL. We’ll use the MySQL Create User command and describe it with the help of examples.

By using this command, you can create a new MySQL user and grant privileges. Usually, you might be using the root user to access a database. The root user has complete control and can’t be restricted.

Hence. It is mandatory to create users depending on the type of access s/he needs. And that’s where this command is going to help.

MySQL Create User with Password

The MySQL Create User statement can be used to create new users with a password and can be assigned permissions on a need basis.

Syntax

It comes in the following forms:

-- MySQL CREATE USER with PASSWORD
CREATE USER [IF NOT EXISTS] [User_Name]
    IDENTIFIED [BY/WITH] ['Password'];

Here, the User_Name field has two elements: The actual username and the machine hostname.

user_name@host_name
e.g. john@myhost.com

Please note the following:

• The user_name is the name of the logged-in user whereas the host_name is the name of the machine running the MySQL DB.
• The host_name subfield is optional. If you remove it, even then the user can establish a connection.
• Both the user_name  and host_name can include special chars. You need to quote them accurately: ‘user_name’ or ‘host_name.’

You must supply a password for the user right after the IDENTIFIED BY clause

Also, by using the IF NOT EXISTS option, you can make sure a new user is created when s/he is not available.

You must remember that MySQL CREATE USER command creates a new user who doesn’t have any privileges. Hence, you need to grant permissions to the new user.

We feel that the following posts would guide you even more along with this tutorial:

• MySQL Optimize Table
• MySQL Aggregate Functions

Creating users in MySQL – Examples

First of all, you must have an active connection with the MySQL DB. So, if it is not the case, then run the following MySQL client command:

mysql -u root -p

If you would have set a root password, then provide the same.

Enter Password : XXXXXXXX

Once the connection is successful, you can list down the available users.

mysql> SELECT  user from  MYSQL.user;

It will give the below result:

+------------------+
| Users            |
+------------------+
| mysql.sys        |
| root             |
+------------------+

MySQL Create User with Password

Now, try to define a new user known as ‘john’:

mysql> CREATE user  john@localhost IDENTIFIED by 'xxYYYxx';

Run the below command to display all users.

mysql> select user from mysql.user;

+------------------+
| Users            |
+------------------+
| mysql.sys        |
| john             |
| root             |
+------------------+

You have now successfully a new user called John. Now, start another MySQL session and log in as john.

mysql -u john -p

Supply the password for john and press the Enter key:

Enter Password : xxYYYxx

In the next step, you have to list down the databases that john can access.

mysql> SHOW   DATABASES;

You might see something like:

Databases
-------------------
information_schema

There is no database that john can use. So, we’ll now create one for him.

mysql> CREATE DATABASE johndb;

Now, select the database you created.

mysql> use johndb;

The database is empty, so create a dummy table named ‘test_table.’

mysql> CREATE TABLE test_table(
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(30) not null,
-> married bool default false);

Grant Privileges to New Users

Now, assign the required privileges on the ‘johndb’ to ‘john’:

mysql> GRANT ALL PRIVILEGES on johndb.* to john@localhost;

You can read more about MySQL Grant Privileges here.

Now again go to the console and run the command to show the databases.

mysql> SHOW DATABASES;

This time, john should be able to list the johndb:

Databases
-------------------
johndb
information_schema

Now, select the johndb and check the available tables.

mysql> USE johndb;
mysql> SHOW  TABLES;

You can now confirm that john can list the test_table.

Tables_in_johndb
-------------------
test_table

Let’s now see if john can add some records to the test_table.

mysql> INSERT  INTO test_table(name) values('John Travolta');
mysql> SELECT  * from test_table;

The above statements would give the below result:

Records in test_table
-------------------------
1, John Travolta, 0

Now, you can assert that the user john can run any operation on the johndb database.

Now, it’s time to disconnect from the MySQL DB and close the running sessions.

Summary – MySQL CREATE USER with PASSWORD

Trust us that you can now easily create a user in MySQL with a password, and grant privileges. However, you can take up more examples and practice.

While you are on a learning spree, more information is what you need. So, don’t miss on to the below posts.

• Quick Steps to Install MySQL on Windows 7
• Easy Steps to Install MySQL on Ubuntu 16.04
• MySQL Date and Date Functions

And that’s it for this tutorial, we hope you found it helpful and informative!