【mysql性能调优 • 四】mysql用户权限原理和实战（史上最详细）

news/2024/11/29 3:38:04/

前言

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

在这里插入图片描述
在学习这一章节的时候，我们可以先了解一下【数据库原理 • 五】数据库安全性与完整性的定义
和【数据库原理 • 三】关系数据库标准语言SQL的概念和发展历史

mysql用户权限原理和实战

  • 一、访问控制
    • 1.1 连接验证（阶段1）
    • 1.2 允许的连接
    • 1.3 连接的优先级
    • 1.4 请求验证（阶段二）
  • 二、用户管理
    • 2.1 新增用户
    • 2.2 用户修改
      • 2.2.1 使用alter修改用户
      • 2.2.2 DML语句管理mysql用户
    • 2.3 删除用户
    • 2.4 查看用户
      • 2.4.1 show create user 显示用户非权限属性
      • 2.4.2 select from mysql.user 查看已经创建的用户
      • 2.4.3 select current_user() 查看当前登录用户
  • 三、密码管理
    • 3.1 修改密码
      • 3.1.1 基本使用 alter
      • 3.1.2 其他方式 mysqladmin
      • 3.1.3 其他方式 set password
      • 3.1.4 修改当前登录用户密码
    • 3.2 密码过期设置
      • 3.2.1 使密码立即过期 PASSWORD EXPIRE
      • 3.2.2 使密码从不过期 EXPIRE NEVER
      • 3.2.3 默认过期时间 PASSWORD EXPIRE DEFAULT
      • 3.2.4 指定过期间隔 EXPIRE INTERVAL 90 DAY
    • 3.3 set password
    • 3.4 密码过期策略
      • 3.4.1 全局参数
      • 3.4.2 指定账户设置

一、访问控制

1.1 连接验证（阶段1）

当我们连接mysql的时候

  • 通过提供的账号和密码来验证身份
  • 验证账号是否锁定

服务首先检查凭据，然后检查账户的锁定状态。任一步骤失败都会拒绝访问，没有问题就接收连接。锁定状态记录在user表account_locked列中。

mysql> select user,account_locked from user;
+--------+----------------+
| user   | account_locked |
+--------+----------------+
| root   | N              |
| multis | N              |
| multis | N              |
| root   | N              |
+--------+----------------+
4 rows in set (0.00 sec)

1.2 允许的连接

下表显示了如何将各种的组合User和Host值在 user表适用于传入的连接

UserHost允许的连接
‘fred’h1.example.net来自h1.example.net的fred
‘’‘h1.example.net’来自h1.example.net的任何用户
‘fred’‘%’任何主机的fred
‘’‘’任何主机的任何用户
‘fred’‘%.example.net’从example.net的任何主机的fred
‘fred’‘x.example.net%’从x.example.net，从x.example.com等的任何主机的fred
‘fred’‘198.51.100.177’从ip198.51.100.177主机的fred
‘fred’‘198.51.100.%’从ip198.51.100C类网中任何主机的fred
‘fred’'198.51.100.0/255.255.255.0与前面相同

1.3 连接的优先级

当有多个匹配项时，需要确认使用哪个匹配项，按照以下的方式解决问题

  • 当服务器将user表读入内存，它会对user表进行排序
  • 客户端尝试连接时，服务器将按排序顺序浏览各行
  • 服务器使用与客户端主机名和用户名匹配的第一行

排序顺序

  • 优先Host进行排序，其次User
  • 有具体文字比%更具有高优先级（127.0.0.1比localhost高）

示例一
要查看其工作原理，假设user 表如下所示：

+-----------+----------+-
| Host      | User     | ...
+-----------+----------+-
| %         | root     | ...
| %         | jeffrey  | ...
| localhost | root     | ...
| localhost |          | ...
+-----------+----------+-

读入到内存时，自动排序后的结果如下所示：

+-----------+----------+-
| Host      | User     | ...
+-----------+----------+-
| localhost | root     | ...
| localhost |          | ...
| %         | jeffrey  | ...
| %         | root     | ...
+-----------+----------+-

挡localhost 上的root用户连接mysql时候，这个时候会匹配| localhost | root |这个条目对应的权限。 其他条目虽然也满足， 但不会匹配它们对应的权限。

示例二

创建如下两个用户，进行连接的优先级的测试

[root@mysql1 ~]# [root@mysql2 ~]# mysql -root-p123456
mysql> create user ''@'192.168.79.140' identified by '123456';
mysql> create user 'test'@'%' identified by '123456';
mysql> select host,user from user;
+----------------+------+
| host           | user |
+----------------+------+
| %              | test |
| 192.168.79.140 |      |
| localhost      | root |
+----------------+------+
3 rows in set (0.00 sec)
mysql> exit;

用刚才我们创建的用户登录mysql

[root@mysql2 ~]# mysql -utest -p123456 -h192.168.79.140

读入到内存时，自动排序后的结果如下所示：

+----------------+------+
| host           | user |
+----------------+------+
| 192.168.79.140 |      |
| localhost      | root |
| %              | test |
+----------------+------+

第一行匹配test的连接是| 192.168.79.140 | |

我们可以通过CURRENT_USER()函数查看，当前用户

mysql> select current_user();
+-----------------+
| current_user()  |
+-----------------+
| @192.168.79.140 |
+-----------------+
1 row in set (0.00 sec)

建议生产不要有不带用户名只有主机的用户

1.4 请求验证（阶段二）

建立连接后，服务器进入访问控制的第二阶段。该连接发出的每个请求，会检查是否具有足够的权限。
这些权限对应着： user（用户），db（数据库），tables_priv（表），columns_priv（列），或procs_priv（存储过程），proxies_priv（代理用户权限） 这些表。

二、用户管理

我们需要知道的，mysql用户名和主机名在一起才是一个有效的用户。

2.1 新增用户

语法和Oracle是相似的，如下

 create user 'user'@'localhost' identified by 'passwd' [ACCOUNT UNLOCK]

ACCOUNT UNLOCK 用户默认是没有锁定的，如果账户所有，我登录账号不会有第二阶段的请求验证。如下创建一个本地可以登陆的multis用户，默认账户为未锁定状态。

mysql> create user 'multis'@'localhost' identified by '123456' account unlock;
Query OK, 0 rows affected (0.00 sec)

2.2 用户修改

2.2.1 使用alter修改用户

查询用户状态

mysql> select user,host,account_locked from user;
+--------+-----------+----------------+
| user   | host      | account_locked |
+--------+-----------+----------------+
| root   | localhost | N              |
| multis | localhost | N              |
+--------+-----------+----------------+
2 rows in set (0.00 sec)

1、使用alter修改用户状态

mysql> alter user 'multis'@'localhost' account lock;
Query OK, 0 rows affected (0.00 sec)mysql> select user,host,account_locked from user;
+--------+-----------+----------------+
| user   | host      | account_locked |
+--------+-----------+----------------+
| root   | localhost | N              |
| multis | localhost | Y              |
+--------+-----------+----------------+
2 rows in set (0.00 sec)

重新打开一个窗口进行mysql登陆，登陆时候报错帐户被锁定

[root@mysql2 ~]# mysql -umultis -p
Enter password: 
ERROR 3118 (HY000): Access denied for user 'multis'@'localhost'. Account is locked.

2、使用alter修改用户密码

mysql> alter user 'test'@'localhost' identified by '654321';
Query OK, 0 rows affected (0.01 sec)

2.2.2 DML语句管理mysql用户

直接更新mysql.user表的信息后，需要flush privileges，将内存和磁盘中的数据保持一致。

1、修改用户名

mysql> update mysql.user set user = 'test' where user = 'multis' and host='localhost';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

2、修改用户ip

mysql> update mysql.user set host = '127.0.0.1' where user = 'test' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)mysql> select user,host,account_locked from user;
+------+-----------+----------------+
| user | host      | account_locked |
+------+-----------+----------------+
| root | localhost | N              |
| test | 127.0.0.1 | N              |
+------+-----------+----------------+
2 rows in set (0.00 sec)

2.3 删除用户

方法一：

mysql> drop user 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec
mysql> create user 'test'@'localhost' identified by '123456' account unlock;
Query OK, 0 rows affected (0.00 sec)

方案二：

mysql> create user 'test'@'localhost' identified by '123456' account unlock;
Query OK, 0 rows affected (0.00 sec)mysql> delete from mysql.user where user = 'test' and host='localhost';
Query OK, 1 row affected (0.00 sec)mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

2.4 查看用户

2.4.1 show create user 显示用户非权限属性

mysql> create user test@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)mysql> show create user 'test'@'localhost' \G;
*************************** 1. row ***************************
CREATE USER for test@localhost: CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
1 row in set (0.00 sec)ERROR: 
No query specified

在这里插入图片描述

2.4.2 select from mysql.user 查看已经创建的用户

mysql> select * from mysql.user where user='test' and host='localhost' \G;
*************************** 1. row ***************************Host: localhostUser: testSelect_priv: NInsert_priv: NUpdate_priv: NDelete_priv: NCreate_priv: NDrop_priv: NReload_priv: NShutdown_priv: NProcess_priv: NFile_priv: NGrant_priv: NReferences_priv: NIndex_priv: NAlter_priv: NShow_db_priv: NSuper_priv: NCreate_tmp_table_priv: NLock_tables_priv: NExecute_priv: NRepl_slave_priv: NRepl_client_priv: NCreate_view_priv: NShow_view_priv: NCreate_routine_priv: NAlter_routine_priv: NCreate_user_priv: NEvent_priv: NTrigger_priv: N
Create_tablespace_priv: Nssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0max_updates: 0max_connections: 0max_user_connections: 0plugin: mysql_native_passwordauthentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9password_expired: Npassword_last_changed: 2023-04-18 22:00:36password_lifetime: NULLaccount_locked: N
1 row in set (0.00 sec)ERROR: 
No query specified

2.4.3 select current_user() 查看当前登录用户

mysql> select current_user;
+----------------+
| current_user   |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

三、密码管理

3.1 修改密码

3.1.1 基本使用 alter

mysql> alter user 'test'@'localhost' identified by '654321';
Query OK, 0 rows affected (0.01 sec)

3.1.2 其他方式 mysqladmin

[root@mysql2 ~]# mysqladmin -utest -p654321 password
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
New password: 
Confirm new password: 
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

3.1.3 其他方式 set password

1 、使用默认加密

mysql> set password for 'test'@'%' ='abcdefg';
Query OK, 0 rows affected (0.01 sec)

2、使用PASSWORD()函数加密（mysql使用该方式进行密码加密）

mysql> set password for 'test'@'%' =password('123456');
Query OK, 0 rows affected (0.01 sec)

3.1.4 修改当前登录用户密码

mysql> alter user current_user() identified by '123456';
Query OK, 0 rows affected (0.00 sec)mysql> alter user user() identified by '123456';
Query OK, 0 rows affected (0.00 sec)

3.2 密码过期设置

在设置密码过期时 identified by '123456' 可以忽略

3.2.1 使密码立即过期 PASSWORD EXPIRE

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
重置密码后才能正常登陆

1、修改用户密码过期

mysql> alter user 'test'@'localhost' identified by '123456' password expire;
Query OK, 0 rows affected (0.00 sec)mysql> show create user 'test'@localhost \G;
*************************** 1. row ***************************
CREATE USER for test@localhost: CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE ACCOUNT UNLOCK
1 row in set (0.00 sec)ERROR: 
No query specified

2、我们可以发现密码策略从PASSWORD EXPIRE DEFAULT变为PASSWORD EXPIRE，登陆mysql，使用sql语句无法使用。

mysql> select current_user();
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

3、使用root用户修改密码

mysql> alter user 'test'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

4、密码修改完成，可以正常登陆mysql

[root@mysql2 ~]# mysql -utest -p123456
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

3.2.2 使密码从不过期 EXPIRE NEVER

1、查询test用户非权限属性

mysql> show create user 'test'@'localhost' \G;
*************************** 1. row ***************************
CREATE USER for test@localhost: CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
1 row in set (0.00 sec)ERROR: 
No query specified

2、修改test用户密码永不过期

mysql> alter user 'test'@'localhost' identified by '123456' password expire;
Query OK, 0 rows affected (0.00 sec)

3、查询test用户非权限属性

mysql> show create user 'test'@'localhost' \G;
*************************** 1. row ***************************
CREATE USER for test@localhost: CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE NEVER ACCOUNT UNLOCK
1 row in set (0.00 sec)ERROR: 
No query specified

3.2.3 默认过期时间 PASSWORD EXPIRE DEFAULT

1、查询test用户非权限属性

mysql> show create user 'test'@'localhost' \G;
*************************** 1. row ***************************
CREATE USER for test@localhost: CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE NEVER ACCOUNT UNLOCK
1 row in set (0.00 sec)ERROR: 
No query specified

2、我们可以看出当前test密码是永不过期，我们修改为默认设置过期时间

mysql> alter user 'test'@'localhost' password expire default;
Query OK, 0 rows affected (0.01 sec)

3、查询test用户非权限属性

mysql> show create user 'test'@'localhost' \G;
*************************** 1. row ***************************
CREATE USER for test@localhost: CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
1 row in set (0.00 sec)ERROR: 
No query specified

3.2.4 指定过期间隔 EXPIRE INTERVAL 90 DAY

1、查询test用户非权限属性

mysql> show create user 'test'@'localhost' \G;
*************************** 1. row ***************************
CREATE USER for test@localhost: CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
1 row in set (0.00 sec)ERROR: 
No query specified

2、我们可以看出当前test密码是默认过期时间，我们修改为指定过期间隔

mysql> alter user 'test'@'localhost' password expire interval 90 day;
Query OK, 0 rows affected (0.00 sec)

3、查询test用户非权限属性

mysql> show create user 'test'@'localhost' \G;
*************************** 1. row ***************************
CREATE USER for test@localhost: CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE INTERVAL 90 DAY ACCOUNT UNLOCK
1 row in set (0.00 sec)ERROR: 
No query specified

3.3 set password

使用Sset password修改密码有两种

1 、使用默认加密

mysql> set password for 'test'@'%' ='abcdefg';
Query OK, 0 rows affected (0.01 sec)

2、使用password()函数加密（mysql采用该方式进行密码的加密）

mysql> set password for 'test'@'%' =password('123456');
Query OK, 0 rows affected (0.01 sec)

3.4 密码过期策略

3.4.1 全局参数

我们可以使用select @@default_password_lifetime来查询密码过期的全局参数，默认值为0


mysql> select @@default_password_lifetime ;
+-----------------------------+
| @@default_password_lifetime |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (0.00 sec)

如果需要修改全局参数，在服务器中my.cnf参数文件中修改或者添加default_password_lifetime参数，重启服务mysql服务生效

[mysqld]
default_password_lifetime=180

default_password_lifetime=180 密码有效期为6个月，最大值65535
default_password_lifetime=0 密码永不过期

1、停止mysql服务

mysql> shutdown;
Query OK, 0 rows affected (0.01 sec)mysql> exit

2、修改参数文件

[root@mysql2 ~]# vim /etc/my.cnf
[root@mysql2 ~]# cat /etc/my.cnf

在这里插入图片描述
3、启动mysql服务

[root@mysql2 ~]# nohup mysqld --defaults-file=/etc/my.cnf &
[root@mysql2 ~]# lsof -i:3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1321 mysql   15u  IPv6  24478      0t0  TCP *:mysql (LISTEN)

4、登陆msyql，查询全局参数

[root@mysql2 ~]# mysql -uroot -p123456
## 忽略mysql登陆部分 ##
mysql> select @@default_password_lifetime;
+-----------------------------+
| @@default_password_lifetime |
+-----------------------------+
|                         180 |
+-----------------------------+
1 row in set (0.00 sec)

3.4.2 指定账户设置

现在要求每90天更改一次密码，我们可以创建用户的时候指定90天后密码过期

mysql> create user 'test1'@'localhost' identified by '123456' password expire interval 90 day;
Query OK, 0 rows affected (0.00 sec)

后续慢慢更新


http://www.ppmy.cn/news/46267.html

相关文章

Python 自然语言处理实用指南：第一、二部分

Python 自然语言处理实用指南：第一、二部分

原文：Hands-on natural language processing with Python 协议：CC BY-NC-SA 4.0 译者：飞龙 本文来自【ApacheCN 深度学习 译文集】，采用译后编辑（MTPE）流程来尽可能提升效率。 不要担心自己的形象&#xff…
阅读更多...
对话ChatGPT：Prompt是普通人“魔法”吗？

对话ChatGPT：Prompt是普通人“魔法”吗？

在ChatGPT、Midjourney、Stable Diffusion等新事物的作用下，不少人或多或少听说过Prompt的概念。 虽然OpenAI掀起的大模型浪潮再度刷新了人们对AI的认知，但现阶段的AI终归还不是强人工智能，大模型里的“知识”存储在一个隐性空间里&#xff0…
阅读更多...
基于互相关性的信号同步

基于互相关性的信号同步

许多测量涉及多个传感器异步采集的数据。如果您要集成信号并以关联式研究它们，您必须同步它们。为此，请使用 xcorr。 例如，假设有一辆汽车经过一座桥。它产生的振动由位于不同位置的三个相同传感器进行测量。信号有不同到达时间。 将三个时…
阅读更多...
Java集合底层原理

Java集合底层原理

目录 ArrayList集合源码创建ArrayList集合扩容机制 LinkedList集合源码添加数据 迭代器源码HashSet底层原理HashMap源码创建HashMap对象添加元素 TreeMap源码基本属性与构造器添加元素 以下源码来自JDK11 ArrayList集合源码 创建ArrayList集合 /* 无参构造,返回一个空数组 参…
阅读更多...
DPDK系列之十三虚拟化virtio源码分析之应用层

DPDK系列之十三虚拟化virtio源码分析之应用层

一、应用 其实不管怎么设计，如何开发，结果都是要展现一个结果，能够为人所用。虽然说virtio的应用场景有不少，但是在DPDK中主要就是网卡。所以，在此处主要是对网卡的抽象的实现，即对上层的应用实现底层的vi…
阅读更多...
【ChatGPT】基于tensorflow2实现transformer（GPT-3.5）

【ChatGPT】基于tensorflow2实现transformer（GPT-3.5）

请记住，您是一位NLP领域的专家和优秀的算法工程师。使用带有 tensorflow2.0 subclass api 的 python 从头开始实现 transformer 模型。 全部内容如下： 构建transformer模型架构和依赖层；生成并预处理一些假样本数据，用于训练上面…
阅读更多...
NOIP模拟赛 T3区间

NOIP模拟赛 T3区间

题目大意 有 n n n个数字，第 i i i个数字为 a i a_i ai​。有 m m m次询问，每次给出 k i k_i ki​个区间，每个区间表示第 l i , j l_{i,j} li,j​到第 r i , j r_{i,j} ri,j​个数字，求这些区间中一共出现了多少种不同的数字。部…
阅读更多...
用Abp实现找回密码和密码强制过期策略

用Abp实现找回密码和密码强制过期策略

文章目录 重置密码找回密码发送验证码校验验证码发送重置密码链接创建接口 密码强制过期策略改写接口 Vue网页端开发重置密码页面忘记密码控件密码过期提示 项目地址 用户找回密码，确切地说是 重置密码，为了保证用户账号安全，原始密码将不再…
阅读更多...
最新文章

Copyright @ 2022~2023