Bug 17237 - Stop koha-create from creating MySQL users without host restriction
Summary: Stop koha-create from creating MySQL users without host restriction
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Packaging (show other bugs)
Version: Main
Hardware: All All
: P5 - low minor (vote)
Assignee: Magnus Enger
QA Contact: Josef Moravec
URL:
Keywords:
Depends on: 16690
Blocks:
  Show dependency treegraph
 
Reported: 2016-09-02 06:11 UTC by Magnus Enger
Modified: 2019-10-14 19:57 UTC (History)
6 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
Bug 17237: Remove CREATE USER 'user'@'%' line (1.39 KB, patch)
2018-04-25 17:02 UTC, Mark Tompsett
Details | Diff | Splinter Review
Bug 17237: Remove CREATE USER 'user'@'%' line (1.46 KB, patch)
2018-09-25 13:21 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 17237: Remove CREATE USER 'user'@'%' line (1.51 KB, patch)
2018-10-02 17:50 UTC, Josef Moravec
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Magnus Enger 2016-09-02 06:11:22 UTC
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?
Comment 1 Magnus Enger 2016-09-02 08:23:11 UTC
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.
Comment 2 Mark Tompsett 2017-06-22 16:35:43 UTC
(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?
Comment 3 Mark Tompsett 2018-04-25 16:25:40 UTC
Bug 16690 will remove the requirement for it from the code. This bug will fix koha-create to not generate the '%'.
Comment 4 Mark Tompsett 2018-04-25 17:02:59 UTC
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 '%'
Comment 5 Martin Renvoize 2018-09-25 13:21:56 UTC
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>
Comment 6 Martin Renvoize 2018-09-25 13:22:30 UTC
Does just what it says on the tin when applied with bug 16690, signing off.
Comment 7 Josef Moravec 2018-10-02 17:50:13 UTC
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>
Comment 8 Nick Clemens 2018-10-05 16:28:25 UTC
Awesome work all!

Pushed to master for 18.11
Comment 9 Martin Renvoize 2018-10-08 08:54:53 UTC
Pushed to 18.05.x for 18.05.05
Comment 10 Fridolin Somers 2018-11-08 15:11:11 UTC
Pushed to 17.11.x for 17.11.12