MySQL数据库 (十七) MySQL用户管理，创建修改删除用户，用户的权限管理

目录一、用户管理用户创建用户删除用户修改用户密码二、数据库的权限管理给用户授权回收权限三、总结我们正式进入 MySQL 用户管理。分为两部分 1. 删改查用户 2.对用户做权限管理。一、用户管理先从最核心的问题讲起为什么不能所有人都用 root 账户用户管理到底解决了什么问题首先我们先理解 root 账户和普通用户账户的本质区别。root 账户是 MySQL 的超级管理员账户拥有数据库的所有权限能查看、修改、删除所有数据库、表和数据也能创建用户、分配权限、修改全局配置相当于数据库的 “最高权限管理员”。而普通用户账户是我们根据业务需要创建的、权限受限的账户它只能拥有管理员分配的特定权限比如只能访问某个数据库、只能查询不能修改、只能操作特定表等相当于数据库里的 “普通员工”只能在自己的权限范围内工作。接着我们来看只用 root 账户会带来的安全隐患。如果所有开发人员、业务人员都用 root 账户登录数据库每个人都拥有最高权限风险会非常大。比如开发人员不小心执行了drop database命令会直接删除整个数据库或者业务人员误操作修改了核心数据也无法限制和追溯。更严重的是如果 root 账户被泄露攻击者就能完全控制整个数据库窃取或篡改所有数据造成不可逆的损失。就像公司里所有人都用老板的账号办公谁都能修改财务数据、删除客户信息风险可想而知。这时候MySQL 的用户管理就派上用场了它的核心目标就是 “权限最小化”让每个用户只拥有完成工作所必需的权限避免权限过度集中。比如图里的例子张三只能操作 mytest 这个数据库李四只能操作 msg 这个数据库。管理员会为他们分别创建普通用户然后只给他们分配对应数据库的权限。这样一来张三就算误操作也只会影响mytest库不会波及其他业务数据李四的操作也只会限制在 msg 库范围内从根源上降低了误操作和数据泄露的风险。同时用户管理还能实现权限的精细化控制。除了限制数据库访问还可以给用户分配更细粒度的权限比如只能查询数据不能修改或者只能修改特定表不能删除甚至可以限制用户的登录 IP 地址只允许从公司内网登录。这种精细化的权限划分能像 “数据防火墙” 一样把不同业务、不同人员的数据隔离开避免交叉影响也方便后续的操作审计和责任追溯。简单来说root 账户是 “万能钥匙”能打开所有门但风险极高而普通用户账户是 “专用钥匙”只能打开自己权限范围内的门既满足了业务需求又保证了数据安全。用户管理的本质就是通过创建不同权限的普通用户替代直接使用 root 账户实现权限的隔离、限制和管控这也是数据库安全管理的基础。用户首先要明确一个基础事实MySQL 里所有登录账户、账户对应的权限、登录限制、加密密码全部统一存放在系统自带的 mysql 数据库中的 user 表里面。这张表是 MySQL 管理所有用户的底层数据表我们执行创建用户、修改密码、分配权限的操作底层本质都是对这张 user 表的数据做增、删、改操作。我们可以通过 select * from user\G; 这条语句完整查看这张表里所有字段与所有已存在的数据库用户记录也可以精简查询只查看关键的三列用户名、登录主机、加密密码对应语句是 select USER, HOST, authentication_string from user;。接下来我们拆分图中标注出来的四个核心字段逐个讲清楚各自的作用这是看懂 user 表的关键 :第一个字段是 HOST它用来限制当前用户能够从哪一台主机发起登录请求。如果字段的值是localhost就代表这个账户只允许在数据库本机登录外部其他电脑、服务器都无法使用这个账号远程连接数据库如果配置成百分号 %就代表允许任意 IP 地址远程登录实际生产环境会根据业务 IP 做精准限制避免全开放带来安全风险。第二个核心字段是 USER也就是我们日常所说的数据库用户名它需要和 HOST 字段搭配才能组成一个完整、唯一的登录账户。举个例子rootlocalhost和 root% 是两条完全独立的用户记录权限、密码互不干扰不能单纯只靠用户名区分账户主机地址是账户身份不可缺少的一部分。第三个字段是 authentication_string这里存放的不是我们手动输入的明文密码而是经过 MySQL 内置加密算法处理后的加密字符串。数据库不会保存原始明文密码每次用户登录输入密码时MySQL 会把输入的密码加密和这一列存储的字符串做比对匹配成功才允许登录这种设计可以防止数据库泄露后别人直接获取所有用户的原始密码。图里还附带了配套字段 password_last_changed用来记录上一次修改密码的时间方便管理员管控密码轮换规范。第四类字段是所有以 _priv 结尾的字段比如 Select_priv、Insert_priv、Drop_priv、Create_priv 等等每一个字段的值只有 Y 或者 N 两种。Y 代表当前用户拥有这一项操作权限N 代表没有对应权限。root 用户对应的这一行记录里几乎所有权限字段都是 Y这也对应了我们之前讲的 root 是超级管理员拥有数据库全部操作权限而 mysql.session、mysql.sys 这类系统内置用户全部权限字段都是 N仅用于 MySQL 内部程序运行不会给业务人员使用。每一类权限分别对应查询、新增、删除、建表、删库、创建用户、修改视图等不同数据库操作。然后我们讲底层创建用户的逻辑。从理论层面来说既然用户信息都存在 user 表中我们完全可以直接使用 insert 语句往 user 表插入一条完整记录手动填写 HOST、USER、加密密码、所有权限字段的值以此新增用户。但这个方式实际工作中几乎没人使用原因是 user 表内部字段数量极多需要手动填写几十项权限标识、配套的安全参数书写复杂、极易出错手动插入很容易出现权限缺失、账户锁定、密码加密格式不匹配等隐性问题维护成本很高。基于直接 insert 操作的缺陷MySQL 提供了专门的标准化语句 create user这条语句会自动帮我们填充 user 表中所有配套字段自动完成密码加密、默认权限初始化、安全参数填充不需要人工填写大量冗余字段操作简洁、不容易出现漏洞也是生产环境、日常开发中创建用户的标准方式。简单总结user 表是存储用户数据的底层容器我们日常使用的用户管理命令都是封装好的快捷操作底层最终都会作用于这张系统表。创建用户下面我们就用专门的语法来插入新用户:语法:首先我们先掌握标准创建用户的专用语法。MySQL 不推荐直接往 mysql.user 表 insert 插入数据而是提供 create user 专用语句完整格式为create user 用户名登陆主机/ip identified by 密码;。这条语句执行之后MySQL 会自动完成所有底层操作自动把用户名、登录主机、加密后的密码、默认权限参数全部写入 mysql 库的 user 表不用我们手动填写大量字段操作简单且不容易出现配置漏洞。语句里的 identified by 后面填写明文密码MySQL 会自动把密码加密存入 authentication_string 字段不会保存明文保证账号安全。举例:首先我们创建本地登录用户 whb。执行语句 create user whblocalhost identified by 123456;执行成功后我们执行查询语句 select USER, HOST, authentication_string from user;可以看到 user 表里新增了一行记录USER 字段为 whbHOST 字段是 localhostauthentication_string 字段是一串加密后的字符串原始明文 123456 不会直接存储在库中。这一步就验证了create user 本质就是封装好的、向 user 表新增用户记录的操作。创建用户之后规范操作需要执行flush privileges; 这条刷新权限语句。这条命令的作用是让 MySQL 立刻重新读取 mysql 库下 user 表、db 表的权限数据把新创建的用户、分配的权限加载到内存中生效。如果不执行刷新语句部分 MySQL 版本会出现新建账号登录失败、权限不生效的问题所以创建、修改、删除用户后执行 flush privileges 是标准规范操作。刷新完成后执行 quit 退出当前 root 会话我们就可以使用新账号登录。​本地登录的测试流程非常顺畅在服务器本机终端输入 mysql -uwhb -p回车后输入设置的密码 123456就能正常进入 mysql 交互命令行代表本地登录完全生效。原因是我们创建用户时指定的 HOST 参数是 localhostlocalhost 代表仅允许数据库本机发起连接服务器本地终端自然符合登录条件校验可以通过。然后我们分析远程登录报错的问题。截图里使用 Windows 电脑远程连接数据库服务器执行连接命令后抛出报错 ERROR 1130提示对应 IP 不允许连接这个 MySQL 服务。我们回头查询 user 表可以看到whb 这条用户记录的 HOST 字段是 localhost这个配置只放行本机访问外部其他 IP、外网设备全部会被拦截这就是远程登录失败的根本原因。为什么?这里需要理清 HOST 参数的核心逻辑用户名和 HOST 主机地址组合起来才是完整独立账号。localhost 仅本机可用如果想要支持任意设备远程登录创建用户时 HOST 要写成百分号%如果仅允许指定办公服务器远程访问可以把 HOST 写死成对应固定 IP比如 whb120.78.126.148精准限制登录来源 IP提升数据库安全。如果已经创建了localhost的本地账号又需要远程访问要么重新创建一条 HOST 为 % 或者指定 IP 的同名用户要么修改现有用户的 HOST 字段放开远程登录权限。简单总结整个创建用户的完整流程使用 create user 语法定义用户名、登录主机、登录密码执行语句写入 user 表刷新权限让配置生效登录权限完全由 HOST 字段控制localhost只支持本机登录无法跨网络远程连接想要远程访问必须修改或者创建 HOST 为 %/ 指定 IP 的用户记录。删除用户所以接下来我们先删除这个用户:​删除语法:首先讲解删除 MySQL 用户的标准语法与容易踩的报错坑。删除用户专用语句是 drop user 用户名主机名;这里有一个核心硬性规则用户名必须搭配对应的 HOST 主机地址二者缺一不可不能只写用户名。就像截图里第一次执行 drop user whb; 直接抛出报错 ERROR 1396这条语句等价于尝试删除 whb% 这个账号但我们之前创建的账号是 whblocalhost数据库里不存在 whb% 这条记录删除操作自然会失败。只有完整写全账号的两部分执行 drop user whblocalhost;MySQL 才能精准匹配 user 表里对应的那条用户记录执行成功并删除这条数据。删除用户操作底层会同步清理 mysql.user 表、权限相关配套表里的该用户记录删除完成后建议执行flush privileges;刷新权限让变更立刻生效。那下面我们就新建一个可以跨网络远程登陆的用户怎么创建呢?接下来讲解创建支持远程跨网络登录的用户核心改动就在于 HOST 参数的设置。我们之前本地账号 HOST 写的是 localhost现在想要外网、其他服务器访问就把 HOST 替换成百分号%完整创建语句为 create user whb% identified by 123321;。语句执行完成后我们查询 user 表可以直观看到新增的 whb 记录对应的 HOST 字段值是 %和之前localhost的本地账号形成区分两条记录是完全独立的两套账号密码、登录限制互不干扰。这里必须说明百分号%的含义与生产环境的安全禁忌。HOST 字段写 %代表这个 whb 账号允许全球任意 IP 地址发起连接不管是办公电脑、外网服务器、未知陌生主机只要密码正确就能登录数据库实验场景可以用来验证远程连接功能但正式线上业务绝对不推荐使用 %。开放全 IP 访问会极大提升数据库被暴力破解、恶意入侵的风险正规生产环境只会把 HOST 设置为业务固定服务器 IP例如 whb120.78.126.148仅放行业务指定机器缩小登录来源范围。然后我们结合截图里的远程连接实操过程解释两次连接结果的差异。第一次远程连接时数据库里只有 HOST 为 localhost的 whb 账号Windows 客户端的 IP 不属于本机 localhostMySQL 校验 HOST 匹配失败直接抛出 1130 报错拒绝连接在创建好 HOST 为 % 的 whb 账号之后再次执行远程连接命令 mysql -h120.78.126.148 -P 8080 -uwhb -p输入正确密码数据库校验 HOST 字段 % 匹配任意来访 IP校验通过顺利进入 MySQL 交互终端。登录成功后我们执行show databases;查看库列表也能正常切换 test_db 数据库证明远程读写链路完全打通Windows 客户端可以正常操作 Linux 服务器上的 MySQL 服务。结论:第一MySQL 本身原生支持远程网络连接但前提条件是数据库内部必须存在 HOST 匹配来访 IP 的用户账号没有对应账号就会直接拦截远程访问第二从服务器安全角度来说不要轻易把 MySQL 端口直接暴露在公网环境也不要创建 HOST 为 % 的全开放账号线上业务需要搭配固定 IP 白名单、防火墙端口限制、复杂高强度密码多层防护图中的 % 远程账号仅用于本地实验学习不能直接照搬上线使用。修改用户密码我们分段把修改用户密码的两种方式分别进行说明:首先先讲修改密码的底层核心特点。MySQL 会自动对明文密码进行加密存储不会保存原始密码文本同时只要语句里包含密码相关操作这条 SQL 都不支持事务回滚一旦执行修改密码会直接更新无法通过 rollback 撤销修改前一定要确认新密码无误。语法:自己改自己的密码:第一种方式是普通用户登录后修改自己的密码语法为set passwordpassword(新的密码);。这条语句不需要填写用户名和主机当前登录的是谁就修改谁对应的密码。底层等价于手动执行update 语句更新 mysql.user 表的 authentication_string 字段。但直接写 update 修改系统 user 表不推荐日常使用容易遗漏 HOST 条件导致修改错同名不同主机的账号仅用来理解底层原理即可。root用户修改指定用户的密码:第二种方式是超级管理员 root 修改其他指定用户的密码这也是生产环境最常用的操作语法格式为set password for 用户名主机名password(新的密码);。这里必须完整带上用户名和对应的 HOST 主机标识精准定位要修改的账号避免混淆本地账号和远程账号。下面我们以第二种方法来设置密码:执行 set password for whb%password(1234abcd);执行完成后查询 user 表能看到 whb% 这条记录的 authentication_string 加密字符串发生变化代表密码修改成功。这里要重点区分同名但 HOST 不同的账号比如 whblocalhost和 whb% 是两条独立记录如果只写 whb 不写主机语句会执行失败必须完整匹配创建用户时填写的 HOST 值才能精准修改对应账号的密码。修改完密码之后有一个规范操作需要补充执行 flush privileges; 刷新权限让新的密码配置立刻加载到内存生效避免出现修改完成后旧密码还能登录、新密码校验失败的异常情况。二、数据库的权限管理首先讲权限管理的前置操作:我们需要先用 root 超级管理员账号创建待分配权限的普通用户执行 create user zhangsan% identified by 123456;这条语句创建了一个允许任意 IP 远程登录、密码为 123456 的 zhangsan 账号。执行创建语句之后执行 flush privileges; 刷新权限让这个新用户的账户信息加载生效。这里有一个核心知识点刚创建完成的空白用户默认不具备任何数据库操作权限哪怕登录成功也无法查询、新增、修改任何库表数据必须由 root 管理员主动分配权限之后才能执行对应操作这也是权限管理存在的意义。下面我们就来认识一下数据库的权限只有了解了相关权限后才能正确的对用户进行控制接下来我们认识表格里全部数据库权限表格分为三列分别是权限关键字、user 表对应存储字段、权限生效的上下文范围我们拆分常用核心权限分类说明。 第一类是针对数据表最基础的增删改查权限也是开发人员最常用的权限。SELECT 对应查询表数据INSERT 对应插入新数据UPDATE 对应修改已有数据DELETE 对应删除表内数据这四类权限的生效范围都仅限制在数据表层级分配后用户只能对指定表执行对应操作。除此之外INDEX 权限允许用户给表创建索引ALTER 权限允许修改表结构CREATE 可以新建数据表DROP 可以删除数据表CREATE VIEW、SHOW VIEW 则分别对应创建视图、查看视图的操作权限。第二类是存储过程相关权限ALTER ROUTINE、CREATE ROUTINE 用来创建和修改存储过程EXECUTE 用来执行存储过程生效范围为函数、存储程序。 第三类是服务器全局高危管理权限这类权限分配要极度谨慎仅允许给 DBA 管理员账号。CREATE USER 可以新建、删除、修改其他数据库用户SUPER 是最高全局权限能执行各类高危系统操作SHUTDOWN 可以直接关闭 MySQL 数据库服务RELOAD 权限允许刷新权限、重载配置FILE 权限可以读写服务器本地磁盘文件线上环境基本不会开放给普通业务账号存在极高数据泄露风险。 表格最后一列的上下文也很好区分一部分权限仅作用在单张数据表一部分作用在整个数据库还有一部分直接作用于整个 MySQL 服务属于全局管控权限。给用户授权刚创建的用户没有任何权限。需要给用户授权。语法:给用户授权的标准语法格式为grant 权限列表 on 库.对象名 to 用户名登录位置 [identified by 密码];。首先是权限列表部分如果只需要单一权限直接写单个关键字例如grant select on ...如果需要同时分配多个权限使用逗号隔开例如grant select,delete,create on ...如果需要授予指定范围内全部操作权限可以写grant all privileges on ...代表该用户能在对应库对象上执行所有操作。然后是 on 后面的库。对象匹配规则一共有三种常用写法。第一种*.*代表服务器内部所有数据库、所有表、视图、存储过程等全部对象一般只给 root 超级用户第二种库名.*代表某一个指定数据库内部的全部数据表、视图比如 test_db.*代表仅管控 test_db 库下所有对象是业务账号最常用的匹配格式第三种可以精确到单张表例如 test_db.emp只给用户开放 emp 这一张表的操作权限权限粒度最细。最后是末尾的 identified by 密码可选参数这个参数有两种执行逻辑。如果 to 后面的用户名 主机账号已经存在执行 grant 授权时附带该参数会在分配权限的同时直接修改这个用户的登录密码如果该账号不存在grant 语句会直接新建这个用户并同步分配权限、设置登录密码相当于把 create user 和 grant 两条语句合并成一条执行。下面我们举例:首先先看授权前的测试场景我们用 root 管理员提前创建了 rootDB 数据库在库里新建 user 表并插入两条测试数据。之后切换到 zhangsan% 这个普通用户登录执行 show databases; 可以看到 zhangsan 能看到系统自带库但当执行 use rootDB; 切换数据库时直接抛出报错 ERROR 1044提示访问被拒绝。 这个报错的核心原因我们之前提到过刚创建出来的普通用户默认只有 usage 权限没有任何库表操作权限zhangsan 没有被分配 rootDB 库的访问权限自然无法进入这个数据库、更不能读写里面的数据这也印证了数据库 “默认零权限” 的安全设计。下面我们添加权限:接下来执行管理员授权操作root 账号执行授权语句grant all on rootDB.user to zhangsan%;。我们拆分这条语句的含义grant all 代表授予全部操作权限增删改查、建索引、修改表结构等操作全部放开on rootDB.user 代表权限范围做了精准限制只针对 rootDB 数据库下单独一张 user 表生效rootDB 里其他表、其他数据库的全部对象zhangsan 依旧没有任何操作权限to zhangsan%精准指定了要授权的完整账号区分开不同主机的同名用户。语句执行成功后权限规则会写入 mysql 系统库的权限表。授权完成后我们重新以 zhangsan 用户执行验证操作。先执行 show databases;列表里能清晰看到 rootDB 数据库已经出现代表权限生效再执行use rootDB;切换数据库不再抛出权限不足的报错可以正常进入库中同时能够查询、修改 user 表内的数据。这里要区分清楚权限边界我们只给了 rootDB.user 单表的全部权限如果 rootDB 中还有其他数据表zhangsan 访问其他表依然会提示权限不足这条授权语句不会放开整个 rootDB 库仅限制在指定单张表。然后我们学习查看用户已有权限的专用语句 show grants for zhangsan%;这条语句可以完整打印出该用户身上所有已分配的权限规则方便管理员核对权限、排查权限缺失问题。 从图里的查询结果能看到两行权限记录第一行 GRANT USAGE ON *.* TO zhangsan% 是所有新建用户自带的基础权限仅允许登录数据库不具备任何数据操作能力第二行就是我们刚刚手动授予的权限 GRANT ALL PRIVILEGES ON rootDB.user TO zhangsan%明确标注了权限范围是 rootDB 库的 user 表权限等级为全部操作权限直观验证授权操作已经成功写入系统。最后梳理这套流程的完整逻辑新建普通用户默认无任何业务操作权限访问未授权库表会直接拦截报错管理员通过 grant 语句能够精准控制权限生效的库、表范围做到最小权限分配授权后可以使用 show grants 语句查看账号全部权限用来校验配置是否符合预期从创建用户、报错验证、授权、权限核验形成一套完整的数据库权限管控流程。回收权限首先先掌握回收权限的标准语法和授权 grant 语句是一一对应的反向操作完整格式为revoke 权限列表 on 库.对象名 from 用户名登陆位置;。grant 是给用户增加操作权限revoke 就是从用户身上移除已经分配好的权限权限范围、账号标识的写法规则和授权完全一致必须精准写清库、表、完整账号名否则无法生效。比如现在我们想回收 insert 权限:收回 zhangsan 的 insert 插入权限我们执行语句revoke insert on rootDB.user from zhangsan%;这条语句的作用是只移除 zhangsan 在 rootDB 库 user 表的插入数据权限其余 select 查询、delete 删除、update 修改等权限依然保留。执行完成后切换 zhangsan 账号做测试执行 select * from user;可以正常查出表里三条数据执行 delete from user where id1;也能正常删除数据但是执行插入语句insert into user values (4,里飞);时数据库抛出 ERROR 1142 报错提示 INSERT 操作被拒绝。这个现象直观验证了回收效果查询、删除权限还在唯独插入权限被移除无法执行新增数据操作。现在我们回收指定范围内用户的全部权限。我们执行语句revoke all on rootDB.user from zhangsan%;这里写 revoke all 就代表一次性收回该用户在 rootDB.user 这张表上所有曾经授予的权限不再区分查询、删除、插入等细分权限。回收完成后执行 show grants for zhangsan%; 查看账号权限列表页面里只剩下系统自带的 GRANT USAGE ON *.* TO zhangsan% 这一行记录之前授予 rootDB.user 的所有权限记录完全消失。此时再用 zhangsan 执行select * from user;查询表会直接提示 SELECT 权限不足show databases 列表里也看不到 rootDB 这个数据库整张表的所有操作都会被数据库拦截。我们还可以拓展另一种回收写法如果想要回收整个 rootDB 数据库下所有表的某类权限可以写revoke select on rootDB.* from zhangsan%;星号代表库内全部数据表这条语句会一次性移除 zhangsan 在 rootDB 所有表的查询权限不管是 user 表还是其他数据表都无法执行 select 查询。当把一个用户的所有业务权限全部回收完毕后该账号仅保留登录数据库的 usage 基础权限不能对任何库表做增删改查、建表删表等操作和刚创建完成的空白用户状态完全一致。最后梳理授权与回收权限的配套逻辑grant 用来新增权限revoke 用来撤销已分配权限二者语法结构对称权限作用域规则完全相同回收权限不会删除用户本身只会拿走对应的操作权限账号依然存在可以正常登录数据库只有执行 drop user 语句才能彻底删除账号本身revoke 仅负责权限管控不操作账户数据。三、总结本文系统讲解了MySQL用户管理和权限控制的核心机制。首先阐述了root账户与普通账户的本质区别root拥有全部权限而普通账户权限受限遵循最小权限原则。主要内容包括用户管理用户信息存储在mysql.user表中创建用户使用CREATE USER语句需指定用户名、主机和密码主机限制包括localhost(仅本地)、%(任意主机)和特定IP密码修改通过SET PASSWORD实现权限管理权限分为表级、库级和全局权限使用GRANT授权REVOKE回收权限权限范围可精确到单表或整个数据库新用户默认只有登录权限(USAGE)安全实践生产环境避免使用%开放所有主机权限分配应遵循最小化原则操作后执行FLUSH PRIVILEGES使变更生效通过合理的用户和权限管理可以有效保障数据库安全防止误操作和数据泄露。谢谢大家的观看!