Details
-
New Feature
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
mysql_secure_installation currently uses DML for all of its tasks. I think it should use DDL and DCL instead. The main reason is that I think it would be safer in environments where replication is used. Currently, slaves can see errors like this:
2019-08-05 17:09:10 11 [ERROR] mysqld: Can't find record in 'global_priv'
|
2019-08-05 17:09:10 11 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table mysql.global_priv; Can't find record in 'global_priv', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mariadb-bin.000002, end_log_pos 7529, Gtid 0-1-111, Internal MariaDB error code: 1032
|
2019-08-05 17:09:10 11 [Warning] Slave: Can't find record in 'global_priv' Error_code: 1032
|
2019-08-05 17:09:10 11 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mariadb-bin.000002' position 7289
|
- To set the root user's password, it currently uses the following statement:
UPDATE mysql.global_priv SET priv=json_set(priv, '$.plugin', 'mysql_native_password', '$.authentication_string', PASSWORD('$esc_pass')) WHERE User='root';"
|
https://github.com/MariaDB/server/blob/mariadb-10.4.7/scripts/mysql_secure_installation.sh#L311
I think it should use ALTER USER IF EXISTS instead. For example:
ALTER USER IF EXISTS 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('$esc_pass');
|
ALTER USER IF EXISTS 'root'@'127.0.0.1' IDENTIFIED VIA mysql_native_password USING PASSWORD('$esc_pass');
|
ALTER USER IF EXISTS 'root'@'::1' IDENTIFIED VIA mysql_native_password USING PASSWORD('$esc_pass');
|
- To delete the anonymous users, it currently uses the following statement:
DELETE FROM mysql.global_priv WHERE User='';"
|
https://github.com/MariaDB/server/blob/mariadb-10.4.7/scripts/mysql_secure_installation.sh#L331
I think it should use DROP USER IF EXISTS instead. For example:
DROP USER IF EXISTS ''@'%';
|
MDEV-13486 might have to be fixed before this could work.
- To delete the non-local root users, it currently uses the following statement:
DELETE FROM mysql.global_priv WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');"
|
https://github.com/MariaDB/server/blob/mariadb-10.4.7/scripts/mysql_secure_installation.sh#L343
I think it should use DROP USER IF EXISTS instead. For example:
DROP USER IF EXISTS 'root'@'$HOSTNAME';
|
- To remove privileges on the test database, it currently uses the following statement:
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%'
|
https://github.com/MariaDB/server/blob/mariadb-10.4.7/scripts/mysql_secure_installation.sh#L361
I think it should use REVOKE instead. For example:
REVOKE ALL PRIVILEGES ON test.* FROM ''@'%';
|
MDEV-13486 might have to be fixed before this could work.
Attachments
Issue Links
- is blocked by
-
MDEV-13486 Make mysql_install_db create a real ''@'%' anonymous account for the test database
-
- Closed
-
-
-
- Closed
-
- is part of
-
MDEV-26593 Replace mysql_secure_installation with a notice that it is useless
-
- Open
-