En este post vamos a ver cómo gestionar usuarios MySQL desde la línea de comandos de MySQL. Tambie veremos lo más básico que debemos saber al respecto al crear, borrar y modificar los usuarios así como saber el modo de manejar y asignar o revocar los privilegios que cada usuario tendrá respecto a las bases de datos.
La gestión de usuarios también se puede realizar desde clientes, interfaces gráficas y aplicaciones web como por ejemplo:
phpMyAdmin, Workbench, heidi o el administrador gráfico de MySQL, en este caso vamos a lo que siempre tendremos a mano, la shell de MySQL.
Lo primero que debemos hacer es acceder a la shell de MySQL con un usuario que tenga privilegios de administración, en este caso lo hacemos como root y desde el propio host por línea de comandos:
# mysql -u root -p Enter password: ********** [....] mysql>
Creando usuario MySQL con CREATE USER
“CREATE USER” es un comando de MySQL que nos va a permitir crear usuarios y asignarles una contraseña con el parámetro “IDENTIFIED BY“. Más adelante veremos que este paso lo podemos evitar utilizando directamente el comando “GRANT” para la creación del usuario y asignación de privilegios en un único paso.
La sintaxis es la siguiente:
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password'];
Así pues, si quisieramos crear un usuario llamado “TBC” y con contraseña “thebigcomputer” ejecutaríamos el siguiente comando:
mysql> CREATE USER TBC IDENTIFIED BY 'thebigcomputer'; Query OK, 0 rows affected (0.01 sec)
Es importante tener en cuenta que siempre hay que poner «;» al final de cada sentencia, sino esta no se ejecutara.
Tambien tenemos la opción de especificar la contraseña con su valor en hash. Este valor debe ser generado anteriormente con la función PASSWORD() de MySQL:
mysql> SELECT PASSWORD('thebigcomputer'); +-------------------------------------------+ | PASSWORD('thebigcomputer') | +-------------------------------------------+ | *CE32R870801502ACAD44FA46CASDF2134UHAS234 | +-------------------------------------------+ 1 row in set (0.00 sec) mysql> CREATE USER TBC IDENTIFIED BY PASSWORD '*CE32R870801502ACAD44FA46CASDF2134UHAS234'; Query OK, 0 rows affected (0.01 sec)
Eliminando usuario en MySQL con DROP USER
Eliminar un usuario MySQL es sencillo, al utilizar el comando “DROP USER” seguido del nombre del usuario, conseguiremos eliminarlo de nuestra base de datos:
mysql> DROP USER TBC;
Query OK, 0 rows affected (0.00 sec)
Asignando privilegios al usuario con GRANT
Cuando creamos un usuario utilizando CREATE USER, efectivamente la creación es satisfactoria, pero veréis que el usuario únicamente puede conectarse y visualizar ciertos parámetros del servidor. La verdad es que podemos crear directamente los usuarios con el comando GRANT y a la vez asignar los privilegios que queramos respecto a las bases de datos del sistema. Básicamente GRANT asigna privilegios al usuarios indicado, y si no existe lo crea.
La sintaxis y modo de uso del comando sería la siguiente:
GRANT ON privilegios TO 'TBC'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
En el siguiente ejemplo, asignamos todos los privilegios posibles al usuario “TBC” (si no existe se crea) sobre todas las bases de datos y tablas, para conexiones desde localhost únicamente y además le concedemos el permiso GRANT para que tenga la opción de asignar permisos a otros usuarios:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'TBC'@'localhost' IDENTIFIED BY 'mipassword' WITH GRANT OPTION;
Recordad que tenemos comando HELP de la shell de MySQL os mostrará la ayuda relativa a cada comando. Si quieres encontrar ayuda del comando GRANT usaremos «HELP GRANT» .
Si quisiéramos asignar privilegios a un usuario que va a conectar desde distintos hosts, lo haríamos de modo individual, es decir:
mysql> GRANT ALL ON db1.* TO 'TBC'@'host1'; mysql> GRANT ALL ON db1.* TO 'TBC'@'host2';
Lo mismo para distintas bases de datos o tablas:
mysql> GRANT ALL ON db1.* TO 'TBC'@'localhost'; mysql> GRANT ALL ON db2.* TO 'TBC'@'localhost'; mysql> GRANT ALL ON db3.tabla1 TO 'TBC'@'localhost'; mysql> GRANT ALL ON db4.tabla1 TO 'TBC'@'localhost';
Para asignar un “wildcard” de hosts utilizamos el carácter %. Es decir, de esta forma el usuario podrá conectarse a MySQL de forma remota:
mysql> GRANT ALL ON db1.* TO 'foo'@'%';
Visualizando privilegios de un usuario con SHOW GRANTS
El comando SHOW GRANTS podremos visualizar los privilegios asignados a un usuario:
mysql> SHOW GRANTS for 'TBC'@'localhost';
+-------------------------------------------------------------------------------+
| Grants for TBC@localhost |
+-------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'TBC'@'localhost' IDENTIFIED BY PASSWORD '...' |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Revocando privilegios al usuario con REVOKE
Para eliminar privilegios que previamente hemos asignado a un usuario MySQL con el comando GRANT, podremos utilizar el comando REVOKE. La sintaxis sería la siguiente:
REVOKE privilegios, GRANT OPTION FROM 'usuario'@'host';
La opción “GRANT OPTION” sólo es necesaria si el usuario ha sido configurado con el privilegio “WITH GRANT OPTION”.
mysql> SHOW GRANTS for 'TBC'@'localhost';
+--------------------------------------------------------------------------------+
| Grants for TBC@localhost |
+--------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'TBC'@'localhost' IDENTIFIED BY PASSWORD '*CEE' |
| GRANT SELECT ON `test`.* TO 'TBC'@'localhost' |
+--------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> SHOW GRANTS for 'TBC'@'host1';
+--------------------------------------------------------------------------------+
| Grants for TBC@host1 |
+--------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'TBC'@'host1' IDENTIFIED BY PASSWORD '*CEE...' |
+--------------------------------------------------------------------------------+
1 row in set (0.01 sec)
Una vez visualizados los privilegios con el comando SHOW GRANTS, eliminamos los que estimemos oportuno:
mysql> REVOKE SELECT ON prueba.* FROM 'TBC'@'localhost';
Query OK, 0 rows affected (0.01 sec)
En el caso de ‘TBC’@’localhost’ vemos que tiene todos los privilegios para todas las bases de datos, en lugar de eliminar completamente esa entrada de privilegios podemos hacerla más restrictiva, por ejemplo manteniendo todos los permisos excepto INSERT:
mysql> REVOKE INSERT ON *.* FROM 'TBC'@'localhost';
Query OK, 0 rows affected (0.00 sec)
Y vemos como MySQL automáticamente elimina el privilegio INSERT y mantiene el resto:
mysql> SHOW GRANTS for 'TBC'@'%'; ERROR 1141 (42000): There is no such grant defined for user 'TBC' on host '%' mysql> SHOW GRANTS for 'TBC'@'localhost'; +---------------------------------------------------------------------------+ | Grants for TBC@localhost | +---------------------------------------------------------------------------+ | GRANT SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, | | FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, | | CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, | | REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE,| | CREATE USER, EVENT, TRIGGER ON *.* TO 'foo'@'localhost' IDENTIFIED BY | | PASSWORD '*CE32R870801502ACAD44FA46CASDF2134UHAS234 ' | +---------------------------------------------------------------------------+ 1 row in set (0.01 sec)
FLUSH PRIVILEGES
El comando “FLUSH PRIVILEGES” sirve para recargar la tabla de privilegios pero sólo es necesario cuando se manipulan directamente las tablas de privilegios ejecutando los comandos INSERT, DELETE, etc en lugar de usar los comandos GRANT y REVOKE:
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
Es decir, en los casos que he especificado en este artículo no es necesario utilizar este modo de recargar los privilegios, sólo sería necesario si crearemos o eliminaremos los usuarios “a mano” directamente sin los comandos específicos para ello de MySQL:
mysql> INSERT INTO user VALUES ('localhost','TBC',mipassword'),('Y','Y','Y','Y','Y','Y','N','N','N','Y','Y','Y','Y','Y');
