MySQL permite la configuración de usuarios para las distintas bases de datos, de este modo solo los usuarios autorizados serán capaces de realizar ciertas operaciones sobre las bases de datos definidas.
Hay que tener en cuenta que un usuario es distinto de otro aunque se llame igual cuando la ip de origen es distinta, esto hará que las cuentas MySQL sean mas seguras ya que si la conexión no proviene de una ip permitida para ese usuario se le denegará el acceso.
Consultar usuarios y permisos:
USE mysql;
SELECT User,Host from user;
+------------------+-----------+
| User | Host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
SHOW GRANTS FOR 'root'@'localhost';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, 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, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Crear usuarios:
USE mysql;
CREATE USER user00@'%' IDENTIFIED BY 'XXXX';
CREATE USER user01@'localhost' IDENTIFIED BY 'XXXX';
CREATE USER user02@'localhost' IDENTIFIED BY 'XXXX';
GRANT ALL PRIVILEGES ON prueba.* TO user00@'%';
GRANT SELECT ON prueba.* TO user01@'localhost';
GRANT SELECT, INSERT, DELETE ON prueba.* TO user02@'localhost';
SHOW GRANTS FOR user00@'%';
+----------------------------------------------------+
| Grants for user00@% |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `user00`@`%` |
| GRANT ALL PRIVILEGES ON `prueba`.* TO `user00`@`%` |
+----------------------------------------------------+
2 rows in set (0.00 sec)
SHOW GRANTS FOR user01@'localhost';
+----------------------------------------------------+
| Grants for user01@localhost |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `user01`@`localhost` |
| GRANT SELECT ON `prueba`.* TO `user01`@`localhost` |
+----------------------------------------------------+
2 rows in set (0.01 sec)
SHOW GRANTS FOR user02@'localhost';
+--------------------------------------------------------------------+
| Grants for user02@localhost |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `user02`@`localhost` |
| GRANT SELECT, INSERT, DELETE ON `prueba`.* TO `user02`@`localhost` |
+--------------------------------------------------------------------+
2 rows in set (0.01 sec)
Los permisos USAGE son para que MySQL sea consciente de que el usuario existe sin asignarle ningún permiso real.
Eliminar permisos y eliminar usuario:
REVOKE ALL PRIVILEGES ON *.* FROM user02@'localhost';
DROP USER user02@'localhost';
USE mysql;
SELECT user, host FROM user WHERE user='user02';
Empty set (0.00 sec)
Cambiar password usuario:
ALTER USER user01@'localhost' IDENTIFIED BY 'YYYY';
Con esto podremos tener controlados los accesos a nuestras bases de datos, de todos modos no está de mas que apliquemos reglas de firewall permitiendo el acceso al puerto 3306 solo desde ips de confianza.