MySQL操作之用户管理权限管理：(DC)（五）

序号	类型	地址
1	MySQL	MySQL操作之概念、SQL约束（一）
2	MySQL	MySQL操作之数据定义语言（DDL)（二）
3	MySQL	MySQL操作之数据操作语言（DML)（三）
4	MySQL	MySQL操作之数据查询语言：(DQL)（四-1）（单表操作）
5	MySQL	MySQL操作之数据查询语言：(DQL)（四-2）（多表查询）
6	MySQL	MySQL操作之数据控制语言：(DC)（五）
7	MySQL	MySQL操作之数据库函数
8	MySQL	MySQL管理之数据类型
9	MySQL	MySQL管理之索引
10	MySQL	MySQL管理之事务管理
11	MySQL	MySQL管理之存储过程
12	MySQL	MySQL管理之视图
13	MySQL	MySQL管理之数据备份与还原
14	MySQL	Linux（centos 7.5）服务器安装MySQL
15	MyBatis	MyBatis从入门到多表关联
16	MyBatis	MyBatis常用方法
17	MyBatis	Mybatis逆向工程的使用（附文件地址）
18	MyBatis	spring boot连接Mybatis数据库的配置文件(MySql、SQLserver、Oracle)
19	MyBatis-Plus	Mybatis-Plus使用案例(包括初始化以及常用插件)
20	MyBatis-Plus	Mybatis-Plus(Service CRUD 接口)
21	MyBatis-Plus	Mybatis-plus 4种条件构造器方式
22	MyBatis-Plus	Mybatis-Plus 执行自定义SQL
23	MyBatis-Plus	MyBatis-plus配置自定义SQL(执行用户传入SQL)
24	MyBatis-Plus	Mybatis-Plus(连接Hive)
25	MyBatis-Plus	Mybatis-Plus 代码生成器

---

文章目录

• 一、简介
• 二、用户管理

• 2.1 User表
• 2.2 创建普通用户
• 2.3 删除普通用户
• 2.4 修改用户密码
• 2.5 解决root用户密码丢失

• 三、权限管理

• 3.1 MySQL的权限
• 3.2 授予权限
• 3.3 查看权限
• 3.4 收回权限

---

一、简介

MySQL中用户分为root用户和普通用户。root用户为超级管理员，具有所有权限(创建用户、删除用户、管理用户)。

二、用户管理

2.1 User表

• 新建MySQL时，会自动安装一个mysql的数据库，该数据库下面的表都是权限表。
• 其中：user表时最重要权限表。记录了允许连接到服务器的账号信息以及一些全局权限信息。
• user表有42个字段，大致分为4类。

1. 用户列

• 包括Host、User、Password，分别代表主机名、用户名和密码。
• Host、User：为联合主键。
• Host、User、Password：三个字段都匹配时，才会允许建立连接。
• 修改密码：只需要修改User表中Password即可。

2. 权限列

• 包括Select_priv、Insert_priv、Update_priv等以priv结尾的字段，这些字段决定了用户的权限。
• Select_priv：查询权限。
• Insert_priv：插入权限。
• Update_priv：更新权限。
• 数据类型都是枚举类型Enum。
• 默认值为：N(没有权限)。

3. 安全列

• 用户管理用户的安全信息，其中包含6个字段。
• ssl_type和ssl_cipher：用户加密。
• x509_issuer和x509_subject标准：用来标识用户。
• plugin和authentication_string：用于存储于授权相关的插件。

4. 资源控制列

• 用于限制用户使用的资源。其中包括4个字段。
• max_questions：每小时允许用户执行查询操作的次数。
• max_updates：每小时允许用户执行更新操作的次数。
• max_connection：每小时允许用户建立连接的次数。
• max_user_connection：每小时允许用户同时建立连接的次数。

所属分类	字段名	含义
1.用户列	Host	主机名
1.用户列	User	用户名
1.用户列	Password	密码
2. 权限列	Select_priv	确定用户是否可以通过SELECT命令选择数据
2. 权限列	Insert_priv	确定用户是否可以通过INSERT命令插入数据
2. 权限列	Update_priv	确定用户是否可以通过UPDATE命令修改现有数据
2. 权限列	Delete_priv	确定用户是否可以通过DELETE命令删除现有数据
2. 权限列	Create_priv	确定用户是否可以创建新的数据库和表
2. 权限列	Drop_priv	确定用户是否可以删除现有数据库和表
2. 权限列	Reload_priv	确定用户是否可以执行刷新和重新加载MySQL所用各种内部缓存的特定命令。 包括日志、权限、主机、查询和表
2. 权限列	Shutdown_priv	确定用户是否可以关闭MySQL服务器。 在将此权限提供给root账户之外的任何用户时，都应当非常谨慎
3. 安全列	ssl_type	支持ssl标准加密安全字段
3. 安全列	ssl_cipher	支持ssl标准加密安全字段
3. 安全列	x509_issuer	支持x509标准字段
3. 安全列	x509_subject	支持x509标准字段
3. 安全列	plugin	5.5.7开始,mysql引入plugins以进行用户连接时的密码验证,plugin创建外部/代理用户
3. 安全列	authentication_string	通过authentication_string可以控制两者的映射关系。 (PAM plugin等,PAM可以支持多个服务名)尤其是在使用代理用户时，并须声明这一点
4. 资源控制列	max_questions	每小时允许用户执行查询操作的次数
4. 资源控制列	max_updates	每小时允许用户执行更新操作的次数
4. 资源控制列	max_connection	每小时允许用户建立连接的次数
4. 资源控制列	max_user_connection	每小时允许用户同时建立连接的次数

2.2 创建普通用户

有中方式创建：

• 使用GRANT语句创建用户
• 使用CREATE USER语句创建用户
• 使用Insert语句创建用户

1. 使用GRANT语句创建用户

• GRANT语句不仅可以创建新用户，还可以对用户进行授权。
• 该语句会自动加载权限表，不需要手动刷新。
• 而且安全、准确、错误少。
• 使用GRANT语句创建用户是最常用的方法。
• 创建用户时，MySQL会对用户的密码自动加密，以提高安全性。

语法：

GRANT privileges ON database.table
	TO `username`@`hostname` [IDENTIFIED BY[PASSWORD]`password`]

• privileges：表示该用户具有的权限信息。
• database.table：表示新用户的权限范围表，可以在指定的库、表上使用权限。
• username：新用户名称。
• hostname：主机名。
• Password：新用户的密码。

创建新用户：用户名user01，密码1581145，授予对test.student有查询权限。

GRANT SELECT ON test.student TO `user01`@`1581145` IDENTIFIED BY `123`

2. 使用CREATE USER语句创建用户

• 使用CREATE USER语句创建新用户，服务器会自动修改授权表。不需要手动刷新。
• 但，新建的用户是没有权限的。

CREATE USER `username`@`hostname` [IDENTIFIED BY [PASSWORD]`password`]

• username：表示新创建的用户名。
• hostname：表示主机名。
• password：表示用户的密码。
• [PASSWORD]：该参数是可选的，假如密码为普通字符串就不需要使用。

语句：

CREATE USER `user02`@`127.0.0.1` IDENTIFIED BU `123`

3. 使用Insert语句创建用户

• 在User表添加数据。
• 通常只需要添加Host、User、Password这三个字段即可，其它字段取默认值。
• ssl_cipher、x509_issuer、x509_subject字段没有默认值，也需要添加。
• 需要手动刷新权限表或者重启MySQL服务。

新建用户

INSERT INTO mysql.`user` ( `host`, `user`, `Password`, ssl_cipher, x509_issuer, x509_subject )
	VALUES( 'localhost', 'user03', PASSWORD ( '123' ), '', '', '' );

刷新权限：

FLUSH PRIVILEGES;

2.3 删除普通用户

1. 使用DROP User语句删除用户

语法

DROP USER `username`@`hostname` [,`username`@`hostname`];

DROP USER `user01`@`localhost`;

2. 使用DELETE语句删除用户

• DELETE不仅可以删除普通表数据，还可以删除user表数据。

语法

DELETE FROM mysql.user WHERE Host='hostname' AND User='username';

sql

DELETE FROM mysql.user WHERE Host = 'localhost' AND User = 'user02';

2.4 修改用户密码

1. 使用mysqladmin修改密码

dd

mysqladmin -u username [-h hostname] -p password new_password

• username：要修改的用户名。
• -h hostname：可以不写，默认localhost。
• -p password：为关键字。
• new_password：新设置的密码。

修改密码：

mysqladmin -u root -p password 1581145

上述语句执行完成后，会提示输入密码。这个密码是旧密码。

2. 使用Update语句修改密码

语法：

UPDATE mysql.user set Password=PASSWORD('new_password')
	WHERE User = 'username' AND Host='hostname';

sql

UPDATE mysql.user set Password=PASSWORD('1581145')
	WHERE User = 'root' AND Host='localhost';

刷新权限：

FLUSH PRIVILEGES;

3. 使用SET语句修改密码

• 用户登录MySQL服务器以后，可以使用set语句，修改密码。
• 需使用PASSWORD('new_password')进行密码加密。

语法：

SET PASSWORD FOR `username`@'hostname' = PASSWORD('new_password');
SET PASSWORD = PASSWORD('new_password');

SET PASSWORD = PASSWORD('1581145');

4. root使用GRANT修改普通用户密码

语法：

GRANT USAGE ON *.* TO 'usernmae'@'localhost' IDENTIFIED BY [PASSWORD]‘new_password';

2.5 解决root用户密码丢失

• --skip-grant-tables：可以停止MySQL的权限判断。也就是任何用户都可以登录MySQL。

1、停止MySQL服务

net stop mysql;

2、使用--skip-grant-tables启动MySQL服务

mysqld --skip-grant-tables

3、登录MySQL服务器

mysql -u -root

4、使用Update语句设置root用户密码

UPDATE mysql.user SET PASSWORD = PASSWORD ( '158145' ) 
	WHERE User = 'root' AND Host = 'localhot';

5、加载权限表

FLUSH PRIVILEGES;

三、权限管理

3.1 MySQL的权限

MySQL的权限信息，被存储在user、db、host、tables、priv、cloumn_priv和procs_priv中。当MySQL启动时，会加载这些表，并将权限信息读取到内存中。

• Create和Drop权限：可以创建数据库、表、索引，或者删除已有的数据库、表、索引。
• Insert、Delete、Update和Select权限：对数据库表进行增删改查。
• Index权限：可以创建和删除索引。适用于所有表。
• Alter权限：用于修改表的结构或者重命名表。
• Grant权限：允许用户为其它用户授权。可用户数据库和表。
• File权限：能读写MySQL服务器上的所有文件。

3.2 授予权限

语法：

GRANT privileges [(columns)][,privileges [(columns)]...] ON database.table
	TO `username`@`hostname` [IDENTIFIED BY[PASSWORD]`password`]
	[,`username`@`hostname` [IDENTIFIED BY[PASSWORD]`password`]]...
	[WITH with_option [with_option]...]

• privileges：表示权限类型。
• columns：表示权限作用域某一列。
• IDENTIFIED BY：为用户设置密码。

其中WITH关键字有5个参数取值：

1. GRANT OPTION：将自己权限授予其它用户。
2. MAX_QUERIED_PER_HOUR count：设置每小时最多可执行多少次count查询。
3. MAX_UPDATES_PER_HOUR count：设置每小时最多可执行多少次更新。
4. MAX_CONNECTIONS_PER_HOUR count：设置每小时最大连接数。
5. MAX_USER_CONNECTIONS：设置每个用户最多可以同时建立的连接数量。s

授予权限：

GRANT INSERT,SELECT ON *.* TO 'user04'@'localhost' IDENTIFIED BY '123' 
	WITH GRANT OPTION;

3.3 查看权限

SHOW GRANTS：用来查看用户的权限

SHOW GRANTS FOR 'username'@'hostname';

SHOW GRANTS FOR 'root'@'1581145';

结果：

root用户

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' 
IDENTIFIED BY PASSWORD '*B105526BD04216F24C8DC48AF956866AA539F35C' WITH GRANT OPTION

普通用户( SELECT,INSERT)

GRANT SELECT,INSERT ON *.* TO 'user03'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'

3.4 收回权限

REVOKE：用于收回用户的权限。

REVOKE privileges [columns] [,privileges [columns]] ON database.table
	FROM 'username'@'hostname'[,username'@'hostname']...

• privileges：表示参数列表。
• columns：表示作用在哪一列。

收回Insert权限

REVOKE INSERT ON *.* FROM 'user04'@'localhost';

收回全部权限

REVOKE ALL PRIVILEGES,GRANT OPTION ON *.* FROM 'user04'@'localhost';