初めてのGRANT文で、SQLの記述がシンプルに出来た話

はじめに：背景と、この記事で伝えたいこと

初めての SQLの GRANT 文を使ってローカル環境に新しいデータベースを作成し、ユーザーに適切な権限を付与する流れを紹介します。※紹介しているスクリーンショットは、勉強用のものです。

きっかけは、あるプロジェクトで先輩エンジニア様達は、「ローカル環境で新しいDBを作成」してSQL抽出していることでした。そのプロジェクトでは、2つのDB構成で成り立っています。あるSQL抽出タスクのため、2つ本番DBのレコードをローカル環境に再現する必要があります。このときに、2つのDBを、またいでクエリ実行するのに「データベース名」を明示的に記述する必要があり、SQLが複雑になる問題がありました。そこで、先輩方からご指導をいただいたように新規DBの作成に挑戦してみました。

すると、早速問題発生しました。ローカル環境でDBが作成できません。先輩方から教わったように、DBクライアントソフトのGUIツールから新規DBを作成しようとした際に下記のエラーに遭遇しました。

ERROR 1044 (42000): Access denied for user ‘sail’@’%’ to database ‘new_DB_test001’

どうやら現在のユーザーだと新規DBを作成する権限がありませんでした。原因を調査すると、「DBにもLinuxのようなユーザー権限の仕組みがある」ことを学びました。

権限調査でわかったこと

調査1：「ユーザーの権限ってLinuxだけじゃないの？」

このエラーをきっかけに、DBにもユーザーごとに操作できる範囲が制限されることを知りました。まずは、自分がどのユーザーでログインしているのかを確認します。

docker exec -it dbコンテナ名 bash
mysql -u sail -p

SELECT CURRENT_USER();

+—————-+
| CURRENT_USER() |
+—————-+
| sail@% |
+—————-+

出力を見ると、私は 「sail」 というユーザーでログインしていました。

調査2：「ユーザーのsailは、何ができるの？」と聞いてみる

現在は、ユーザーsailとしてログインしているとわかりました。次に、ユーザーの持つ権限を確認するために以下を実行します。

SHOW GRANTS FOR sail@'%';

出力結果：

GRANT ALL PRIVILEGES ON `laravel`.* TO `sail`@`%`
GRANT ALL PRIVILEGES ON `testing%`.* TO `sail`@`%`

表示内容は下記です。

• 既存の2つのDB(laravelとtesting)では、権限があることがわかりました。
• でも「新しいデータベースを作る権限」は、持っていないことがわかりました。

下記：スクリーンショットのイメージ

解決方法：root権限で GRANT文 を実行する

調査から、原因は「権限不足」であることが判明しました。ここで必要なのが、rootユーザー です。root権限では、一般ユーザーへ権限を与えることが出来ます。そのため、ここでは 「root権限」 を持つユーザーに切り替える必要があります。

同様の手順で rootユーザーとしてログインします。

docker exec -it dbコンテナ名 bash
mysql -u root -p

SELECT CURRENT_USER();

+—————-+
| CURRENT_USER() |
+—————-+
| root@localhost |
+—————-+

無事に 「root権限」 を確認できました！！
下記のように、DB作成・権限付与・変更を反映します。

// 1.新しいDBを作成
CREATE DATABASE new_DB_test001 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
// 2.新しく作成したDBに sail が使えるように権限を付与
GRANT ALL PRIVILEGES ON new_DB_test001.* TO 'sail'@'%';
// 3.権限の変更を反映
FLUSH PRIVILEGES;

上記で、一般ユーザー (sail) が new_DB_test001を使えるようになりました！

下記：rootのイメージ

GUI比較用：DBクライアントソフト表示
一般ユーザーのsailでも新規作成したDB権限を付与したものが表示されて、操作できるようになりました。

結果：DBの権限操作をして、集約することでSQLの記述がシンプルに！！

GRANT文によるDB権限を操作したことで、ユーザーごとにアクセス可能な範囲を制御できることを経験できました。今回の権限操作により、Laravelとtestingという2つのDBを、新規に作成したnew_DB_test001に必要なテーブルをINSERTしてまとめることができました。

今回のやり方で、1つのDBにまとめることで、「データベース名＋テーブル名＋カラム名」 → 「テーブル名＋カラム名」 と記述が短いSQLでデータを抽出できるようになりました！

まとめ：GRANTって「鍵を渡す」イメージです。

今回の学びを一言で表すならば、GRANT文は「家の鍵をパートナーに渡す」イメージです。

• SQLの権限はユーザーとホストで管理されている。
• 現在のログイン状態は SELECT CURRENT_USER() で確認できる。
• 操作可能範囲は SHOW GRANTS FOR 'user'@'host'; で確認できる。
• FLUSH PRIVILEGES を忘れると権限が反映されない。

GUIツールでは気づきにくい「権限」の仕組みを理解したことで、ローカル環境のDB操作に、自信を持ってできるようになりました。