GA

2025/02/17

validate_passwordコンポーネントを消さずにパスワードポリシーを満たさないパスワードを設定する

TL;DR

  • CREATE USER .. IDENTIFIED WITH caching_sha2_password AS x'..' の形式で入れればMySQL側は元の文字列を知らないのでパスワードポリシーに引っかかることはない

自分で建てたわけではないMySQL を使って遊んでいるところだけれど、 PMM を入れる時にちょっと簡単なパスワードを使いたいと思った。


mysql> CREATE USER pmm@xxx IDENTIFIED BY 'xxx';

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

いつものローカルLinuxだったら —initialize-insecure でセットアップしちゃうからすっかり忘れてた。

mysql> SHOW VARIABLES LIKE '%validate%';
+-------------------------------------------------+--------+
| Variable_name                                   | Value  |
+-------------------------------------------------+--------+
| innodb_validate_tablespace_paths                | OFF    |
| lakehouse_validate_table                        |        |
| validate_password.changed_characters_percentage | 0      |
| validate_password.check_user_name               | ON     |
| validate_password.dictionary_file               |        |
| validate_password.length                        | 8      |
| validate_password.mixed_case_count              | 1      |
| validate_password.number_count                  | 1      |
| validate_password.policy                        | MEDIUM |
| validate_password.special_char_count            | 1      |
+-------------------------------------------------+--------+
10 rows in set (0.00 sec)

使いたいパスワードに数字が入ってないのがいけないんだと思うので、無理を通すなら validate_password コンポーネント(MySQL 8.0とそれ以降で validate_password プラグインから validate_password コンポーネントに変わる) をアンインストールしてインストールしなおすのも手か？

mysql> UNINSTALL COMPONENT 'file://component_validate_password';
ERROR 1142 (42000): DELETE command denied to user 'admin'@'xxx' for table 'component'

権限が足りない。であれば設定だけちょろまかして

mysql> SET SESSION validate_password.number_count = 0;
ERROR 1229 (HY000): Variable 'validate_password.number_count' is a GLOBAL variable and should be set with SET GLOBAL

mysql> SET GLOBAL validate_password.number_count = 0;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

これも権限が足りない。
それでもパスワードを強いものにするのは何か負けた気がするので考えてみたら、ハッシュ化されたあとのauthentication_stringを使えば生のパスワードはMySQLにもわからないんだと思い出す。

他のマシンから SELECT authentication_string FROM mysql.user WHERE uesr = 'pmm' で値を引いてそのまま新しい方に使ってみる。これはたぶん転ける。

mysql> CREATE USER pmm@xxx IDENTIFIED  WITH caching_sha2_password AS '$A$005$..';
ERROR 1827 (HY000): The password hash doesn't have the expected format.

caching_sha2_passwordのauthentication_stringはバイナリなので、端末に表示されてない制御文字が含まれている可能性が十分ある。
なので、16進数で取ってきて x'..' の修飾子で突っ込む。

mysql> CREATE USER pmm@xxx IDENTIFIED WITH caching_sha2_password AS x'24412430xxxxx';

Query OK, 0 rows affected (0.00 sec)

思惑通り成功。
………これ誰でもvalidate_passwordすり抜けられるんじゃ（）

​
投稿者 時刻: 0 件のコメント :
ラベル:

2025/02/15

table_open_cacheに関する実験

TL;DR

  • table_open_cache を増やすとメモリ使用量が増えるというのは聞いたことがあるけど実際どれくらい増えるのか調べてみたかった
  • 1コネクションで1つずつ SELECT * FROM t で65535個のテーブルを開かせてみた。
    • かなりシンプルなテーブルなので複雑なのにするともっと食うかもしれない
    • 競合が起こらないと仮定したケースなのでスピードはテーブルキャッシュヒット率に関わらずほぼ一定
      • 起動直後が遅いのはデータディクショナリがあったまってないからじゃなかろうか
  • 肝心のメモリ使用量は綺麗な結果にはならなかった
    • ↓memory(%)は全部でだいたい15GBなので1%が150MB
    • 雑に、1万増えると270MBくらい？
table_open_cache count memory(%) time(s)
4000 0 1.9 N/A
4000 1 6.6 155
4000 2 7.2 105
4000 3 7.2 107
8000 0 3.1 N/A
8000 1 6.6 139
8000 2 7.4 104
8000 3 7.4 105
12000 0 3.2 N/A
12000 1 6.8 149
12000 2 9.0 109
12000 3 9.5 109
24000 0 3.1 N/A
24000 1 7.2 146
24000 2 9.3 109
24000 3 11.3 110
65535 0 3.1 N/A
65535 1 7.3 146
65535 2 9.1 104
65535 3 9.7 105
100000 0 3.1 N/A
100000 1 7.9 146
100000 2 10.2 104
100000 3 11.1 104

準備。
d2スキーマに65535個のテーブルを作る。

$ perl -MDBI -E 'my $conn= DBI->connect("dbi:mysql:d1;mysql_socket=/usr/mysql/8.0.41/data/mysql.sock", "root", ""); for (my $n= 1; $n <= 65535; $n++) { my $sql= sprintf("CREATE TABLE IF NOT EXISTS d2.%05d (num int)", $n); $conn->do($sql); }'

一度シャットダウンして起動しなおす。 innodb_buffer_pool_load_at_startup はOFF。

$ ps auxwwww | grep mysqld

yoku0825 1738789 31.0  1.9 1017124 305532 pts/0  Sl   00:49   0:03 /usr/mysql/8.0.41/bin/mysqld --basedir=/usr/mysql/8.0.41 --datadir=/usr/mysql/8.0.41/data --plugin-dir=/usr/mysql/8.0.41/lib/plugin --server-id=1080 --default-authentication-plugin=mysql_native_password --mysqlx=off --early-plugin-load=keyring_file.so --loose-keyring-file-data=/usr/mysql/8.0.41/keyring --admin-address=127.0.0.1 --admin-port=63080 --create-admin-listener-thread=ON --loose-innodb-print-ddl-logs --innodb-validate-tablespace-paths=OFF --log-error=error.log --pid-file=/usr/mysql/8.0.41/data/mysql.pid --socket=/usr/mysql/8.0.41/data/mysql.sock --port=64080

$ mysql80 -sse "SELECT @@table_open_cache"
4000

テスト用のクエリは65535個の各テーブルをSELECT *する（何も入ってないので空っぽ）だけ。

$ date ; time perl -MDBI -E 'my $conn= DBI->connect("dbi:mysql:d1;mysql_socket=/usr/mysql/8.0.41/data/mysql.sock", "root", ""); for (my $n= 1; $n <= 65535; $n++) { my $sql= sprintf("SELECT * FROM d2.%05d", $n); $conn->do($sql); }' ; date
Fri Feb 14 00:50:48 GMT 2025

real    2m35.310s
user    0m0.989s
sys     0m1.514s
Fri Feb 14 00:53:23 GMT 2025

$ ps auxwwww | grep mysqld 

yoku0825 1738789 14.9  6.6 2896216 1069348 pts/0 Sl   00:49   0:58 /usr/mysql/8.0.41/bin/mysqld --basedir=/usr/mysql/8.0.41 --datadir=/usr/mysql/8.0.41/data --plugin-dir=/usr/mysql/8.0.41/lib/plugin --server-id=1080 --default-authentication-plugin=mysql_native_password --mysqlx=off --early-plugin-load=keyring_file.so --loose-keyring-file-data=/usr/mysql/8.0.41/keyring --admin-address=127.0.0.1 --admin-port=63080 --create-admin-listener-thread=ON --loose-innodb-print-ddl-logs --innodb-validate-tablespace-paths=OFF --log-error=error.log --pid-file=/usr/mysql/8.0.41/data/mysql.pid --socket=/usr/mysql/8.0.41/data/mysql.sock --port=64080

1回目の時点では 422 table/sec でオープン完了、メモリの利用率は4.7%増(15.3GBくらいメモリがあるので700MBくらいの増)

テーブルオープンにはデータディクショナリもアクセスするからInnoDBバッファプールもあるだろうしテーブルキャッシュが効かなさそうな数だけど2回目は速くなるはず。

$ date ; time perl -MDBI -E 'my $conn= DBI->connect("dbi:mysql:d1;mysql_socket=/usr/mysql/8.0.41/data/mysql.sock", "root", ""); for (my $n= 1; $n <= 65535; $n++) { my $sql= sprintf("SELECT * FROM d2.%05d", $n); $conn->do($sql); }' ; date
Fri Feb 14 00:59:08 GMT 2025

real    1m45.284s
user    0m0.906s
sys     0m1.410s
Fri Feb 14 01:00:53 GMT 2025

$ !ps
ps auxwwww | grep mysqld ; date
yoku0825 1738789 11.1  7.2 3027288 1161812 pts/0 Sl   00:49   1:42 /usr/mysql/8.0.41/bin/mysqld --basedir=/usr/mysql/8.0.41 --datadir=/usr/mysql/8.0.41/data --plugin-dir=/usr/mysql/8.0.41/lib/plugin --server-id=1080 --default-authentication-plugin=mysql_native_password --mysqlx=off --early-plugin-load=keyring_file.so --loose-keyring-file-data=/usr/mysql/8.0.41/keyring --admin-address=127.0.0.1 --admin-port=63080 --create-admin-listener-thread=ON --loose-innodb-print-ddl-logs --innodb-validate-tablespace-paths=OFF --log-error=error.log --pid-file=/usr/mysql/8.0.41/data/mysql.pid --socket=/usr/mysql/8.0.41/data/mysql.sock --port=64080
yoku0825 1739879  0.0  0.0   9292  1092 pts/0    S+   01:04   0:00 grep --color=auto mysqld
Fri Feb 14 01:04:25 GMT 2025

3回目。

$ date ; time perl -MDBI -E 'my $conn= DBI->connect("dbi:mysql:d1;mysql_socket=/usr/mysql/8.0.41/data/mysql.sock", "root", ""); for (my $n= 1; $n <= 65535; $n++) { my $sql= sprintf("SELECT * FROM d2.%05d", $n); $conn->do($sql); }' ; date
Fri Feb 14 01:04:47 GMT 2025

real    1m47.792s
user    0m0.776s
sys     0m1.589s
Fri Feb 14 01:06:35 GMT 2025

$ !ps
ps auxwwww | grep mysqld ; date
yoku0825 1738789 13.2  7.2 3027288 1162340 pts/0 Sl   00:49   2:26 /usr/mysql/8.0.41/bin/mysqld --basedir=/usr/mysql/8.0.41 --datadir=/usr/mysql/8.0.41/data --plugin-dir=/usr/mysql/8.0.41/lib/plugin --server-id=1080 --default-authentication-plugin=mysql_native_password --mysqlx=off --early-plugin-load=keyring_file.so --loose-keyring-file-data=/usr/mysql/8.0.41/keyring --admin-address=127.0.0.1 --admin-port=63080 --create-admin-listener-thread=ON --loose-innodb-print-ddl-logs --innodb-validate-tablespace-paths=OFF --log-error=error.log --pid-file=/usr/mysql/8.0.41/data/mysql.pid --socket=/usr/mysql/8.0.41/data/mysql.sock --port=64080
yoku0825 1739938  0.0  0.0   9292  1152 pts/0    S+   01:07   0:00 grep --color=auto mysqld
Fri Feb 14 01:07:31 GMT 2025

2回目3回目は 606 table / secくらい。メモリは0.6%増で90MBくらい。

……というのをパラメータを変えながら延々試していた。

1つだけハマったのが、 table_open_cache=100000 と table_open_cache=200000 の時にだけ何故かメモリ使用量が逆転する状況になって、なんでかと頭を捻ってたら

$ grep table_open_cache /usr/mysql/8.0.41/data/error.log
2025-02-14T03:07:36.786684-00:00 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 431 (requested 200000)

MySQLが自動調整していました。

​
投稿者 時刻: 0 件のコメント :
ラベル:
登録: 投稿 ( Atom )