How to create a user with password in MySQL

How to create a user with password in MySQL

This tutorial explains how to create a user with 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 introduce a new user and grant him/her the required permissions or rights. 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.

[email protected]_name
e.g. [email protected]

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 him/her the desired access.

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

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  [email protected] 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 User

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

mysql> GRANT ALL PRIVILEGES on johndb.* to [email protected];

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

We hope you should now feel comfortable in using the MySQL CREATE USE with PASSWORD command. However, you can take up more examples and practice.

Also, to learn SQL from scratch to depth, do read our step by step MySQL tutorial.

Happy Coding !

Mysql

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Best MySQL DigitalOcean Performance – ScaleGrid vs. DigitalOcean Managed Databases

Compare ScaleGrid MySQL vs. DigitalOcean Managed Databases - See which offers the best MySQL throughput, latency, and pricing on DigitalOcean across workloads.

Exploring MySQL Binlog Server - Ripple

How MySQL Ripple binlog server can help you improve the performance of your master server by introducing a proxy server between the master and slave servers.MySQL does not limit the number of slaves that you can connect to the master server in a replication topology.

MySQL In 20 Minutes | Introduction To MySQL | MySQL Database | SQL DBMS

What MySQL is? What is a Database and Database Management System and we will discuss how to work with databases and process data using MySQL

MySQL on Azure Performance Benchmark – ScaleGrid vs. Azure Database

In this performance benchmark for MySQL on Azure, we put ScaleGrid up against Azure Database. See which provider gives you the most value for your money! Microsoft Azure is one of the most popular cloud providers in the world, and a natural fit for database hosting on applications leveraging Microsoft across their infrastructure. MySQL is the number one open source database that’s commonly hosted through Azure instances. While Microsoft offers their own Azure Database product, there are other alternatives available that may be able to help you improve your MySQL performance.

Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)の解決

railsでWebアプリケーションを作成しているのですが、rails sでサーバーを起動した際に、Can't connect to local MySQL server through socket  '/var/lib/mysql/mysql.sock' (2) のエラーが出てしまいます