ユーザー管理

MySQLの認証

インストール直後は、以下のユーザが登録されている。

$ /usr/local/mysql/bin/mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.24a-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select host, user, password from mysql.user;
+-------------------+------+----------+
| host              | user | password |
+-------------------+------+----------+
| localhost         | root |          |
| host.powerdee.com | root |          |
| host.powerdee.com |      |          |
| localhost         |      |          |
+-------------------+------+----------+
4 rows in set (0.00 sec)

MySQLはホスト名とユーザー名の２つでユーザーを識別している。またOracleのようにスキーマと関連されているようなことはなく、認証と権限管理のみと関連されている。

初期登録されているユーザーにはrootという名前の「管理者ユーザー」とユーザー名が空白になっている「匿名ユーザー」が存在している。

ホストは、localhostとインストールしたサーバのhostname(この場合host.powerdee.com)のみである為、リモートからは接続ができない。

認証メカニズム

クライアント側のホスト名とMySQL接続ユーザー名が以下の順番で照合されて最初に一致したユーザーが認証される。

ユーザー名とホスト名の両方が一致する
ホスト名が一致し、userテーブルのユーザー名(user)が空白である
ユーザー名が一致し、userテーブルのホスト名(host)が空白である
userテーブルのユーザー名(user)及びホスト名(host)がともに空白である

ユーザーの登録と削除

ユーザーの登録は、GRANT文もしくは、CREATE USER文により行う。
GRANT構文は、以下のようになっている。括弧[]はオプション、#は整数値。

GRANT 付与する権限の種類[(カラムリスト)] [,付与する権限の種類[(カラムリスト) …] 
    ON データベース名.テーブル名 
    TO ユーザー名@ホスト名
    [IDENTIFIED BY [パスワード]]
    [WITH [GRANT_OPTION | MAX_QUERIES_PER_HOUR # |
                          MAX_UPDATES_PER_HOUR # |
                          MAX_CONNECTIONS_PER_HOUR #]]

CREATE USER構文は、以下。

CREATE USER ユーザー名@ホスト名 [IDENTIFIED BY [パスワード]
    [, ユーザー名@ホスト名 [IDENTIFIED BY [パスワード]] ...

匿名ユーザーが登録されていると何かとややこしい為、ユーザーを一度削除してしまう。

mysql> TRUNCATE TABLE mysql.user;
mysql> FLUSH PRIVILEGES;

ローカルからのみ接続できるrootユーザー(管理者ユーザー)を作成する。パスワードは任意に設定する。
「WITH GRANT OPTION」句を付けるとほかのユーザーに権限を付与できるようになる。

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '********' WITH GRANT OPTION;

データベース名に'_'文字が含まれる場合は、任意の１文字とマッチすることになる為。
以下のようにエスケープ文字列を使用すること。

mysql> GRANT ALL PRIVILEGES ON `demo\_db`.* TO 'user1'@'localhost' IDENTIFIED BY '********' WITH GRANT OPTION;

以下のようにホスト名にはワイルドカード(%)を指定することも可能。

mysql> GRANT ALL PRIVILEGES ON *.* TO 'user1'@'192.168.0.%' IDENTIFIED BY '********';

パスワードの変更

パスワードの変更は、以下のように行う。

mysql> SET PASSWORD = PASSWORD('********');

管理者ユーザー(root)なら他のユーザーのパスワードを変更することも可能。

mysql> SET PASSWORD FOR user1 = PASSWORD('********');

コマンドラインからmysqladminで変更することもできる。

# mysqladmin -u root password new_password
# mysqladmin reload

以下のように直接INSERT文を発行してユーザーを登録したい場合は、PASSWORD関数を利用すること。

INSERT INTO USER (HOST, USER, PASSWORD) VALUES ('%', 'DEMOUSER', PASSWORD('biscuit'));

ユーザーの削除

以下のように、DROP USER文で削除する。

mysql> DROP USER user1@'192.168.0.%';

パスワード無効ユーザー作成の制限

Windowsのインストーラ付き版の場合、初期化パラメータファイル(my.ini)のsql_modeにNO_AUTO_CREATE_USERが指定されている為、パスワード無効ユーザが作成できなくなっている。

mysql> select @@global.sql_mode;    <-- 初期化パラメータsql_modeを確認
+----------------------------------------------------------------+
| @@global.sql_mode                                              |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> grant all on *.* to user1;
ERROR 1133 (42000): Can't find any matching row in the user table
mysql> set sql_mode="";    <-- sql_modeを無効にした。
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to user1;    <-- パスワード無しで作成できた。
Query OK, 0 rows affected (0.00 sec)

インデックスへ戻る