[MDEV-20259] mysql_secure_installation should use DDL and DCL instead of DML Created: 2019-08-05  Updated: 2023-11-30

Status: Open
Project: MariaDB Server
Component/s: Scripts & Clients
Fix Version/s: None

Type: New Feature Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Ralf Gebhardt
Resolution: Unresolved Votes: 1
Labels: None

Issue Links:
Blocks
is blocked by MDEV-13486 Make mysql_install_db create a real '... Closed
is blocked by MDEV-20947 Use GRANT ... TO PUBLIC for default t... Closed
PartOf
is part of MDEV-26593 Replace mysql_secure_installation wit... Open

 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.

 Comments   
Comment by Eric Herman [ 2021-07-09 ]

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:

  • IF EXISTS clause was added to DROP USER in 10.1.3
  • IF NOT EXISTS was added to CREATE USER in 10.1.3
  • The ALTER USER statement was introduced in 10.2.0
  • SET PASSWORD works for plugins since 10.4

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.

Comment by Eric Herman [ 2021-07-11 ]

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 ....

Generated at Thu Feb 08 08:58:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.