[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 ?
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?
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.
On 6/21/20 5:47 PM, Daniel Black wrote:
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.
(clean install) systemctl start mariadb mysql >
SELECT User, Host FROM mysql.global_priv; +-------------+-----------+ | User | Host | +-------------+-----------+ | mariadb.sys | localhost | | mysql | localhost | | root | localhost | +-------------+-----------+
SHOW CREATE USER; +----------------------------------------------------------------------------------------------------+ | CREATE USER for root@localhost | +----------------------------------------------------------------------------------------------------+ | CREATE USER `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket | +----------------------------------------------------------------------------------------------------+
DROP USER `root`@`localhost`;
SELECT User, Host FROM mysql.global_priv; +-------------+-----------+ | User | Host | +-------------+-----------+ | mariadb.sys | localhost | | mysql | localhost | +-------------+-----------+
exit
this^ is the stage at which i'd get a oops-i-deleted-my-root-user instance for 'fixing' ...
systemctl restart mariadb mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
systemctl stop mariadb.service killall mysqld killall mysqld_safe sleep 5 mysqld_safe \ --defaults-file=/usr/local/etc/mariadb/my.cnf \ --skip-grant-tables \ --skip-networking &
mysql -u root >
CREATE USER `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket;
ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement
so, atm, i can't access the 'normal' running server without root user, and can't create the root user when server's running '--skip-grant-tables'.
i guess i'm missing the 'perfectly usable' part :-/
can you provide an explicit example of how to -- at this puposefully fubar'd stage -- create / init a root user?
On Sun, 21 Jun 2020 18:24:19 -0700 PGNet Dev pgnet.dev@gmail.com wrote:
On 6/21/20 5:47 PM, Daniel Black wrote:
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.
(clean install) systemctl start mariadb mysql
SELECT User, Host FROM mysql.global_priv; +-------------+-----------+ | User | Host | +-------------+-----------+ | mariadb.sys | localhost | | mysql | localhost | | root | localhost | +-------------+-----------+
SHOW CREATE USER; +----------------------------------------------------------------------------------------------------+ | CREATE USER for root@localhost | +----------------------------------------------------------------------------------------------------+ | CREATE USER `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket | +----------------------------------------------------------------------------------------------------+
DROP USER `root`@`localhost`;
SELECT User, Host FROM mysql.global_priv; +-------------+-----------+ | User | Host | +-------------+-----------+ | mariadb.sys | localhost | | mysql | localhost | +-------------+-----------+
exit
this^ is the stage at which i'd get a oops-i-deleted-my-root-user instance for 'fixing' ...
systemctl restart mariadb mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
systemctl stop mariadb.service killall mysqld killall mysqld_safe sleep 5 mysqld_safe \ --defaults-file=/usr/local/etc/mariadb/my.cnf \ --skip-grant-tables \ --skip-networking &
mysql -u root
CREATE USER `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket;
ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement
so, atm, i can't access the 'normal' running server without root user, and can't create the root user when server's running '--skip-grant-tables'.
i guess i'm missing the 'perfectly usable' part :-/
The skip-grant-tables preventing standard modification is a bit horrible still. One day I need to work out why that restriction is there.
can you provide an explicit example of how to -- at this puposefully fubar'd stage -- create / init a root user?
$ cat > /tmp/reset.sql DROP USER IF EXISTS `root`@`localhost`; CREATE USER `root`@`localhost` IDENTIFIED VIA mysql_native_password AS PASSWORD('notsosecure') OR unix_socket ; GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION;
ctrl-D (to end tell the shell to end the file, it shouldn't be part of the file).
$ sudo -u mysql /usr/sbin/mysqld --verbose --init-file=/tmp/reset.sql
$ mysql -u root -pnotsosecure Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 10 Server version: 10.4.14-MariaDB-debug-log Source 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 current_user(); +----------------+ | current_user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.001 sec)
MariaDB [(none)]> show create user ; +--------------------------------------------------------------------------------------------------------------------------------------+ | CREATE USER for root@localhost | +--------------------------------------------------------------------------------------------------------------------------------------+ | CREATE USER `root`@`localhost` IDENTIFIED VIA mysql_native_password USING '*7A7CEFE3EAE64F196620D6CC3CEF498B0DDABB85' OR unix_socket | +--------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec)
MariaDB [(none)]> shutdown; Query OK, 0 rows affected (0.001 sec)
On 6/21/20 7:23 PM, Daniel Black wrote:
so, atm, i can't access the 'normal' running server without root user, and can't create the root user when server's running '--skip-grant-tables'.
i guess i'm missing the 'perfectly usable' part :-/
The skip-grant-tables preventing standard modification is a bit horrible still. One day I need to work out why that restriction is there.
it'll certainly be convenient if it _does_ get straightened out.
can you provide an explicit example of how to -- at this puposefully fubar'd stage -- create / init a root user?
$ cat > /tmp/reset.sql
(snip)
well that's a certainly useful idea/reminder! would be nice on the wiki/docs (of course, might be there, and I missed it)
(clean install & normal start) mysql >
SELECT User, Host FROM mysql.global_priv; +-------------+-----------+ | User | Host | +-------------+-----------+ | mariadb.sys | localhost | | mysql | localhost | | root | localhost | +-------------+-----------+
DROP USER `root`@`localhost`; SELECT User, Host FROM mysql.global_priv; +-------------+-----------+ | User | Host | +-------------+-----------+ | mariadb.sys | localhost | | mysql | localhost | +-------------+-----------+
exit
verify no access
mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
kill
systemctl stop mariadb.service killall mysqld killall mysqld_safe
gen script
cat << 'EOSSL' > /tmp/root_reset.sql CREATE USER `root`@`localhost` IDENTIFIED VIA unix_socket OR ed25519 USING PASSWORD('s...4'); GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION; EOSSL
start/init (needed the '--defaults-file', here; otherwise, lots of errors!)
sudo -u mysql \ /sbin/mysqld \ --defaults-file=/usr/local/etc/mariadb/my.cnf \ --init-file=/tmp/root_reset.sql &
test, the 'new' root user's there
mysql -e "SELECT User, Host FROM mysql.global_priv;" +-------------+-----------+ | User | Host | +-------------+-----------+ | mariadb.sys | localhost | | mysql | localhost | | root | localhost | +-------------+-----------+
kill again
systemctl stop mariadb.service killall mysqld killall mysqld_safe
start normal
systemctl start mariadb
check, looks good
mysql -e " SELECT CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)) FROM mysql.global_priv WHERE user = 'root'; " +-------------------------------------------------------------------------+ | CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)) +-------------------------------------------------------------------------+ | root@localhost => { "access": 1073741823, "plugin": "ed25519", "authentication_string": "T...g", "auth_or": [
{ "plugin": "unix_socket" },
{ } ], "password_last_changed": 1592795308 } |
access with
mysql -e status | grep -i "Current user:" Current user: root@localhost
and
mysql -u root -pnotsosecure -e status | grep -i "Current user:" Current user: root@localhost
both work.
why this ALSO works,
mysql -u root -pblah -e status | grep -i "Current user:" Current user: root@localhost
i'm unclear. but a different issue ...
so, back-in-biz with at least restoring a horked/missing root user.
thxalot!
participants (2)
-
Daniel Black -
PGNet Dev