maria-discuss team mailing list archive

[Daniel Black <daniel@xxxxxxxxxxxxx>]

On Sun, 21 Jun 2020 18:24:19 -0700
PGNet Dev <pgnet.dev@xxxxxxxxx> 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)