用户权限管理

lmio 收录于类别数据库和系列 mysql数据库

2023-05-26 2023-05-26 约 4615 字预计阅读 10 分钟

系列 - mysql数据库

一、实验目的及任务

1.1 实验目的

了解用户及权限的作用
掌握创建用户操作
掌握设置用户密码操作
掌握授予子用户权限操作

1.2 实验任务

创建不同的用户
对用户进行加锁、解锁操作
授予子用户权限并进行查看

二、实验环境

Linux 系统
MySQL5.7

三、实验原理

在之前的章节里，我们都是通过root用户，也就是超级用户登录数据库进行相关操作。在日常的工作环境下，为了保证数据库的安全，数据库管理员会对需要操作数据库的人员分配相应的用户名、密码以及该人员应有的权限，使其只能在特定的范围内对数据库进行操作。本章我们主要讲一下MySQL中关于用户及权限的相关知识。

3.1 用户与权限

用户是数据库的使用者和管理者， MySQL通过用户的设置来控制数据库操作人员的访问与操作范围。在安装 MySQL时，系统会自动安装一个名为 mysql的数据库，该数据库主要用于维护数据库的用户以及权限的控制和管理。其中，MySQL中的所有用户信息都保存在 mysql.user数据表中。

MySQL中存在4个控制权限的表，分别为user表，db表，tables_priv表，columns_priv表。

user表：存放用户账户信息以及全局级别（所有数据库）权限，决定了来自哪些主机的哪些用户可以访问数据库实例，如果有全局权限则意味着对所有数据库都有此权限 db表：存放数据库级别的权限，决定了来自哪些主机的哪些用户可以访问此数据库 tables_priv表：存放表级别的权限，决定了来自哪些主机的哪些用户可以访问数据库的这个表 columns_priv表：存放列级别的权限，决定了来自哪些主机的哪些用户可以访问数据库表的这个字段

`user`表中字段

1. 账号字段

`host`,`user`字段共同组合的复合主键用于区分MySQL中的用户。`host`字段表示运行访问的客户端的ip地址，`user`字段表示用户的名称。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21


select host,user from mysql.user;
    
    +-----------+------------------+
    
    | host      | user             |
    
    +-----------+------------------+
    
    | %         | admin            |
    
    | localhost | mysql.infoschema |
    
    | localhost | mysql.session    |
    
    | localhost | mysql.sys        |
    
    | localhost | root             |
    
    +-----------+------------------+
    
    5 rows in set (0.00 sec)

2. 身份验证字段

在 MySQL中，`mysql.user`表使用`plugin`和`authentication_ string`字段保存用户身份验证的信息。其中，`plugin`字段用于指证插件名称，`authentication_string`字段是根据`plugin`指定的插件算法对账户明文密码(如123456)加密后的字符串。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21


select host,user from mysql.user;
    
    +-----------+------------------+
    
    | host      | user             |
    
    +-----------+------------------+
    
    | %         | admin            |
    
    | localhost | mysql.infoschema |
    
    | localhost | mysql.session    |
    
    | localhost | mysql.sys        |
    
    | localhost | root             |
    
    +-----------+------------------+
    
    5 rows in set (0.00 sec)

> 除此之外，与身份验证的账号密码相关的字段还有`password_expired`(密码是否过期)、 `password_last_changed`(密码最后一次修改的时间)以及 `password_lifetime`(密码的有效期)

3. 安全连接字段

在客户端与 MySQL服务器连接时，除了可以基于账户名以及密码的常规验证外，还可以判断当前连接是否符合SSL安全协议，与其相关的字段有以下几种。

1.  `ssl type`:用于保存安全连接的类型，它的可选值有＂(空)、ANY(任意类型)、X509(X509证书)、 SPECIFIED(规定的)4种。
2.  `ssl cipher`:用于保存安全加密连接的特定。
3.  `x509 Issuer`:保存由CA签发的有效的X509证书。
4.  `x509 subject`:保存包含主题的有效的X509证书。

4. 资源限制字段

在`mysql.user`表中提供的以“max”开头的字段，保存对用户可使用的服务器资源的限制，用来防止用户登录 MySQL服务器后的不法或不合规范的操作浪费服务器的资源，各字段的具体含义如下所示。

1.  `max_questions`:保存每小时允许用户执行查询操作的最多次数。
2.  `max_updates`:保存每小时允许用户执行更新操作的最多次数。
3.  `max_connections`:保存每小时允许用户建立连接的最多次数。
4.  `max_user_ connections`:保存允许单个用户同时建立连接的最多数量。

以上列举的用户资源限制字段默认值均为0，表示对此用户没有任何的资源限制。

5. 权限字段

在 `mysql.user`表中提供的以“_priv”结尾的字段一共有29个，这些字段保存了用户的全局权限，如 `Select_priv`查询权限、 `Insert_priv`插入权限、 `Update_priv`更新权限等。

其中，`user`表对应的权限字段的数据类型都是ENUM枚举类型，取值只有N或Y两种。N表示该用户没有对应权限，Y表示该用户有对应权限。为了保证数据库的安全，这些字段的默认值都为N，如果需要可以对其进行修改。

6. 账户锁定字段

在 `mysql.user`表中提供的 `account_locked`字段用于保存当前用户是锁定还是解锁状态。该字段是一个枚举类型，当其值为N时表示解锁，此用户可以用于连接服务器;当其值定，不能用于连接服务器使用为Y时表示该用户已被锁。

3.2 用户管理

创建用户

CREATE USER [IF NOT EXISTS]

`账户名[用户身份验证选项门][，账户名[用户身份验证选项]]…

[NITH资源控制选项][密码管理选项 | 账户锁定选项] ` 例. 创建密码为123456的user1用户。

CREATE USER 'user1'@'localhost' IDENTIFIED BY '123456';

例. 同时创建多个用户。

1
2
3
4
5


CREATE USER

'user2'@'localhost' IDENTIFIED BY '222222',

'user3'@'localhost' IDENTIFIED BY '333333';

在上述语法中，在一个 CREATE USER语句中可同时创建多个用户，多个用户之间使用逗号分隔，并且在创建每个用户时可以单独为其设置密码，省略用户身份验证选项时，表明此用户在登录服务器时可以免密登录，但为了保证数据安全，不推荐用户这样做。

例. 创建user4，并设置其密码账户密码生存期为30天，即每30天就得变更一次密码。

1
2
3


CREATE USER 'user4'@'localhost' IDENTIFIED BY '444444'

PASSWORD EXPIRE INTERVAL 30 DAY;

设置用户是否被锁定。利用account关键字为创建的用户设置是否被锁定，lock表示锁定，unlock表示解锁，被锁定的用户不能在客户端登录MySQL服务器。

例. 创建user5，并将该用户锁定。

CREATE USER 'user5'@'localhost' IDENTIFIED BY '555555' ACCOUNT LOCK;

修改用户

基本语法如下

ALTER USER [IF EXISTS]

账户名[用户身份验证选项][，账户名[用户身份验证选项1]…

[WITH资源限制选项][密码管理选项|账户锁定选项]

例. 修改user5的密码为123456，并将其解锁。

ALTER USER 'user5'@'localhost' IDENTIFIED BY '123456' ACCOUNT UNLOCK;

重命名用户

在利用 ALTER USER修改用户时只能修改指定账户的相关选项，如密码、验证插件、资源控制选项等，而不能够为用户重新命名，此时可以使用 MySQL专门提供的 RENAMEUSER语句实现，基本语法格式如下。

RENAME USER

旧用户名 1 TO 新用户名1 [,旧用户名 2 T0 新用户名2]…

例. 将用户user5重命名为xiaoming

RENAME USER ‘user5’@’localhost’ TO ‘xiaoming’@’localhost’;

被重命名的旧用户不存在或新用户名已存在时，系统会报错。

删除用户

在 MySQL中经常会创建多个普通用户管理数据库，但如果发现某些用户是没有必要的，就可以将其删除，通常删除用户的方式采用 MySQL提供的专门SQL语句。其基本语法格式如下。

DROP USER [IF EXISTS] 账户名 [,账户名]…;

在上述语法中， MYSQL.5.x版本之后DROP USER语句可以同时删除一个或多个MySQL中的指定用户，并会同时从授权表中删除账户对应的权限行。

例. 删除用户user5。

1

DROP USER IF EXISTS user5;

在上述示例中，在不添加 IF EXISTS关键字时，若删除了一个不存在的用户，则该语句的执行会发生错误;在添加后，会在删刷除不存在的用户时生成一个警告作为提示。其中，在删除账户时，如果省略主机地址，则默认为“％”。

当 DROP USER语句删除当前正在打开的用户时，则该用户的会话不会被自动关闭。只有在该用户会话关闭后，删除操作才会生效，再次登录将会失败。另外，利用已删除的用户登录服务器创建的数据库或对象不会因此删除操作而失效。

3.3 权限管理

在实际项目开发中，为了保证数据的安全，数据库管理员需要为不同层级的操作人员分配不同的权限，限制登录 MYSQL服务器的用户只能在其权限范围内操作。同时管理员还可以根据不同的情况为用户增加权限或回收权限，从而控制数据操作人员的权限。

授予权限

MySQL提供的GRANT用于实现用户权限的授予，其基本语法格式如下。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


GRANT
权限类型 [字段列表][，权限类型[字段列表]] ...

ON [目标类型] 权限级别

TO 账户名 [用户身份验证选项] [, 账户名用[户身份验证选项]] ...

[REQUIRE连接方式]

[WITH {GRANT OPTION | 资源控制选项}]

在上述语法中，权限类型指的就是表7-7中的权限列，字段列表用于设置列权限，ON后的目标类型默认为 TABLE，表示将全局、数据库、表或列中的某些权限授予给指定的用户。

回收权限

在 MySQL中，为了保证数据库的安全，需要将用户不必要的权限回收。例如，数据管理员发现某个用户不应该具有 DELETE权限，就应该及时将其收回。为此， MySQL专门提供了一个 REVOKE语句用于回收指定用户的权限。其基本语法格式如下。

1
2
3


REVOKE 权限类型 [(字段列表)] [, 权限类型[(字段列表)]] ...

ON [目标类型]权限级别 FROM 账户名[,账户名] ...

在上述语法中，权限类型、目标类型以及权限级别与授予权限GRANT的参数相同。

刷新权限

在GRANT、 CREATE USER等操作会将服务器的缓存信息保存到内存中，而 REVOKE、DROP USER操作并不会同步到内存中，因此可能会造成服务器内存的消耗，所以在REVOKE、 DROP USER后推荐使用 MySQL提供的 FLUSH PRIVILEGES重新加载用户的权限。SQL语句如下。

FLUSH PRIVILEGES;

四、实验步骤

4.1 用户管理

创建用户

例. 创建密码为123456的user1用户。

CREATE USER 'user1'@'localhost' IDENTIFIED BY '123456';

例. 同时创建多个用户。

1
2
3
4
5


CREATE USER

'user2'@'localhost' IDENTIFIED BY '222222',

'user3'@'localhost' IDENTIFIED BY '333333';

例. 创建user4，并设置其密码账户密码生存期为30天，即每30天就得变更一次密码。

1
2
3


CREATE USER 'user4'@'localhost' IDENTIFIED BY '444444'

PASSWORD EXPIRE INTERVAL 30 DAY;

例. 创建user5，并将该用户锁定。

CREATE USER ‘user5’@’localhost’ IDENTIFIED BY ‘555555’ ACCOUNT LOCK;

修改用户

例. 修改user5的密码为123456，并将其解锁。

ALTER USER 'user5'@'localhost' IDENTIFIED BY '123456' ACCOUNT UNLOCK;

重命名用户

例. 将用户user5重命名为xiaoming

RENAME USER 'user5'@'localhost' TO 'xiaoming'@'localhost';

被重命名的旧用户不存在或新用户名已存在时，系统会报错。

删除用户

例. 删除用户user4。

DROP USER IF EXISTS user4;

4.2 用户权限管理

查看权限

我们来查看一下刚刚创建的user1拥有的权限

show grants for user1@localhost;

USAGE表示user1没有任何权限

授予权限

我们在上节课的背景下，假设user1是用户，他只能可以对商品goods表进行查看操作，我们现在来授予他查看goods表的权限

grant select on p10.goods to user1@localhost;

然后我们重新查一下用户user1拥有的权限

show grants for user1@localhost;

我们退出登录一下，使用user1重新登录，试试用户user1是否可以读goods表的数据，并且只能读取数据。

通过上图可以看到，用户user1只能对goods表进行查询数据，不能进行增删改操作。

回收权限

我们现在尝试将user1用户拥有的查询goods表权限收回

在此之前，我们需要切换回原来的root用户，因为user1没有修改用户权限的权限

revoke select on p10.goods from user1@localhost;

通过上图，可以看到use1用户已无任何权限，此时我们再次登录user1，是找不到goods表的。

刷新权限

flush privileges;

通过该语句可以对所有用户的权限进行刷新操作，避免造成服务器内存的消耗。

五、实验结论

本章主要讲解了在MySQL中的用户管理及用户权限管理。通过本章的学习希望能够掌握MySQL中创建用户、修改用户等相关操作，以及对用户进行授予权限、回收权限等操作。

目录

用户权限管理

一、实验目的及任务

1.1 实验目的

1.2 实验任务

二、实验环境

三、实验原理

3.1 用户与权限

user表中字段

1. 账号字段

2. 身份验证字段

3. 安全连接字段

4. 资源限制字段

5. 权限字段

6. 账户锁定字段

3.2 用户管理

创建用户

修改用户

重命名用户

删除用户

3.3 权限管理

授予权限

回收权限

刷新权限

四、实验步骤

4.1 用户管理

创建用户

修改用户

重命名用户

删除用户

4.2 用户权限管理

查看权限

授予权限

回收权限

刷新权限

五、实验结论

`user`表中字段