A typical db host should have these GRANTs:
Grants for wikiadmin@10.% GRANT PROCESS, REPLICATION CLIENT ON *.* TO `wikiadmin`@`10.%` IDENTIFIED BY PASSWORD '*redacted' GRANT SELECT, EXECUTE ON `sys`.* TO `wikiadmin`@`10.%` GRANT SELECT ON `performance_schema`.* TO `wikiadmin`@`10.%` GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `%wik%`.* TO `wikiadmin`@`10.%` GRANT SELECT ON `heartbeat`.`heartbeat` TO `wikiadmin`@`10.%` Grants for wikiadmin@localhost GRANT PROCESS, REPLICATION CLIENT ON *.* TO `wikiadmin`@`localhost` IDENTIFIED BY PASSWORD '*redacted' GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `%wik%`.* TO `wikiadmin`@`localhost`
I wrote a script to analyze grants and here are the results of analyzing 210 dbs:
General issues
DB unavaiable
No further check was done on this db
- db2098.codfw.wmnet:3317 (s7)
- db2098.codfw.wmnet:3318 (s8)
10.% user missing
No further check was done on this grants of 10.% user in these dbs
- db1133.eqiad.wmnet:3306 (s1)
- db1128.eqiad.wmnet:3306 (s1)
- db1125.eqiad.wmnet:3306 (s4)
- db1124.eqiad.wmnet:3306 (s4)
Expected - Test hosts
localhost user missing
No further check was done on this grants of localhost user in these dbs
- db2097.codfw.wmnet:3311 (s1)
- db1163.eqiad.wmnet:3306 (s1)
- db1140.eqiad.wmnet:3311 (s1)
- db1139.eqiad.wmnet:3311 (s1)
- db1105.eqiad.wmnet:3311 (s1)
- db1102.eqiad.wmnet:3312 (s2)
- db2101.codfw.wmnet:3315 (s5)
- db1140.eqiad.wmnet:3316 (s6)
Expected
Localhost
appserver grant missing
- db1100.eqiad.wmnet:3306 (s5)
Expected
replication grant missing
None!
Extra grant #1
The extra grant:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `%wik%`.* TO `wikiadmin`@`localhost`
- db1100.eqiad.wmnet:3306 (s5)
Expected
Extra grant #2
The extra grant:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `centralauth`.* TO `wikiadmin`@`localhost`
Expected
10.%
appserver grant missing
- db1100.eqiad.wmnet:3306 (s5)
Expected
replication grant missing
None!
sys grant missing
None!
heartbeat grant missing
None!
performance_schema grant missing
None!
Extra grant #1
The extra grant:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `%wik%`.* TO `wikiadmin`@`10.%`
- db1100.eqiad.wmnet:3306 (s5)
Expected
Extra grant #2
The extra grant:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `centralauth`.* TO `wikiadmin`@`10.%`
Expected