Re: [Maria-discuss] mdb 10.4.13 manual delete/recreate of 'root' user fails: ERROR 1471 (HY000): The target table user of the INSERT is not insertable-into ?

22 Jun 2020

      On Sun, 21 Jun 2020 16:34:25 -0700
PGNet Dev <pgnet.dev@gmail.com> wrote:
...
 i've installed distro-packaged MDB
mariadb -V
      mariadb  Ver 15.1 Distrib 10.4.13-MariaDB, for Linux (x86_64) using readline 5.1
on
lsb_release -rd
      Description:    Arch Linux
      Release:        rolling
  uname -rm
      5.7.3-arch1-1 x86_64
i'd like to modify & delete/restore my 'root' user; an exercise in recovery ...
starting with a clean install,
mysql -e "
   SELECT User, Host, HEX(authentication_string)
    FROM mysql.user;"
  "
      +-------------+-----------+----------------------------+
      | User        | Host      | HEX(authentication_string) |
      +-------------+-----------+----------------------------+
      | mariadb.sys | localhost |                            |
      | root        | localhost | 696E76616C6964             |
      | mysql       | localhost | 696E76616C6964             |
      +-------------+-----------+----------------------------+
mysql -e "
   SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';')
    FROM mysql.user
    WHERE  user = 'root';
  "
      +----------------------------------------------------------+
      | CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') |
      +----------------------------------------------------------+
      | SHOW GRANTS FOR 'root'@'localhost';                      |
      +----------------------------------------------------------+
adding grants & mod'ing,
mysql
      >
REVOKE ALL           ON *.* FROM 'root'@'localhost';
   REVOKE GRANT OPTION  ON *.* FROM 'root'@'localhost';
   REVOKE ALL           ON *.* FROM 'root'@'%';
   REVOKE GRANT OPTION  ON *.* FROM 'root'@'%';
   GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
   GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'         WITH GRANT OPTION;
   ALTER USER 'root'@'localhost' IDENTIFIED VIA unix_socket;
   ALTER USER 'root'@'%'         IDENTIFIED VIA unix_socket;
   FLUSH PRIVILEGES;
   exit;
works as expected
mysql -e "
   SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';')
    FROM mysql.user
    WHERE  user = 'root';
  "
      +----------------------------------------------------------+
      | CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') |
      +----------------------------------------------------------+
      | SHOW GRANTS FOR 'root'@'%';                              |
      | SHOW GRANTS FOR 'root'@'localhost';                      |
      +----------------------------------------------------------+
mysql -e "
   SELECT CONCAT('plugin: ', plugin, '   \'', user, '\'@\'', host, '\';')
    FROM mysql.user
    WHERE  user = 'root';
  "
      +-----------------------------------------------------------------+
      | CONCAT('plugin: ', plugin, '   \'', user, '\'@\'', host, '\';') |
      +-----------------------------------------------------------------+
      | plugin: unix_socket   'root'@'localhost';                       |
      | plugin: unix_socket   'root'@'%';                               |
      +-----------------------------------------------------------------+
mysql -e "
   SELECT User, Host, HEX(authentication_string) FROM mysql.user;
  "
      +-------------+-----------+----------------------------+
      | User        | Host      | HEX(authentication_string) |
      +-------------+-----------+----------------------------+
      | mariadb.sys | localhost |                            |
      | root        | localhost |                            |
      | mysql       | localhost | 696E76616C6964             |
      | root        | %         |                            |
      +-------------+-----------+----------------------------+
mysql -e status
      --------------
      mysql  Ver 15.1 Distrib 10.4.13-MariaDB, for Linux (x86_64) using readline 5.1
Connection id:          24
      Current database:
      Current user:           root@localhost
      SSL:                    Not in use
      Current pager:          stdout
      Using outfile:          ''
      Using delimiter:        ;
      Server:                 MariaDB
      Server version:         10.4.13-MariaDB-log Arch Linux
      Protocol version:       10
      Connection:             Localhost via UNIX socket
      Server characterset:    utf8mb4
      Db     characterset:    utf8mb4
      Client characterset:    utf8
      Conn.  characterset:    utf8
      UNIX socket:            /run/mysqld/mysqld.sock
      Uptime:                 15 min 9 sec
Threads: 8  Questions: 93  Slow queries: 0  Opens: 19  Flush tables: 1  Open tables: 13  Queries per second avg: 0.102
      --------------
if i stop, then safe-restart mdb
systemctl stop mariadb.service
  killall mysqld
  killall mysqld
  sleep 10
mysqld_safe \
   --defaults-file=/usr/local/etc/mariadb/my.cnf \
   --skip-grant-tables \
   --skip-networking &
mysql -u root mysql
      MariaDB [(mysql)]>
server still looks good,
status;
      --------------
      mysql  Ver 15.1 Distrib 10.4.13-MariaDB, for Linux (x86_64) using readline 5.1
Connection id:          9
      Current database:       mysql
      Current user:           root@
      SSL:                    Not in use
      Current pager:          /usr/bin/less
      Using outfile:          ''
      Using delimiter:        ;
      Server:                 MariaDB
      Server version:         10.4.13-MariaDB-log Arch Linux
      Protocol version:       10
      Connection:             Localhost via UNIX socket
      Server characterset:    utf8mb4
      Db     characterset:    utf8mb4
      Client characterset:    utf8
      Conn.  characterset:    utf8
      UNIX socket:            /run/mysqld/mysqld.sock
      Uptime:                 14 sec
Threads: 8  Questions: 37  Slow queries: 0  Opens: 34  Flush tables: 1  Open tables: 30  Queries per second avg: 2.642
      --------------
if I *destroy* the 'root' user
user mysql;
  DELETE FROM mysql.user
   WHERE  user = 'root'
   AND host = 'localhost'
   OR  host = '%';
  flush privileges;
  SELECT User, Host, HEX(authentication_string) FROM mysql.user;
+-------------+-----------+----------------------------+
      | User        | Host      | HEX(authentication_string) |
      +-------------+-----------+----------------------------+
      | mariadb.sys | localhost |                            |
      | mysql       | localhost | 696E76616C6964             |
      +-------------+-----------+----------------------------+
then, get a pass hash,
select password('testpass');
      +-------------------------------------------+
      | password('testpass')                      |
      +-------------------------------------------+
      | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |
      +-------------------------------------------+
& attempt to re-init & insert the 'root' user
insert into `user` VALUES('localhost','root','*00E247AC5F9AF26AE0194B41E1E769DEE1429A29','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'','');
it fails
ERROR 1471 (HY000): The target table user of the INSERT is not insertable-into
what different/additional steps are required to recreate a deleted root user?
There are perfectly usable SHOW CREATE USER (https://mariadb.com/kb/en/show-create-user/) to get the SQL to create a user, and CREATE USER (https://mariadb.com/kb/en/create-user/) like what SHOW CREATE USER outputs, it is a portable, future safe way to recreate users that isn't dependent on however structure MariaDB uses internally.

DROP USER (https://mariadb.com/kb/en/drop-user/) is for removing users.

FLUSH PRIVILEGES (https://mariadb.com/kb/en/flush/) isn't need when you use any proper SQL to create/modify/drop users.

Re: [Maria-discuss] mdb 10.4.13 manual delete/recreate of 'root' user fails: ERROR 1471 (HY000): The target table user of the INSERT is not insertable-into ?

Daniel Black