https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Severity|enhancement |minor -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690 --- Comment #1 from M. Tompsett <mtompset@hotmail.com> --- Created attachment 52174 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=52174&action=edit Bug 16690: SHOW GRANTS fails to detect proper permissions If the DB is on a remote machine, the web server and the db server are different, but the SHOW GRANTS code in installer/install.pl is trying to use the SAME machine. And even if the permissions were allowed accessing from both the web and db servers, MySQL won't return the SHOW GRANTS without access to the mysql.user table. To install *.* permissions became easiest to get working. This makes it so that you can specify the web and db server separately, so the SHOW GRANT will match the machine the user is connected from and return properly. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=15088 -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=15427 -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart@bugs.koha-c | |ommunity.org Status|Signed Off |Needs Signoff --- Comment #4 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Signed-off-by line is missing. Dylan please adds it or at least let a comment to explicitly tell that you tested the patch and confirmed it works as expected. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=17237 -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #52174|0 |1 is obsolete| | --- Comment #6 from M. Tompsett <mtompset@hotmail.com> --- Created attachment 64847 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=64847&action=edit Bug 16690: SHOW GRANTS fails to detect proper permissions If the DB is on a remote machine, the web server and the db server are different, but the SHOW GRANTS code in installer/install.pl is trying to use the SAME machine. And even if the permissions were allowed accessing from both the web and db servers, MySQL won't return the SHOW GRANTS without access to the mysql.user table. To install *.* permissions became easiest to get working. This makes it so that you can specify the web and db server separately, so the SHOW GRANT will match the machine the user is connected from and return properly. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |nick@bywatersolutions.com -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690 --- Comment #9 from M. Tompsett <mtompset@hotmail.com> --- (In reply to Katrin Fischer from comment #8)

Patches still apply. Mark, could you add a test plan please and say something about the requirements in order to test this?

This is about the koha-create --use-db method, if doing a manual install from source. But I figure you can get the same result with a simple package install and a couple tweaks. As you can see in comment #1, I created a DB, but did not grant permissions for '%'. This means when the web installer attempts to SHOW GRANTS it is going to fail, because hostname on a regular install is the KohaApp VM, not the KohaDB VM. The 'Do an install' is a simple package installation. 'Hack the kohaconf...' is something like: $ sudo vi /etc/koha/sites/library/koha-conf.xml and making the appropriate adjustments to match the information used in the GRANT ALL. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Summary|Installing DB on remote |Remote DB installation |server fails without GRANT |fails |ALL, because wrong | |permissions are checked | -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |ASSIGNED --- Comment #11 from M. Tompsett <mtompset@hotmail.com> --- I'm having difficulty retesting my own patch, so I've changed the status back to ASSIGNED, while I looking into this. I blame plack. ;) -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690 --- Comment #13 from M. Tompsett <mtompset@hotmail.com> --- Created attachment 74510 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=74510&action=edit Bug 16690: Simplify and correct the SHOW GRANTS MySQL / MariaDB allow the current connected user to check their own grants with CURRENT_USER. There is no need for the installer to know the IP address of the webserver. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |17237 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17237 [Bug 17237] Stop koha-create from creating MySQL users without host restriction -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #64847|0 |1 is obsolete| | Attachment #64848|0 |1 is obsolete| | Attachment #74510|0 |1 is obsolete| | Attachment #74511|0 |1 is obsolete| | --- Comment #15 from M. Tompsett <mtompset@hotmail.com> --- Created attachment 74849 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=74849&action=edit Bug 16690: Simplify SHOW GRANTS to work when connected If the DB is on a remote machine, the web server and the db server are different, but the SHOW GRANTS code in installer/install.pl is trying to use the SAME machine. And even if the permissions were allowed accessing from both the web and db servers, MySQL won't return the SHOW GRANTS without access to the mysql.user table. To install *.* permissions became easiest to get working. Unless the DB is set up with 'user'@'%', which is also a potential security issue. MySQL / MariaDB allow the current connected user to check their own grants with CURRENT_USER. There is no need for the installer to know the IP address of the webserver. This also removes the need to have permissions for 'koha_kohadev'@'%', because the only process to be accessing the koha DB is from a known host/ip. This tightens security too. TEST PLAN --------- Install 2 fresh VMs from a Debian ISO. Make sure they are on the same network (192.168.50.x) as the kohadevbox. You will need to remember one as DB_IPADDRESS. On the DB VM & Third VM: sudo apt-get install mariadb-server mariadb-client net-tools -- the third vm just needs to be able to run mysql to access the DB VM. On DB VM: sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf -- make sure the bind-address line is commented out with a # sudo service mariadb restart -- congratulations, your DB server is listening to remote calls now. sudo mysql -u root CREATE DATABASE koha_kohadev; GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'localhost' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; -- now you have an empty DB ready to run a web install on. However, because only koha_kohadev from localhost is allowed, we expect failure when we try to run the web installation step when we get there. Let's confirm that everything is working as expected before trying. It will also demonstrate the reason why this patch is superior to the existing code. On a kohadevbox: mysql -u koha_kohadev -h DB_IPADDRESS -p -- this should be denied On DB VM: DROP USER 'koha_kohadev'@'localhost'; GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; On a kohadevbox: mysql -u koha_kohadev -h DB_IPADDRESS -p -- this should give you a SQL prompt SHOW GRANTS FOR CURRENT_USER; -- this should show two lines based on 'koha_kohadev'@'%'; SHOW GRANTS FOR 'koha_kohadev'@'192.168.50.10'; -- this should give an access denied error. SHOW GRANTS FOR 'koha_kohadev'@'%'; -- this should show two lines based on 'koha_kohadev'@'%'; QUIT -- This case requires the unless code currently in place, because we aren't checking CURRENT_USER. On DB VM: DROP USER 'koha_kohadev'@'%'; GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'192.168.50.10' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; On a kohadevbox: mysql -u koha_kohadev -h DB_IPADDRESS -p -- this should give you a SQL prompt SHOW GRANTS FOR CURRENT_USER; -- this should show two lines based on 'koha_kohadev'@'%'; SHOW GRANTS FOR 'koha_kohadev'@'192.168.50.10'; -- this should show two lines based on 'koha_kohadev'@'192.168.50.10'; SHOW GRANTS FOR 'koha_kohadev'@'%'; -- this should give an access denied error. QUIT -- This case demonstrates that we have two failure points: 1) The GRANT command by the DB Admin and 2) The koha-conf.xml setting. This is why CURRENT_USER is superior: only (2) is the failure point. On DB VM: GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; SELECT host,user FROM mysql.user; -- Should see both koha_kohadev for 192.168.50.10 and %. On a kohadevbox: mysql -u koha_kohadev -h DB_IPADDRESS -p -- this should give you a SQL prompt SHOW GRANTS FOR CURRENT_USER; -- this should show two lines based on 'koha_kohadev'@'192.168.50.10'; SHOW GRANTS FOR 'koha_kohadev'@'192.168.50.10'; -- this should show two lines based on 'koha_kohadev'@'192.168.50.10'; SHOW GRANTS FOR 'koha_kohadev'@'%'; -- this should give an access denied error. QUIT -- This case doesn't need the unless. CURRENT_USER still just works. On an third VM on the same network: mysql -u koha_kohadev -h DB_IPADDRESS -p -- this should give you a SQL prompt SHOW GRANTS FOR CURRENT_USER; -- this should show two lines based on 'koha_kohadev'@'%'; SHOW GRANTS FOR 'koha_kohadev'@'192.168.50.10'; -- this should give an access denied error. SHOW GRANTS FOR 'koha_kohadev'@'%'; -- this should show two lines based on 'koha_kohadev'@'%'; QUIT -- This case demonstrates that it may be more open than a DB administrator would prefer. And notice, CURRENT_USER still just works. On DB VM: DROP USER 'koha_kohadev'@'192.168.50.10'; DROP USER 'koha_kohadev'@'%'; GRANT ALL PRIVILEGES ON *.* TO 'koha_kohadev'@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; -- This basically give koha_kohadev free reign to do pretty dangerous stuff. On an third VM on the same network: mysql -u koha_kohadev -h DB_IPADDRESS -p -- this should give you a SQL prompt SHOW GRANTS FOR CURRENT_USER; -- this should show a line based on 'koha_kohadev'@'%'; SHOW GRANTS FOR 'koha_kohadev'@'192.168.50.10'; -- this should give a no such grant error. SHOW GRANTS FOR 'koha_kohadev'@'%'; -- this should show two lines based on 'koha_kohadev'@'%'; QUIT -- This case demonstrates that it may be more open than a DB administrator would prefer. And notice, CURRENT_USER still just works. In the old code, both cases were literally checked. This tweak is an optimization which doesn't require setting permissions to the mysql.user table. Without it, the code says the user doesn't have permissions to check the show grants. This issue is not visible to the user, because both cases are checked. On DB VM: SELECT host,user FROM mysql.user; -- for each one do an appropriate DROP USER 'user'@'host'; GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'192.168.50.10' IDENTIFIED BY 'password'; On kohadevbox: -- Make sure the /etc/koha/sites/kohadev/koha-conf.xml points to the DB VM. -- Make sure a web install runs correctly On third VM: -- Make sure unable to connect as koha_kohadev/password. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #74849|0 |1 is obsolete| | --- Comment #16 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 79325 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=79325&action=edit Bug 16690: Simplify SHOW GRANTS to work when connected If the DB is on a remote machine, the web server and the db server are different, but the SHOW GRANTS code in installer/install.pl is trying to use the SAME machine. And even if the permissions were allowed accessing from both the web and db servers, MySQL won't return the SHOW GRANTS without access to the mysql.user table. To install *.* permissions became easiest to get working. Unless the DB is set up with 'user'@'%', which is also a potential security issue. MySQL / MariaDB allow the current connected user to check their own grants with CURRENT_USER. There is no need for the installer to know the IP address of the webserver. This also removes the need to have permissions for 'koha_kohadev'@'%', because the only process to be accessing the koha DB is from a known host/ip. This tightens security too. TEST PLAN --------- Install 2 fresh VMs from a Debian ISO. Make sure they are on the same network (192.168.50.x) as the kohadevbox. You will need to remember one as DB_IPADDRESS. On the DB VM & Third VM: sudo apt-get install mariadb-server mariadb-client net-tools -- the third vm just needs to be able to run mysql to access the DB VM. On DB VM: sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf -- make sure the bind-address line is commented out with a # sudo service mariadb restart -- congratulations, your DB server is listening to remote calls now. sudo mysql -u root CREATE DATABASE koha_kohadev; GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'localhost' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; -- now you have an empty DB ready to run a web install on. However, because only koha_kohadev from localhost is allowed, we expect failure when we try to run the web installation step when we get there. Let's confirm that everything is working as expected before trying. It will also demonstrate the reason why this patch is superior to the existing code. On a kohadevbox: mysql -u koha_kohadev -h DB_IPADDRESS -p -- this should be denied On DB VM: DROP USER 'koha_kohadev'@'localhost'; GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; On a kohadevbox: mysql -u koha_kohadev -h DB_IPADDRESS -p -- this should give you a SQL prompt SHOW GRANTS FOR CURRENT_USER; -- this should show two lines based on 'koha_kohadev'@'%'; SHOW GRANTS FOR 'koha_kohadev'@'192.168.50.10'; -- this should give an access denied error. SHOW GRANTS FOR 'koha_kohadev'@'%'; -- this should show two lines based on 'koha_kohadev'@'%'; QUIT -- This case requires the unless code currently in place, because we aren't checking CURRENT_USER. On DB VM: DROP USER 'koha_kohadev'@'%'; GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'192.168.50.10' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; On a kohadevbox: mysql -u koha_kohadev -h DB_IPADDRESS -p -- this should give you a SQL prompt SHOW GRANTS FOR CURRENT_USER; -- this should show two lines based on 'koha_kohadev'@'%'; SHOW GRANTS FOR 'koha_kohadev'@'192.168.50.10'; -- this should show two lines based on 'koha_kohadev'@'192.168.50.10'; SHOW GRANTS FOR 'koha_kohadev'@'%'; -- this should give an access denied error. QUIT -- This case demonstrates that we have two failure points: 1) The GRANT command by the DB Admin and 2) The koha-conf.xml setting. This is why CURRENT_USER is superior: only (2) is the failure point. On DB VM: GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; SELECT host,user FROM mysql.user; -- Should see both koha_kohadev for 192.168.50.10 and %. On a kohadevbox: mysql -u koha_kohadev -h DB_IPADDRESS -p -- this should give you a SQL prompt SHOW GRANTS FOR CURRENT_USER; -- this should show two lines based on 'koha_kohadev'@'192.168.50.10'; SHOW GRANTS FOR 'koha_kohadev'@'192.168.50.10'; -- this should show two lines based on 'koha_kohadev'@'192.168.50.10'; SHOW GRANTS FOR 'koha_kohadev'@'%'; -- this should give an access denied error. QUIT -- This case doesn't need the unless. CURRENT_USER still just works. On an third VM on the same network: mysql -u koha_kohadev -h DB_IPADDRESS -p -- this should give you a SQL prompt SHOW GRANTS FOR CURRENT_USER; -- this should show two lines based on 'koha_kohadev'@'%'; SHOW GRANTS FOR 'koha_kohadev'@'192.168.50.10'; -- this should give an access denied error. SHOW GRANTS FOR 'koha_kohadev'@'%'; -- this should show two lines based on 'koha_kohadev'@'%'; QUIT -- This case demonstrates that it may be more open than a DB administrator would prefer. And notice, CURRENT_USER still just works. On DB VM: DROP USER 'koha_kohadev'@'192.168.50.10'; DROP USER 'koha_kohadev'@'%'; GRANT ALL PRIVILEGES ON *.* TO 'koha_kohadev'@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; -- This basically give koha_kohadev free reign to do pretty dangerous stuff. On an third VM on the same network: mysql -u koha_kohadev -h DB_IPADDRESS -p -- this should give you a SQL prompt SHOW GRANTS FOR CURRENT_USER; -- this should show a line based on 'koha_kohadev'@'%'; SHOW GRANTS FOR 'koha_kohadev'@'192.168.50.10'; -- this should give a no such grant error. SHOW GRANTS FOR 'koha_kohadev'@'%'; -- this should show two lines based on 'koha_kohadev'@'%'; QUIT -- This case demonstrates that it may be more open than a DB administrator would prefer. And notice, CURRENT_USER still just works. In the old code, both cases were literally checked. This tweak is an optimization which doesn't require setting permissions to the mysql.user table. Without it, the code says the user doesn't have permissions to check the show grants. This issue is not visible to the user, because both cases are checked. On DB VM: SELECT host,user FROM mysql.user; -- for each one do an appropriate DROP USER 'user'@'host'; GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'192.168.50.10' IDENTIFIED BY 'password'; On kohadevbox: -- Make sure the /etc/koha/sites/kohadev/koha-conf.xml points to the DB VM. -- Make sure a web install runs correctly On third VM: -- Make sure unable to connect as koha_kohadev/password. Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690 --- Comment #17 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Nice thorough test plan, all works as expected here. Signing Off -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #79325|0 |1 is obsolete| | --- Comment #18 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Created attachment 79662 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=79662&action=edit Bug 16690: Simplify SHOW GRANTS to work when connected If the DB is on a remote machine, the web server and the db server are different, but the SHOW GRANTS code in installer/install.pl is trying to use the SAME machine. And even if the permissions were allowed accessing from both the web and db servers, MySQL won't return the SHOW GRANTS without access to the mysql.user table. To install *.* permissions became easiest to get working. Unless the DB is set up with 'user'@'%', which is also a potential security issue. MySQL / MariaDB allow the current connected user to check their own grants with CURRENT_USER. There is no need for the installer to know the IP address of the webserver. This also removes the need to have permissions for 'koha_kohadev'@'%', because the only process to be accessing the koha DB is from a known host/ip. This tightens security too. TEST PLAN --------- Install 2 fresh VMs from a Debian ISO. Make sure they are on the same network (192.168.50.x) as the kohadevbox. You will need to remember one as DB_IPADDRESS. On the DB VM & Third VM: sudo apt-get install mariadb-server mariadb-client net-tools -- the third vm just needs to be able to run mysql to access the DB VM. On DB VM: sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf -- make sure the bind-address line is commented out with a # sudo service mariadb restart -- congratulations, your DB server is listening to remote calls now. sudo mysql -u root CREATE DATABASE koha_kohadev; GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'localhost' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; -- now you have an empty DB ready to run a web install on. However, because only koha_kohadev from localhost is allowed, we expect failure when we try to run the web installation step when we get there. Let's confirm that everything is working as expected before trying. It will also demonstrate the reason why this patch is superior to the existing code. On a kohadevbox: mysql -u koha_kohadev -h DB_IPADDRESS -p -- this should be denied On DB VM: DROP USER 'koha_kohadev'@'localhost'; GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; On a kohadevbox: mysql -u koha_kohadev -h DB_IPADDRESS -p -- this should give you a SQL prompt SHOW GRANTS FOR CURRENT_USER; -- this should show two lines based on 'koha_kohadev'@'%'; SHOW GRANTS FOR 'koha_kohadev'@'192.168.50.10'; -- this should give an access denied error. SHOW GRANTS FOR 'koha_kohadev'@'%'; -- this should show two lines based on 'koha_kohadev'@'%'; QUIT -- This case requires the unless code currently in place, because we aren't checking CURRENT_USER. On DB VM: DROP USER 'koha_kohadev'@'%'; GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'192.168.50.10' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; On a kohadevbox: mysql -u koha_kohadev -h DB_IPADDRESS -p -- this should give you a SQL prompt SHOW GRANTS FOR CURRENT_USER; -- this should show two lines based on 'koha_kohadev'@'%'; SHOW GRANTS FOR 'koha_kohadev'@'192.168.50.10'; -- this should show two lines based on 'koha_kohadev'@'192.168.50.10'; SHOW GRANTS FOR 'koha_kohadev'@'%'; -- this should give an access denied error. QUIT -- This case demonstrates that we have two failure points: 1) The GRANT command by the DB Admin and 2) The koha-conf.xml setting. This is why CURRENT_USER is superior: only (2) is the failure point. On DB VM: GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; SELECT host,user FROM mysql.user; -- Should see both koha_kohadev for 192.168.50.10 and %. On a kohadevbox: mysql -u koha_kohadev -h DB_IPADDRESS -p -- this should give you a SQL prompt SHOW GRANTS FOR CURRENT_USER; -- this should show two lines based on 'koha_kohadev'@'192.168.50.10'; SHOW GRANTS FOR 'koha_kohadev'@'192.168.50.10'; -- this should show two lines based on 'koha_kohadev'@'192.168.50.10'; SHOW GRANTS FOR 'koha_kohadev'@'%'; -- this should give an access denied error. QUIT -- This case doesn't need the unless. CURRENT_USER still just works. On an third VM on the same network: mysql -u koha_kohadev -h DB_IPADDRESS -p -- this should give you a SQL prompt SHOW GRANTS FOR CURRENT_USER; -- this should show two lines based on 'koha_kohadev'@'%'; SHOW GRANTS FOR 'koha_kohadev'@'192.168.50.10'; -- this should give an access denied error. SHOW GRANTS FOR 'koha_kohadev'@'%'; -- this should show two lines based on 'koha_kohadev'@'%'; QUIT -- This case demonstrates that it may be more open than a DB administrator would prefer. And notice, CURRENT_USER still just works. On DB VM: DROP USER 'koha_kohadev'@'192.168.50.10'; DROP USER 'koha_kohadev'@'%'; GRANT ALL PRIVILEGES ON *.* TO 'koha_kohadev'@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; -- This basically give koha_kohadev free reign to do pretty dangerous stuff. On an third VM on the same network: mysql -u koha_kohadev -h DB_IPADDRESS -p -- this should give you a SQL prompt SHOW GRANTS FOR CURRENT_USER; -- this should show a line based on 'koha_kohadev'@'%'; SHOW GRANTS FOR 'koha_kohadev'@'192.168.50.10'; -- this should give a no such grant error. SHOW GRANTS FOR 'koha_kohadev'@'%'; -- this should show two lines based on 'koha_kohadev'@'%'; QUIT -- This case demonstrates that it may be more open than a DB administrator would prefer. And notice, CURRENT_USER still just works. In the old code, both cases were literally checked. This tweak is an optimization which doesn't require setting permissions to the mysql.user table. Without it, the code says the user doesn't have permissions to check the show grants. This issue is not visible to the user, because both cases are checked. On DB VM: SELECT host,user FROM mysql.user; -- for each one do an appropriate DROP USER 'user'@'host'; GRANT ALL PRIVILEGES ON `koha_kohadev`.* TO 'koha_kohadev'@'192.168.50.10' IDENTIFIED BY 'password'; On kohadevbox: -- Make sure the /etc/koha/sites/kohadev/koha-conf.xml points to the DB VM. -- Make sure a web install runs correctly On third VM: -- Make sure unable to connect as koha_kohadev/password. Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> I have not followed the whole test plan but trusting author and SO Changes make sense to me -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to Master --- Comment #19 from Nick Clemens <nick@bywatersolutions.com> --- Awesome work all! Pushed to master for 18.11 -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Summary|Remote DB installation is |Improve security of remote |less secure |database installations -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690 Fridolin SOMERS <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to Stable |RESOLVED Resolution|--- |FIXED -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks|23470 | Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23470 [Bug 23470] Elasticsearch - OpacSuppression not implemented in search result -- You are receiving this mail because: You are watching all bug changes.