In MySQL, we can define the access that users have, both at the database level and the table level. We can define what operations each user is allowed to perform per database and per table. We will learn how to do it in this article.
How to create a user
We can create a new user in two (2) ways, using the graphical interface of PHPMyAdmin or through the execution of a command line.
Create a SQL user with the graphical interface of PHPMyAdmin
To create a user with the PHPMyAdmin interface, we must click on the “User accounts” Tab and then click on the “Add user account” link.
The next screen has several sections. In the first section “Login Information“, we have to fill in the basic information of the new user such as User Name and Password.
The privilege section is as follows. In this section, we define if the new user will be allowed to perform the following operations:
- Data Operations: Will the new user be able to execute a SELECT, INSERT a new record, UPDATE one or more fields, or DELETE records? These privileges are separately granted, and one or more of these privileges can be granted to the user.
- Changes to Structure: Will the new user be able to modify the structure of the database? This involves creating or modifying tables, indexes, views, etc.
- Administration: Will the new user be able to manage the database? This allows for advanced tasks such as granting privileges to other users, user management, table locking, etc.
- Resource limits: Are there any limitations imposed on this user? This includes setting limits to a user, such as querying limits, connectivity limits, record update limit, etc.
There is also the option of granting all the privileges to the new user by checking the option “Check all”.
PHPMyAdmin user privileges.
After assigning the basic user information and privileges, we have now created a new user using the graphical interface of PHPMyAdmin.
Create a user in MySQL via Command Line
If you are familiar with the execution of command lines, we can also create a user through this method. If we want to replicate the previous example, in which we create a powerful user with access to all the tables and all the privileges assigned, we run the command as follows:
However, we do not always want to give full access to a user. Imagine that we only want to grant the new user access to make queries. To achieve this goal we must execute the following commands:
First, we remove any grant that was previously given to this user. This ensures us that we are only granting the necessary privileges. Next, we grant the SELECT privilege on this user.
Now, if we want the user to be able to select, insert, and update records but we want to prevent the user from deleting them, we run the following commands: