はじめに
MySQL でデータベースの作成からデータベースユーザーの作成とアクセス権限の付与、はよく実行するので覚えているのですが、反対の操作、つまりデータベースアクセス権限の削除、データベースユーザーの削除、データベースの削除を忘れがちになるのでメモしておきます。
一番ややこしく感じさせるのは、作成時は 2 コマンドでできるのに対して、削除時は 3 コマンドが必要になる、というところでしょうか。
作成時のコマンド
$ mysql -u root
(データベース作成)
> CREATE DATABASE dbname;
(データベースユーザー作成とアクセス権限付与)
> GRANT ALL PRIVILEGES ON dbname.* TO dbuser@'localhost' IDENTIFIED BY 'dbpassword';
ちなみに、作成時も3コマンドで対応することができます。
$ mysql -u root
(データベース作成)
> CREATE DATABASE dbname;
(データベースユーザー作成)
> CREATE USER dbuser@'localhost' IDENTIFIED BY 'dbpassword';
(データベースに対するユーザーのアクセス権限付与)
> GRANT ALL PRIVILEGES ON dbname.* TO dbuser@'localhost;
接続元ホストは、ホスト名ではなくIPアドレスで指定することもできます。
(1ホスト)
> GRANT ALL PRIVILEGES ON dbname.* TO dbuser@'192.168.1.21/255.255.255.255' IDENTIFIED BY 'dbpassword';
(1サブネット上の複数のホスト)
> GRANT ALL PRIVILEGES ON dbname.* TO dbuser@'192.168.1.0/255.255.255.0' IDENTIFIED BY 'dbpassword';
削除時のコマンド
$ mysql -u root
(アクセス権限の剥奪)
> REVOKE ALL PRIVILEGES ON dbname.* FROM dbuser@'localhost';
(適用)
> FLUSH PRIVILEGES;
(データベースユーザーの削除)
> DROP USER dbuser@'localhost';
(データベースの削除)
> DROP DATABASE dbname;
登録されている各情報の確認方法
管理文書などでしっかり管理出来ていればいいのですが、あいにく現物を見るしかない、という状況もよくあります。このようなときに、どんなデータベースやユーザーが登録されているのか確認する方法を紹介します。
データべース
$ mysql -u root
(データベース一覧)
> SHOW DATABASES;
データベースユーザー
この一覧から、登録済みのデータベースユーザーのユーザ名とホスト名のペア ( dbuser@’localhost’ の部分 ) を確認できます。
$ mysql -u root
(データベースユーザー一覧)
> SELECT user, host, password FROM mysql.user;
アクセス権限
どのデータベースユーザーがどのデータベースにアクセスできるのかを簡単に確認するには、次の SQL 文を使用します。
$ mysql -u root
(データベースユーザーと、そのユーザーがアクセスできるデータベースの一覧)
> SELECT user, host, db FROM mysql.db;
さらに詳しい権限の内訳を調べるには、上記結果から user, host, db のうち任意の条件を指定すると、余計なレコードが表示されず、目的の情報のみ表示できます。
SQL 文の最後、「;」ではなく「\G」を付けると、表形式から1レコード毎に Key-Value 形式で表示でき、見やすくなります。
$ mysql -u root
(データベースユーザーと、そのユーザーがアクセスできるデータベースの一覧)
> SELECT * FROM mysql.db WHERE user = 'dbuser' AND host = 'localhost' AND db = 'dbname' \G
(結果サンプル)
*************************** 1. row ***************************
Host: localhost
Db: dbname
User: dbuser
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Execute_priv: Y
1 row in set (0.00 sec)
上記の結果サンプルは、「ALL PRIVILEGES」を指定したときの権限の詳細です。GRANT 構文で指定した「ALL PRIVILEGES」は特定の 1 つの権限の名前ではなく、「GRANT 以外のすべての権限」と言い換えることができます。GRANT 権限はその名のとおり、別のユーザーに権限を付与したり剥奪したりできる権限を与えるかどうか、という権限です。
もし、レコードの読み取りだけを許可したい、という場合には、「ALL PRIVILEGES」ではなく「SELECT」のみ指定して権限を与えるのがいいでしょう。
なお、ごく一部の権限しか与えていないような状況でも、すべての権限を剥奪するときは、厳密に「REVOKE SELECT …」などと個別に権限を指定しなくても、「REVOKE ALL PRIVILEGES …」を指定してしまって問題ありません。
$ mysql -u root
> GRANT SELECT ON dbname.* TO dbuser@'localhost' IDENTIFIED BY 'dbpassword';
Query OK, 0 rows affected (0.00 sec)
> SELECT * FROM mysql.db WHERE user = 'dbuser' AND db = 'dbname' \G
*************************** 1. row ***************************
Host: localhost
Db: dbname
User: dbuser
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
1 row in set (0.00 sec)
「MySQL のデータベースユーザーとデータベースアクセス権限」への1件の返信
[…] 今回は最低限の設定のみ紹介しました。MariaDBまたはMySQLのデータベース作成、ユーザー作成、データベース権限設定などはこちらの投稿を参照してください。 […]