GRANTと最小権限 — rootで繋ぐと全部持っていかれる

GRANTと最小権限。データベースと役割別の鍵、アクセスを確認するチェックリスト。

※本記事はアフィリエイト広告(PR)を含みます。

rootで繋ぐと、事故のとき全部持っていかれます。GRANTで最小権限のユーザーを設計します。

これは深掘りです。本線の順路ではありません。 読まなくても先へは進めます。 本線の安全にはできない、被害を小さくする — セキュリティ点検の回の続きです。あの回で「最小権限」という言葉を出して、ss -tlnp で開いているポートを棚卸ししました。この記事はその先、データベース側の権限と接続経路だけを1本かけて詰めます。

前回: スロークエリの特定と書き換え — 遅いのはどのSQLか

作業場所はVMの中(Ubuntu Server)です。MySQL 8.0 以降が動いていて、MySQLの導入とSQLの基本|テーブル作成とSELECT・INSERTで作った appdb と appuser があれば、他に用意するものはありません。練習用のユーザーはこの記事の中で作って、最後に全部消します。appuser は消しません。

この記事に出てくるパスワードは、すべてダミーです。 DummyPass_App_1! のような文字列をそのまま使わないでください。自分で違う値を決めて打ってください。

この記事は守る側だけを書きます。 攻撃の手順は書きません。書かなくても、守り方は全部書けます。

対象レベル: 中級〜上級 / 区分: 発展

目次

いま、アプリはどのユーザーでDBに繋いでいますか

VMの中で、これを打ってみてください。

bash
$ grep -i '^DB_USER' ~/server/.env

返ってきた名前が root だったら、この記事はあなたのための記事です。

なぜまずいのか。root だから危ない、という話ではありません。 危ないのは、「アプリが破られたときに、破った側が手にする力」がそのユーザーの権限そのものになることです。

セキュリティ点検の回で、SQLインジェクションという名前と、その塞ぎ方(プリペアドステートメント)は扱いました。この記事ではその話を繰り返しません。 代わりに、その1つ手前と1つ後ろを見ます。

アプリのコードに1箇所でも穴が空いたとき、被害はどこで止まるか。

同じ穴でも、繋いでいるユーザーによって結末がまったく違います。

アプリのDBユーザー穴が1つ空いたときに、起こりうる最悪
root(全権)全部のデータベースが読まれる。テーブルごと消される。新しいユーザーを作られて居座られる。設定ファイルを書き出される
appdb に4操作だけappdb の中身が読まれる、書き換えられる。それ以上には広がらない
1つの表の一部の列だけその列だけ

どれも「破られている」ことに変わりはありません。 変わるのは被害の範囲です。これが多層防御の考え方で、本線の回で言った「安全にする、ではなく、壊れたときの被害を小さくする」の、データベース版です。

そしてもう1つ。権限を絞ると、事故も止まります。

深夜の障害対応で WHERE を忘れた UPDATE を打った話は別の回に書きました。あのとき使っていたのが「読み取り専用ユーザー」だったら、何も起きませんでした。 権限は攻撃者だけでなく、寝ぼけた自分からもデータを守ります。

この記事でわかること

  • root で繋いでいるかどうかが、被害の大きさをそのまま決めるということ。穴の有無ではなく、穴が開いたあとの話
  • 'app'@'localhost' と 'app'@'%' は別のユーザーで、% を書いた瞬間に「世界中のどこからでも」になるということ。しかもSSHトンネルを通した接続は localhost に見える
  • 権限は「操作の種類」だけでなく、データベース・表・列の単位で絞れること。行を絞る手段は権限ではなくビューであること
  • GRANT を打っても、いま繋がっている接続にはすぐ効かないものがあること(グローバル権限は次の接続から)
  • general_log を本番で有効にしてはいけない理由(速度ではなく、平文のパスワードが記録に残ること)
  • バックアップ用ユーザーに PROCESS が要ることと、それが他人のクエリを覗ける権限でもあるというトレードオフ

この記事でやること

  1. いまのユーザーと権限を棚卸しする(SELECT user, host と SHOW GRANTS)
  2. 最小権限のユーザーを3つ作り直す(アプリ用 / マイグレーション用 / 読み取り専用)。列単位と行単位の絞り方も試す
  3. 作ったユーザーで実際に繋いで、DROP TABLE が拒否されるのを目で見る
  4. 接続経路を絞る(bind-address の確認、ss -tlnp、SSHトンネル、REQUIRE SSL)
  5. パスワード・監査・バックアップ権限を整えて、10項目の点検チェックリストで締める

先に知る言葉

この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。

言葉意味
アカウントMySQLではユーザー名と接続元ホストの組で1つ。'app'@'localhost' と 'app'@'%' は別のアカウント
権限(プリビレッジ)SELECT や DROP など、やってよい操作の種類。アカウントごとに持つ
GRANT / REVOKE権限を与える / 取り上げる SQL
DDLCREATE TABLE や ALTER TABLE のような、表の形を変える命令。データを変える INSERT などは DML
bind-addressMySQLがどのネットワークインターフェースで待ち受けるかの設定。127.0.0.1 なら同じマシンの中からしか届かない
SSHトンネルSSHの通信路の中に、別の接続を通す仕組み。3306番を外に開けずに、手元からDBに繋ぐために使う
TLS通信を暗号化する仕組み。HTTPSのSの中身と同じもの。MySQLの接続にも使える
監査ログ誰がいつ何をしたかの記録。障害の原因追跡と、事故が起きたあとの範囲特定に使う

① いまの状態を棚卸しする

作り直す前に、いま何があるかを見ます。 数えられないものは守れません。

MySQLに管理者として入ります。Ubuntu の MySQL は root を「Linuxのユーザーが誰か」で認証するので、パスワードは聞かれません。

bash
$ sudo mysql

アカウントの一覧

SELECT user, host, plugin, account_locked, password_expired
FROM mysql.user
ORDER BY user, host;

こういうものが返ってきます。

+------------------+-----------+-----------------------+----------------+------------------+
| user             | host      | plugin                | account_locked | password_expired |
+------------------+-----------+-----------------------+----------------+------------------+
| appuser          | localhost | caching_sha2_password | N              | N                |
| debian-sys-maint | localhost | caching_sha2_password | N              | N                |
| mysql.infoschema | localhost | caching_sha2_password | Y              | N                |
| mysql.session    | localhost | caching_sha2_password | Y              | N                |
| mysql.sys        | localhost | caching_sha2_password | Y              | N                |
| root             | localhost | auth_socket           | N              | N                |
+------------------+-----------+-----------------------+----------------+------------------+

この画面で見るのは3つです。

  • host が % のアカウントが無いか。 あれば「どこからでも繋げる」という意味です(後述)
  • user が空文字のアカウントが無いか。 匿名ユーザーです。古い環境に残っていることがあります
  • mysql. で始まるものは MySQL 自身が使う内部アカウントです。account_locked が Y なのでログインには使えません。触らないでください

匿名ユーザーだけは、名指しで数えておきます。

SELECT COUNT(*) AS anonymous_accounts FROM mysql.user WHERE user = '';

0 なら問題ありません。

そのアカウントに何が許されているか

SHOW GRANTS FOR 'appuser'@'localhost';
+------------------------------------------------------------------------+
| Grants for appuser@localhost                                           |
+------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `appuser`@`localhost`                            |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `appdb`.* TO `appuser`@`localhost` |
+------------------------------------------------------------------------+

読み方です。

出力意味
USAGE ON *.*権限ゼロという意味。「ログインはできる」以上の意味は無い。必ず1行目に出るので、これがあるからといって何かができるわけではない
ON appdb.* の部分appdb の中の全部の表に対して(出力ではバッククォートで囲まれて出ます)
SELECT, INSERT, UPDATE, DELETEその4つだけ。DROP も CREATE も入っていない

ON *.* の位置に注目してください。 ここが *.* なら全データベース、appdb.* ならそのデータベースだけ、appdb.tasks ならその表だけです。この1箇所が被害範囲を決めます。

自分自身の権限は、こちらでも見られます。

SHOW GRANTS FOR CURRENT_USER;

危ないアカウントを機械で洗う

1つずつ SHOW GRANTS を打つのは、アカウントが増えると破綻します。全部まとめて見る方法があります。

SELECT grantee, privilege_type, is_grantable
FROM information_schema.user_privileges
WHERE privilege_type IN ('SUPER', 'FILE', 'PROCESS', 'CREATE USER', 'SHUTDOWN')
   OR is_grantable = 'YES'   -- GRANT OPTION は行ではなく、この列に YES で出る
ORDER BY grantee;

ここに、自分が作ったアプリ用のユーザーが出てきたら赤信号です。 information_schema.user_privileges はグローバル権限(ON *.* のもの)だけを並べます。アプリ用ユーザーがここに出る理由はありません。

GRANT OPTION は特に危険です。 「権限を他人に配る権限」なので、1つ持っていれば自分で自分を昇格させられます。 アプリ用ユーザーには絶対に付けません。

ここまでが棚卸しです。 自分の環境がどうなっているかを見てから、次に進んでください。


② host の意味 — localhost と % は別のユーザー

権限を作り直す前に、ここを間違えると全部が無意味になるので先に潰します。

MySQLのアカウントは「名前」ではなく「名前と接続元の組」です。

CREATE USER 'app'@'localhost' IDENTIFIED BY 'ダミー';
CREATE USER 'app'@'%'         IDENTIFIED BY 'ダミー';

これは同名の2アカウントではなく、まったく別の2アカウントです。パスワードも権限も別々に持ちます。

書き方意味
'app'@'localhost'同じマシンの中から繋いできたときだけ
'app'@'127.0.0.1'同じマシンにTCPで繋いできたとき(localhost とは別扱いになる場合がある)
'app'@'192.168.1.50'そのIPからだけ
'app'@'192.168.1.%'そのネットワークの中からだけ
'app'@'%'どこからでも。世界中どこからでも

% を安易に使わない

ネットで見つかる手順には 'user'@'%' がよく出てきます。動くからです。 繋がらないときに % にすると、たしかに繋がります。

それは「鍵が合わないので、玄関を開けっぱなしにした」のと同じです。

どこから繋ぐかで分けます。

繋ぐ場所使う host
同じVMの中で動いている Node.jslocalhost
同じVMの中の Docker コンテナからコンテナのIPは変わるので、Docker のネットワーク側で絞る。DBをコンテナの外に出さないのが先
別のVM(アプリサーバー)からそのVMのIPを直接書く。% にしない
手元のWindowsから、調べもののためアカウントを作らない。SSHトンネルを通す(後述)。トンネルの向こうでは localhost に見える

% があると localhost が効かないことがある

ここが本当にハマるところです。

MySQLは接続してきた相手に対して、「最も具体的に一致する」アカウント1つを選びます。'app'@'localhost' と 'app'@'%' の両方があると、ローカルからの接続には localhost のほうが選ばれます。

つまり、% のアカウントに与えた権限は、ローカルで試しても再現しません。 「権限を足したのに変わらない」と言いながら、見ているアカウントが違うという事故が起きます。

いま自分がどのアカウントとして繋がっているかは、必ずこれで確かめます。

SELECT USER() AS 接続してきた自分, CURRENT_USER() AS 一致したアカウント;
+--------------------------+---------------------+
| 接続してきた自分         | 一致したアカウント  |
+--------------------------+---------------------+
| appuser@localhost        | appuser@localhost   |
+--------------------------+---------------------+

USER() は「名乗った内容」、CURRENT_USER() は「MySQLが実際に採用したアカウント」です。 この2つがずれていたら、あなたが見ている SHOW GRANTS は別のアカウントのものです。


③ 最小権限のユーザーを3つ作り直す

1つのユーザーで全部やらない、というのがこの章です。

アプリの動作、マイグレーション、分析。この3つは、必要な権限がまったく違います。 同じユーザーでやっているということは、いちばん強い仕事に合わせて、常時その強さで繋いでいるということです。

用途必要なもの要らないもの
アプリ用SELECT INSERT UPDATE DELETEDDL全部。DROP も ALTER も CREATE も
マイグレーション用上記 + CREATE ALTER DROP INDEX REFERENCESGRANT OPTION、他DBへの権限
読み取り専用(分析)SELECT だけ書き込み全部

マイグレーション用が強いのは事実です。 ただし使うのは ALTER TABLE を流す数分だけで、ORMとマイグレーションの回でやったように、デプロイ手順の中の1ステップに閉じ込められます。24時間動き続けるアプリのプロセスが持ち歩く必要はありません。

作る

sudo mysql で入った管理者のセッションで打ちます。

パスワードは全部ダミーです。 下の文字列をそのまま使わないでください。自分で決めた値に置き換えてください。

CREATE USER 'd_sec_app'@'localhost' IDENTIFIED BY 'ダミーのパスワードに置き換える1';
CREATE USER 'd_sec_mig'@'localhost' IDENTIFIED BY 'ダミーのパスワードに置き換える2';
CREATE USER 'd_sec_ro'@'localhost'  IDENTIFIED BY 'ダミーのパスワードに置き換える3';

次に権限です。対象の書き方に注目してください。

GRANT SELECT, INSERT, UPDATE, DELETE
  ON appdb.* TO 'd_sec_app'@'localhost';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX, REFERENCES
  ON appdb.* TO 'd_sec_mig'@'localhost';

GRANT SELECT
  ON appdb.* TO 'd_sec_ro'@'localhost';

ON appdb.* を ON *.* と書かないこと。 これだけで、他のデータベース(mysql を含む)には手が届かなくなります。

さらに絞れます。表を名指しにできます。

GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.tasks TO 'd_sec_app'@'localhost';

ただしこれは維持が大変です。 表を1つ足すたびに GRANT を足す必要があり、足し忘れると本番でだけ落ちます。 アプリ用は appdb.* にして、データベースそのものを用途ごとに分けるほうが現実的です。

確かめる

SHOW GRANTS FOR 'd_sec_app'@'localhost';
SHOW GRANTS FOR 'd_sec_mig'@'localhost';
SHOW GRANTS FOR 'd_sec_ro'@'localhost';

FLUSH PRIVILEGES; は要りません。 GRANT / REVOKE / CREATE USER を使った変更は、その場でメモリにも反映されます。 FLUSH PRIVILEGES が要るのは mysql.user 表を直接 UPDATE したときだけで、そもそも直接 UPDATE してはいけません。

列単位で絞る

「この表は見せたいが、この列は見せたくない」は、権限で書けます。

REVOKE SELECT ON appdb.* FROM 'd_sec_ro'@'localhost';
GRANT SELECT (id, title, created_at) ON appdb.tasks TO 'd_sec_ro'@'localhost';

列名を括弧で書くだけです。 ただし、先に appdb.* の SELECT を剥がしておくこと。 権限はレベルごとの足し算なので、データベース単位の SELECT が残っていると、列で絞っても全部の列が読めます(つまずいたところ4と同じ理屈です)。INSERT と UPDATE も同じように列を指定できます。

GRANT UPDATE (title) ON appdb.tasks TO 'd_sec_app'@'localhost';

注意が1つあります。 列単位の権限を持つユーザーが SELECT * を打つと、こう止まります(出る列名は、表の作りによって変わります)。

ERROR 1143 (42000): SELECT command denied to user 'd_sec_ro'@'localhost' for column 'is_private' in table 'tasks'

* は「全部の列」なので、許されていない列が1つでもあれば拒否されます。 列単位で絞るなら、アプリ側も列を明示して書く必要があります。 これは面倒に見えますが、別の回に書いたとおり SELECT * はもともと書かないほうがよいので、実害は多くありません。

行単位で絞る — 権限ではなくビューで

MySQLに「この行だけ見せる」という権限はありません。 やりたいときは、絞った結果に名前を付けたもの(ビュー)を作り、そのビューにだけ権限を与えます。

CREATE VIEW appdb.v_d_sec_public_tasks AS
SELECT id, title, created_at
FROM appdb.tasks
WHERE is_private = 0;

列 is_private が無い場合は、手元にある列で条件を作り替えてください。 どの列があるかは DESCRIBE appdb.tasks; で見られます。

GRANT SELECT ON appdb.v_d_sec_public_tasks TO 'd_sec_ro'@'localhost';
REVOKE SELECT (id, title, created_at) ON appdb.tasks FROM 'd_sec_ro'@'localhost';

これで d_sec_ro は、is_private = 0 の行しか見えなくなります。 元の表そのものには触れません。

なぜこれで守れるのか。 ビューは既定で「ビューを作った人の権限」で実行されます。だから、中身の表への権限を持たないユーザーでも、ビュー越しになら読めます。 逆に言えば、ビューを作る人を間違えると、意図せず強い権限で実行されます。 ビューそのものの詳しい話はここでは扱いません。この記事で必要なのは「行の絞り込みは権限ではなくビューでやる」という1点だけです。

の確認 — 実際に繋いで、拒否させる

権限は「与えた」で終わりにしません。 与えなかったものが本当に拒否されるかを、目で見ます。

いまのセッションから出ます。

EXIT;

アプリ用ユーザーで繋ぎ直します。

bash
$ mysql -u d_sec_app -p appdb

-p のうしろにパスワードを続けて書かないでください。 理由は後述しますが、コマンド履歴に平文で残ります。 -p だけ書いて、改行してから聞かれたら打ちます。

まず、読めることを確認します。

SELECT COUNT(*) FROM tasks;

次に、できてはいけないことを、わざとやります。

DROP TABLE tasks;
ERROR 1142 (42000): DROP command denied to user 'd_sec_app'@'localhost' for table 'tasks'

このエラーが出れば成功です。 出なかったら権限が余っています。SHOW GRANTS FOR CURRENT_USER; に戻ってください。

他のデータベースにも手が届かないことを確かめます。

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| appdb              |
| information_schema |
+--------------------+

mysql も performance_schema も見えません。 見えているのは、権限を持っているものと、誰にでも見える information_schema だけです。

USE mysql;
ERROR 1044 (42000): Access denied for user 'd_sec_app'@'localhost' to database 'mysql'

ここまで確認できたら、この記事の山場の半分は終わりです。

EXIT;

読み取り専用ユーザーも、同じようにぶつけておきます。

bash
$ mysql -u d_sec_ro -p appdb
DELETE FROM tasks WHERE id = 1;
ERROR 1142 (42000): DELETE command denied to user 'd_sec_ro'@'localhost' for table 'tasks'

「読み取り専用で繋いでいるつもり」を、実際に確かめる方法がこれです。 障害対応で本番のDBを覗くときは、このユーザーで入ります。

EXIT;

④ 接続経路を絞る

権限は「誰が何をできるか」の話でした。 ここからは「そもそも誰が接続画面まで到達できるか」です。

強いパスワードは、総当たりを試される場所に置かないのが一番です。

いま、3306番はどこに開いているか

設定値を見ます。

SHOW VARIABLES LIKE 'bind_address';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| bind_address  | 127.0.0.1 |
+---------------+-----------+
値意味
127.0.0.1✅ 同じマシンの中からしか届かない。Ubuntu の MySQL の既定
* または 0.0.0.0全部のネットワークインターフェースで待ち受けている
192.168.1.20そのインターフェースだけ

設定ファイルはここにあります。

bash
$ grep -rn 'bind-address' /etc/mysql/
/etc/mysql/mysql.conf.d/mysqld.cnf:bind-address = 127.0.0.1

設定を見ただけで安心しないでください。実際に何が待ち受けているかは、OS側で確かめます。

bash
$ sudo ss -tlnp | grep 3306
LISTEN 0  151  127.0.0.1:3306  0.0.0.0:*  users:(("mysqld",pid=812,fd=22))

127.0.0.1:3306 なら、外からは届きません。 ここが 0.0.0.0:3306 や *:3306 になっていたら、VMのIPを知っている人は誰でも接続画面まで到達できます。

ss -tlnp の読み方は本線のLinuxネットワークの回で扱いました。 -t がTCP、-l が待ち受けているものだけ、-n が名前を引かない、-p がプロセス名です。

3306番をインターネットに晒さない

これは、この記事でいちばん強く言いたいことです。

グローバルIPに3306番を開けると、開けた直後から、世界中から接続の試行が来ます。 誰かに狙われるからではなく、インターネット全体を機械的に舐めている人がいるからです。開いているポートは、それだけで見つかります。

「強いパスワードにしてあるから大丈夫」では足りません。 理由は3つです。

  • 試行が無制限にできる状態そのものが、時間の問題になります
  • MySQL側の脆弱性が見つかったとき、パスワードの強さは関係ありません。更新するまでの数日が全部危険な時間になります
  • 平文で流れるかもしれません(TLSを使っていない場合)

代わりにやることは2つです。

(a) 同じホストに置く。 アプリとDBが同じVMなら 127.0.0.1 のままで足ります。これがいちばん強い守りです。

(b) 外から触りたいときはSSHトンネルを通す。 Windows の PowerShell から打ちます。

PowerShell
PS> ssh -L 13306:127.0.0.1:3306 ユーザー名@VMのIPアドレス

意味はこうです。 「手元の 13306 番に来たものを、SSHの中を通して、VMの中の 127.0.0.1:3306 に届ける」。

この接続が繋がっている間、別のPowerShellから手元の 13306 番に繋げばDBに届きます。 DBのポートは1ミリも外に開いていません。 VS CodeのリモートSSHの回で使ったのと同じ経路を、DBに使っているだけです。

ここに落とし穴があります。 トンネルを通った接続は、MySQLからは「同じマシンの中から来た接続」に見えます。 つまり 'user'@'localhost' のアカウントにそのまま繋がります。

host を localhost に絞っても、SSHで入れる人は防げません。 防いでいるのはSSHの鍵です。守りは1枚ではなく重なっている、という具体例がこれです。

ファイアウォールでも塞いでおきます。

bash
$ sudo ufw status

3306が ALLOW に入っていたら、消します。

bash
$ sudo ufw delete allow 3306

Docker でDBを動かしている場合は要注意です。 ports: "3306:3306" と書くと、ufw を素通りして外に開きます。 これはDockerの回で実際に踏みました。コンテナ間だけで繋ぐなら ports を書かず、同じネットワークに入れるだけで届きます。

TLSで繋ぐ

別のマシンからDBに繋ぐ経路が1つでもあるなら、通信を暗号化します。 MySQL 8.0 は、初回起動時に自己署名の証明書を自分で作って置いていくので、たいていの環境ではすでに使える状態になっています。

いま暗号化されているかは、繋いだあとにこれで見えます。

SHOW STATUS LIKE 'Ssl_cipher';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
+---------------+------------------------+

値が空なら、暗号化されていません。 Unixソケット(同じマシン内の localhost 接続)では空になりますが、それは経路がそもそもマシンの外に出ていないからなので問題ありません。

アカウント単位で「TLSでないと繋がせない」と決められます。

ALTER USER 'd_sec_ro'@'localhost' REQUIRE SSL;

より厳しくするなら REQUIRE X509 で、「クライアント側も証明書を出すこと」を要求できます。自己署名の証明書は「経路が暗号化される」ことしか保証しません(相手が本物のサーバーかは確かめられません)。それでも、平文で流れるよりは確実に良いので、まずここから始めます。


接続経路を絞る考え方は、Web サーバーと DB サーバーを別の台に分けると、さらに一段はっきりします。クラウド VPS byGMOのように複数台をローカル接続で組める VPS なら、DB の 3306 番をインターネット側には開けず、ローカル側からだけ受け付ける構成を自分で試せます。2026-11 時点の 4GB プランは 12 ヶ月契約で月 2,618 円(税込)に別途サーチャージ費が毎月加わり合計で月 3,927 円、料金は日割りではなく利用開始月と解約月も 1 ヶ月分かかります。

⑤ パスワードの扱い

変える

ALTER USER 'd_sec_app'@'localhost' IDENTIFIED BY '新しいダミーのパスワード';

8.0.19 以降なら、間違いを繰り返すアカウントを自動でロックできます。

ALTER USER 'd_sec_app'@'localhost'
  FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 2;

「5回失敗したら2日ロック」という意味です。バージョンは先に確かめてください。

SELECT VERSION();

ロックされたアカウントは、こう解けます。

ALTER USER 'd_sec_app'@'localhost' ACCOUNT UNLOCK;

コマンド履歴に残る

これが、この章のいちばん大事な話です。

bash
$ mysql -u d_sec_app -pDummyPass_App_1!

これをやってはいけません。 3つのことが同時に起きます。

  • ~/.bash_history に平文で残ります。 そのファイルを読める人全員に知られます
  • ps で他のユーザーからも見えます。 実行中のコマンドの引数は、同じマシンにいる誰からも見えます
  • 警告が出ますが、接続自体は成功します。 だから気づかずに習慣になります
mysql: [Warning] Using a password on the command line interface can be insecure.

正しい打ち方は、-p だけ書いて改行することです。

bash
$ mysql -u d_sec_app -p appdb

自動化したい(スクリプトから使いたい)場合は、設定ファイルに書きます。

bash
$ nano ~/.my.cnf
[client]
user=d_sec_app
password=ダミーのパスワードに置き換える

必ず自分だけが読めるようにします。

bash
$ chmod 600 ~/.my.cnf
$ ls -l ~/.my.cnf
-rw------- 1 ubuntu ubuntu 62 Sep 20 10:12 /home/ubuntu/.my.cnf

-rw------- になっていることを目で見てください。 権限の読み方は本線のパーミッションの回のとおりです。

MYSQL_PWD という環境変数でも渡せますが、使わないでください。 公式ドキュメントが名指しで非推奨にしています。環境変数は子プロセスに引き継がれ、同じマシンの他のユーザーから見えることがあります。

MySQLクライアント側の履歴も見ておく

シェルの履歴だけではありません。 mysql コマンドで打ったSQLは ~/.mysql_history に残ります。

bash
$ ls -l ~/.mysql_history
$ grep -ci 'identified by' ~/.mysql_history

MySQLのクライアントは、パスワードを含む文(CREATE USER ... IDENTIFIED BY など)を履歴に書かないよう除外します。 ただし除外は完全ではありません。 変わった書き方をすると残ることがあるので、上のように自分で数えて確かめます。

0 以外が返ってきたら、消します。

bash
$ rm ~/.mysql_history

そして、残っていたパスワードは変更してください。 秘密情報の回に書いたとおり、漏れたかもしれない秘密への唯一の正解は「作り直す」です。消して回ることではありません。

アプリが使うパスワードを .env にどう置くか、Gitにどう入れないかは秘密情報の回で扱いました。 ここでは繰り返しません。この記事の担当は「DBの側で何ができるか」だけです。


⑥ 監査 — 誰がいつ何をしたかを、どう残すか

事故が起きたあとに必ず聞かれるのは、この3つです。

いつからか。どこまで見られたか。誰がやったか。

記録が無いと、1つも答えられません。 「たぶん大丈夫」しか言えない状態は、被害が確定していない状態より悪いことがあります。

general_log を本番で使ってはいけない

MySQLには、実行された全部のSQLを記録する機能があります。

SHOW VARIABLES LIKE 'general_log';

OFF であることを確認してください。 そして、本番では ON にしません。 理由は速度ではありません。

  • 平文のパスワードが記録に残ります。 CREATE USER ... IDENTIFIED BY 'xxx' も、アプリが投げた SELECT ... WHERE token = 'xxx' も、全部そのまま書かれます
  • 個人情報が全部残ります。 ログファイルの管理が、DB本体と同じ厳しさになります
  • ファイルが際限なく育ちます。 ディスクが埋まるとDBが止まります

使ってよいのは、開発中のVMで、数分だけです。 使い終わったら必ず戻します。

SET GLOBAL general_log = 'OFF';

接続元を記録する

「全部のSQL」ではなく「誰がどこから繋いだか」だけなら、軽く取れます。

いま繋がっている接続は、これで見えます。

SELECT id, user, host, db, command, time
FROM information_schema.processlist
ORDER BY time DESC;

過去も含めた集計は performance_schema にあります。

SELECT user, host, current_connections, total_connections
FROM performance_schema.accounts
ORDER BY total_connections DESC;
+-----------+-----------+---------------------+-------------------+
| user      | host      | current_connections | total_connections |
+-----------+-----------+---------------------+-------------------+
| appuser   | localhost |                   4 |              2841 |
| d_sec_ro  | localhost |                   0 |                 3 |
| NULL      | NULL      |                  1 |                 1 |
+-----------+-----------+---------------------+-------------------+

「作った覚えのないユーザーが total_connections を持っている」は、それだけで調べる理由になります。

失敗した接続は、こちらに出ます。

SELECT ip, sum_connect_errors, sum_auth_errors, first_seen, last_seen
FROM performance_schema.host_cache;

sum_auth_errors が積み上がっているIPがあれば、そこから試行が来ています。 ただし performance_schema の中身は、MySQLを再起動すると消えます。 長期の記録には使えません。

誰がいつ何をしたかを、長期に残す

MySQLの標準機能で「誰が何を変えたか」を完全に残す方法はありません。(商用版とMariaDBには専用の監査プラグインがあります。ここでは扱いません。)

現実的な手は2つです。

(a) バイナリログ。 MySQLがデータの変更を記録している仕組みで、レプリケーションとリカバリのためのものです。「何がどう変わったか」は完全に残りますが、「アプリのどのユーザーがやったか」は入っていません。

SHOW VARIABLES LIKE 'log_bin';

(b) 表に持つ。 これがいちばん確実です。

ALTER TABLE appdb.tasks
  ADD COLUMN updated_by VARCHAR(64) NULL,
  ADD COLUMN updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

updated_by はアプリが埋めます。 DBのユーザーは全リクエストで同じ(appuser)なので、「アプリにログインしていた人が誰か」はアプリしか知りません。

整理するとこうです。 DBの権限は「誰が何をできるか」を決める。DBのログは「何が起きたか」を残す。「アプリの誰がやったか」はアプリが記録するしかない。 3つは別々の仕事で、どれか1つで代用できません。

ログを1箇所に集める話はログ集約の回で、監視の考え方は監視とログの回で扱いました。


⑦ バックアップ用ユーザーの、最小権限

バックアップは root で取られがちです。 「全部読めないと取れない」と思うからです。そんなことはありません。

mysqldump で appdb を取るのに必要なものだけを与えます。

CREATE USER 'd_sec_bak'@'localhost' IDENTIFIED BY 'ダミーのパスワードに置き換える4';

GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT
  ON appdb.* TO 'd_sec_bak'@'localhost';

GRANT PROCESS ON *.* TO 'd_sec_bak'@'localhost';

それぞれの理由です。

権限なぜ要るか
SELECT中身を読むため。バックアップの本体
SHOW VIEWビューの定義を書き出すため
TRIGGERトリガーを書き出すため
LOCK TABLES整合性のとれた瞬間を取るため。--single-transaction を使う場合は不要になることが多い
EVENTイベントスケジューラを使っている場合だけ
PROCESSInnoDBの表領域の情報を読むために必要。ただしグローバル権限で、他人が実行中のクエリも見えるようになる

PROCESS はトレードオフです。 これがあると SHOW PROCESSLIST で他のユーザーが実行中のSQLが見えます。バックアップ用ユーザーが漏れたときの被害が、その分だけ広がります。「必要だから付ける」で終わらせず、必要だと分かったうえで付けます。

そして、バックアップ用ユーザーに書き込み権限は要りません。

復元は別の仕事です。 復元には CREATE や DROP が要りますが、復元は自動で走るものではなく、人が判断して実行するものです。別のユーザーで、そのときだけやります。 壊して直す練習は障害対応の回でやったとおりです。

動くことを確かめます。

bash
$ mysqldump -u d_sec_bak -p --single-transaction appdb > /tmp/d_sec_test.sql
bash
$ ls -lh /tmp/d_sec_test.sql
$ head -5 /tmp/d_sec_test.sql

ファイルができて、中身が読めれば成功です。 権限が足りない場合は、どの権限が足りないかをエラーが名指しで言います。 それを見てから足してください。先に全部付けてから減らす、は絶対にやらないでください。減らし忘れます。

確認が終わったら消します。

bash
$ rm /tmp/d_sec_test.sql

⑧ アプリ側の守りは、ここでは扱わない

この記事はDB側の話に絞りました。 アプリ側でやることは、すでに本線で扱っています。

やることどこに書いたか
入力の検証、SQLインジェクションの塞ぎ方セキュリティ点検の回
パスワードをどう保存するかパスワード保存とbcryptの回
ログインの仕組みセッションとJWTの回
秘密情報の置き場所SOPSとageの回
ブラウザ側の防御ヘッダWebの防御ヘッダの回
データが壊れないようにする制約の回

そのうえで、この記事の立場を1行で書きます。

アプリ側の守りは「破られないようにする」ためのもの。DB側の権限は「破られたあとに、どこで止まるか」を決めるもの。 前者は完璧にできません。だから後者を必ずやります。


点検チェックリスト — この10項目を確認する

上から順に打って、全部が想定どおりなら、DB側の守りはひととおり終わっています。

1. アプリは root で繋いでいないか

bash
$ grep -i '^DB_USER' ~/server/.env

2. host が % のアカウントは無いか

SELECT user, host FROM mysql.user WHERE host = '%';

空で返ることが期待値です。

3. 匿名アカウントは無いか

SELECT COUNT(*) FROM mysql.user WHERE user = '';

4. アプリ用ユーザーが強いグローバル権限を持っていないか

SELECT grantee, privilege_type
FROM information_schema.user_privileges
WHERE privilege_type IN ('SUPER','FILE','PROCESS','CREATE USER','SHUTDOWN')
   OR is_grantable = 'YES';

5. アプリ用ユーザーに DDL が付いていないか

SHOW GRANTS FOR 'appuser'@'localhost';

DROP ALTER CREATE が出てこないこと。

6. 3306番は外に開いていないか

bash
$ sudo ss -tlnp | grep 3306

127.0.0.1:3306 であること。

7. ファイアウォールに 3306 の穴が残っていないか

bash
$ sudo ufw status

8. general_log は OFF か

SHOW VARIABLES LIKE 'general_log';

9. パスワードがコマンド履歴に残っていないか

bash
$ grep -ci 'password\|identified by' ~/.bash_history ~/.mysql_history

10. バックアップが、バックアップ専用ユーザーで取れるか

bash
$ mysqldump -u バックアップ用ユーザー -p --single-transaction appdb | head -5

この10個は、月に1回打つものとして書きました。 1回やって終わりにすると、増えたユーザーと開いたポートに気づけません。 3回打ったコマンドはスクリプトにするの回でやったとおり、1本のシェルスクリプトにまとめておくのがおすすめです。


つまずいたところ

遠回りしたところを、消さずに書いておきます。

1. GRANT したのに、権限が増えなかった

GRANT PROCESS ON *.* TO ... を打って、繋ぎっぱなしのセッションで SHOW PROCESSLIST を打っても拒否され続けました。 「GRANT が効いていない」と思って何度も打ち直しました。ON *.* のグローバル権限は、そのアカウントが次に接続したときから効きます。 EXIT して繋ぎ直したら、一発で通りました。データベース単位・表単位の権限は次の文からすぐ効くので、余計に混乱しました。

2. FLUSH PRIVILEGES を毎回打っていた

ネットの手順に必ず書いてあるので、儀式として打っていました。GRANT や CREATE USER を使っている限り、要りません。 必要なのは mysql.user を直接 UPDATE したときだけで、それは最初からやってはいけないことでした。

3. 'app'@'%' を作ったのに、権限を足しても変わらなかった

ローカルから試していたので、'app'@'localhost' のほうが選ばれていました。 % に足した権限は、当然ながら1つも効いていません。SELECT CURRENT_USER(); を打っていれば1分で分かりました。 それ以来、権限を触るときは必ず最初に CURRENT_USER() を見ます。

4. REVOKE したのに、まだ読めた

REVOKE SELECT ON appdb.tasks FROM ... を打ったのに、まだ SELECT できました。appdb.* の権限が別に残っていたからです。MySQLの権限は足し算で、レベルごとに別々に持ちます。 表単位で剥がしても、データベース単位が残っていれば通ります。SHOW GRANTS を全部読んでいれば見えていました。

5. 列単位で絞ったら、アプリが全部落ちた

GRANT SELECT (id, title) に切り替えた瞬間、アプリの全画面が ERROR 1142 になりました。アプリが SELECT * を書いていたからです。列を絞るなら、アプリ側で列を明示するのが先でした。順番を逆にやりました。

6. ビューを作ったのに、元の表も読めたままだった

「ビューを作れば絞れる」と思っていました。元の表への SELECT を REVOKE するまで、絞れていません。 ビューは別の入口を1つ増やしただけで、古い入口を閉じるのは別の作業です。

7. general_log を本番で1日つけて、ディスクを埋めた

「調査のため」と言って有効にしたまま忘れました。翌日、ディスクが100%になってMySQLが起動しなくなりました。 消したファイルの中には、その日にアプリが投げたSQLが全部、平文で入っていました。 復旧より、そのファイルが誰の目に触れたかを確かめるほうに時間がかかりました。

8. -p のうしろにパスワードを書く癖が付いていた

警告が出ても接続できるので、無視していました。~/.bash_history を grep したら、10回以上出てきました。 そのパスワードは全部変えました。警告は「動くけど危ない」という意味で、動くほうだけ見ていました。

9. バックアップ用ユーザーに ALL PRIVILEGES を付けていた

「動かないと困るから、とりあえず全部」で始めて、減らすのを忘れました。 半年後に SHOW GRANTS を見て気づきました。「先に全部付けて、あとで減らす」は減らせません。 いまはエラーを見てから1つずつ足します。 そのほうが速く、そして何が必要だったかが記録に残ります。

10. SSHトンネルを通したら 'user'@'localhost' で入れてしまった

host を localhost に絞ったので「手元からは繋げないはず」と思っていました。トンネルの向こうでは、MySQLからはローカル接続に見えます。 これはトンネルの仕様どおりで、正しい動作です。アカウントの host で防いでいるのは「ネットワーク越しの直接接続」だけで、SSHで入れる人は別の層で防ぐのだと理解し直しました。


後片づけ

この記事で作ったユーザーは、すべて d_sec_ で始まっています。 ビューは v_d_sec_ です。

appuser は消さないでください。 本線のアプリがそれで繋いでいます。 消すとアプリが Access denied で止まります。

まず、これから消すものが本当に練習用だけかを確かめます。

SELECT user, host FROM mysql.user WHERE user LIKE 'd\_sec\_%';
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| d_sec_app | localhost |
| d_sec_bak | localhost |
| d_sec_mig | localhost |
| d_sec_ro  | localhost |
+-----------+-----------+

この4つだけが出ることを目で見てから、消します。

DROP USER 'd_sec_app'@'localhost';
DROP USER 'd_sec_mig'@'localhost';
DROP USER 'd_sec_ro'@'localhost';
DROP USER 'd_sec_bak'@'localhost';

練習用のビューも消します。

DROP VIEW IF EXISTS appdb.v_d_sec_public_tasks;

appuser が無事なことを確かめます。

SELECT user, host FROM mysql.user WHERE user = 'appuser';
SHOW GRANTS FOR 'appuser'@'localhost';

⑥で tasks に列を足した場合は、戻せます。

ALTER TABLE appdb.tasks DROP COLUMN updated_by, DROP COLUMN updated_at;

残したままでも実害はありません。 ただし ALTER TABLE は取り消せないので、DDLの回に書いたとおり、打つ前に何が消えるかを読んでください。 列を消せば、その列に入っていた値も消えます。

設定をいじった場合は戻します。

SHOW VARIABLES LIKE 'general_log';
SET GLOBAL general_log = 'OFF';

~/.my.cnf を作った場合は、置いたままにするなら権限を再確認、消すなら削除します。

bash
$ ls -l ~/.my.cnf
EXIT;

最後に、アプリがまだ動くことを確かめてください。

bash
$ curl -s http://localhost:3000/api/tasks | head -3

権限を触った日は、必ずこれをやります。 「権限を絞ったつもりが、アプリを止めていた」に、次の日の朝まで気づかないことがあります。


まとめ

  • アプリが root で繋いでいると、アプリの穴1つが「全データベースの穴」になる。 穴を無くすのは無理なので、穴が開いたときの範囲を先に決めておく
  • MySQLのアカウントは「名前」ではなく「名前と接続元の組」。 'app'@'localhost' と 'app'@'%' は別のアカウントで、権限もパスワードも別
  • % は「世界中のどこからでも」。 繋がらないから % にする、をやらない。どこから繋ぐかで分ける
  • 両方あると、ローカル接続には localhost のほうが選ばれる。 権限が効かないと思ったら SELECT CURRENT_USER();
  • SHOW GRANTS の ON の右側が被害範囲。 *.* は全部、appdb.* はそのDB、appdb.tasks はその表
  • USAGE ON *.* は「権限ゼロ」という意味。 必ず1行目に出るので、これを見て何かができると誤読しない
  • 用途で3つに分ける。 アプリ用(4操作)/ マイグレーション用(DDLあり、使うのは数分)/ 読み取り専用(分析と障害対応)
  • アプリ用ユーザーに GRANT OPTION を付けない。 「権限を配る権限」なので、自分で自分を昇格できてしまう
  • 列は GRANT SELECT (col) で絞れる。 ただし SELECT * は許されていない列が1つでもあれば拒否されるので、アプリ側で列を明示するのが先
  • 行を絞る権限は無い。 ビューを作って、ビューにだけ権限を与え、元の表の権限を REVOKE する。 剥がすまでは絞れていない
  • 権限は足し算で、レベルごとに別々。 表単位で REVOKE しても、データベース単位が残っていれば通る
  • グローバル権限(ON *.*)は次の接続から効く。 「GRANT が効かない」の大半はこれ。繋ぎ直して確かめる
  • FLUSH PRIVILEGES は要らない。 必要なのは mysql.user を直接 UPDATE したときだけで、それはやってはいけない
  • 与えたら、必ず拒否させて確かめる。 DROP TABLE を打って ERROR 1142 が出るところまでやって、はじめて絞れたと言える
  • bind_address が 127.0.0.1 なら外から届かない。 設定値だけでなく ss -tlnp で実際に確かめる
  • 3306番をインターネットに晒さない。 強いパスワードでも足りない(試行が無制限・脆弱性が出た日・平文の可能性)。同一ホストか、SSHトンネル
  • SSHトンネルを通った接続は localhost に見える。 アカウントの host では防げない。そこを守っているのはSSHの鍵
  • Docker の ports: は ufw を素通りする。 コンテナ間だけなら ports を書かない
  • TLSは SHOW STATUS LIKE 'Ssl_cipher'; で確かめ、ALTER USER ... REQUIRE SSL で強制できる。 自己署名は「暗号化」だけを保証する
  • mysql -pパスワード と書かない。 ~/.bash_history に残り、ps で他人からも見える。-p だけ書いて改行する
  • 自動化するなら ~/.my.cnf に書いて chmod 600。 MYSQL_PWD は使わない
  • ~/.mysql_history も見る。 除外は完全ではない。残っていたら、消すだけでなくパスワードを変える
  • general_log を本番で使わない。 遅いからではなく、平文のパスワードと個人情報がそのまま残り、ディスクを埋めてDBを止めるから
  • 接続元は performance_schema.accounts と host_cache で見える。 再起動で消えるので、長期の記録には使えない
  • 「誰がやったか」はDBには残らない。 DBのユーザーは全リクエストで同じ。アプリが updated_by を書くしかない
  • バックアップ用ユーザーに ALL PRIVILEGES は要らない。 SELECT SHOW VIEW TRIGGER LOCK TABLES + グローバルの PROCESS
  • PROCESS は他人の実行中クエリが見える権限でもある。必要だと分かったうえで付ける
  • 「先に全部付けて、あとで減らす」は減らせない。 エラーを見てから1つずつ足す。 そのほうが速く、記録も残る
  • 権限を触った日は、必ずアプリが動くことを確かめる。 絞りすぎて止めたことに、翌朝まで気づかないことがある

参考リンク


次に読む

この記事は 【深掘り】SQL の 16 / 17 本目です。

記事
← 前の記事スロークエリの特定と書き換え — 遅いのはどのSQLか
→ 次の記事リレーショナルモデルと集合演算 — SQLは「表」を扱っていない

上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。

全体の順路は はじめに にあります。

roadmap.sh のどこをやっているか

Backend›SQL — Security
  • 最小権限のユーザーを作る / GRANT と REVOKE
  • 接続経路を絞る・監査・バックアップ権限

この記事は、上の項目を実際に手を動かして確かめる回です。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次