MariaDBユーザー作成時のHOST指定とパスワード設定｜mysql CLIで安全に構成する

この記事のポイント

・CREATE USER 'user'@'host' IDENTIFIED BY 'pass' でユーザーを作成できる
・localhost（UNIX ソケット）と 127.0.0.1（TCP）は別エントリ—接続方式に合わせて使い分ける
・MariaDB 10.4 以降の root は unix_socket 認証が既定—sudo mysql でパスワードなしにログイン可
・作成後は SELECT User,Host,plugin FROM mysql.user; で必ず確認する

'appuser'@'localhost'   -- localhost 経由（UNIX ソケット）でのみ接続可
'appuser'@'127.0.0.1'  -- TCP/IP ループバックでのみ接続可

[rocky@sv01 ~]$ sudo mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.5.22-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

CREATE USER 'ユーザー名'@'HOST' IDENTIFIED BY 'パスワード';

MariaDB [(none)]> CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'P@ssw0rd_2024';
Query OK, 0 rows affected (0.001 sec)

# 特定データベース（appdb）への全権限
MariaDB [(none)]> GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';
Query OK, 0 rows affected (0.001 sec)

# 読み取りのみ許可する場合
MariaDB [(none)]> GRANT SELECT ON appdb.* TO 'appuser'@'localhost';
Query OK, 0 rows affected (0.001 sec)

[rocky@sv01 ~]$ mysql -u appuser -p appdb
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.5.22-MariaDB MariaDB Server

MariaDB [appdb]>

-- ローカル CLI と TCP ループバックの両方を許可する例
CREATE USER 'appuser'@'localhost'  IDENTIFIED BY 'P@ssw0rd_2024';
CREATE USER 'appuser'@'127.0.0.1' IDENTIFIED BY 'P@ssw0rd_2024';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'127.0.0.1';

MariaDB [(none)]> SELECT User, Host, plugin FROM mysql.user WHERE User='root';
+------+-----------+-------------+
| User | Host      | plugin      |
+------+-----------+-------------+
| root | localhost | unix_socket |
+------+-----------+-------------+
1 row in set (0.001 sec)

MariaDB [(none)]> ALTER USER 'root'@'localhost'
    ->   IDENTIFIED VIA mysql_native_password USING PASSWORD('NewRootP@ss2024');
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> ALTER USER 'appuser'@'localhost' IDENTIFIED BY '新しいパスワード';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SELECT User, Host, plugin FROM mysql.user;
+-------------+-----------+-----------------------+
| User        | Host      | plugin                |
+-------------+-----------+-----------------------+
| root        | localhost | unix_socket           |
| appuser     | localhost | mysql_native_password |
| appuser     | 127.0.0.1 | mysql_native_password |
| mariadb.sys | localhost | mysql_native_password |
+-------------+-----------+-----------------------+
4 rows in set (0.001 sec)

MariaDB [(none)]> SHOW GRANTS FOR 'appuser'@'localhost';
+----------------------------------------------------------------+
| Grants for appuser@localhost                                    |
+----------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON `appdb`.* TO `appuser`@`localhost`    |
+----------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> DROP USER 'appuser'@'localhost';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SELECT User, Host FROM mysql.user WHERE User='appuser';
+---------+-----------+
| User    | Host      |
+---------+-----------+
| appuser | 127.0.0.1 |
+---------+-----------+
1 row in set (0.000 sec)

MariaDB [(none)]> SHOW GRANTS FOR 'appuser'@'localhost';
+--------------------------------------------+
| Grants for appuser@localhost               |
+--------------------------------------------+
| GRANT USAGE ON *.* TO `appuser`@`localhost`|
+--------------------------------------------+
1 row in set (0.000 sec)