MySQL8.0でアカウントの権限付与・剥奪について理解する

GRANT文

背景

MySQL8.0でEXISTS述語について理解するに引き続いて応用情報の勉強中です．

今回はアカウントの権限付与・剥奪についてです．権限付与はGRANT，剥奪はREVOKEを使用します．それは覚えているのですが
後ろに書く権限，アカウント名，テーブル名の順番などはどのようになるか？
というところまで正しく覚えきれておらずだったのでメモを残しておきます．

MySQLの環境は毎度同じくこちらを使いました．

準備

今回は何度かユーザーを切り替えます．準備は全てrootユーザーでの作業です．

create user hoge identified by 'abc123';

create database hoge_app;

use hoge_app

create table article (
    id int not null primary key AUTO_INCREMENT,
    user_id int not null,
    title varchar(40) not null,
    text varchar(200) not null
);

insert into article (user_id, title, text) values 
(1, 'abc', 'test_text1'),
(1, 'xyz', 'test_text2'),
(2, 'hogehoge', 'test_text3'),
(3, 'hogehoge', 'test_text4');

select * from article;
+----+---------+----------+------------+
| id | user_id | title    | text       |
+----+---------+----------+------------+
|  1 |       1 | abc      | test_text1 |
|  2 |       1 | xyz      | test_text2 |
|  3 |       2 | hogehoge | test_text3 |
|  4 |       3 | hogehoge | test_text4 |
+----+---------+----------+------------+

権限がないことを確認

先程作成したhogeユーザーでmysqlに入ります．

$ mysql -uhoge -h 127.0.0.1 -pabc123 -P 3306

面倒なのでパスワード(abc123)を直書きしていますが，historyコマンドでパスワードが見れてしまうのでしないほうがベーターです．

まずは権限を確認します．

show grants;
+----------------------------------+
| Grants for hoge@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO `hoge`@`%` |
+----------------------------------+

USAGE
This privilege specifier stands for “no privileges.”

と公式にもある通り，USAGEは権限を何も持っていないことを指します．作成したばかりのユーザーは何も権限を持っていません．

DBを参照や作成しようとしてもエラーとなります．

use hoge_app
ERROR 1044 (42000): Access denied for user 'hoge'@'%' to database 'hoge_app'

create database test_db;
ERROR 1044 (42000): Access denied for user 'hoge'@'%' to database 'test_db'

権限付与(GRANT 権限 ON テーブル名 TO ユーザー名)

hogeユーザーはexitして，rootユーザーで入り権限付与していきます．

$ mysql -uroot -h 127.0.0.1 -ppassword -P 3306

hogeユーザーにhoge_appデータベースの全てのテーブルにおいて，SELECTとINSERT権限を付与します．
書き方はGRANT 権限 ON テーブル名 TO ユーザー名の順です．

grant select, insert on hoge_app.* to hoge;

rootユーザーをexitして，hogeユーザーで入り権限が付与されているかを確認します．

$ mysql -uhoge -h 127.0.0.1 -pabc123 -P 3306

権限を確認してみると2行目が増えていることがわかります．

show grants;
+----------------------------------------------------+
| Grants for hoge@%                                  |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `hoge`@`%`                   |
| GRANT SELECT, INSERT ON `hoge_app`.* TO `hoge`@`%` |
+----------------------------------------------------+

SELECTとINSERTができるかを確認します．

use hoge_app

select * from article;
+----+---------+----------+------------+
| id | user_id | title    | text       |
+----+---------+----------+------------+
|  1 |       1 | abc      | test_text1 |
|  2 |       1 | xyz      | test_text2 |
|  3 |       2 | hogehoge | test_text3 |
|  4 |       3 | hogehoge | test_text4 |
+----+---------+----------+------------+

insert into article (user_id, title, text) values 
(1, 'insert hoge test title', 'insert hoge test text');

select * from article;
+----+---------+------------------------+-----------------------+
| id | user_id | title                  | text                  |
+----+---------+------------------------+-----------------------+
|  1 |       1 | abc                    | test_text1            |
|  2 |       1 | xyz                    | test_text2            |
|  3 |       2 | hogehoge               | test_text3            |
|  4 |       3 | hogehoge               | test_text4            |
|  5 |       1 | insert hoge test title | insert hoge test text |
+----+---------+------------------------+-----------------------+

権限剥奪(REVOKE 権限 ON テーブル名 FROM ユーザー名)

権限付与はできたのでhogeユーザーはexitして，rootユーザーで入り権限剥奪をしてみます．

$ mysql -uroot -h 127.0.0.1 -ppassword -P 3306

hogeユーザーにhoge_appデータベースの全てのテーブルにおいて，INSERT権限を剥奪します．
書き方はREVOKE 権限 ON テーブル名 FROM ユーザー名の順です．
GRANTのときはTOでしたが，REVOKEではFROMになります．
英語の意味を考えればなんとなくわかりますが，間違えてしまいそうですね．

to　〜　→　〜へ(権限を与える)
from　〜　→　〜から(権限を剥奪する)

revoke insert on hoge_app.* from hoge;

rootユーザーをexitして，hogeユーザーで入り権限が剥奪されているかを確認します．

$ mysql -uhoge -h 127.0.0.1 -pabc123 -P 3306

2行目からINSERTがなくなっているのがわかります．

show grants;
+--------------------------------------------+
| Grants for hoge@%                          |
+--------------------------------------------+
| GRANT USAGE ON *.* TO `hoge`@`%`           |
| GRANT SELECT ON `hoge_app`.* TO `hoge`@`%` |
+--------------------------------------------+

SELECTはできて，INSERTができないことを確認します．

use hoge_app

select * from article;
+----+---------+------------------------+-----------------------+
| id | user_id | title                  | text                  |
+----+---------+------------------------+-----------------------+
|  1 |       1 | abc                    | test_text1            |
|  2 |       1 | xyz                    | test_text2            |
|  3 |       2 | hogehoge               | test_text3            |
|  4 |       3 | hogehoge               | test_text4            |
|  5 |       1 | insert hoge test title | insert hoge test text |
+----+---------+------------------------+-----------------------+

insert into article (user_id, title, text) values
    -> (1, 'revoke hoge test title', 'revoke hoge test text');
ERROR 1142 (42000): INSERT command denied (省略)