We were representing one of our audit report to one of our customers and he had some interesting question about the mysql default system users. So we thought it would be worth sharing our learning as a short note.
The questions were like
what are these users called and used for ?
These are called reserved users in mysql. They gets created during the data directory initialization.
5.7
mysql> select user,host,authentication_string,account_locked from mysql.user where account_locked='Y';
+---------------+-----------+-------------------------------------------+----------------+
| user | host | authentication_string | account_locked |
+---------------+-----------+-------------------------------------------+----------------+
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | Y |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | Y |
+---------------+-----------+-------------------------------------------+----------------+
8.0mysql> select user,host,authentication_string,account_locked from mysql.user where account_locked='Y'; +------------------+-----------+------------------------------------------------------------------------+----------------+ | user | host | authentication_string | account_locked | +------------------+-----------+------------------------------------------------------------------------+----------------+ | mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | Y | | mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | Y | | mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | Y | +------------------+-----------+------------------------------------------------------------------------+----------------+
what if someone hacks this user ?
These are locked users and they cannot be used for client connections. These users passwords are invalid passwords so even if someone tries to login its impossible to login with these passwords.
[root@genex-monitoring ec2-user]# mysql -u mysql.sys -p
Enter password:
ERROR 1045 (28000): Access denied for user 'mysql.sys'@'localhost' (using password: YES)
Error log Messages
=====================
2023-03-02T15:58:13.695784Z 161 [Note] Access denied for user 'mysql.sys'@'localhost' (using password: YES)
2023-03-02T15:58:22.326059Z 162 [Note] Access denied for user 'mysql.sys'@'localhost' (using password: YES)what can mysql possibly be able to do with these users ?
- mysql.sys@localhost user is used by sys schema all the views , procedures and functions.
- mysql.session@localhost User is used internally by plugins to access the server.
- mysql.infoschema@localhost User is used as definer for all information_schema views.
Can we drop reserved users ?
Yes you can drop these users without any issues however if you drop mysql.sys@localhost user then sys database views,procedures and functions wouldn’t work still we don’t recommend to drop any system users.
mysql> drop user `mysql.session`;
ERROR 1396 (HY000): Operation DROP USER failed for 'mysql.session'@'%'
mysql> drop user `mysql.sys`@localhost;
Query OK, 0 rows affected, 3 warnings (0.01 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------------+
| Warning | 4005 | User 'mysql.sys'@'localhost' is referenced as a definer account in a view. |
| Warning | 4005 | User 'mysql.sys'@'localhost' is referenced as a definer account in a stored routine. |
| Warning | 4005 | User 'mysql.sys'@'localhost' is referenced as a definer account in a trigger. |
+---------+------+--------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> drop user `mysql.session`@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> show warnings;
Empty set (0.00 sec)
mysql> drop user `mysql.infoschema`@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> show warnings;
Empty set (0.00 sec)
mysql> exit
Bye
Reserved User Create statement for reference
mysql> show create user `mysql.infoschema`@localhost;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for mysql.infoschema@localhost |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| CREATE USER `mysql.infoschema`@`localhost` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create user `mysql.session`@localhost;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for mysql.session@localhost |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER `mysql.session`@`localhost` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create user `mysql.sys`@localhost;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for mysql.sys@localhost |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER `mysql.sys`@`localhost` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)Conclusion
Hope you find this information useful. Keep reading and follow us for more exclusive content for open source databases. Do contact us if you would like to get your database instance audited we will be happy to assist you in securing and improving your database infrastructure footprint.
Cheers !! Enjoy MySQL !!!