I am trying to tune MySQL using MySQLTuner (http://mysqltuner.com/). When I run it on my Koha server I get a bunch of warnings like this: [!!] User 'koha_x@%' hasn't specific host restriction. [!!] User 'koha_y@%' hasn't specific host restriction. [!!] User 'koha_z@%' hasn't specific host restriction. And in mysql.user I have: | % | koha_x | | % | koha_y | | % | koha_z | | localhost | koha_x | | localhost | koha_y | | localhost | koha_z | The code that creates these users is in koha-create, line 654 an onwards: # Set up MySQL database for this instance. if [ "$op" = create ] then mysql --defaults-extra-file=/etc/mysql/koha-common.cnf <<eof CREATE DATABASE \`$mysqldb\`; CREATE USER \`$mysqluser\`@'$mysql_hostname' IDENTIFIED BY '$mysqlpwd'; CREATE USER \`$mysqluser\`@'%' IDENTIFIED BY '$mysqlpwd'; GRANT ALL PRIVILEGES ON \`$mysqldb\`.* TO \`$mysqluser\`; FLUSH PRIVILEGES; eof fi #` if [ "$op" = use ] then mysql --defaults-extra-file=/etc/mysql/koha-common.cnf --force <<eof CREATE USER \`$mysqluser\`@'$mysql_hostname' IDENTIFIED BY '$mysqlpwd'; CREATE USER \`$mysqluser\`@'%' IDENTIFIED BY '$mysqlpwd'; GRANT ALL PRIVILEGES ON \`$mysqldb\`.* TO \`$mysqluser\`; FLUSH PRIVILEGES; eof fi #` Perhaps it is just a lack of imagination, but I can't really think of a use for the @% unrestricted user. Am I missing something, or can we just get rid of it? Or if they are needed in some scenarios, can we be more clever about when we create unrestricted users, so they are only created when needed?
From IRC: <eythian> I think it's for when koha-create creates databases on remote servers, perhaps. <eythian> It probably should be off by default but configureable.
(In reply to Magnus Enger from comment #1) > From IRC: > > <eythian> I think it's for when koha-create creates databases on remote > servers, perhaps. > <eythian> It probably should be off by default but configureable. If so, might I suggest looking at bug 16690?
Bug 16690 will remove the requirement for it from the code. This bug will fix koha-create to not generate the '%'.
Created attachment 74850 [details] [review] Bug 17237: Remove CREATE USER 'user'@'%' line Having a 'user'@'%' line decreases database security. Bug 16690 closes the need for such a line, and this bug removes the use of it. TEST PLAN --------- While this depends on 16690, it is not required for testing. On a kohadev box: sudo koha-create --create-db test_17237_a git bz apply 17237 sudo perl ~/misc4dev/cp_debian_files.pl restart_all sudo systemctl daemon-reload sudo koha-create --create_db test_17237_b sudo mysql -u root SELECT user,host from mysql.user; -- test_17237_a will have two entries, one being '%' -- test_17237_b will only have one entry, not '%'
Created attachment 79372 [details] [review] Bug 17237: Remove CREATE USER 'user'@'%' line Having a 'user'@'%' line decreases database security. Bug 16690 closes the need for such a line, and this bug removes the use of it. TEST PLAN --------- While this depends on 16690, it is not required for testing. On a kohadev box: sudo koha-create --create-db test_17237_a git bz apply 17237 sudo perl ~/misc4dev/cp_debian_files.pl restart_all sudo systemctl daemon-reload sudo koha-create --create_db test_17237_b sudo mysql -u root SELECT user,host from mysql.user; -- test_17237_a will have two entries, one being '%' -- test_17237_b will only have one entry, not '%' Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Does just what it says on the tin when applied with bug 16690, signing off.
Created attachment 79848 [details] [review] Bug 17237: Remove CREATE USER 'user'@'%' line Having a 'user'@'%' line decreases database security. Bug 16690 closes the need for such a line, and this bug removes the use of it. TEST PLAN --------- While this depends on 16690, it is not required for testing. On a kohadev box: sudo koha-create --create-db test_17237_a git bz apply 17237 sudo perl ~/misc4dev/cp_debian_files.pl restart_all sudo systemctl daemon-reload sudo koha-create --create_db test_17237_b sudo mysql -u root SELECT user,host from mysql.user; -- test_17237_a will have two entries, one being '%' -- test_17237_b will only have one entry, not '%' Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Awesome work all! Pushed to master for 18.11
Pushed to 18.05.x for 18.05.05
Pushed to 17.11.x for 17.11.12