MySQL权限管理(二)

一. 权限拾遗

1. GRANT与创建用户

mysql> grant select on sys.* to 'perf'@'127.0.0.1' identified by '123';
Query OK, 0 rows affected, 1 warning (0.01 sec) -- 这里有一个warning

mysql> show warnings;
-- 输入warning的Message如下：
-- Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement.

上面的这个例子使用GRANT赋权限的同时创建了'perf'@'127.0.0.1'这个用户，但是出现了warning，从给出的提示看来，以后的MySQL版本会废弃掉这种方式

• 正确的创建用户并赋权的方式

mysql> create user 'pref'@'127.0.0.1' identified by '123';
Query OK, 0 rows affected (0.00sec)
mysql> grant select on sys.* to 'perf'@'127.0.0.1';
Query OK, 0 rows affected (0.00sec)

2. 查看某一个用户的权限

mysql> show grants for 'perf'@'127.0.0.1';   
+-----------------------------------------------+
| Grants for perf@127.0.0.1                     |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO 'perf'@'127.0.0.1'      | -- USAGE表示用户可以登录
| GRANT SELECT ON `sys`.* TO 'perf'@'127.0.0.1' | -- 对sys库的所有表有select权限
+-----------------------------------------------+
2 rows in set (0.00 sec)

3. 删除某一个用户

mysql> drop user 'perf'@'127.0.0.1';
Query OK, 0 rows affected (0.00sec)

4. MySQL权限信息

mysql> select * from mysql.user where user='perf'\G
**********