Bug 17513

Summary: koha-create does not set GRANTS correctly
Product: Koha Reporter: Jonathan Druart <jonathan.druart>
Component: Architecture, internals, and plumbingAssignee: Jonathan Druart <jonathan.druart>
Status: CLOSED FIXED QA Contact: Marcel de Rooy <m.de.rooy>
Severity: normal    
Priority: P5 - low CC: julian.maurice, kyle, m.de.rooy, martin.renvoize, mirko, mtj
Version: Main   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: Small patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Attachments: Bug 17513: Create GRANT for user@localhost in koha-create
[SIGNED OFF] Bug 17513: Create GRANT for user@localhost in koha-create
Bug 17513: Create GRANT for user@localhost in koha-create

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