MySQL allows the configuration of users for different databases, so only authorized users will be able to perform certain operations on defined databases.
It should be noted that a user is different from another even if they have the same name when the source IP is different, this will make MySQL accounts more secure since if the connection does not come from an allowed IP for that user, access will be denied.
Check users and permissions:
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 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Create users:
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)
The USAGE permissions are for MySQL to be aware that the user exists without assigning any real permission.
Remove permissions and delete user:
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)
Change user password:
ALTER USER user01@'localhost' IDENTIFIED BY 'YYYY';
With this, we can control access to our databases. However, it is not a bad idea to apply firewall rules allowing access to port 3306 only from trusted IPs.