ユーザー作成と削除


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

revoke

警告:もし新しいユーザを作成して IDENTIFIED BY 条項を指定しないと、そのユーザは
パスワードを持ちません。これはとても不安定です。しかし、新規ユーザに空ではない
パスワードを提供する為に IDENTIFIED BY が与えられない限り、GRANT が新規ユーザ
を作成するのを防ぐ為に NO_AUTO_CREATE_USER SQL モード
有効にする事ができます。

showroot

Comments are closed.

Post Navigation