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