Bugzilla – Attachment 79325 Details for
Bug 16690
Improve security of remote database installations
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 16690: Simplify SHOW GRANTS to work when connected
Bug-16690-Simplify-SHOW-GRANTS-to-work-when-connec.patch (text/plain), 8.67 KB, created by
Martin Renvoize (ashimema)
on 2018-09-24 16:08:27 UTC
(
hide
)
Description:
Bug 16690: Simplify SHOW GRANTS to work when connected
Filename:
MIME Type:
Creator:
Martin Renvoize (ashimema)
Created:
2018-09-24 16:08:27 UTC
Size:
8.67 KB
patch
obsolete
>From a822dbb459587066319bba867d9a7f1ae6a86e42 Mon Sep 17 00:00:00 2001 >From: Mark Tompsett <mtompset@hotmail.com> >Date: Tue, 7 Jun 2016 22:48:57 -0400 >Subject: [PATCH] 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> >--- > installer/install.pl | 30 +++++------------------------- > 1 file changed, 5 insertions(+), 25 deletions(-) > >diff --git a/installer/install.pl b/installer/install.pl >index a7aaa28e2f..2c4c2eda91 100755 >--- a/installer/install.pl >+++ b/installer/install.pl >@@ -145,10 +145,11 @@ elsif ( $step && $step == 2 ) { > $template->param( 'checkdatabasecreated' => 1 ); > } > >- #Check if user have all necessary grants on this database. >- my $rq = >- $dbh->prepare( >- "SHOW GRANTS FOR \'$info{user}\'\@'$info{hostname}'"); >+ # Check if user have all necessary grants on this database. >+ # CURRENT_USER is ANSI SQL, and doesn't require mysql table >+ # privileges, making the % check pointless, since they >+ # couldn't even check GRANTS if they couldn't connect. >+ my $rq = $dbh->prepare('SHOW GRANTS FOR CURRENT_USER'); > $rq->execute; > my $grantaccess; > while ( my ($line) = $rq->fetchrow ) { >@@ -168,27 +169,6 @@ elsif ( $step && $step == 2 ) { > ); > } > } >- unless ($grantaccess) { >- $rq = >- $dbh->prepare("SHOW GRANTS FOR \'$info{user}\'\@'\%'"); >- $rq->execute; >- while ( my ($line) = $rq->fetchrow ) { >- my $dbname = $info{dbname}; >- if ( $line =~ m/$dbname/ || index( $line, '*.*' ) > 0 ) >- { >- $grantaccess = 1 >- if ( >- index( $line, 'ALL PRIVILEGES' ) > 0 >- || ( ( index( $line, 'SELECT' ) > 0 ) >- && ( index( $line, 'INSERT' ) > 0 ) >- && ( index( $line, 'UPDATE' ) > 0 ) >- && ( index( $line, 'DELETE' ) > 0 ) >- && ( index( $line, 'CREATE' ) > 0 ) >- && ( index( $line, 'DROP' ) > 0 ) ) >- ); >- } >- } >- } > $template->param( "checkgrantaccess" => $grantaccess ); > } # End mysql connect check... > >-- >2.18.0
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 16690
:
52174
|
53162
|
64847
|
64848
|
74510
|
74511
|
74849
|
79325
|
79662