Bug 27303 - Behaviour depends on DB user permissions
Summary: Behaviour depends on DB user permissions
Status: NEW
Alias: None
Product: Koha
Classification: Unclassified
Component: Installation and upgrade (command-line installer) (show other bugs)
Version: Main
Hardware: All All
: P5 - low major (vote)
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2020-12-23 15:39 UTC by Tomás Cohen Arazi
Modified: 2021-01-05 06:55 UTC (History)
1 user (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
Attachment to Bug 27303 - Behaviour depends on DB user permissions (4.23 KB, patch)
2021-01-05 06:55 UTC, Jonathan Druart
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Tomás Cohen Arazi 2020-12-23 15:39:37 UTC
I was writing a helper method to check if a table is a view, for the merger of 'reserves' and 'old_reserves' into 'holds', and found that running things like [1]:

    my $sth = $dbh->prepare("
        SELECT COUNT( * ) AS count
        FROM information_schema.COLUMNS
        WHERE COLUMN_NAME =  'reserve_id'
        AND (
          TABLE_NAME LIKE  'reserves'
          OR
          TABLE_NAME LIKE  'old_reserves'
        )
    ");

Will give different results depending on the user permissions. So if (for some reason) the sysadmin sets two Koha instances on the same DB server, and the user has more privileges than expected, it will count things from databases other than those of the running instance.

In the case of the views this was easy to spot:

root@kohadevbox:misc4dev(master)$ mysql -hdb -ppassword
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 232
Server version: 10.3.27-MariaDB-1:10.3.27+maria~focal mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.tables WHERE TABLE_NAME='reserves';
+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+------------+
| koha_test    | reserves   |
| koha_kohadev | reserves   |
+--------------+------------+
2 rows in set (0.02 sec)


What we need to do, is adding the database name on the WHERE condition, like:

my $db_name = C4::Context->config('database');
...
   WHERE TABLE_SCHEMA=$db_name;


[1] taken from updatedatabase.pl:5573~5582
Comment 1 David Cook 2020-12-23 22:51:03 UTC
Yikes. Yep.
Comment 2 Jonathan Druart 2021-01-05 06:55:22 UTC
Created attachment 114833 [details] [review]
Attachment to Bug 27303 - Behaviour depends on DB user permissions

Bug 27303: Replace SELECT from information_schema by our *_exists subs