Introduction

MySQL is an open-source relational database management system (RDBMS), backed by Oracle and based on Structured Query Language (SQL). It is often considered the most popular for use with web applications and online publishing.

This article will give you a brief overview of how to manage users and fine-grained permissions on MySQL tables or databases. Additionally, the values in bold within the examples are the ones you will need to adapt to your requirements.

You must also have access to the MySQL monitor with a user who has been granted the necessary permissions. In this example, we will use the root user to log in:

# mysql -u root -p

Although you may have access for the root user disabled and will need to use the appropriate user, you can review the following article to learn How to recover the root password in MySQL. 

Below is the table of contents for basic user and permission management in MySQL:

Table of contents

• Creating a user

• Granting permissions

• Revoking permissions

• Deleting users

• Listing users

• Checking user permissions

• Changing a user's password

• Documentation

Creating a user

To create a local user, you can use the following statement:

mysql> CREATE USER 'usuario'@'localhost' IDENTIFIED BY 'contraseña';

For example, to create an external user with access from any location, we would specify:

mysql> CREATE USER 'usuario'@'%' IDENTIFIED BY 'contraseña';

You can also create the user from outside the MySQL monitor with the execution option: 

# mysql -u root -p -e "CREATE USER 'usuario'@'localhost' IDENTIFIED BY 'contraseña'"

For more details on how to create users in MySQL, click here.

Granting permissions

First, we will list the most common types of permissions in MySQL with a brief description:

• ALL PRIVILEGES: All privileges.

• CREATE: Allows creating new tables or databases.

• DROP: Allows deleting tables or databases.

• DELETE: Allows deleting rows from tables.

• INSERT: Allows inserting rows into tables.

• SELECT: Allows querying information from a database or table.

• UPDATE: Allows updating rows in tables.

• GRANT OPTION: Allows granting or removing privileges for other users.

The GRANT option lets you grant permissions to a user, and the syntax is as follows:

GRANT [Tipo de permiso] ON [Nombre de base de datos].[Nombre de tabla] TO [Usuario]

For example, to assign all privileges on all databases and tables to the local user created earlier:

GRANT ALL PRIVILEGES ON *.* TO 'usuario'@'localhost';

As another example, if a user needs permissions to retrieve information from a table in a specific database, it would be:

GRANT SELECT ON base_de_datos.tabla_especifica TO 'usuario'@'localhost';

For more details on how to grant permissions in MySQL, click here.

Deleting users

To delete a user, you must use the DROP option.

For example, to delete the local user created earlier, the syntax is as follows:

mysql> DROP USER 'usuario'@'localhost';

For more details on deleting users in MySQL, click here.

Revoking permissions

The REVOKE option lets you revoke a user's privileges. The syntax would be:

mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'usuario'@'localhost';

For more details on how to revoke permissions in MySQL, click here.

Listing users

Users created in MySQL will be stored in the "user" table within the "mysql" database. You can query it to display the users:

mysql> SELECT User, Host FROM mysql.user;

Checking user permissions

With the SHOW GRANTS option, you can review a user's permissions. The syntax is as follows:

mysql> SHOW GRANTS FOR 'usuario'@'localhost';

For more details on how to check permissions in MySQL, click here. 

Changing a user's password

With the ALTER USER option, you can change a user's password. The syntax is as follows:

# mysql -u root -p -e "ALTER USER 'usuario'@'localhost' IDENTIFIED BY 'nueva_contraseña'"

For more details on how to check permissions in MySQL, click here. 

Documentation

For more information, click here to go to the official MySQL documentation.

We hope this tutorial has helped you 🙂. Remember, if you have any questions about this or any other issue related to your servers on Clouding, feel free to write to soporte@clouding.io We're here for whatever you need!