maria-discuss team mailing list archive

[Daniel Black <daniel@xxxxxxxxxxxxx>]

On Sun, 21 Jun 2020 16:34:25 -0700
PGNet Dev <pgnet.dev@xxxxxxxxx> 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.