[Koha-bugs] [Bug 16690] Remote DB installation is less secure

[bugzilla-daemon]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

Martin Renvoize  changed:

   What|Removed |Added

 Status|Pushed to Master|Pushed to Stable

--- Comment #20 from Martin Renvoize  ---
Pushed to 18.05.x for 18.05.05

-- 
You are receiving this mail because:
You are watching all bug changes.
___
Koha-bugs mailing list
Koha-bugs@lists.koha-community.org
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/

[Koha-bugs] [Bug 16690] Remote DB installation is less secure

[bugzilla-daemon]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

Nick Clemens  changed:

   What|Removed |Added

 Status|Passed QA   |Pushed to Master

--- Comment #19 from Nick Clemens  ---
Awesome work all!

Pushed to master for 18.11

-- 
You are receiving this mail because:
You are watching all bug changes.
___
Koha-bugs mailing list
Koha-bugs@lists.koha-community.org
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/

[Koha-bugs] [Bug 16690] Remote DB installation is less secure

[bugzilla-daemon]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

Katrin Fischer  changed:

   What|Removed |Added

Summary|Remote DB installation is   |Remote DB installation is
   |less secure.|less secure

-- 
You are receiving this mail because:
You are watching all bug changes.
___
Koha-bugs mailing list
Koha-bugs@lists.koha-community.org
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/

[Koha-bugs] [Bug 16690] Remote DB installation is less secure.

[bugzilla-daemon]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

Jonathan Druart  changed:

   What|Removed |Added

  Attachment #79325|0   |1
is obsolete||

--- Comment #18 from Jonathan Druart  
---
Created attachment 79662
  -->
https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=79662=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'@'%';

[Koha-bugs] [Bug 16690] Remote DB installation is less secure.

[bugzilla-daemon]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

Jonathan Druart  changed:

   What|Removed |Added

 Status|Signed Off  |Passed QA

-- 
You are receiving this mail because:
You are watching all bug changes.
___
Koha-bugs mailing list
Koha-bugs@lists.koha-community.org
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/

[Koha-bugs] [Bug 16690] Remote DB installation is less secure.

[bugzilla-daemon]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

--- Comment #17 from Martin Renvoize  ---
Nice thorough test plan, all works as expected here.  Signing Off

-- 
You are receiving this mail because:
You are watching all bug changes.
___
Koha-bugs mailing list
Koha-bugs@lists.koha-community.org
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/

[Koha-bugs] [Bug 16690] Remote DB installation is less secure.

[bugzilla-daemon]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

Martin Renvoize  changed:

   What|Removed |Added

 Status|Needs Signoff   |Signed Off
 CC||martin.renvoize@ptfs-europe
   ||.com

-- 
You are receiving this mail because:
You are watching all bug changes.
___
Koha-bugs mailing list
Koha-bugs@lists.koha-community.org
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/

[Koha-bugs] [Bug 16690] Remote DB installation is less secure.

[bugzilla-daemon]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

Martin Renvoize  changed:

   What|Removed |Added

  Attachment #74849|0   |1
is obsolete||

--- Comment #16 from Martin Renvoize  ---
Created attachment 79325
  -->
https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=79325=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'@'%';

[Koha-bugs] [Bug 16690] Remote DB installation is less secure.

[bugzilla-daemon]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16690

M. Tompsett  changed:

   What|Removed |Added

Summary|Remote DB installation  |Remote DB installation is
   |fails   |less secure.

-- 
You are receiving this mail because:
You are watching all bug changes.
___
Koha-bugs mailing list
Koha-bugs@lists.koha-community.org
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/