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
-
Activity
Taking a closer look, I do not see an obvious way to replace:
DELETE FROM mysql.global_priv WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');"
|
Without first executing a query to gain the list of hostnames for use in:
drop_errors=0
|
for HOSTNAME in $NON_LOCALHOST_HOST_NAMES; do
|
do_query "DROP USER IF EXISTS 'root'@'$HOSTNAME';"
|
if [ $? -ne 0 ]; then
|
drop_errors=1
|
fi
|
done
|
Perhaps a syntax extension to DROP USER is still required ....
There is another reason to invest in this: for many valid reasons people can-and-do copy from these scripts, so they should lead-by-example and use the official user-space API and not lure people into thinking that direct manipulation of the mysql. tables is "the right and official way" to do it.
This may have once been a problem, but at a glance I see that:
Thus, I believe all the building blocks exist to make this script be living documentation – a running example safe for emulation – of user creation/removal/modification.