本教程详细讲解 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 位的密码。
创建账号成功会返回:
特别提示:本教程创建的用户名的 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';
返回值:
最后,运行 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';
返回结果:
上表显示 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 教程,可继续拓展学习: