13.09.2023

How to Create MySQL User and Grant Privileges?

Introduction

Databases play a crucial role in efficiently storing and managing vast volumes of data. They streamline the process of data updates, enhancing reliability and accuracy while minimizing redundancy. These are some of the compelling reasons why databases are indispensable:

Databases find application in various domains, exemplifying their versatility:

In essence, databases are indispensable tools for the efficient management of large datasets across diverse environments. Whether the goal is data storage, organization, security, management, or analysis, a database proves to be an essential asset.

User creation

Let's examine MySQL, assuming it's already installed as database software with administrative privileges. To begin, we'll initiate access to the MySQL system by entering the following command:

mysql -u root -p -h localhost

Screenshot №1 — Login

Now we can create user in several ways by using CLI of client MySQL or GUI web to interact. In that instruction we will consider CLI version of interaction with server, for create type command below:

CREATE USER 'ff'@'localhost' Identified by 'password';

And on the screenshot below we can see next command to set privileges:

GRANT ALL on *.* to 'ff'@'localhost';

Screenshot №2 — Creation of user

The 'ff'@'localhost' means 'name_of_user'@'host' which you use to connect to the DBMS. In the next command ALL means all rights to the setting databases, you can grant privileges on the table or object level by turning on parameter. For example, GRANT ALL on product. that means we grant access to the determine table witn label product! Also you can grant only limited list of rights and commands. For example, GRANT create,drop,insert. Let's check that:

Grant CREATE,DROP on product.* to 'ff'@'localhost';

Screenshot №3 — Grant

Query OK means all command are execute properly!

Conclusion

In this instruction, we explored the process of creating a user in MySQL, emphasizing the significance of access control and privileges, which are fundamental aspects of database administration. By understanding these principles, users can harness the full potential of their databases to effectively manage and utilize their data resources.