Cómo asignar privilegios a usuarios en MySQL o MariaDB

¿QUÉ SON LOS PRIVILEGIOS EN MYSQL/MARIADB?

Los usuarios de MySQL/MariaDB se utilizan para conectar a un servidor de base de datos (SGBD). MySQL y MariaDB disponen de su propio sistema de permisos, y cada vez que necesitemos acceder a una base de datos, deberemos iniciar sesión con un nombre de usuario y una contraseña correcta. Cada vez que creemos una nueva base de datos MySQL/MariaDB, deberemos especificar un nombre de usuario y contraseña para acceder.

Por seguridad, es necesario asignar correctamente los privilegios de los usuarios

1.- ELEMENTOS NECESARIOS PARA ESTE TUTORIAL

• Un PC con cualquier Sistema Operativo
• Servidor MySQL/MariaDB y acceso de administrador al SGBD de MySQL/MariaDB

2.- CONECTAR AL SGBD (SISTEMA GESTOR DE BASES DE DATOS)

El primer paso es conectar al servidor de bases de datos o SGBD. Para ello, desde consola y desde cualquier Sistema Operativo e independientemente si el SGBD es MySQL o MariaDB, ejecutaremos un comando -como el siguiente- respetando la sintaxis y los espacios. La sintaxis es la siguiente:

raul@debian:~$ mysql -h [equipo] -u [usuario] -p

Por ejemplo, para conectar al servidor local ubicado en la misma máquina, con el usuario administrador root, introduciremos lo siguiente:

raul@debian:~$ mysql -u root -p
Enter password: ********
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 318
Server version: 10.4.14-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

3.- LISTAR TODOS LOS USUARIOS

La tabla user es donde el SGBD guarda toda la información relativa a los usuarios.

Si deseamos listar todos los usuarios y el host desde el que pueden acceder, simplemente debemos seleccionar dicha tabla y la columna "User". La tabla user pertenece a la tabla mysql. En el siguiente ejemplo, mostramos el nombre de todos los usuarios almacenados en la tabla user:

MariaDB [(none)]> SELECT User,Host FROM mysql.user;
+----------+------------------+
| User     | Host             |
+----------+------------------+
| newuser  | %                |
| remote   | %                |
| telegraf | %                |
| username | %                |
| root     | 127.0.0.1        |
| root     | ::1              |
| username | host_or_wildcard |
| newuser  | localhost        |
| pma      | localhost        |
| remote   | localhost        |
| root     | localhost        |
| sysadmin | localhost        |
| telegraf | localhost        |
+----------+------------------+
13 rows in set (0.018 sec)

4.- CREAR USUARIO DE EJEMPLO

Para no modificar los privilegios actuales de los usuarios ya existentes en el sistema, crearemos un usuario llamado "antonio" que podrá acceder solamente desde "localhost" al SGBD. Para ello, ejecutaremos el siguiente comando desde el SGBD:

MariaDB [(none)]> CREATE USER 'antonio'@'localhost' IDENTIFIED BY 'antonio';
Query OK, 0 rows affected (0.048 sec)

5.- MOSTRAR LOS PRIVILEGIOS DEL USUARIO

Ahora, "antonio" tiene acceso con la contraseña "antonio" a entrar al SGBD pero no a usar las bases de datos. Para ver los privilegios que tiene el usuario "antonio" ejecutaremos el comando:

MariaDB [(none)]> SHOW GRANTS FOR 'antonio'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for antonio@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `antonio`@`localhost` IDENTIFIED BY PASSWORD '*E31BAC86033F438E42D17F5DF3815C36C657D253' |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

6.- ASIGNAR PRIVILEGIOS

Existen diferentes tipos de privilegios que podemos asignar a los usuarios de un SGBD con MySQL/MariaDB. A continuación se detallan todos ellos y que hace cada uno de ellos:

• ALL PRIVILEGES: Como vimos antes, esto le otorgaría a un usuario de MySQL acceso completo a una base de datos designada (o si no se selecciona ninguna base de datos, acceso global a todo el sistema)
• CREATE: Permite crear nuevas tablas o bases de datos
• DROP: Permite eliminar tablas o bases de datos
• DELETE: Permite eliminar filas de las tablas
• INSERT: Permite insertar filas en las tablas
• SELECT: Les permite usar el comando SELECT para leer las bases de datos
• UPDATE: Permite actualizar las filas de las tablas
• GRANT OPTION: Permite otorgar o eliminar privilegios de otros usuarios

Si queremos conceder todos los privilegios al usuario "antonio" y en la base de datos "test", ejecutaremos:

MariaDB [(none)]> GRANT ALL PRIVILEGES ON test.* TO 'antonio'@'localhost';
Query OK, 0 rows affected (0.017 sec)

Volvemos a mostrar la lista de privilegios del usuario y vemos que ahora aparecen nuevos privilegios sobre la base de datos "test":

MariaDB [(none)]> SHOW GRANTS FOR 'antonio'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for antonio@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `antonio`@`localhost` IDENTIFIED BY PASSWORD '*E31BAC86033F438E42D17F5DF3815C36C657D253' |
| GRANT ALL PRIVILEGES ON `test`.* TO `antonio`@`localhost`                                                      |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)

Si queremos conceder permisos de insertar, actualizar, borrar y listar al usuario "antonio" en la base de datos "gestco", ejecutaremos:

MariaDB [(none)]> GRANT INSERT,UPDATE,DELETE,SELECT ON gestco.* TO 'antonio'@'localhost';
Query OK, 0 rows affected (0.029 sec)

Volvemos a mostrar la lista de privilegios del usuario y vemos que ahora aparecen nuevos privilegios sobre la base de datos "gestco":

MariaDB [(none)]> SHOW GRANTS FOR 'antonio'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for antonio@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `antonio`@`localhost` IDENTIFIED BY PASSWORD '*E31BAC86033F438E42D17F5DF3815C36C657D253' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `gestco`.* TO `antonio`@`localhost`                                    |
| GRANT ALL PRIVILEGES ON `test`.* TO `antonio`@`localhost`                                                      |
+----------------------------------------------------------------------------------------------------------------+
4 rows in set (0.049 sec)

Si queremos conceder privilegios de solo lectura al usuario "antonio" y en la base de datos "pruebas", ejecutaremos:

MariaDB [(none)]> GRANT SELECT ON pruebas.* TO 'antonio'@'localhost';
Query OK, 0 rows affected (0.016 sec)

Volvemos a mostrar la lista de privilegios del usuario y vemos que ahora aparecen nuevos privilegios de solo lectura sobre la base de datos "pruebas":

MariaDB [(none)]> SHOW GRANTS FOR 'antonio'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for antonio@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `antonio`@`localhost` IDENTIFIED BY PASSWORD '*E31BAC86033F438E42D17F5DF3815C36C657D253' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `gestco`.* TO `antonio`@`localhost`                                    |
| GRANT ALL PRIVILEGES ON `test`.* TO `antonio`@`localhost`                                                      |
| GRANT SELECT ON `pruebas`.* TO `antonio`@`localhost`                                                           |
+----------------------------------------------------------------------------------------------------------------+
4 rows in set (0.049 sec)

7.- MODIFICAR PRIVILEGIOS DE USUARIO

Para modificar los privilegios de un usuario sobre una base de datos, es tan sencillo como volver a asignarle nuevos privilegios. Los viejos privilegios se perderán y se asignaran los nuevos. Por ejemplo, si queremos que el usuario "antonio" tenga permisos de insertar, actualizar y seleccionar pero sin permisos de borrar, ejecutaremos:

MariaDB [(none)]> GRANT INSERT,UPDATE,SELECT ON gestco.* TO 'antonio'@'localhost';
Query OK, 0 rows affected (0.019 sec)

Volvemos a mostrar la lista de privilegios del usuario y vemos que ahora aparecen los nuevos privilegios y el usuario ya no tiene permisos para borrar datos de la base de datos "pruebas":

MariaDB [(none)]> SHOW GRANTS FOR 'antonio'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for antonio@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `antonio`@`localhost` IDENTIFIED BY PASSWORD '*E31BAC86033F438E42D17F5DF3815C36C657D253' |
| GRANT SELECT, INSERT, UPDATE ON `gestco`.* TO `antonio`@`localhost`                                            |
| GRANT ALL PRIVILEGES ON `test`.* TO `antonio`@`localhost`                                                      |
| GRANT SELECT ON `vivelamagia`.* TO `antonio`@`localhost`                                                       |
+----------------------------------------------------------------------------------------------------------------+
4 rows in set (0.002 sec)

También podemos revocar los privilegios con el comando REVOKE. Si queremos quitar los privilegios de insertar, actualizar y borrar al usuario "antonio" sobre la base de datos "gestco", ejecutaremos:

MariaDB [(none)]> REVOKE INSERT,UPDATE,DELETE ON gestco.* FROM 'antonio'@'localhost';
Query OK, 0 rows affected (0.008 sec)

Volvemos a mostrar la lista de privilegios del usuario y vemos que ahora aparecen los nuevos privilegios y el usuario solamente tiene permiso de lectura en base de datos "pruebas":

MariaDB [(none)]> SHOW GRANTS FOR 'antonio'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for antonio@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `antonio`@`localhost` IDENTIFIED BY PASSWORD '*E31BAC86033F438E42D17F5DF3815C36C657D253' |
| GRANT SELECT ON `gestco`.* TO `antonio`@`localhost`                                                            |
| GRANT ALL PRIVILEGES ON `test`.* TO `antonio`@`localhost`                                                      |
| GRANT SELECT ON `vivelamagia`.* TO `antonio`@`localhost`                                                       |
+----------------------------------------------------------------------------------------------------------------+
4 rows in set (0.000 sec)

ENJOY!