MySQL权限篇之USAGE

mysql数据库用户在创建的时候就会赋予USAGE权限，这个权限很小，几乎为0。

只能连接数据库和查询information_schema的权限。

不过这个权限也很奇怪，你无法revoke。比如：

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.11-log |
+------------+
1 row in set (0.00 sec)


mysql> use mysql
Database changed
mysql> select user,host from user;
+-----------+--------------+
| user      | host         |
+-----------+--------------+
| root      | %            |
| backup    | 192.168.1.77 |
| mysql.sys | localhost    |
| root      | localhost    |
+-----------+--------------+
4 rows in set (0.03 sec)


mysql> create user 'ut01'@'%' identified by '20127163';
Query OK, 0 rows affected (0.09 sec)


mysql> show grants for 'ut01'@'%';
+----------------------------------+
| Grants for ut01@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO 'ut01'@'%' |
+----------------------------------+
1 row in set (0.04 sec)

mysql> 

此时ut01用户可以连接数据库。

mysql> revoke usage on *.* from 'ut01'@'%';
Query OK, 0 rows affected (0.06 sec)

mysql>

此时ut01用户还是可以连接数据库。并且：
mysql> show grants for 'ut01'@'%';
+----------------------------------+
| Grants for ut01@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO 'ut01'@'%' |
+----------------------------------+
1 row in set (0.00 sec)


mysql> 

可见，USAGE权限实际不能revoke。

并且任何其他权限都隐式包含USAGE权限。但是即使revoke了某个用户所有的权限，其USAGE权限还是会保留的。

注意，注意：某个用户如果只有usage权限，那么这个用户还是可以修改自己的密码的。

比如：

mysql> show grants for 'ut01'@'%';
+----------------------------------+
| Grants for ut01@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO 'ut01'@'%' |
+----------------------------------+
1 row in set (0.00 sec)


mysql>

C:\Users\Administrator>mysql -u'ut01'
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 49
Server version: 5.7.11-log MySQL Community Server (GPL)


Copyright (c) 2000, 2016, 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> alter user user() identified by'20127164'; #密码修改成功
Query OK, 0 rows affected (0.31 sec)


mysql> alter user 'ut01'@'%' account lock;
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
mysql>

但是用户不能对自己执行修改密码之外的其他操作，需要具有更高的权限才行。