-
ユーザー作成と削除
mysql> grant usage on DB001.* to 'test_user';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+-------------+-----------+
| user | host |
+-------------+-----------+
| test_user | % |
| variable | % |
| root | 127.0.0.1 |
| root | colinux |
| admin | localhost |
| root | localhost |
| super_admin | localhost |
+-------------+-----------+
7 rows in set (0.00 sec)
mysql> drop user 'test_user';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select user,host from mysql.user;
+-------------+-----------+
| user | host |
+-------------+-----------+
| variable | % |
| root | 127.0.0.1 |
| root | colinux |
| admin | localhost |
| root | localhost |
| super_admin | localhost |
+-------------+-----------+
6 rows in set (0.00 sec)
mysql>
-
パスワードを付けて再度、ユーザーを作成。
mysql> grant usage on DB001.* to 'test_user' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+-------------+-----------+
| user | host |
+-------------+-----------+
| test_user | % |
| variable | % |
| root | 127.0.0.1 |
| root | colinux |
| admin | localhost |
| root | localhost |
| super_admin | localhost |
+-------------+-----------+
7 rows in set (0.00 sec)
mysql>
-
ユーザーに追加で権限を割り振る
mysql> grant select on DB001.* to 'test_user';
Query OK, 0 rows affected (0.00 sec)
mysql> grant insert on DB001.* to 'test_user';
Query OK, 0 rows affected (0.01 sec)
-
ユーザー作成スクリプトを確認して権限の確認
mysql> show grants for 'test_user';
+----------------------------------------------------------------------------------------------------------+
| Grants for test_user@% |
+----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_user'@'%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT SELECT, INSERT ON `DB001`.* TO 'test_user'@'%' |
+----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
追記:その他の例
mysql> show grants for 'admin'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for admin@localhost |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*3210C0C06DEE12SD1618BB00005TGCA2EC9C1A77' |
| GRANT ALL PRIVILEGES ON `DB02`.* TO 'admin'@'localhost' |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
-
=========================================================
作成したユーザーから付与した権限をなくす。
=========================================================
付与した権限を一つ一つ剥奪する。
mysql> show grants for 'test_user'\G
*************************** 1. row ***************************
Grants for test_user@%: GRANT USAGE ON *.* TO 'test_user'@'%' IDENTIFIED BY PASS
WORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'
*************************** 2. row ***************************
Grants for test_user@%: GRANT SELECT, INSERT ON `DB001`.* TO 'test_user'@'%'
2 rows in set (0.00 sec)
mysql> revoke insert on DB001.* from 'test_user';
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for 'test_user'\G
*************************** 1. row ***************************
Grants for test_user@%: GRANT USAGE ON *.* TO 'test_user'@'%' IDENTIFIED BY PASS
WORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'
*************************** 2. row ***************************
Grants for test_user@%: GRANT SELECT ON `DB001`.* TO 'test_user'@'%'
2 rows in set (0.00 sec)
mysql>
mysql> revoke all privileges,grant option from 'test_user';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'test_user'\G
*************************** 1. row ***************************
Grants for test_user@%: GRANT USAGE ON *.* TO 'test_user'@'%' IDENTIFIED BY PASS
WORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'
1 row in set (0.01 sec)
mysql>
-
上記のように、revoke all privileges,grant optionしても接続権限は消えていない。
接続権限、「SHOW VARIABLES」、「SHOW STATUS」、「SHOW DATABASE」
なども利用できる。
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
+——————–+
1 row in set (0.01 sec)
mysql>
全ての権限をrevokeするには、以下のようにdrop userして法が良いかと思う。
mysql> drop user 'test_user';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'test_user'\G
ERROR 1141 (42000): There is no such grant defined for user 'test_user' on host
'%'
mysql> show grants for 'test_user'\G
警告:もし新しいユーザを作成して IDENTIFIED BY 条項を指定しないと、そのユーザは
パスワードを持ちません。これはとても不安定です。しかし、新規ユーザに空ではない
パスワードを提供する為に IDENTIFIED BY が与えられない限り、GRANT が新規ユーザ
を作成するのを防ぐ為に NO_AUTO_CREATE_USER SQL モードを
有効にする事ができます。