このエントリーをはてなブックマークに追加


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

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

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;を発行してください。

おもな権限の種類

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

ALL(or ALL PRIVIREGES)全ての権限をあたえる
USAGE全ての権限をあたえない
CREATEデータベース、テーブル、またはインデックス
INSERTテーブルに対するINSERT
SELECTテーブルに対するSELECT
UPDATEテーブルに対するUPDATE
DELETEテーブルに対するDELETE
ALTERテーブルに対するALTER

参考リンク


トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2021-01-23 (土) 23:05:24