Creating a MySQL user and setting permissions

What is MySQL?

MySQL is a widely used database management system that allows users to efficiently store, manage and retrieve data. It is known for its reliability, ease of use and high performance, making it a popular choice for both small and large applications. In the context of creating a MySQL user and setting permissions, understanding how MySQL works is very important to ensure data security and integrity.

Creating a MySQL user

When a new user is created in MySQL, it is very important to assign appropriate permissions to him/her so that he/she can access only necessary databases and tables. This will help prevent unauthorized access and protect sensitive information from being leaked. MySQL provides a flexible permissions system that allows database administrators to define specific privileges for users, such as granting the ability to read, write, or perform certain operations.

Creating a new MySQL user is performed inside the MySQL shell, which can be accessed from the console with the command:
mysql -u root -pNext, you will need to enter or paste the root user password.

The SQL query (or command) to create a user looks like this (already inside the MySQL shell):
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

• newuser - user name;
• localhost - hostname, can also be specified in IP address format;
• password - this user's password.

As a result, we get a user without any rights to perform actions. Moreover, newuser user will not even be able to authorize in MySQL shell.

Next, it is necessary to set access rights for this user. Allow all, you can do with a simple command:
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';This gives the user the rights to read, edit, run and perform any task for all databases and tables.
The first asterisk here indicates access rights to all databases, the second asterisk indicates access rights to all tables.

It is important to realize that with the command above we have given the newuser user full access to the entire contents of our entire database, which in most cases may not be safe. We will discuss how to set more restricted permissions in this article below.

After setting permissions for MySQL user, it is necessary to reset all privileges to activate newuser, which can be done with the command:
FLUSH PRIVILEGES;

Setting MySQL user permissions

Users may be granted the following rights:

• ALL PRIVILEGES — full rights to everything (described above);
• CREATE — user can create new tables and/or databases;
• DROP — user can delete tables and/or databases;
• DELETE — user can delete rows from specified tables (or all tables);
• INSERT — user can insert rows into specified tables (or all tables);
• SELECT — user can make selections to read data from specified tables (or all tables);
• UPDATE — user can update rows of specified tables (or all tables);
• GRANT OPTION — user can manage the rights of other users.

The command to set the necessary permissions for a MySQL user will have the following form:
GRANT permission ON database.table TO 'newuser'@'localhost';On this command:

• permission - must be replaced with the appropriate permissions, such as SELECT;
• database - database name. You can specify an asterisk when accessing all databases;
• table - table name. You can specify an asterisk when accessing all tables.

Each time you change privileges, you must reset privileges again as described earlier in this article.

To revoke any rights a similar command is used, only GRANT should be replaced by REVOKE and TO should be replaced by FROM:
REVOKE permission ON database.table FROM 'newuser'@'localhost';You can view the current permissions with the SQL command:
SHOW GRANTS FOR 'newuser'@'localhost';You can delete an existing user using the next command:
DROP USER 'newuser'@'localhost';To exit the MySQL shell, use the command:
quit;

Conclusion

In the process of creating a new MySQL user, it is important to consider not only its name and password, but also the assignment of specific privileges that are required to perform tasks related to working with data. Setting permissions helps to ensure data security and protection, minimizing the risks of unauthorized access and database errors. Proper distribution of access rights helps improve team performance, allowing each user to focus on their responsibilities without worrying about access to unnecessary functions. Creating users and correctly configuring their permissions is not just a technical task, but a mandatory step on the way to secure data management in MySQL.

And don't forget to end each command with a semicolon when working in the MySQL shell.