はじめに
本ページは以下の手順についてまとめたものです。
・MySQLサーバ(MySQL 5.7.5-m15)にMySQLユーザを追加する手順。MySQLサーバ以外のマシン(Ubuntu)から追加したMySQLユーザで接続する手順。
・MySQLサーバに追加したMySQLユーザの権限を剥奪(停止)する手順。
・MySQLサーバに追加したMySQLユーザのパスワードを変更する手順。
・MySQLサーバに追加したMySQLユーザを削除する手順。
対象環境
MySQLサーバの環境は以下になります。
・64bit版 CentOS release 6.6 (2.6.32-504.8.1.el6.x86_64)
・MySQL 5.7.5-m15
MySQLサーバへ接続するマシン(以下:MySQLクライアントと表記します)の環境は以下になります。
・64bit版 Ubuntu Server 14.04 LTS (3.13.0-44-generic)
参考サイト
MySQLクライアント側にmysqlコマンドをインストールする
(1) MySQLクライアント(Ubuntu)にMySQLをインストールします。
以下の手順でUbuntuサーバにmysqlコマンドをインストールしておきます。
MySQLサーバにMySQLユーザを追加する
(2) MySQLサーバにMySQLをインストールします。
以下の手順でMySQLサーバをセットアップします。MySQLにはexample_schemaスキーマとexample_tableテーブルを作成しておきます。
(3) MySQLユーザ追加前の状態を確認します。
[root@example-MySQL-5-7-5 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.5-m15 Source distribution
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT user, host FROM mysql.user ORDER BY user, host;
+------+-----------+
| user | host |
+------+-----------+
| root | localhost |
+------+-----------+
1 row in set (0.00 sec)
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| example_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
mysql>
mysql> use example_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql> show tables;
+--------------------------+
| Tables_in_example_schema |
+--------------------------+
| example_table |
+--------------------------+
1 row in set (0.00 sec)
mysql>
(4) MySQLサーバに新規にMySQLユーザを追加します。
今回の例では、MySQLサーバにMySQLユーザ「example_user」を追加します。
MySQLユーザ「example_user」には、IPアドレスが198.51.100.0/24セグメントのサーバからMySQLサーバのexample_schemaスキーマのexample_tableテーブルに対して、SELECT, INSERT, UPDATE, DELETEの実行を許可する権限を付与します。
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON example_schema.example_table TO 'example_user'@'198.51.100.0/255.255.255.0' IDENTIFIED BY 'Example_passwd123$';
(5) MySQLサーバにMySQLユーザが追加された事を確認します。
mysql> SELECT user, host FROM mysql.user ORDER BY user, host;
+--------------+----------------------------+
| user | host |
+--------------+----------------------------+
| example_user | 198.51.100.0/255.255.255.0 |
| root | localhost |
+--------------+----------------------------+
2 rows in set (0.00 sec)
mysql> show grants for 'example_user'@'198.51.100.0/255.255.255.0';
+--------------------------------------------------------------------------------------------------------------------------------------+
| Grants for example_user@198.51.100.0/255.255.255.0 |
+--------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'example_user'@'198.51.100.0/255.255.255.0' IDENTIFIED BY PASSWORD '*0B9CBC2ECF9226344AFC65404E453C57D1686B52' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `example_schema`.`example_table` TO 'example_user'@'198.51.100.0/255.255.255.0' |
+--------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
mysql> \q
Bye
[root@example-MySQL-5-7-5 ~]#
(6) MySQLクライアント(Ubuntu)からMySQLサーバに対して、example_userユーザでmysql接続できるかテストします。
接続先のMySQLサーバのIPアドレスが「198.51.100.25」の場合、以下のコマンドを実行します。
root@example-ubuntu-server:~# mysql -u example_user --password="Example_passwd123$" -h 198.51.100.25 example_schema
なお、MySQLサーバのiptables等でTCP3306番ポート(MySQL用ポート)に対する接続制限をかけている場合は、あらかじめMySQLクライアントからMySQLサーバに対する3306番ポートめ通信を許可しておきます。
(7) MySQLクライアント(Ubuntu)からMySQLサーバにexample_userユーザでmysql接続したら、SELECT, INSERT, UPDATE, DELETEを実行できるかテストします。
MySQLサーバに接続できたら、example_schemaスキーマのexample_tableテーブルに対して、SELECT, INSERT, UPDATE, DELETEを試してみます。
以下のようにSELECT, INSERT, UPDATE, DELETEが動作すれば、MySQLユーザの追加は完了です。
root@example-ubuntu-server:~# mysql -u example_user --password="Example_passwd123$" -h 198.51.100.25 example_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 5.7.5-m15 Source distribution
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT * FROM `example_schema`.`example_table`;
+------+------+
| id | name |
+------+------+
| 1 | test |
| 2 | test |
+------+------+
2 rows in set (0.01 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `example_schema`.`example_table`( id, name ) VALUES( 3, 'test' );
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM `example_schema`.`example_table`;
+------+------+
| id | name |
+------+------+
| 1 | test |
| 2 | test |
| 3 | test |
+------+------+
3 rows in set (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM `example_schema`.`example_table`;
+------+------+
| id | name |
+------+------+
| 1 | test |
| 2 | test |
+------+------+
2 rows in set (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `example_schema`.`example_table`( id, name ) VALUES( 3, 'test' );
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM `example_schema`.`example_table`;
+------+------+
| id | name |
+------+------+
| 1 | test |
| 2 | test |
| 3 | test |
+------+------+
3 rows in set (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT * FROM `example_schema`.`example_table`;
+------+------+
| id | name |
+------+------+
| 1 | test |
| 2 | test |
| 3 | test |
+------+------+
3 rows in set (0.00 sec)
mysql>
mysql> UPDATE `example_schema`.`example_table` SET name = 'test3' WHERE id = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM `example_schema`.`example_table`;
+------+-------+
| id | name |
+------+-------+
| 1 | test |
| 2 | test |
| 3 | test3 |
+------+-------+
3 rows in set (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT * FROM `example_schema`.`example_table`;
+------+-------+
| id | name |
+------+-------+
| 1 | test |
| 2 | test |
| 3 | test3 |
+------+-------+
3 rows in set (0.00 sec)
mysql>
mysql> DELETE FROM `example_schema`.`example_table` WHERE id = 3;
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM `example_schema`.`example_table`;
+------+------+
| id | name |
+------+------+
| 1 | test |
| 2 | test |
+------+------+
2 rows in set (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> \q
Bye
root@example-ubuntu-server:~#
MySQLサーバに追加したMySQLユーザの権限を剥奪(停止)する
(8) MySQLユーザに追加したMySQLユーザの権限を剥奪(停止)したくなった場合、以下のコマンドを実行します。
例えばMySQLユーザ(example_user)のINSERT, UPDATE, DELETE権限を剥奪(停止)したくなった場合、以下のコマンドを実行します。ただし、権限を剥奪するだけで、MySQLユーザそのものは削除しません。
[root@example-MySQL-5-7-5 ~]# mysql -u root -p
(中略)
mysql> REVOKE INSERT, UPDATE, DELETE ON example_schema.example_table FROM 'example_user'@'198.51.100.0/255.255.255.0';
(9) MySQLユーザの権限が剥奪された事を確認します。
mysql> REVOKE INSERT, UPDATE, DELETE ON example_schema.example_table FROM 'example_user'@'198.51.100.0/255.255.255.0';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT user, host FROM mysql.user ORDER BY user, host;
+--------------+----------------------------+
| user | host |
+--------------+----------------------------+
| example_user | 198.51.100.0/255.255.255.0 |
| root | localhost |
+--------------+----------------------------+
2 rows in set (0.00 sec)
mysql> SELECT user, host FROM mysql.user ORDER BY user, host;
+--------------+----------------------------+
| user | host |
+--------------+----------------------------+
| example_user | 198.51.100.0/255.255.255.0 |
| root | localhost |
+--------------+----------------------------+
2 rows in set (0.00 sec)
mysql> show grants for 'example_user'@'198.51.100.0/255.255.255.0';
+--------------------------------------------------------------------------------------------------------------------------------------+
| Grants for example_user@198.51.100.0/255.255.255.0 |
+--------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'example_user'@'198.51.100.0/255.255.255.0' IDENTIFIED BY PASSWORD '*0B9CBC2ECF9226344AFC65404E453C57D1686B52' |
| GRANT SELECT ON `example_schema`.`example_table` TO 'example_user'@'198.51.100.0/255.255.255.0' |
+--------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
MySQLクライアントからMySQLサーバに対して、MySQLユーザ(example_user)でmysql接続して、INSER,UPDATE,DELETEが実行できなくなった事を確認します。
root@example-ubuntu-server:~# mysql -u example_user --password="Example_passwd123$" -h 198.51.100.25 example_schema
(中略)
mysql> SELECT * FROM `example_schema`.`example_table`;
+------+------+
| id | name |
+------+------+
| 1 | test |
| 2 | test |
+------+------+
2 rows in set (0.00 sec)
mysql> INSERT INTO `example_schema`.`example_table`( id, name ) VALUES( 3, 'test' );
ERROR 1142 (42000): INSERT command denied to user 'example_user'@'************' for table 'example_table'
mysql>
mysql> SELECT * FROM `example_schema`.`example_table`;
+------+------+
| id | name |
+------+------+
| 1 | test |
| 2 | test |
+------+------+
2 rows in set (0.00 sec)
mysql>
mysql> DELETE FROM `example_schema`.`example_table` WHERE id = 2;
ERROR 1142 (42000): DELETE command denied to user 'example_user'@'************' for table 'example_table'
mysql>
mysql> SELECT * FROM `example_schema`.`example_table`;
+------+------+
| id | name |
+------+------+
| 1 | test |
| 2 | test |
+------+------+
2 rows in set (0.01 sec)
mysql>
mysql> UPDATE `example_schema`.`example_table` SET name = 'test2' WHERE id = 2;
ERROR 1142 (42000): UPDATE command denied to user 'example_user'@'************' for table 'example_table'
mysql> SELECT * FROM `example_schema`.`example_table`;
+------+------+
| id | name |
+------+------+
| 1 | test |
| 2 | test |
+------+------+
2 rows in set (0.00 sec)
mysql>
MySQLサーバに追加したMySQLユーザのパスワードを変更する
(10) MySQLユーザのパスワードを変更したくなった場合、以下のコマンドを実行します。
[root@example-MySQL-5-7-5 ~]# mysql -u root -p
(中略)
mysql> SET PASSWORD FOR 'example_user'@'198.51.100.0/255.255.255.0'=PASSWORD('NewExample_passwd456$');
Query OK, 0 rows affected (0.00 sec)
mysql>
MySQLクライアントからMySQLサーバに対して、MySQLユーザ(example_user)の新しいパスワードでmysql接続できるかテストします。
root@example-ubuntu-server:~# mysql -u example_user --password="NewExample_passwd456$" -h 198.51.100.25 example_schema
(中略)
mysql> SELECT * FROM `example_schema`.`example_table`;
+------+------+
| id | name |
+------+------+
| 1 | test |
| 2 | test |
+------+------+
2 rows in set (0.00 sec)
mysql> \q
Bye
root@example-ubuntu-server:~#
一応、古いパスワードで接続できなくなった事も確認します。
root@example-ubuntu-server:~# mysql -u example_user --password="Example_passwd123$" -h 198.51.100.25 example_schema
ERROR 1045 (28000): Access denied for user 'example_user'@'************' (using password: YES)
root@example-ubuntu-server:~#
MySQLサーバに追加したMySQLユーザを削除する
(10) MySQLサーバに追加したMySQLユーザを削除したくなった場合、以下のコマンドを実行します。
[root@example-MySQL-5-7-5 ~]# mysql -u root -p
(中略)
mysql> SELECT user, host FROM mysql.user ORDER BY user, host;
+--------------+----------------------------+
| user | host |
+--------------+----------------------------+
| example_user | 198.51.100.0/255.255.255.0 |
| root | localhost |
+--------------+----------------------------+
2 rows in set (0.00 sec)
mysql> DROP USER 'example_user'@'198.51.100.0/255.255.255.0';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT user, host FROM mysql.user ORDER BY user, host;
+------+-----------+
| user | host |
+------+-----------+
| root | localhost |
+------+-----------+
1 row in set (0.00 sec)
mysql> show grants for 'example_user'@'198.51.100.0/255.255.255.0';
ERROR 1141 (42000): There is no such grant defined for user 'example_user' on host '198.51.100.0/255.255.255.0'
mysql>
mysql> \q
Bye
[root@example-MySQL-5-7-5 ~]#
MySQLクライアントからMySQLサーバに対して、MySQLユーザ(example_user)でmysql接続できなくなった事をテストします。
root@example-ubuntu-server:~# mysql -u example_user --password="NewExample_passwd456$" -h 198.51.100.25 example_schema
ERROR 1045 (28000): Access denied for user 'example_user'@'************' (using password: YES)
root@example-ubuntu-server:~#
以上になります。