第15讲：DCL类型的SQL语句之用户权限控制

文章目录

1.常见的以及权限定义

MySQL中常用的权限有以下几种，更多的权限可以参考官方文档：https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html
权限说明ALL，ALL PRIVILEGES所有权限SELECT查询数据的权限INSERT插入数据的权限UPDATE修改数据的权限DELETE删除数据的权限ALTER修改表属性、字段结构的权限DROP删除数据库、表、视图的权限CREATE创建数据库和表的权限

2.用户权限控制的语法格式

查询用户所拥有的权限：

SHOW GRANT FOR '用户名'@'主机名'

授予权限的命令格式格式：

GRANT '权限列表' ON 数据库.表名 TO '用户名'@'主机名'

• 授权是将用户授予对某个数据库的操作权限，因此指定完权限后，需要指定这个权限应用于哪个数据库的那张表，也可以使用通配符*，表示所有的数据库和表，最后将权限与用户进行绑定。

撤销权限的命令格式：

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名'

同时授权多个权限时，使用逗号进行分隔，授权时，数据库名称和表名可以使用*通配符，代表所有的数据库以及表。

3.查询jiangxl用户拥有那些权限

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

在输出jiangxl用户拥有的权限列表后，看到的是USAGE，表示没有任何的权限。

4.用户权限控制配置锦集

4.1.为jiangxl用户分配插入/修改/查询的权限

mysql>grantinsert,update,selecton db_1.*to'jiangxl'@'%';

mysql>show grants for'jiangxl'@'%';+-----------------------------------------------------------+| Grants for jiangxl@%|+-----------------------------------------------------------+|GRANTUSAGEON*.*TO`jiangxl`@`%`||GRANTSELECT,INSERT,UPDATEON`db_1`.*TO`jiangxl`@`%`|+-----------------------------------------------------------+

4.2.为jiangxl用户分配创建数据库和表的权限

mysql>grantcreate,dropon db_1.*to'jiangxl'@'%';

mysql>show grants for'jiangxl'@'%';+-------------------------------------------------------------------------+| Grants for jiangxl@%|+-------------------------------------------------------------------------+|GRANTUSAGEON*.*TO`jiangxl`@`%`||GRANTSELECT,INSERT,UPDATE,CREATE,DROPON`db_1`.*TO`jiangxl`@`%`|+-------------------------------------------------------------------------+

4.3.为jiangxl用户分配修改表结构的权限

mysql>grantalteron db_1.*to'jiangxl'@'%';

mysql>show grants for'jiangxl'@'%';+--------------------------------------------------------------------------------+| Grants for jiangxl@%|+--------------------------------------------------------------------------------+|GRANTUSAGEON*.*TO`jiangxl`@`%`||GRANTSELECT,INSERT,UPDATE,CREATE,DROP,ALTERON`db_1`.*TO`jiangxl`@`%`|+--------------------------------------------------------------------------------+

4.4.为jiangxl用户分配全部权限

mysql>grantallon db_1.*to'jiangxl'@'%';

mysql>show grants for'jiangxl'@'%';+---------------------------------------------------+| Grants for jiangxl@%|+---------------------------------------------------+|GRANTUSAGEON*.*TO`jiangxl`@`%`||GRANTALLPRIVILEGESON`db_1`.*TO`jiangxl`@`%`|+---------------------------------------------------+

4.5.权限分配总结

同一个用户再不同的时刻，分配了多个权限，例如上午为jiangxl用户分配了增改查的权限，下午为jiangxl用户分配了创建和删除数据库、表的权限，那么这些权限，会同时追加到一条记录中，也就是说在执行查看权限的命令时，永远只会显示两条记录，第一条是默认的权限记录，第二条就是我们授权的权限记录，无论授权了多少次，都会以追加的形式展示。

如果第一次分配了个别权限，那么最后一次分配了ALL权限，那么最后会被ALL全部覆盖。

5.撤销用户的权限

撤销权限时，如果跟的权限列表是ALL，那么所有的权限都会被撤销。

1）撤销jiangxl用户的增改查权限

mysql>revokeinsert,update,selecton db_1.*from'jiangxl'@'%';

2）撤销jiangxl用户的创建和删除数据库、表的权限

mysql>revokecreate,dropon db_1.*from'jiangxl'@'%';

3）撤销jiangxl用户的全部权限

mysql>revokeallon db_1.*from'jiangxl'@'%';