MySQL 权限管理查询手册 - 创建、授权、取消授权、删除、重命名账号

A kitten
蒋川
B 端产品经理,卡拉云联合创始人
阅读时长 1 分钟

MySQL 权限管理查询手册

本教程详细讲解 MySQL 权限管理所涉及到全部问题,包含创建账号、授权账号、取消授权、删除账号及重命名账号。是非常好到 MySQL 权限管理查询手册,可收藏本文,方便在今后的工作中使用。

一. MySQL 权限认证原理

MySQL 对访问账号进行验证分为两步。

第一步验证:服务器会先检测账号账号的 host 值,判断账号是否有权限连接 MySQL 数据库。比如当账号 host 值设置为 localhost 时,此账号仅能从本地服务器连接数据库,无法远程登录服务器,当host设置成222.211.1.77时,此账号仅能 IP 地址为222.211.1.77 的服务器上发起对数据库的远程连接。(如何远程连接 MySQL 可参考卡拉云另一篇教程

第二步验证:当账号通过第一步验证后,即可连接到数据库。此后,MySQL 会检测登录用户键入执行的每一条命令,当命令中有权限命令时,MySQL 会比照此账号的权限列表,判断是否有权限执行这条命令,给出「执行完成」或「拒绝执行」的反馈。

二. 创建、授权、取消授权、删除、修改账号

1.如何创建新用户

首先我们使用 root 账号登录 MySQL Server:

mysql -u root -p

登录成功后,提示符变为 mysql>

接着,在 mysql> 下,我们创建一个新用户,本教程使用kalacloud 作为示例。

CREATE USER 'kalacloud'@'localhost' IDENTIFIED BY 'password';

kalacloud: 可替换为你想创建的用户名,本教程使用kalacloud 作为示例用户名

password:为此新建账号对应的密码。

这里要注意,如果你的 MySQL 密码强度设置为最高,但你设置了简单密码会出现 ERROR 1819 错误。

MySQL 设置的账号设置密码不符合强度会报1819 错误

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

MySQL 强密码由「数字 + 大字母 + 小写字母 + 符号」四个部分组成大于 8 位的密码。

创建账号成功会返回:

CREATE-USER

特别提示:本教程创建的用户名的 host 设置为 localhost ,如果想要远程访问 MySQL 数据库,需要将这里改为发起访问的 服务器 ip 或者使用 % 通配符 ip 地址。

具体可查看《如何远程连接 MySQL 数据库,阿里云腾讯云外网连接教程》这篇教程,教程中会详细讲解通过 TCP/IP 远程访问 MySQL 数据库的几种形式及可能碰到的问题。

将账号 host 设置localhost ,通常是用户使用 SSH 连接服务器后本地登录 MySQL 数据库,也是为了安全考虑,关于 MySQL 设置安全,我们将在接下来的 MySQL 教程中讲解,更多 MySQL 教程可访问我们的网站卡拉云查看。

刚刚我们已经成功创建了kalacloud 账号,但此账号对数据库没有任何访问权限,接着你需要给这个账号授权,它才能增删改查对应对数据库。

kalacloud 账号全部权限

GRANT ALL PRIVILEGES ON * . * TO 'kalacloud'@'localhost';

返回值:

GRANT-ALL-PRIVILEGES

最后,运行 FLUSH PRIVILEGES 命令,刷新 MySQL 的系统权限相关表,更新缓存。

FLUSH PRIVILEGES;

此时,这个账号就拥有所有数据库的所有增删改查权限。但在实际工作中,我们出于安全考虑,很少这样配置账号权限。接着我们讲解如何给账号分配特定权限。

2.授予账号特定权限

以下是常见的可授权账号使用的权限

  • ALL PRIVILEGES :允许 MySQL 用户完全访问指定的数据库(或者如果没有选择数据库,则可以跨系统进行全局访问)
  • CREATE:允许他们创建新表或数据库
  • DROP:允许他们删除表或数据库
  • DELETE:允许他们从表中删除行
  • INSERT:允许他们向表中插入行
  • SELECT:允许他们使用 SELECT 命令来读取数据库
  • UPDATE:允许他们更新表行
  • GRANT OPTION:允许他们授予或删除其他用户的权限

向特定用户授权特定数据库和表的权限,代码模版:

GRANT type_of_permission ON database_name.table_name TO 'kalacloud'@'localhost';
  • 'kalacloud'@'localhost':需要被授权的账号
  • type_of_permission :这里写权限类型,权限之间使用, 分隔。
  • database_name.table_name:指定特定的数据库(database_name)和其中的表(table_name)的权限范围。

如果你想使账号可访问任何数据库或任何表,可用* 代替。

举个例子:以下代码是给账号授权:创建(CREATE)、修改(ALTER)、删除(DROP) 数据库、表、用户,任意表的插入(INSERT)、更新(UPDATE)、删除(DELETE)操作权限。可以使用 SELECT 查询数据,使用 REFERENCES 建立外键关系权限,以及使用 RELOAD 权限执行 FLUSH 操作的权限。我们使用*.* 表示权限范围,即授权此账号可以访问 MySQL Server 中的所有数据库和所有表。

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'kalacloud'@'localhost' WITH GRANT OPTION;

记得每次更改权限后,要执行 FLUSH PRIVILEGES; 刷新 MySQL 的系统权限相关表,更新缓存。

MySQL 账号权限查询表

权限权限级别说明
CREATE数据库、表或索引创建数据库、表或索引权限
DROP数据库或表删除数据库或表权限
GRANT OPTION数据库、表或保存的程序赋予权限选项
REFERENCES数据库或表
ALTER更改表,比如添加字段、索引等
DELETE删除数据权限
INDEX索引权限
INSERT插入权限
SELECT查询权限
UPDATE更新权限
CREATE VIEW视图创建视图权限
SHOW VIEW视图查看视图权限
ALTER ROUTINE存储过程更改存储过程权限
CREATE ROUTINE存储过程创建存储过程权限
EXECUTE存储过程执行存储过程权限
FILE访问服务器中的文件文件访问权限
CREATE TEMPORARY TABLES服务器管理创建临时表权限
LOCK TABLES服务器管理锁表权限
CREATE USER服务器管理创建用户权限
PROCESS服务器管理查看进程权限
RELOAD服务器管理执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限
REPLICATION CLIENT服务器管理复制权限
REPLICATION SLAVE服务器管理复制权限
SHOW DATABASES服务器管理查看数据库权限
SHUTDOWN服务器管理关闭数据库权限
SUPER服务器管理执行kill线程权限

3.取消账号特定权限

如果要撤销账号某些权限,取消的代码结构与授权几乎相同:

REVOKE type_of_permission ON database_name.table_name FROM 'kalacloud'@'localhost';

特别注意:在撤销权限时,语法要用 FROM ,而不是授权时使用的 TO,这里

请注意,在撤销权限时,语法要求您使用FROM,而不是TO我们在授予权限时使用的。否则 ERROR 1064 报错。

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'to 'kalacloud'@'localhost'' at line 1

您可以通过运行以下命令来查看用户的当前权限:

SHOW GRANTS FOR 'kalacloud'@'localhost';

返回结果:

03-SHOW-GRANTS

上表显示 kalacloud@localhost 包含对所有数据库中所有表的INSERT, CREATE, DROP, ALTER权限。

4.对账号重命名及修改账号host

RENAME USER 'kalacloud'@'localhost' to 'kalacloud-new'@'%';

可使用此代码对账号重命名,且、或修改账号 host

5.删除账号

像删除数据库一样,删除账号也可以使用 DROP,我们可以使用 DROP 删除指定账号:

DROP USER 'kalacloud'@'localhost';

三. MySQL 数据库账号安全原则

1.严格初始化配置:初始化安装数据库时,将无密码用户删除,设置强制「强密码」,使创建账号使,必须给账号配置强密码。

2.最小授权:只授权满足此账号用途的最小权限,防止权限边界不清,导致的滥用。

3.限制 host 范围:限制账号登录主机地址,仅能从指定主机登录。

4.周期收回账号权限:周期性的检查 MySQL Server 内账号及对应权限,删除不在使用的账号,回收不使用的权限。

四. 教程总结

本教程详细讲解了如何创建、授权、修改、删除账号权限等操作,从本教程开始,你已经掌握了 MySQL 数据库的基础操作,接下来,可以跟随我们的数据库教程更深入的学习。如果你还有什么疑问,欢迎一起讨论。我的微信 HiJiangChuan。更多数据库相关教程可访问 卡拉云 查看。

卡拉云联合创始人蒋川的微信

有关 MySQL 教程,可继续拓展学习:

© 2021 卡拉云 Built with ❤️ in Beijing

京ICP备15049164号-9