MYSQLﾕｰｻﾞｰ作成、権限確認、削除

ﾕｰｻﾞｰ作成と削除

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>

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

Post Navigation