What is a user in MySQL?

A user is a record in the USER table of the MySQL server. It contains the login information, account privileges, and host information for the MySQL account.

Why do we create a user in MySQL?

We create a user in MySQL for accessing and managing the databases. Moreover, we can decide the privileges of different users with different roles by creating and adding new users to the database.

What is the default username and password in MySQL?

By default, the user name in MySQL is 'root,' and there is no password. However, you can put a password during the process of installation.

How many users can be created in MySQL?

There is no such limitation on creating a number of users in the MySQL database. Users' accounts get stored in rows and columns, which consume some space. In theory, there can be infinite users, but in reality, you will hit resource boundaries. So, it depends on MySQL configuration settings.

What is the difference between SQL and MySQL?

SQL stands for Structured Query Language, and it's a language used by databases, whereas MySQL is an open-source relational database management system developed by Oracle, which is based on SQL.

Table of contents

Introduction

MySQL Create User

Grant privileges to the MySQL new user

FAQs

Key Takeaways

Last Updated: Mar 27, 2024

MySQL Create User

Author Apoorv Dixit

Do you think IIT Guwahati certified course can help you in your career?

Yes

Introduction

MySQL is an open-source database management system based on SQL- Structured Query Language. It helps users store, organize and retrieve data. It has a variety of operations to grant specific permissions to different users within the table and database.

When MySQL server installation completes, it includes options of managing through root user or specific user account only. But for security reasons, sometimes it becomes necessary to grant permission to other users without giving them complete control. In such cases, you need to create non-root users and grant them specific privileges and permissions to modify the database. In this article, we will discuss how to create new users and manage their permissions and privileges.

MySQL Create User

We can create a MySQL user with the following command:

CREATE USER ‘account_name’ IDENTIFIED BY 'password';

In the above query, the user will only be created only if it does not exist. The account_name has two parts; username, and hostname, separated by the '@' symbol.

Username is the name of the user, and hostname is the host's name from which the host can connect from the database server. So it is written like:

username@hostname.

Here hostname is optional, and to connect with any host server; the user can use:

username@%

Now let's discuss one example step by step to create a user in the MySQL server database.

Step 1. Open MySQL server using MySQL client tool.

Step 2. Enter the account password and hit enter.

Step 3. You can execute the following command to get the list of all users.

mysql> select user from mysql.user;

The output will be similar to the output given below.

+-----------+

| user |

+-----------+

| mysql.sys |

| mysqlxsys |

| root |

+-----------+

3 rows in set (0.00 sec)

Step 4. Now to add a new user, execute the following query.

mysql> create user ninja@localhost identified by 'sqlninja';

A new user with the username 'ninja' will be created. When we again run the command to show the list of all users in the output, a new user will be added to the list.

+-----------+

| user |

+-----------+

| mysql.sys |

| mysqlxsys |

| root |

|ninja |

+-----------+

4 rows in set (0.00 sec)

Step 5. We can also use the IF NOT EXISTS clause along with the above query to create a user only if it does not exist.

mysql> CREATE USER IF NOT EXISTS james@localhost IDENTIFIED BY 'ninjames';

Grant privileges to the MySQL new user

Privileges are the permissions that a user has or actions that he can perform in a given database. You can grant specific privileges to users as per requirements. MySQL provides multiple types of privileges to a new user account. Some of the most common privileges are given below

ALL PRIVILEGE: It permits all privileges to a new user account, and the user has full access to the database.
CREATE: It enables the user to create entirely new databases and tables.
INSERT: It enables users to insert rows into tables.
DROP: The user can drop(remove) the entire database or table.
DELETE: The user can delete rows from a specific table.
SELECT: It enables the user to read information from the database.
UPDATE: It enables the user to update the table rows.
GRANT Option: The user can modify the privileges of other users.

Let's see some examples to understand these privileges.

To grant all the privileges to a user, run the following query.

mysql> GRANT ALL PRIVILEGES ON * . * TO ninja@localhost;

To grant specific privileges, run the following query.

mysql> GRANT CREATE, SELECT, INSERT ON * . * TO ninja@localhost;

If you want to check the existing privileges of a user, you can use;

mysql> SHOW GRANTS for username;

Sometimes, you need to flush a user's privileges, as per immediate changes. For that, you can use:

FLUSH PRIVILEGES;

FAQs

What is a user in MySQL?
A user is a record in the USER table of the MySQL server. It contains the login information, account privileges, and host information for the MySQL account.
Why do we create a user in MySQL?
We create a user in MySQL for accessing and managing the databases. Moreover, we can decide the privileges of different users with different roles by creating and adding new users to the database.
What is the default username and password in MySQL?
By default, the user name in MySQL is 'root,' and there is no password. However, you can put a password during the process of installation.
How many users can be created in MySQL?
There is no such limitation on creating a number of users in the MySQL database. Users' accounts get stored in rows and columns, which consume some space. In theory, there can be infinite users, but in reality, you will hit resource boundaries. So, it depends on MySQL configuration settings.
What is the difference between SQL and MySQL?
SQL stands for Structured Query Language, and it's a language used by databases, whereas MySQL is an open-source relational database management system developed by Oracle, which is based on SQL.

Key Takeaways

After completing the article, you should have learned how to create and add new users to the MySQL database. You should have also understood the different privileges a user can have and how to grant special privileges to different users. But as it is said that you learn by practicing more, from here on, you can experiment by creating other users and changing their privileges for your database.

Recommended Readings:

MySQL Case Statement

You can also check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions.

Refer to the Guided Path to learn more about DBMS. You can visit Coding Ninjas Studio to practice programming problems for your complete interview preparation and land your dream job.