Bug 17513 - koha-create does not set GRANTS correctly
Summary: koha-create does not set GRANTS correctly
Status: RESOLVED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: master
Hardware: All All
: P5 - low normal (vote)
Assignee: Jonathan Druart
QA Contact: Marcel de Rooy
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-10-27 14:15 UTC by Jonathan Druart
Modified: 2017-06-14 22:18 UTC (History)
6 users (show)

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


Attachments
Bug 17513: Create GRANT for user@localhost in koha-create (1.36 KB, patch)
2016-10-27 14:32 UTC, Jonathan Druart
Details | Diff | Splinter Review
[SIGNED OFF] Bug 17513: Create GRANT for user@localhost in koha-create (1.44 KB, patch)
2016-10-29 07:47 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 17513: Create GRANT for user@localhost in koha-create (1.56 KB, patch)
2016-11-18 13:00 UTC, Marcel de Rooy
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Jonathan Druart 2016-10-27 14:15:39 UTC
There is a behavior change between MySQL and MariaDB:

With:
 CREATE DATABASE koha_kohadev;
 CREATE USER koha_kohadev@localhost IDENTIFIED BY 'password';
 CREATE USER koha_kohadev@'%' IDENTIFIED BY 'password';
 GRANT ALL PRIVILEGES ON koha_kohadev.* TO koha_kohadev;

Using Mysql, as admin user:
mysql> SHOW GRANTS FOR koha_kohadev@'localhost';
  GRANT USAGE ON *.* TO 'koha_kohadev'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'

mysql> SHOW GRANTS FOR koha_kohadev;
  GRANT USAGE ON *.* TO 'koha_kohadev'@'%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'
  GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'%'

Using Mysql, as koha_user user:

mysql> SHOW GRANTS FOR koha_kohadev@'localhost';
  GRANT USAGE ON *.* TO 'koha_kohadev'@'localhost' IDENTIFIED BY PASSWORD <secret>

mysql> SHOW GRANTS FOR koha_kohadev;
  GRANT USAGE ON *.* TO 'koha_kohadev'@'%' IDENTIFIED BY PASSWORD <secret>
  GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'%'


Using MariaDB, as admin user:
MariaDB [(none)]> SHOW GRANTS FOR kohaçkohadev@localhost;
  GRANT USAGE ON *.* TO 'koha_kohadev'@'%' IDENTIFIED BY PASSWORD '*'2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19

MariaDB [(none)]> SHOW GRANTS FOR kohaçkohadev;
  GRANT USAGE ON *.* TO 'koha_kohadev'@'%' IDENTIFIED BY PASSWORD '*'2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19
  GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'%'

So all the same so far :)

But,
Using MariaDB, as koha_user user:
MariaDB [(none)]> SHOW GRANTS FOR kohaçkohadev@localhost;
  GRANT USAGE ON *.* TO 'koha_kohadev'@'%' IDENTIFIED BY PASSWORD '*'2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19

MariaDB [(none)]> SHOW GRANTS FOR kohaçkohadev;
ERROR 1044 (42000): Access denied for user 'koha_kohadev'@'localhost' to database 'mysql'

BOOM!
The installer does not parse the expected output and then raise an error.
Comment 1 Jonathan Druart 2016-10-27 14:32:00 UTC
Created attachment 56922 [details] [review]
Bug 17513: Create GRANT for user@localhost in koha-create

See the bug description for the details.

Test plan:
Use this script to create a new koha installation, using MariaDB
You should not display
"user koha_kohadev doesn't have enough privilege on database koha_kohadev "
Comment 2 Martin Renvoize 2016-10-29 07:47:42 UTC
Created attachment 56975 [details] [review]
[SIGNED OFF] Bug 17513: Create GRANT for user@localhost in koha-create

See the bug description for the details.

Test plan:
Use this script to create a new koha installation, using MariaDB
You should not display
"user koha_kohadev doesn't have enough privilege on database koha_kohadev "

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 3 Martin Renvoize 2016-10-29 07:48:45 UTC
Could have sworn this had been signed off.. will amend to singed off instead of passed qa
Comment 4 Martin Renvoize 2016-10-29 07:49:46 UTC
Thanks Jonathan, great to see Maria making it to first class citizen ;)
Comment 5 Marcel de Rooy 2016-11-18 13:00:35 UTC
Created attachment 57643 [details] [review]
Bug 17513: Create GRANT for user@localhost in koha-create

See the bug description for the details.

Test plan:
Use this script to create a new koha installation, using MariaDB
You should not display
"user koha_kohadev doesn't have enough privilege on database koha_kohadev "

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Confirm that this still works (with MySQL).
Comment 6 Kyle M Hall 2016-11-18 14:07:59 UTC
Pushed to master for 16.11, thanks Jonathan!
Comment 7 Julian Maurice 2016-11-30 13:20:14 UTC
Pushed to 3.22.x, will be in 3.22.13
Comment 8 Mirko Tietgen 2016-12-01 15:53:15 UTC
Should go into 16.05 too please, I just ran into it.
Comment 9 Mason James 2017-01-29 11:36:31 UTC
Pushed to 16.05.x, for 16.05.06 release