Bug 17237 - Stop koha-create from creating MySQL users without host restriction
Summary: Stop koha-create from creating MySQL users without host restriction
Status: Needs Signoff
Alias: None
Product: Koha
Classification: Unclassified
Component: Packaging (show other bugs)
Version: master
Hardware: All All
: P5 - low minor (vote)
Assignee: Magnus Enger
QA Contact: Testopia
URL:
Whiteboard:
Keywords:
Depends on: 16690
Blocks:
  Show dependency treegraph
 
Reported: 2016-09-02 06:11 UTC by Magnus Enger
Modified: 2018-04-25 17:02 UTC (History)
2 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Bot Control: ---
When did the bot last check this:
Who signed the patch off:
Text to go in the release notes:


Attachments
Bug 17237: Remove CREATE USER 'user'@'%' line (1.39 KB, patch)
2018-04-25 17:02 UTC, M. Tompsett
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 M. 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 M. 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 M. 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 '%'