MySQL学习(10)︱用户与权限管理

认识DCL

SQL语言按照功能用途分为4类，分别是DDL、DML、DQL、DCL。其中，DCL是数据控制语言，主要用于管理用户和权限。在企业中，这部分工作通常为DBA完成，一般开发人员接触较少。DCL主要能做的：创建用户、删除用户、修改密码、给用户赋予权限、撤销用户权限。

权限体系

MySQL的权限体系大致分为5个层级，全局层级、数据库层级、表层级、列层级和子程序层级。
MySQL的权限信息主要存储在以下几张表中，当用户连接数据库时，MySQL会根据这些表对用户进行权限验证。

用户管理

在MySQL中，使用CREATE来创建用户，用户创建后没有任何权限(仅允许登录)。

• 创建用户：

CREATE USER '用户名'[@'主机名'] [IDENTIFIED BY '密码'];//注意：MySQL的用户账号由两部分组成：用户名和主机名，即用户名@主机名，主机名可以是IP或者机器名称，主机名为%表示允许任何地址的主机远程登陆MySQL数据库。

• 删除用户：

DROP USER '用户名' [@'主机名'];

• 修改密码：

ALTER USER '用户名'@'主机名' IDENTIFIED BY '新密码';

权限管理

在MySQL数据库中，使用grant命令授权、revoke命令撤销授权。

• 授权：

GRANT ALL PRIVILEGES ON databaseName.tableName TO '用户名'[@'主机名'];

• 撤销授权：

REVOKE ALL PRIVILEGES ON databaseName.tableName FROM '用户名'[@'主机名'];

• 刷新权限：

FLUSH PRIVILEGES;

• 查看权限：

SHOW GRANT FOR '用户名'[@'主机名'];

使用grant和revoke进行授权、撤销授权时，需要指定具体是哪些权限，这些权限大致可以分为3类：数据类、结构类、管理类。如下图所示。

例子

1. 以本机的root用户名登陆数据库，查看所有的数据库

SHOW DATABASES;

效果图：

2. 使用mysql数据库并查看其拥有的数据表

USE mysql;
SHOW TABLES;

效果图：

3. 格式化查看user数据表所有的用户

SELECT * FROM user\G;//\G的作用是以格式化输出

效果图：


注：默认有4个用户，此文仅显示其中一个
4. 创建两个新用户

CREATE USER 'BigG'@'%' IDENTIFIED BY '123456';
CREATE USER 'SmallG'@'192.168.0.222' IDENTIFIED BY '123456';

效果图：

5. 再次查看user数据表，刚才新添加的两个用户已经可以在user数据表中查看了

SELECT * FROM USER\G;

6. 使用win+r打开运行，输入cmd打开命令行窗口，输入以下命令来以BigG用户登陆数据库mysql -h localhost -u BigG -p登陆后查看所有的数据库，发现仅有一个数据库

SHOW DATABASES;

效果图：

7. 尝试以BigG用户身份创建数据库，提示创建失败(没有权限)

CREATE DATABASE mydb;

效果图：

8. 回到root用户的窗口，赋予BigG用户select命令的权限

GRANT SELECT ON student.* TO 'BigG'@'%';//数据库名.表名 的形式

效果图：

9. 再次使用第6步的命令来登陆BigG用户，并显示所有的数据库，发现比起授权之前多了一个student数据库

SHOW DATABASES;

效果图：

10. 使用student数据库，可以查看所有数据表

USE student;
SHOW TABLES;

效果图：

11. 使用select命令来验证第8步的权限生效

SELECT * FROM area;

效果图：

12. 尝试删除area数据表中某一项记录，提示没有权限(因为root用户仅赋予了BigG用户使用select命令，未赋予其他命令的权限)

DELETE FROM area WHERE id=6;

效果图：

13. 再次回到root用户的窗口，撤销赋予BigG用户的权限

REVOKE SELECT ON student.* FROM 'BigG'@'%';

效果图：

14. 先在root用户的窗口刷新权限，再去到BigG用户的窗口查看数据库，发现之前的student数据库已经不见了，说明第13步生效了

FLUSH PRIVILEGES;//刷新权限

效果图：

SHOW DATABASES;

15. 使用以下命令mysql -h localhost -u SmallG -p来以SmallG的身份登陆数据库，发现登陆失败(因为创建SmallG用户的时候指定了它的主机名，也就是IP地址，若本机的IP地址不符合要求则无法登陆)
效果图：

由此可见，MySQL对于用户的权限有着非常严格的要求。

注：本文是博主MySQL学习的总结，不支持任何商用，转载请注明出处！如果你也对MySQL学习有一定的兴趣和理解，欢迎随时找博主交流~