Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-29651

information_schema.processlist.host is empty when server started with --skip-grant-tables

    XMLWordPrintable

Details

    Description

      1. MariaDB docs on information_schema.processlist do not mention the fact that :port is appended to the .host column, when connecting via TCP:

      mysql --host 127.0.0.1 --port 3307 --protocol=tcp -uroot -pXXXXXXX -e "select @@version; select * from information_schema.processlist";
      +----------------+
      | @@version      |
      +----------------+
      | 10.6.8-MariaDB |
      +----------------+
      +----+----------+-----------------+------+---------+------+----------------------+----------------------------------------------+---------+-------+-----------+----------+-------------+-----------------+---------------+----------+----------------------------------------------+-------+
      | ID | USER     | HOST            | DB   | COMMAND | TIME | STATE                | INFO                                         | TIME_MS | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | MAX_MEMORY_USED | EXAMINED_ROWS | QUERY_ID | INFO_BINARY                                  | TID   |
      +----+----------+-----------------+------+---------+------+----------------------+----------------------------------------------+---------+-------+-----------+----------+-------------+-----------------+---------------+----------+----------------------------------------------+-------+
      | 13 | root     | localhost:58162 | NULL | Query   |    0 | Filling schema table | select * from information_schema.processlist |   0.401 |     0 |         0 |    0.000 |      139544 |          139544 |             0 |        6 | select * from information_schema.processlist | 27930 |
      +----+----------+-----------------+------+---------+------+----------------------+----------------------------------------------+---------+-------+-----------+----------+-------------+-----------------+---------------+----------+----------------------------------------------+-------+
      1 row in set (0.001 sec)
      

      The MySQL 8.0 docs on information_schema.processlist do mention the presence of the TCP port.

      2. When the server is started with --skip-grant-tables, then information_schema.processlist.host is '' (empty string).

      $ mysql --host 127.0.0.1 --port 3306 --protocol=tcp -uroot -pXXXXX  -e 'select * from information_schema.processlist';
      +----+----------+------+------+---------+------+----------------------+----------------------------------------------+---------+-------+-----------+----------+-------------+-----------------+---------------+----------+----------------------------------------------+------+
      | ID | USER     | HOST | DB   | COMMAND | TIME | STATE                | INFO                                         | TIME_MS | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | MAX_MEMORY_USED | EXAMINED_ROWS | QUERY_ID | INFO_BINARY                                  | TID  |
      +----+----------+------+------+---------+------+----------------------+----------------------------------------------+---------+-------+-----------+----------+-------------+-----------------+---------------+----------+----------------------------------------------+------+
      |  4 | root     |      | NULL | Query   |    0 | Filling schema table | select * from information_schema.processlist |   0.257 |     0 |         0 |    0.000 |      123176 |          123176 |             0 |        3 | select * from information_schema.processlist | 6999 |
      +----+----------+------+------+---------+------+----------------------+----------------------------------------------+---------+-------+-----------+----------+-------------+-----------------+---------------+----------+----------------------------------------------+------+
       
      $ mysql --protocol=socket --socket="$PATH/mysql.sock" -uroot -pXXXXX -e 'select * from information_schema.processlist';
      +----+----------+------+------+---------+------+----------------------+----------------------------------------------+---------+-------+-----------+----------+-------------+-----------------+---------------+----------+----------------------------------------------+------+
      | ID | USER     | HOST | DB   | COMMAND | TIME | STATE                | INFO                                         | TIME_MS | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | MAX_MEMORY_USED | EXAMINED_ROWS | QUERY_ID | INFO_BINARY                                  | TID  |
      +----+----------+------+------+---------+------+----------------------+----------------------------------------------+---------+-------+-----------+----------+-------------+-----------------+---------------+----------+----------------------------------------------+------+
      |  6 | root     |      | NULL | Query   |    0 | Filling schema table | select * from information_schema.processlist |   0.244 |     0 |         0 |    0.000 |      123176 |          123176 |             0 |        7 | select * from information_schema.processlist | 6999 |
      +----+----------+------+------+---------+------+----------------------+----------------------------------------------+---------+-------+-----------+----------+-------------+-----------------+---------------+----------+----------------------------------------------+------+
      


      The lack of documentation about the TCP port is a potential security hazard: if users are looking for connections as root from the local system, they may use a query like the following, and overlook TCP-based connections:

      select * from information_schema.processlist where user='root' and host='localhost';   -- fails to notice TCP connections from localhost
      select * from information_schema.processlist where host regexp '^localhost(:[0-9]+)?$';  -- does include TCP connections from localhost
      

      The fact that host='' when the server is started with --skip-grant-tables feels more like a bug/inadvertent omission. Likely caused by a nonexistent JOIN with mysql.user.

      Attachments

        Activity

          People

            Unassigned Unassigned
            dlenski Daniel Lenski (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.