Page MenuHomePhabricator

Grant tool "tool-db-usage" (s53508) ability to read full table_schema.TABLES contents
Closed, ResolvedPublic

Description

I am creating a tool to assist in T175096: Identify tools hosting databases on labsdb100[13] and notify maintainers that will show information about the schemas and tables that are owned by tools and user accounts. The basic query that I am using to do this is:

SELECT
  SUBSTRING_INDEX(table_schema, '_', 1) as owner
, SUM( data_length ) as data_bytes
, SUM( index_length ) as index_bytes
, SUM( table_rows ) as row_count
, COUNT(1) as tables
FROM information_schema.TABLES
WHERE table_schema regexp '^[psu][0-9]'
GROUP BY owner
ORDER BY data_bytes DESC

When this query is run as my Toolforge user (u3518) it finds 72 distinct owners:

(u3518@c1.labsdb) [information_schema]> SELECT
    ->                   SUBSTRING_INDEX(table_schema, '_', 1) as owner
    ->                 , SUM( data_length ) as data_bytes
    ->                 , SUM( index_length ) as index_bytes
    ->                 , SUM( table_rows ) as row_count
    ->                 , COUNT(1) as tables
    ->                 FROM information_schema.TABLES
    ->                 WHERE table_schema regexp '^[psu][0-9]'
    ->                 GROUP BY owner
    ->                 ORDER BY data_bytes DESC;
+--------------+-------------+-------------+-----------+--------+
| owner        | data_bytes  | index_bytes | row_count | tables |
+--------------+-------------+-------------+-----------+--------+
| u3532        | 38337232896 | 18483609600 | 860150285 |     46 |
| u4817        | 26288865280 | 25651920896 | 207974812 |     35 |
| u2041        | 19788595200 | 10820780032 | 442218836 |      7 |
| p50380g50816 | 18500958436 | 18174479360 | 171328358 |     30 |
| p50380g50494 |  9897607168 |  9147940864 |  73522968 |     17 |
| s51187       |  8993101684 |     1388544 |  96923325 |    384 |
| s51362       |  7441645568 | 23410786304 |  83411345 |     16 |
| p50380g50692 |  5351828488 |  2482273280 | 153929591 |     54 |
| p50380g50921 |  5262351008 |  3673131008 |  97759098 |     21 |
| u2815        |  5219706131 |  2856213504 | 154563899 |     84 |
| u12219       |  2914885632 |   243286016 |  66134870 |     18 |
| s51111       |  2341691392 |  2183348224 |  43533672 |     73 |
| s52690       |  2295183008 |  1088602112 |  62265427 |      8 |
| s53311       |  2244091904 |   196001792 |  51960598 |      8 |
| p50380g50900 |  1892417536 |           0 |  27826983 |      4 |
| s52467       |  1704787968 |  1174929408 |  11345849 |      7 |
| p50380g50491 |  1493155840 |   739557376 |  30958281 |    105 |
| u3945        |  1239744512 |   132603904 |   9480639 |      3 |
| s51172       |  1212006400 |   466026496 |  27961097 |      1 |
| u11049       |  1210769408 |       24576 |   7700146 |      3 |
| s51454       |  1101627392 |   380420096 |  18270590 |     26 |
| u3570        |  1090535424 |  1416429568 |  10124817 |      2 |
| s51334       |   582770688 |   275841024 |  13443019 |      1 |
| u3356        |   458457088 |           0 |   5155192 |      3 |
| p50380g50553 |   335986688 |           0 |   7734476 |      5 |
| u3182        |   291061760 |   779517952 |   5220218 |      1 |
| p50380g50497 |   272220160 |    95996928 |    615755 |    159 |
| u2029        |   265289728 |        8192 |   3292637 |      1 |
| u10276       |   170983424 |        8192 |   3944455 |      1 |
| u2043        |   144390548 |   281989120 |   2294366 |      2 |
| s52490       |   136347648 |   111484928 |    971295 |      5 |
| p50380g50824 |   114950144 |    70385664 |   1604745 |      1 |
| u2843        |    89841664 |   124231680 |   1555310 |      1 |
| s51154       |    85114880 |    31645696 |   1038282 |      6 |
| s51306       |    78774272 |    12648448 |     70406 |      2 |
| u2745        |    64585728 |    17350656 |    527908 |      1 |
| u2402        |    42795008 |      303104 |    196733 |      4 |
| p50380g50728 |    40646600 |     8120320 |    355663 |     21 |
| s51127       |    38398876 |    33644544 |    238865 |     14 |
| p50380g50838 |    30367288 |    20283392 |    533152 |      1 |
| s51058       |    23281664 |        8192 |    500270 |      1 |
| u4974        |    20480000 |    10821632 |    319211 |      1 |
| p50380g50551 |    18808641 |    11518976 |     82714 |     12 |
| s51698       |    16973824 |       49152 |    331992 |      5 |
| s52365       |    16711520 |    32295936 |     45388 |     49 |
| p50380g50592 |    15220736 |           0 |     65275 |      1 |
| s52107       |     9977856 |           0 |     43835 |      1 |
| u3617        |     9945088 |      204800 |    194754 |      9 |
| u2248        |     9166848 |     1040384 |    108205 |      4 |
| u3949        |     8421376 |           0 |    151124 |      2 |
| s52037       |     7405568 |      770048 |     52757 |      4 |
| s51892       |     4691430 |     5875712 |     93782 |     11 |
| s51073       |     2637824 |           0 |     22381 |      1 |
| p50380g50512 |     2507324 |     2019328 |      4709 |     49 |
| s52524       |     2465792 |        8192 |     46148 |      1 |
| u3533        |     2170880 |     2154496 |     13208 |      4 |
| s52744       |     1081344 |           0 |       874 |      1 |
| u4301        |      802816 |           0 |      2643 |      2 |
| s51329       |      540672 |           0 |      4195 |      4 |
| s51755       |      106496 |       98304 |       451 |      6 |
| s53381       |       98304 |       98304 |       429 |      6 |
| s53024       |       57344 |        8192 |      1000 |      1 |
| s53012       |       49152 |       16384 |       335 |      1 |
| p50380g40014 |       16384 |           0 |        47 |      1 |
| s51055       |       16384 |           0 |         0 |      1 |
| s53463       |       16384 |           0 |        69 |      1 |
| u2117        |       16384 |           0 |         0 |      1 |
| s51206       |       16384 |       16384 |         8 |      1 |
| s51223       |       16384 |       16384 |        24 |      1 |
| u3214        |       16384 |           0 |         0 |      1 |
| s53151       |        8192 |        8192 |         0 |      1 |
| s51417       |        8192 |        8192 |         0 |      1 |
+--------------+-------------+-------------+-----------+--------+
72 rows in set, 3 warnings (15.21 sec)

When I run the same query as the tool account (s53508), it only finds 25 owners:

MariaDB [information_schema]> SELECT
    ->                   SUBSTRING_INDEX(table_schema, '_', 1) as owner
    ->                 , SUM( data_length ) as data_bytes
    ->                 , SUM( index_length ) as index_bytes
    ->                 , SUM( table_rows ) as row_count
    ->                 , COUNT(1) as tables
    ->                 FROM information_schema.TABLES
    ->                 WHERE table_schema regexp '^[psu][0-9]'
    ->                 GROUP BY owner
    ->                 ORDER BY data_bytes DESC;
+--------------+-------------+-------------+-----------+--------+
| owner        | data_bytes  | index_bytes | row_count | tables |
+--------------+-------------+-------------+-----------+--------+
| u2041        | 19788578816 | 10820780032 | 442218826 |      6 |
| p50380g50692 |  5351828488 |  2482273280 | 153929591 |     54 |
| p50380g50921 |  5262351008 |  3673131008 |  97759098 |     21 |
| u12219       |  2914885632 |   243286016 |  66134870 |     18 |
| u2815        |  2650903816 |  2105239552 |  73758263 |     40 |
| s51111       |  2341691392 |  2183348224 |  43533672 |     73 |
| s53311       |  2244091904 |   196001792 |  51960598 |      8 |
| s52690       |  1317254816 |   431308800 |  41395499 |      3 |
| p50380g50491 |  1167433728 |   732028928 |  24211629 |     58 |
| s51334       |   582770688 |   275841024 |  13443019 |      1 |
| s51454       |   430841856 |   248389632 |   8796653 |      9 |
| u3182        |   291061760 |   779517952 |   5220218 |      1 |
| s52490       |   136347648 |   111484928 |    971295 |      5 |
| s51306       |    78774272 |    12648448 |     70406 |      2 |
| u2402        |    42795008 |      303104 |    196733 |      4 |
| p50380g50728 |    40646600 |     8120320 |    355663 |     21 |
| s51127       |    26271744 |    26542080 |    109292 |      3 |
| u4974        |    20480000 |    10821632 |    319211 |      1 |
| p50380g50592 |    15220736 |           0 |     65275 |      1 |
| s51892       |     1238011 |     1944576 |     27306 |      6 |
| s53024       |       57344 |        8192 |      1000 |      1 |
| s53012       |       49152 |       16384 |       335 |      1 |
| s51206       |       16384 |       16384 |         8 |      1 |
| s51223       |       16384 |       16384 |        24 |      1 |
| s53463       |       16384 |           0 |        69 |      1 |
+--------------+-------------+-------------+-----------+--------+
25 rows in set, 1 warning (1.04 sec)

The difference in these collections seems to be whether or not the table_schema ends in _p. I would like this tool to be able to see all of the information_schema.TABLES rows and not just the ones that end in _p. It would be nice to have this ability on labsdb1001, labsdb1003, and labsdb1005.

Event Timeline

+-----------------------------------------------------------------+
| Grants for s53508@%                                             |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 's53508'@'%' IDENTIFIED BY PASSWORD '...' |
| GRANT ALL PRIVILEGES ON `s53508\_\_%`.* TO 's53508'@'%'         |
| GRANT SELECT, SHOW VIEW ON `%\_p`.* TO 's53508'@'%'             |
+-----------------------------------------------------------------+
+--------------------------------------------------------------------+
| Grants for u3518@%                                                 |
+--------------------------------------------------------------------+
| GRANT SHOW VIEW ON *.* TO 'u3518'@'%' IDENTIFIED BY PASSWORD '...' |
| GRANT ALL PRIVILEGES ON `u3518\_\_%`.* TO 'u3518'@'%'              |
| GRANT SELECT, SHOW VIEW ON `%\_p`.* TO 'u3518'@'%'                 |
+--------------------------------------------------------------------+
Andrew claimed this task.
Andrew added a subscriber: Andrew.

Done via GRANT SHOW VIEW ON *.* TO 's53508'@'%' on labsdb1001 and labdb1003