MySQL es un sistema de gestión de bases de datos claramente orientado a la web, y una de los síntomas en su arquitectura ha venido siendo que la
creación de los usuarios se realiza en la misma sentencia que el permiso (grant) de acceso a una o varias bases de datos. La orientación de MySQL va cambiando con el tiempo y el uso que se le da a las bases de datos cada vez trasciende más el entorno web, actualmente hay
tres formas de crear un usuario:
la forma clásica, con la sentencia GRANT
Utilizando la sentencia GRANT podemos crear un usuario a la par que otorgarle uno o varios privilegios sobre los objetos de una base de datos, o la base de datos completa.
Al encontrarse una sentencia de tipo GRANT, el motor de MySQL revisa si el usuario existe previamente para el contexto que estamos asignándole permisos, y si dicho usuario no está presente en el sistema, lo crea.
No entraré en detalles sobre todas las opciones que nos permite ejecutar la sentencia GRANT, sino solo en las que se refieren a la creación del usuario.
Pongamos un ejemplo, queremos crear el usuario adolfo para la base de datos test:
- Nos conectamos con un usuario que tenga privilegios, root, como propietario de la base de datos, los tiene.
$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.0.67 Source distribution
Nos conectamos utilizando -u para indicarle el usuario y si quisieramos indicarle un password, deberiamos poner -p (sin añadirle la contraseña), en este caso la cuenta root, al ser una máquina de desarrollo, está desprotegida.
- Lanzamos la sentencia GRANT, indicando los permisos que otorgamos, la base de datos y los objetos de la misma sobre los que estamos asignando privilegios, el nombre del usuario y el password:
mysql> GRANT SELECT, INSERT ON test.* TO 'adolfo'@'localhost' IDENTIFIED BY 'pass_adolfo';
En este ejemplo permitimos al usuario adolfo que seleccione (SELECT) e inserte (INSERT) en todos los objetos (*) de la base de datos test, además indicamos que el contexto sea la máquina local de la base de datos (localhost), lo que impedirá que el usuario se conecte desde otras máquinas, y finalmente asignamos un password mediante IDENTIFIED BY.
Si quisieramos que el usuario no tuviera un password, deberemos omitir la cláusula IDENTIFIED BY.
En el caso de que el modo SQL del servidor estuviera en NO_AUTO_CREATE_USER, la creación de usuarios no estaría permitida a no ser que tuvieran asignado un password no vacío.
- Una vez hecho esto, podremos conectarnos con nuestro usuario y realizar las acciones para las que hemos asignado permisos:
$ mysql -u adolfo -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.0.67 Source distribution
mysql> use test;
Database changed
mysql> select * from frutas;
+-----------+----------+
| nombre | color |
+-----------+----------+
| fresa | rojo |
| manzana | verde |
| uva | verde |
+-----------+----------+
3 rows in set (0,03 sec)
la sentencia CREATE USER
A partir de la versión MySQL 5.0.2 existe la posibilidad de crear usuarios sin necesidad de asignarles privilegios, utilizando la sentencia CREATE USER.
Por ejemplo, para crear el usuario fernando:
$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.0.67 Source distribution
mysql> CREATE USER 'fernando'@'localhost' IDENTIFIED BY 'fer_pass';
Query OK, 0 rows affected (0,00 sec)
Al igual que con la sentencia GRANT, el contexto 'localhost' define que el usuario solamente se puede conectar desde el servidor de MySQL, y el IDENTIFIED BY define el password del usuario, se puede omitir, para un usuario sin password, siempre que el modo SQL no sea NO_AUTO_CREATE_USER.
Conexión con el usuario, utilizando la opción -p:
$ mysql -u fernando -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.0.67 Source distribution
Los privilegios necesarios para ejecutar la sentencia CREATE USER son CREATE USER o bien INSERT en la base de datos mysql.
El usuario recién creado no tiene privilegio alguno, por lo que deberemos asignarle permisos utilizando sentencias GRANT(esta vez sin la cláusula IDENTIFIED BY).
modo hardcore: insertando en la tabla users
Este es un método que MySQL no recomienda demasiado, es un poco más complejo que los otros dos, pero va bien a la hora de resolver problemas, como que por ejemplo alguno de las formas anteriores esté dando algún problema extraño.
Para ello es necesario un usuario con privilegio INSERT en la base de datos mysql. También debo decir que se ha de tener mucho cuidado con esta base de datos, ya que contiene toda la información de usuarios y permisos.
Ejemplo de creación del usuario mariano usando INSERT en nuestra base de datos. Nos conectamos con un usuario con privilegios, en este caso root, y seleccionamos la base de datos mysql mediante la sentencia USE.
$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.0.67 Source distribution
mysql> use mysql
Database changed
Y después realizamos la sentencia de inserción para añadir nuestro usuario:
mysql> INSERT INTO user VALUES('localhost','mariano',PASSWORD('pass_mariano'),'Y','Y',
'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N',
'N','N','N','','','','',0,0,0,0);
Query OK, 1 row affected (0,00 sec)
Es necesario llamar a la función PASSWORD() para almacenar el password codificado, en los otros casos, el IDENTIFIED BY se encarga de hacer la codificación.
En este caso se le dan permisos globales de INSERT y SELECT, para saber a qué corresponde cada columna, se puede hacer un DESCRIBE user.
mysql> DESCRIBE user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
(...)
Para asignar privilegios a bases de datos específicas o tablas específicas, se debe usar GRANT.
Utilizando este método, tenemos que forzar que se refresquen las tablas de permisos usando FLUSH PRIVILEGES.
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0,01 sec)
Una vez hecho esto, ya nos podremos conectar:
$ mysql -u mariano -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 5.0.67 Source distribution
Más información sobre el uso de este método, en comparación con el GRANT, en la
documentación de mysql.
Para que un usuario pueda hacer algo más que consultar algunas variables del sistema debe tener algún privilegio. Lo más simple es conceder el privilegio para seleccionar datos de una tabla concreta. Esto se haría así:
La misma sentencia
GRANT se usa para añadir privilegios a un usuario existente.
mysql> GRANT SELECT ON prueba.gente TO anonimo;
Query OK, 0 rows affected (0.02 sec)
Esta sentencia concede al usuario 'anonimo' el privilegio de ejecutar sentencias
SELECT sobre la tabla 'gente' de la base de datos 'prueba'.
Un usuario que abra una sesión y se identifique como 'anonimo' podrá ejecutar estas sentencias:
mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| prueba |
+----------+
1 row in set (0.01 sec)
mysql> USE prueba;
Database changed
mysql> SHOW TABLES;
+------------------+
| Tables_in_prueba |
+------------------+
| gente |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM gente;
+----------+------------+
| nombre | fecha |
+----------+------------+
| Fulano | 1985-04-12 |
| Mengano | 1978-06-15 |
| Tulano | 2001-12-02 |
| Pegano | 1993-02-10 |
| Pimplano | 1978-06-15 |
| Frutano | 1985-04-12 |
+----------+------------+
6 rows in set (0.05 sec)
mysql>
Como se ve, para este usuario sólo existe la base de datos 'prueba' y dentro de esta, la tabla 'gente'. Además, podrá hacer consultas sobre esa tabla, pero no podrá añadir ni modificar datos, ni por supuesto, crear o destruir tablas ni bases de datos.
Para conceder privilegios globales se usa ON *.*, para indicar que los privilegios se conceden en todas las tablas de todas las bases de datos.
Para conceder privilegios en bases de datos se usa ON nombre_db.*, indicando que los privilegios se conceden sobre todas las tablas de la base de datos 'nombre_db'.
Usando ON nombre_db.nombre_tabla, concedemos privilegios de nivel de tabla para la tabla y base de datos especificada.
En cuanto a los privilegios de columna, para concederlos se usa la sintaxis tipo_privilegio (lista_de_columnas), [tipo_privilegio (lista_de_columnas)].
Otros privilegios que se pueden conceder son:
- ALL: para conceder todos los privilegios.
- CREATE: permite crear nuevas tablas.
- DELETE: permite usar la sentencia DELETE.
- DROP: permite borrar tablas.
- INSERT: permite insertar datos en tablas.
- UPDATE: permite usar la sentencia UPDATE.
Para ver una lista de todos los privilegios existentes consultar la sintaxis de la sentencia
GRANT.
Se pueden conceder varios privilegios en una única sentencia. Por ejemplo:
mysql> GRANT SELECT, UPDATE ON prueba.gente TO anonimo IDENTIFIED BY 'clave';
Query OK, 0 rows affected (0.22 sec)
mysql>
Un detalle importante es que para crear usuarios se debe tener el privilegio GRANT OPTION, y que sólo se pueden conceder privilegios que se posean.
Revocar privilegios
Para revocar privilegios se usa la sentencia
REVOKE.
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON
FROM user [, user] ...
La sintaxis es similar a la de
GRANT, por ejemplo, para revocar el privilegio
SELECT de nuestro usuario 'anonimo', usaremos la sentencia:
mysql> REVOKE SELECT ON prueba.gente FROM anonimo;
Query OK, 0 rows affected (0.05 sec).
Mostrar los privilegios de un usuario
Podemos ver qué privilegios se han concedido a un usuario mediante la sentencia
SHOW GRANTS. La salida de esta sentencia es una lista de sentencias
GRANT que se deben ejecutar para conceder los privilegios que tiene el usuario. Por ejemplo:
mysql> SHOW GRANTS FOR anonimo;
+--------------------------------------------------------------------+
| Grants for anonimo@% |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'anonimo'@'%' IDENTIFIED BY PASSWORD '*5...' |
| GRANT SELECT ON `prueba`.`gente` TO 'anonimo'@'%' |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
Nombres de usuarios y contraseñas
Como podemos ver por la salida de la sentencia
SHOW GRANTS, el nombre de usuario no se limita a un nombre simple, sino que tiene dos partes. La primera consiste en un nombre de usuario, en nuestro ejemplo 'anonimo'. La segunda parte, que aparece separada de la primera por el carácter '@' es un nombre de máquina (host). Este nombre puede ser bien el de una máquina, por ejemplo, 'localhost' para referirse al ordenador local, o cualquier otro nombre, o bien una ip.
La parte de la máquina es opcional, y si como en nuestro caso, no se pone, el usuario podrá conectarse desde cualquier máquina. La salida de
SHOW GRANTS lo indica usando el comodín '%' para el nombre de la máquina.
Si creamos un usuario para una máquina o conjunto de máquinas determinado, ese usuario no podrá conectar desde otras máquinas. Por ejemplo:
mysql> GRANT USAGE ON * TO anonimo@localhost IDENTIFIED BY 'clave';
Query OK, 0 rows affected (0.00 sec)
Un usuario que se identifique como 'anonimo' sólo podrá entrar desde el mismo ordenador donde se está ejecutando el servidor.
En este otro ejemplo:
mysql> GRANT USAGE ON * TO anonimo@10.28.56.15 IDENTIFIED BY 'clave';
Query OK, 0 rows affected (0.00 sec)
El usuario 'anonimo' sólo puede conectarse desde un ordenador cuyo IP sea '10.28.56.15'.
Aunque asignar una constraseña es opcional, por motivos de seguridad es recomendable asignar siempre una.
La contraseña se puede escribir entre comillas simples cuando se crea un usuario, o se puede usar la salida de la función
PASSWORD() de forma literal, para evitar enviar la clave en texto legible.
Si al añadir privilegios se usa una clave diferente en la cláusula IDENTIFIED BY, sencillamente se sustituye la contraseña por la nueva.
Borrar usuarios
Para eliminar usuarios se usa la sentencia
DROP USER.
No se puede eliminar un usuario que tenga privilegios, por ejemplo:
mysql> DROP USER anonimo;
ERROR 1268 (HY000): Can't drop one or more of the requested users
mysql>
Para eliminar el usuario primero hay que revocar todos sus privilegios:
mysql> SHOW GRANTS FOR anonimo;
+--------------------------------------------------------------------+
| Grants for anonimo@% |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'anonimo'@'%' IDENTIFIED BY PASSWORD '*5...' |
| GRANT SELECT ON `prueba`.`gente` TO 'anonimo'@'%' |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> REVOKE SELECT ON prueba.gente FROM anonimo;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP USER anonimo;
Query OK, 0 rows affected (0.00 sec)
mysql>
