MySQLの初期設定(ユーザー作成、権限付与)

背景

MySQLのセットアップ直後では、rootユーザーしか存在しませんが、rootユーザーのみで運用することはないと思います。
必ず、ユーザーを作成し、必要な権限を付与して利用していくことになりますので、そのやり方についての備忘録となります。

前提条件

• Host: Windows11
• Guest: RockyLinux9
  • MySQL8.4

ユーザーの作成

• ユーザーの作成を行う。IDENTIFIED BYの後ろに続く文字列に、パスワードを指定する

mysql > CREATE USER IF NOT EXISTS 'app'@'localhost' IDENTIFIED BY '*********';

• ユーザーを追加する際に、'ユーザー名'@'ホスト名'を指定するが、localhost, %, CIDRなどを指定する。ただし、localhost以外の場合は、mysqlコマンドで接続する場合、-h オプションを利用する必要が出てくる。

  ホスト名	説明
  localhost	同一ホストからのみ接続を許可する。
  %	すべてのホストからの接続を許可する。
  192.168.0.0/24	CIDR形式で許可する。指定範囲のホストからのみ接続を許可する。

mysql > CREATE USER IF NOT EXISTS 'app'@'192.168.0.0/24' IDENTIFIED BY '*********';

$ mysql -u app -h 192.168.0.2 -p -A

• この状態で、mysqlに接続する
  • 権限を何も付与していないので、最低限の情報しか参照できないうえ、データベースの追加などもできない

$ mysql -u app -p -A
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.4.3 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)

mysql> CREATE DATABASE test;
ERROR 1044 (42000): Access denied for user 'app'@'localhost' to database 'test'

権限の付与

• rootユーザーでappユーザーに対して権限を付与する

mysql> GRANT ALL ON test.* TO 'app'@'localhost';
Query OK, 0 rows affected (0.00 sec)

• appユーザーでデータベースtestを作成する

$ mysql -u app -p -A
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.4.3 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| test               |
+--------------------+
3 rows in set (0.00 sec)

• appユーザーでテーブルの追加、データの追加、参照などを行う

mysql> use test
Database changed
mysql> CREATE TABLE sample_tbl (seq_id INT NOT NULL, name VARCHAR(20) NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY (seq_id));
Query OK, 0 rows affected (0.02 sec)

mysql> desc sample_tbl;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| seq_id     | int         | NO   | PRI | NULL    |       |
| name       | varchar(20) | NO   |     | NULL    |       |
| created_at | datetime    | NO   |     | NULL    |       |
| updated_at | datetime    | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> INSERT INTO sample_tbl VALUES (1, 'test name', NOW(), NOW());
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO sample_tbl VALUES (2, 'sample name', NOW(), NOW());
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM sample_tbl;
+--------+-------------+---------------------+---------------------+
| seq_id | name        | created_at          | updated_at          |
+--------+-------------+---------------------+---------------------+
|      1 | test name   | 2024-10-19 15:27:14 | 2024-10-19 15:27:14 |
|      2 | sample name | 2024-10-19 15:27:21 | 2024-10-19 15:27:21 |
+--------+-------------+---------------------+---------------------+
2 rows in set (0.00 sec)

ユーザー作成・権限付与のポイント

• 権限を付与する際には、最低限の範囲、権限に限定して付与するほうが望ましい。ただし、運用するうえで、ミスにつながるような複雑な権限管理も避けるほうが望ましい。
  • 権限を付与する際に、ALLは権限が強すぎるため、適切な権限に変更したほうがよい。複数の権限を付与することもできるので、適切な権限にすることを意識する。
  • すべてのDBに対して権限を付与することができるが、対象のDBを限定したほうが良い。
  • 接続元のホストについても、%を多用することは望ましくない。適切なCIDR設定を行ったほうが良い。
  • 開発環境については、ある程度開発しやすいように緩めの権限設定でもよいが、緩めの権限設定であることを意識しておく必要がある。

最後に

MySQLを使い始める前に必要となる、接続ユーザーの設定などについての備忘録をまとめました。
権限設定などは、セキュリティのリスクにも関係してくるため、利用する際には、特に注意して運用していく必要があります。
接続ユーザー、権限の管理の仕方によっては、アプリケーション側の作りに影響が出てくることがありますので、事前にどのような運用を行うのかをすり合わせて置くほうがよさそうです。