On Sun, 21 Jun 2020 18:24:19 -0700 PGNet Dev <[email protected]> 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) _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
