MySQL のデータベースユーザーとデータベースアクセス権限

はじめに

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)