MySQL/ユーザの作成・変更・削除

• Prev
• Next
• MySQL

---

MariaDBでも以下の手順は有効です。

MySQLのユーザの作成・変更・削除方法 †

MySQLのユーザ追加、ユーザ権限変更、ユーザ削除の手順を以下に記します。

関連資料 †

• WindowsにMySQL5.5をインストール
• CentOSにMySQLをインストール

ユーザ操作を行う前に †

CREATE USERでもユーザを作成することができますが、GRANTでも作成することができます。
以下、GRANTを使ってユーザの操作をします。

ユーザ操作を行う前にデータベースおよびテーブルを作成しました。
作成したデータベースおよびテーブルを利用して以降説明します。

# mysql -p -u root
<snip>
mysql> create database db1;
Query OK, 1 row affected (0.01 sec)

mysql> create database db2;
Query OK, 1 row affected (0.00 sec)

mysql> create database db3;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| db3                |
| mysql              |
+--------------------+
5 rows in set (0.00 sec)

ユーザの追加(ユーザの作成) †

MySQLでユーザを作成する構文は以下の通りです。

管理者用の権限を持ったユーザを作成する †

GRANT ALL PRIVILEGES ON *.* TO 'ユーザ名'@'ホスト名' IDENTIFIED BY 'パスワード' WITH GRANT OPTION;

WITH GRANT OPTIONにより他ユーザの権限を変更することができるようになります。
(@ホスト名を省略した場合は@%となります。)

• 実行結果

  mysql> GRANT ALL PRIVILEGES ON *.* TO admin1 IDENTIFIED BY 'pass1' WITH GRANT OPTION;
  Query OK, 0 rows affected (0.00 sec)

  ユーザ確認を確認。

  mysql> SELECT user, host FROM mysql.user;
  +--------+-----------+
  | user   | host      |
  +--------+-----------+
  | admin1 | %         |
  | root   | 127.0.0.1 |
  | root   | localhost |
  +--------+-----------+
  3 rows in set (0.00 sec)

  権限を確認。

  mysql> SHOW GRANTS FOR admin1;
  +----------------------------------------------------------------------------------------------------------------------------------+
  | Grants for admin1@%                                                                                                              |
  +----------------------------------------------------------------------------------------------------------------------------------+
  | GRANT ALL PRIVILEGES ON *.* TO 'admin1'@'%' IDENTIFIED BY PASSWORD '*xxxxxxxxxx' WITH GRANT OPTION                               |
  +----------------------------------------------------------------------------------------------------------------------------------+
  1 row in set (0.00 sec)

  mysqlデータベース内の供与テーブルから権限を再ロードする。(設定の反映)

  mysql> FLUSH PRIVILEGES;
  Query OK, 0 rows affected (0.00 sec)

ここでは、ホスト名を指定せず、admin1ユーザを作成しました。
ホスト名を指定しない場合は、%になります。
'admin1'@'%' と記述したのと同様の動作になります。

ホスト名の%は、SQLのワイルドカードになるので全サーバを意味します。
したがって、外部のサーバからmysqlコマンドを利用して接続することができます。
接続したいホストをMySQLが動作している自サーバのみにしたい場合などは、以下のようにlocalhostや127.0.0.1を指定します。

GRANT ALL PRIVILEGES ON *.* TO admin1 IDENTIFIED BY 'pass1'@'localhost' WITH GRANT OPTION;

外部のサーバから接続しようとすると、以下のメッセージが表示されMySQLへの接続が拒否されます。

$ mysql -u admin1 -h 192.168.56.6 -p
Enter password: 
ERROR 1130 (00000): Host '192.168.56.1' is not allowed to connect to this MySQL server

全権限をあたえる場合 †

GRANT ALL PRIVILEGES ON データベース名.テーブル名 TO 'ユーザ名'@'ホスト名' IDENTIFIED BY 'パスワード';

(@ホスト名を省略した場合は@%となります。)

上記の構文でdb1の全テーブルに対して全権限をあたえる操作を以下に記します。

mysql> GRANT ALL PRIVILEGES ON db1.* TO 'user1'@'localhost' IDENTIFIED BY 'pass2';
Query OK, 0 rows affected (0.00 sec)

user1が作成されているのが確認できます。

mysql> SELECT user, host FROM mysql.user;
+--------+-----------+
| user   | host      |
+--------+-----------+
| root   | 127.0.0.1 |
| admin1 | localhost |
| root   | localhost |
| user1  | localhost |
+--------+-----------+
4 rows in set (0.00 sec)

user1の権限を確認します。

mysql> SHOW GRANTS FOR user1@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for user1@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*xxxxxxxxxx'                               |
| GRANT ALL PRIVILEGES ON `db1`.* TO 'user1'@'localhost'                                                       |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysqlデータベース内の供与テーブルから権限を再ロードする。(設定の反映)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

特定の権限のみをあたえる場合 †

以下の構文では、SELECTとINSERTをあたえる構文になります。

GRANT SELECT, INSERT ON データベース名.テーブル名 TO 'ユーザ名'@'ホスト名' IDENTIFIED BY 'パスワード';

(@ホスト名を省略した場合は@%となります。)

以下の例は、ユーザ名user2に対し、db1,db2にSELECTとINSERTの権限をあたえます。

mysql> GRANT SELECT, INSERT ON db1.* TO 'user2'@'localhost' IDENTIFIED BY 'pass3';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT, INSERT ON db2.* TO 'user2'@'localhost' IDENTIFIED BY 'pass3';
Query OK, 0 rows affected (0.00 sec)

権限を確認します。

mysql> SHOW GRANTS FOR user2@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for user2@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'localhost' IDENTIFIED BY PASSWORD '*xxxxxxxxxx'                               |
| GRANT SELECT, INSERT ON `db1`.* TO 'user2'@'localhost'                                                       |
| GRANT SELECT, INSERT ON `db2`.* TO 'user2'@'localhost'                                                       |
+--------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

db1の全てのテーブルとdb2の全てのテーブルに対しSELECTとINSERTの権限があたえられていることが確認できます。

mysqlデータベース内の供与テーブルから権限を再ロードする。(設定の反映)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

追加したユーザ(作成したユーザ)を確認する †

SELECT user FROM mysql.user;

ホストを含めたユーザ情報を確認するには以下の構文を使用します。

SELECT user, host FROM mysql.user;

ユーザの一覧の出力結果。

mysql> SELECT user, host FROM mysql.user;
+--------+-----------+
| user   | host      |
+--------+-----------+
| root   | 127.0.0.1 |
| admin1 | localhost |
| root   | localhost |
| user1  | localhost |
| user2  | localhost |
+--------+-----------+
5 rows in set (0.00 sec)

権限を確認する †

SHOW GRANTS FOR ユーザ名;

上記の操作で作成したadmin1とuser2の権限を表示した出力結果です。

mysql> SHOW GRANTS FOR admin1@localhost;
+------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for admin1@localhost                                                                                                              |
+------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin1'@'localhost' IDENTIFIED BY PASSWORD '*xxxxxxxxxx' WITH GRANT OPTION                               |
+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR user2@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for user2@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'localhost' IDENTIFIED BY PASSWORD '*xxxxxxxxxx'                               |
| GRANT SELECT, INSERT ON `db1`.* TO 'user2'@'localhost'                                                       |
| GRANT SELECT, INSERT ON `db2`.* TO 'user2'@'localhost'                                                       |
+--------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

権限の削除 †

あたえられている全ての権限を削除するときは、以下の構文を使用します。

REVOKE ALL PRIVILEGES ON データベース名.テーブル名 FROM ユーザ名@ホスト名;

特定の権限を削除する場合は以下の構文になります。

REVOKE 権限[,権限]... ON データベース名.テーブル名 FROM ユーザ名@ホスト名;

(@ホスト名を省略した場合は@%となります。)

上記操作で作成したuser2を使用します。

mysql> SHOW GRANTS FOR user2@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for user2@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'localhost' IDENTIFIED BY PASSWORD '*xxxxxxxxxx'                               |
| GRANT SELECT, INSERT ON `db1`.* TO 'user2'@'localhost'                                                       |
| GRANT SELECT, INSERT ON `db2`.* TO 'user2'@'localhost'                                                       |
+--------------------------------------------------------------------------------------------------------------+

データーベースdb1のINSERT権限を削除します。

mysql> REVOKE INSERT ON db1.* FROM user2@localhost;
Query OK, 0 rows affected (0.00 sec)

データーベースdb1からINSERT権限が削除されているのが確認できます。

mysql> SHOW GRANTS FOR user2@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for user2@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'localhost' IDENTIFIED BY PASSWORD '*xxxxxxxxxx'                               |
| GRANT SELECT ON `db1`.* TO 'user2'@'localhost'                                                               |
| GRANT SELECT, INSERT ON `db2`.* TO 'user2'@'localhost'                                                       |
+--------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

データベースdb2の全権限を削除します。

mysql> REVOKE ALL PRIVILEGES ON db2.* FROM user2@localhost;
Query OK, 0 rows affected (0.00 sec)

データーベースdb2の権限が削除されているのが確認できます。

mysql> SHOW GRANTS FOR user2@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for user2@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'localhost' IDENTIFIED BY PASSWORD '*xxxxxxxxxx'                               |
| GRANT SELECT ON `db1`.* TO 'user2'@'localhost'                                                               |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysqlデータベース内の供与テーブルから権限を再ロードする。(設定の反映)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

ユーザの削除 †

DROP USER ユーザ名@ホスト名

以下の例は、user2を削除したときの出力です。 削除前のユーザ一覧を確認。

mysql> SELECT user, host FROM mysql.user;
+--------+-----------+
| user   | host      |
+--------+-----------+
| root   | 127.0.0.1 |
| admin1 | localhost |
| root   | localhost |
| user1  | localhost |
| user2  | localhost |
+--------+-----------+
5 rows in set (0.00 sec)

user2を削除する。

mysql> DROP USER user2@localhost;
Query OK, 0 rows affected (0.00 sec)

ユーザ一覧を表示し削除されていることを確認。

mysql> SELECT user, host FROM mysql.user;
+--------+-----------+
| user   | host      |
+--------+-----------+
| root   | 127.0.0.1 |
| admin1 | localhost |
| root   | localhost |
| user1  | localhost |
+--------+-----------+
4 rows in set (0.00 sec)

パスワードの変更 †

ユーザ名@ホスト名を指定してパスワードを変更する場合の構文は以下のようになります。

SET PASSWORD FOR ユーザ名@ホスト名 = PASSWORD('新しいパスワード'); 

自分自身のパスワードを変更する場合は以下の構文になります。

SET PASSWORD = PASSWORD('新しいパスワード');

ユーザ名＠ホスト名を指定してパスワードを変更 †

admin1ユーザのパスワードを変更した例です。

mysql> SET PASSWORD FOR admin1@localhost = PASSWORD('admin1pass');
Query OK, 0 rows affected (0.00 sec)

自分自身のパスワードを変更 †

user1でMySQLに接続しパスワードをuser1passに変更した例です。

# mysql -u user1 -p db1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
<snip>
mysql> SET PASSWORD = PASSWORD('user1pass');
Query OK, 0 rows affected (0.00 sec)

FLUSH PRIVILEGESについて †

FLUSH PRIVILEGESコマンドは設定を再ロードしあす。
ユーザ作成や権限を変更した場合は、FLUSH PRIVILEGES;を発行してください。

おもな権限の種類 †

以下におもな権限を記します。

参考リンク

• MySQL::MySQL 5.1 リファレンスマニュアル::4.7.3 MySQL 提供の権限