C言語＋MySQL入門｜接続ユーザーの権限を確認する

C言語＋MySQL入門｜接続ユーザーの権限を確認する

前の記事では、CプログラムからMySQLへ接続するために、学習用ユーザーのinventory_userを作成しました。

今回は、そのユーザーに必要な権限が付いていることを確認します。SHOW GRANTSで設定を表示するだけでなく、実際にSQLを実行して、商品と在庫履歴を読み書きできるか確かめましょう。

この記事では、次の内容を学びます。

• 現在接続しているアカウントを確認する。
• SHOW GRANTSで付与された権限を表示する。
• productsとstock_historyを参照する。
• 確認用データでINSERT、UPDATE、DELETEを試す。
• 許可していない操作が拒否されることを確認する。

今回は、権限を確認するための一時的なデータを使います。前の記事までに登録・変更した商品を残したまま、確認を終えられる手順で進めます。

inventory_userで接続していることを確認しよう

この記事のSQLは、rootではなく、inventory_userで実行します。

前の記事から接続したままであれば、その接続を使えます。まだ接続していない場合は、前の記事の手順で、127.0.0.1のMySQL Serverへinventory_userとして接続してください。

最初に、認証されたアカウントを確認しましょう。

SELECT CURRENT_USER() AS authenticated_account;

実行結果の例

+--------------------------+
| authenticated_account    |
+--------------------------+
| inventory_user@127.0.0.1 |
+--------------------------+
1 row in set (0.00 sec)

CURRENT_USER()は、MySQLが認証と権限判定に使用するアカウントを返します。

rootと表示された場合は、inventory_userで接続し直してから進めます。管理用アカウントで操作に成功しても、アプリ用ユーザーの権限を確認したことにはならないためです。

続けて、inventory_dbを選択します。

USE inventory_db;

実行結果の例

Database changed

SHOW GRANTSで権限を表示しよう

現在のアカウントに付与された権限は、SHOW GRANTSで確認できます。

SHOW GRANTS;

実行結果の例

+--------------------------------------------------------------------------------------------------------+
| Grants for inventory_user@127.0.0.1                                                                    |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `inventory_user`@`127.0.0.1`                                                     |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `inventory_db`.`products` TO `inventory_user`@`127.0.0.1`      |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `inventory_db`.`stock_history` TO `inventory_user`@`127.0.0.1` |
+--------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

実際のMySQLクライアントでは、これらの内容が表形式で表示されます。表示順などは環境によって異なる場合があります。

SHOW GRANTSは、権限を付与する命令ではありません。現在の設定を表示する命令です。

また、自分自身の権限を表示するために、mysqlのシステムテーブルを参照する権限を追加する必要はありません。dev.mysql.com

USAGEは全操作の許可ではない

最初の行に、USAGE ON *.*と表示されています。

*.*を見ると、すべてのデータベースで操作できるように感じるかもしれません。しかし、USAGEは、その権限レベルでは操作権限がないことを表します。全操作を許可する意味ではありません。

今回の読み書きの権限は、続く2行に示されています。

前の記事では、テーブル単位で権限を付けました。そのため、inventory_dbに今後追加する別のテーブルまで、自動的に操作できるようになるわけではありません。

操作内容と対象範囲を一緒に確認する

権限を読むときは、次の3つを対応させます。

今回のユーザーには、テーブルを作成するCREATEや、テーブル構造を変更するALTERなどは付けていません。

もし、前の記事で設定した内容以外に、広い範囲の権限やロールが表示された場合は、その設定も確認する必要があります。この記事では、新しく作成したユーザーに、前の記事の権限だけを付けた状態で進めます。

図1：権限は操作内容と対象テーブルを組み合わせて確認する

この図から分かること

権限は、命令の種類だけで決まるものではありません。

今回のinventory_userは、productsとstock_historyという指定されたテーブルで、4種類のデータ操作を行えます。操作内容と対象範囲を一緒に確認しましょう。

商品と履歴を参照できるか確認しよう

まず、SELECTを使って既存のデータを確認します。

商品一覧を表示する

SELECT product_id, product_name, price, stock
FROM products
ORDER BY product_id;

実行結果の例

+------------+----------------+--------+-------+
| product_id | product_name   | price  | stock |
+------------+----------------+--------+-------+
|          1 | ノート         | 200.00 |     0 |
|          2 | 油性ボールペン | 150.00 |     0 |
+------------+----------------+--------+-------+
2 rows in set (0.00 sec)

これは、3.9の商品変更・削除まで行った場合の例です。

独自にデータを変更している場合は、内容が異なっていても構いません。ここでは、inventory_userでproductsのデータを取得できることを確認します。

在庫履歴を表示する

SELECT history_id, product_id, movement_type, quantity, recorded_at
FROM stock_history
ORDER BY history_id;

実行結果の例

Empty set (0.00 sec)

前の記事までの手順では、在庫履歴はまだ登録していないため、0件になります。

Empty setは、取得対象の行がないという意味です。権限不足でSQLが拒否された場合とは異なります。

空のテーブルでも、SELECTが正常に実行できれば、参照操作が許可されていることを確認できます。

書き込みの確認には専用のデータを使おう

次に、INSERT、UPDATE、DELETEも実行できるか確認します。

既存のノートや油性ボールペンを使うと、確認のために商品情報を書き換えることになります。そこで、権限確認用の商品と、その商品の履歴を新しく作ります。

今回は、確認中の変更を最後に取り消すために、トランザクションの命令を少しだけ使います。

productsとstock_historyはInnoDBなので、トランザクション内で行った今回のデータ変更をROLLBACKで取り消せます。

トランザクションの詳しい仕組みは後で学びます。この記事では、確認用の変更を確定せずに終えるための手順として使いましょう。

すでに別のトランザクションで作業中の場合は、その作業を適切に終えてから、今回の確認を始めてください。

図2：確認用データで操作を試してから変更を取り消す

この図から分かること

権限の確認には、今回追加したデータだけを使います。

既存の商品を変更せず、確認用のデータで各操作を試します。最後にROLLBACKを実行することで、確認用の変更を確定せずに終了できます。

2つのテーブルで読み書きを確認しよう

以下が、確認用SQLの全体です。inventory_userで接続した同じMySQLクライアント上で実行します。

最初は、セミコロンまでの1文ずつ実行し、結果を確認しながら進めてください。

途中でエラーになった場合は、後続の文へ進まず、同じ接続でROLLBACKを実行して確認を中止します。 特に、INSERTに失敗した後でLAST_INSERT_ID()を使う手順へ進まないようにしましょう。

確認用の商品と履歴で4種類の操作を試すSQL

ファイル名： verify_inventory_permissions.sql

USE inventory_db;

START TRANSACTION;

-- 確認用の商品を追加する
INSERT INTO products (product_name, price, stock)
VALUES ('権限確認用の商品', 10.00, 1);

-- 今回追加した商品IDを保存する
SET @check_product_id = LAST_INSERT_ID();

-- 確認用の入庫履歴を追加する
INSERT INTO stock_history (product_id, movement_type, quantity)
VALUES (@check_product_id, 'IN', 1);

-- 今回追加した履歴IDを保存する
SET @check_history_id = LAST_INSERT_ID();

-- 確認用の商品を更新する
UPDATE products
SET price = 20.00,
    stock = 2
WHERE product_id = @check_product_id;

-- 確認用の履歴を更新する
UPDATE stock_history
SET quantity = 2
WHERE history_id = @check_history_id;

-- 更新後の商品を表示する
SELECT product_id, product_name, price, stock
FROM products
WHERE product_id = @check_product_id;

-- 更新後の履歴を表示する
SELECT history_id, product_id, movement_type, quantity
FROM stock_history
WHERE history_id = @check_history_id;

-- 確認用の履歴を先に削除する
DELETE FROM stock_history
WHERE history_id = @check_history_id;

-- 確認用の商品を削除する
DELETE FROM products
WHERE product_id = @check_product_id;

-- 削除されたことを確認する
SELECT COUNT(*) AS remaining_products
FROM products
WHERE product_id = @check_product_id;

SELECT COUNT(*) AS remaining_histories
FROM stock_history
WHERE history_id = @check_history_id;

-- 確認用の変更を確定せずに終了する
ROLLBACK;

実行結果の例

新しく作成した環境で、前の記事までの操作を順に行っている場合、確認用の商品IDは4、履歴IDは1になる例が考えられます。実際のIDは、それまでの操作によって異なります。

INSERT、UPDATE、DELETEの実行結果は、次のようになります。

更新後の商品を表示するSELECTでは、次のように表示されます。

+------------+------------------+-------+-------+
| product_id | product_name     | price | stock |
+------------+------------------+-------+-------+
|          4 | 権限確認用の商品 | 20.00 |     2 |
+------------+------------------+-------+-------+
1 row in set (0.00 sec)

更新後の履歴を表示するSELECTでは、次のように表示されます。

+------------+------------+---------------+----------+
| history_id | product_id | movement_type | quantity |
+------------+------------+---------------+----------+
|          1 |          4 | IN            |        2 |
+------------+------------+---------------+----------+
1 row in set (0.00 sec)

削除後の確認では、それぞれ0件になります。

+--------------------+
| remaining_products |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

+---------------------+
| remaining_histories |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.00 sec)

最後のROLLBACKが成功すると、次のように表示されます。

Query OK, 0 rows affected (0.00 sec)

処理時間は環境によって異なります。

確認用SQLのポイントを見てみよう

自動採番されたIDを変数に保存する

今回のSQLでは、@check_product_idと@check_history_idを使っています。

@から始まる名前は、MySQLのユーザー定義変数です。ここでは、今回登録した行のIDを、同じ接続内で一時的に覚えておくために使っています。

商品IDを4と決め打ちせず、INSERTに成功した直後のLAST_INSERT_ID()で取得します。

履歴を登録すると、LAST_INSERT_ID()が返す値もその登録結果に変わるため、商品IDと履歴IDはそれぞれの登録直後に保存しています。

この方法なら、自動採番された番号が例と異なっても、今回追加した行だけを操作できます。

履歴を先に削除する理由

productsには、stock_historyからの参照があります。確認用の商品にも履歴を追加したので、商品から先に削除するとRESTRICTによって拒否されます。

そこで、確認用のデータは次の順番で削除します。

1. 今回追加した履歴を削除する。
2. その履歴が参照していた確認用商品を削除する。

これは、今回の検証専用データを片付けるための操作です。実際の入出庫履歴を、商品削除のために消す運用を勧めるものではありません。

また、履歴のquantityを変更する操作も、今回はUPDATE権限の動作確認として行っています。業務上の履歴修正の扱いとは分けて考えましょう。

削除を試した後にもROLLBACKする理由

今回のSQLでは、DELETEの権限を確認するために、確認用の履歴と商品を実際に削除しています。

その後にROLLBACKを行い、一連の確認処理を確定せずに終了します。最初のINSERTも取り消されるため、確認用の商品や履歴が復活して残ることはありません。

ただし、AUTO_INCREMENTで採番された番号は、ROLLBACKしても再利用されるとは限りません。 データが残らなくても、番号に欠番が生じることがあります。

既存のデータが保たれることと、自動採番の状態まで元に戻ることは区別しておきましょう。

確認後のデータを見てみよう

ROLLBACKの後、商品一覧を確認します。

SELECT product_id, product_name, price, stock
FROM products
ORDER BY product_id;

実行結果の例

+------------+----------------+--------+-------+
| product_id | product_name   | price  | stock |
+------------+----------------+--------+-------+
|          1 | ノート         | 200.00 |     0 |
|          2 | 油性ボールペン | 150.00 |     0 |
+------------+----------------+--------+-------+
2 rows in set (0.00 sec)

確認用の商品は残っていません。

在庫履歴の件数も確認します。

SELECT COUNT(*) AS history_count
FROM stock_history;

実行結果の例

+---------------+
| history_count |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

これは、確認前の在庫履歴が0件だった場合の例です。もともと履歴がある環境では、その履歴が残ります。

これで、2つのテーブルに対して、SELECT、INSERT、UPDATE、DELETEを実行できることを確認できました。

許可していない参照が拒否されることを確認しよう

次に、inventory_userへ付与していない操作を確認します。

前の記事では、管理用の接続からmysql.userを参照して、アカウント情報を確認しました。しかし、inventory_userには、このシステムテーブルを参照する権限を付けていません。

ROLLBACKで先ほどの確認処理を終了した後、次のSQLを実行します。

SELECT COUNT(*) AS account_count
FROM mysql.user;

実行結果の例

ERROR 1142 (42000): SELECT command denied to user 'inventory_user'@'localhost' for table 'user'

エラーメッセージ内の接続元は、環境によってlocalhostや127.0.0.1などと表示される場合があります。

このエラーは、今回の設定では想定どおりです。

同じSELECTでも、対象がproductsやstock_historyであれば実行でき、mysql.userであれば拒否されます。権限は命令だけでなく、操作対象にも結び付いているためです。

もし、このSQLが成功した場合は、想定外の権限が付いていないか、CURRENT_USER()とSHOW GRANTSの結果を確認してください。

なお、この確認だけで、あらゆる操作の許可範囲を検証できたわけではありません。今回は、付与内容の確認と、代表的な許可・拒否の動作を確かめています。

図3：同じSELECTでも対象によって許可と拒否が変わる

この図から分かること

SELECTを実行できるユーザーでも、すべてのテーブルを参照できるとは限りません。

今回のinventory_userには、在庫管理で使う2つのテーブルへの操作を許可しています。管理用のシステムテーブルまで参照できる設定にはしていません。

権限不足とデータの制約違反を区別しよう

SQLが失敗したときは、すべてを権限不足と考えないことが大切です。

権限があることは、その操作がどのようなデータに対しても成功するという意味ではありません。

たとえば、DELETE権限があっても、履歴から参照されている商品は、外部キー制約によって削除が拒否されます。

今回の確認中に問題が発生した場合は、トランザクションを開始済みであれば、まず同じ接続で次を実行します。

ROLLBACK;

実行結果の例

Query OK, 0 rows affected (0.00 sec)

その後、接続アカウント、付与された権限、SQLの対象と値を確認しましょう。権限を追加する必要がある場合は、管理作業用のアカウントから、必要な対象と操作を指定します。

第4章で使う接続情報を確認しておこう

ここまでの確認で、Cプログラム用ユーザーが、在庫管理に使う2つのテーブルを読み書きできることを確かめられました。

第4章でも、次の接続情報を使います。

今回は学習用として、両方のテーブルに4種類の操作権限を付けています。実際のアプリで、履歴の変更や削除まで許可するかといった詳しい権限設計は、第9章で扱います。

次の記事では、テーブル作成SQLと確認用データをchap03に保存します。環境を作り直せる形に整理し、第4章のCプログラムからの接続へ進む準備を整えましょう。

Follow me!

おすすめ記事

C言語＋MySQL入門｜商品を変更・削除する C言語＋MySQL入門｜商品を登録・表示する Java超｜メソッドのオーバーライド C言語＋MySQL入門｜商品テーブルを作成する C言語＋MySQL入門｜在庫履歴テーブルを作成する Java超｜クラスとメソッドのしくみ Java道｜メソッドのオーバーライド C言語＋MySQL入門｜学習用の接続ユーザーを作る