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?
