maria-discuss team mailing list archive

[PGNet Dev <pgnet.dev@xxxxxxxxx>]

 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?