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)