扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >Mysql用户权限分配详解
  • 132

0
分享到

Mysql用户权限分配详解

mysql数据库java 2023-09-14 10:09:27 132人浏览 八月长安
摘要

文章目录 MySQL 权限介绍一、Mysql权限级别分析（1）全局级别（1.1） USER表的组成结构（1.1.1） 用户列（1.1.2） 权限列（1.1.3） 安全列（1.1.4） 资源控制

文章目录

Mysql 权限介绍

相信对于绝大多数开发人员都接触过数据库，当今最常用的关系型数据库有Mysql、Oracle、PostgreSql、SQLserver，本文重点讲解Mysql用户权限的分配、管理。用户权限在实际开发中有什么用途呢？总结作用如下：

一、可以根据登录用户限制用户访问资源(库、表)
二、可以根据登录用户限制用户的操作权限(能对哪些库、表执行增删改查操作)
三、可以指定用户登录IP或者域名
四、可以限制用户权限分配

一、mysql权限级别分析

Mysql权限级别分为了五个层级，并且每个级别的权限都对应着不同的表，这些表都存在于mysql库下，在Mysql官方文档中有详细介绍权限的概念MYSQL官方中文文档，以下将简单讲述这五个级别的作用和范围。

（1）全局级别

全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON *.*和REVOKE ALL ON *.*用于授予和撤销全局权限。

在mysql数据库里，存在一张user表，mysql中所有的用户都是存放在user表中，user表的字段可以分为四大类(用户列、权限列、安全例、资源控制列)
在这里插入图片描述

（1.1） USER表的组成结构

通过查看user表结构可以看出，user表保存了用户的登录信息和权限，这个权限是全局性的，针对的是全部数据库资源，换句话说，只要user表里的某个权限是Y(Y表示YES,N表NO)，那么这个权限可以作用于全部数据库资源。
在这里插入图片描述

（1.1.1） 用户列

user表中的用户列包括字段Host、User、Password:

Host: 登录的主机名，表示允许用户从哪台机器登录接连到Mysql服务器。可以使用IP地址或者域名，%表示允许从任何主机连接到Mysql服务器。
User:登录的用户名
PassWord: 用户登录数据库的密码，当该字段为空时，表示该用户账号登录不需要密码

（1.1.2） 权限列

user表中的权限列是以_priv结尾的字段，这些字段默认为N，N表示该权限不能用到所有数据库上，Y则与N的效果相反。以下是权限列各个字段的说明:

列名说明
Select_priv是否拥有SELECT权限命令查询数据
Insert_priv是否拥有INSERT权限命令插入数据
Update_priv是否拥有UPDATE权限命令修改数据
Delete_priv是否拥有DELETE权限命令删除数据
Create_priv是否拥有权限创建新的数据库和表
Drop_priv是否拥有权限删除现有数据库和表
Reload_priv是否拥有权限执行刷新和重新加载MySQL所用各种内部缓存的特定命令，包括日志、权限、主机、查询和表
Shutdown_priv是否拥有权限关闭MySQL服务器
Process_priv是否拥有权限通过SHOW PROCESSLIST命令查看其他用户的进程
File_priv是否拥有权限执行SELECT INTO OUTFILE和LOAD DATA INFILE命令
Grant_priv是否拥有权限将自己已授权的权限授权给其他用户
Index_priv是否拥有权限创建和删除表索引
Alter_priv是否拥有权限重命名和修改表结构
Show_db_priv是否拥有权限查看服务器上所有数据库的名字，包括用户拥有足够访问权限的数据库
Super_priv是否拥有权限执行某些强大的管理功能，例如通过KILL命令删除用户进程，使用SET GLOBAL修改全局MySQL变量，执行关于复制和日志的各种命令
Create_tmp_table_priv是否拥有权限创建临时表
Lock_tables_priv是否拥有权限使用LOCK TABLES命令阻止对表的访问/修改
Repl_slave_priv是否拥有权限读取用于维护复制数据库环境的二进制日志文件。此用户位于主系统中，有利于主机和客户机之间的通信
Create_view_priv是否拥有权限创建视图
Show_view_priv是否拥有权限查看视图或了解视图如何执行
Create_routine_priv是否拥有权限更改或放弃存储过程和函数
Alter_routine_priv是否拥有权限修改或删除存储函数及函数
Create_user_priv是否拥有权限执行CREATE USER命令，这个命令用于创建新的MySQL账户
Event_priv是否拥有权限创建、修改和删除事件
Trigger_priv是否拥有权限创建和删除触发器

（1.1.3） 安全列

user表的安全列有4个字段：ssl_type、ssl_cipher、x509_issuer、x509_subject

ssl用于加密；
x509标准可以用来标识用户。普通的发行版都没有加密功能。可以使用SHOW VARIABLES LIKE 'have_openssl’语句来查看是否具有ssl功能。如果取值为DISABLED，那么则没有ssl加密功能。

（1.1.4） 资源控制列

user表的4个资源控制列是：max_questions、max_updates、max_connections、max_user_connections

max_questions：每小时可以允许执行多少次查询；
max_updates：每小时可以允许执行多少次更新；
max_connections：每小时可以建立多少连接；
max_user_connections：单个用户可以同时具有的连接数。

默认值为0，表示无限制。

（2）数据库层级

数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。GRANT ALL ON
db_name.*和REVOKE ALL ON db_name.*用于授予和撤销数据库权限。

刚刚我们已经讨论了全局级别的权限配置，对user表进行了解析，但是user表上的权限都是针对与所有数据库的，如果我们想将权限力度划分的更细，如果我想让某个数据库用户只能查看某个数据库里的表数据，那么此时就需要用到数据库层级的权限控制，主要分析mysql库里的db表。
在这里插入图片描述
db表里的权限列与user表里的含义差不多一致，只是db表的权限只是针对与某个数据库，而非全局数据库。其中DB字段存储的是数据库名称。

（3）表层级

表权限适用于一个给定表中的所有列。这些权限存储在mysql.talbes_priv表中。GRANT ALL ON
db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限

如果你清楚了全局级别的权限和数据库层的权限，那么表层级的权限就相对好理解了，表层级的权限是用于控制用户对某个数据库里的某个表是否有权限进行操作。比如你想指定某个用户只能操作指定数据库里的某张表，此时就需要用到表层级的权限来控制。
tables_priv表的字段相比较与user和db表要少的多，因为它控制的权限更细，它的结构如下：
在这里插入图片描述

（1.3） tables_priv表的组成结构

列名说明
Host用户登录的主机，可以是IP或者域名
Db数据库名称
User登录用户名
Talbe_name表名称
Grantor权限的设置者
Table_priv对表进行操作的权限，对表的操作权限有：Select、Insert、Update、Delete、Create、Drop、Grant、Index、Alter、Create View、Show View以及Trigger
Column_priv对表中的数据列进行操作的权限 Insert、Update、References

（4）列层级

列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时，您必须指定与被授权列相同的列。

列层级的使用还是比较少，所以这里就不再过多阐述。

（5）子程序层级

REATE ROUTINE, ALTER ROUTINE,EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予为子程序层级，并存储在mysql.procs_priv表中。

（6）权限分配顺序

MySQL权限分配顺序是按照层级由大到小进行解析的，也就是先查看
user表（所有数据库）–>db表(某个数据库)->table_priv(某个表)->columns_pirv(某列)进行查找。
如果发现user表中某个权限是Y，那么就不会继续往下找，如果User表某个权限是N，那么就去找db表，依次往下找。

二、实战用户权限分配

（1）新建用户

创建用户常用的命令是CREATE USER，不过使用CREATE USER语句创建用户时，需要操作员要有相应的权限，也就是user表中的字段Create_priv为Y才能创建用户。比如创建一个名为admin的用户，并且登录密码为123456:

CREATE USER ‘admin’@‘%’ IDENTIFIED BY ‘123456’
一般创建用户的格式为：CREATE USER ‘用户名’@‘登录IP或者域名’ IDENTIFIED BY ‘登录密码’。
其中需要注意的是登录IP或者域名，很多时候会将登录IP设置为**%，%**代表所有主机，表示该用户可以在任何机器上进行登录。192.168.101.%表示允许192.168.101这个网段的主机进行登录。

当你只是完上面的创建admin用户命令后，你可以打开mysql库下的user表，你会发现里面多了一条admin的数据，但是所有的权限都是N，这表明该admin用户没有任何权限，不能查看操作任何数据库资源。
在这里插入图片描述
此时你可以尝试使用admin用户登录你现有的数据库，你会发现admin账号只能查看information_schema这个数据库，那是因为我们在创建admin账号时，并没有分配任何权限。
在这里插入图片描述
如果你想在创建账号的时候就分配给该账号一定的权限，那么你可以使用 : grant all privileges on *.* to 'admin'@'%' identified by '123456' with grant option; 该语句表示创建admin账号允许从任何主机登录并且拥有数据库全部的权限，可以将自己的权限授予给别人。我们将这条语句拆分:

创建账号时并赋予全部权限: grant all privileges on *.* to '用户名'@'登录IP或者域名' identified by '登录密码' with grant option;all privileges: 表示授权用户全部权限,当然你也可以指定赋予具体权限，比如SELECT、UPDATE、CREATE、DROP等。on:表示这些权限可以作用于哪些数据库和哪些表，*.*表示作用于所有数据库和所有表，格式为 数据库名.表名。比如有一个test库，想让admin拥有查看test库所有表的权限，那么可以这样写：grant SELECT on test.* to 'admin'@'%' identified by '登录密码';to：将权限授予哪个用户。格式：'用户名'@'登录IP或域名'。IDENTIFIED by：指定用户的登录密码。with grant option：表示允许用户将自己的权限授权给其它用户。

但请注意可能由于MySQL的数据库版本问题，有些版本不允许在创建用户时就给予权限，那么需要将创建和权限赋予分为两步:

create user 'admin'@'%' identified by '123456' -- 创建用户GRANT all privileges ON  *.* TO 'admin'@'%' -- 将所有权限赋予给admin

一般来说我们不会轻易创建一个用户给他赋予所有权限，也就是 grant all privileges，那么grant后面可以接哪些权限呢？这里简单整理了一部分权限以及其含义：

权限意义
ALL [PRIVILEGES]设置除GRANT OPTION之外的所有简单权限
ALTER允许使用ALTER TABLE
ALTER ROUTINE更改或取消已存储的子程序
CREATE允许使用CREATE TABLE
CREATE USER允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW允许使用CREATE VIEW
DELETE允许使用DELETE
DROP允许使用DROP TABLE
INDEX允许使用CREATE INDEX和DROP INDEX
INSERT允许使用INSERT
SELECT允许使用SELECT
SHOW DATABASESSHOW DATABASES显示所有数据库
SHOW VIEW允许使用SHOW CREATE VIEW
SHUTDOWN允许使用mysqladmin shutdown
UPDATE允许使用UPDATE
GRANT OPTION允许授予权限

以上表格举例了部分权限以及其含义，如果你想给一个账号赋予多个权限，可以用英文逗号分开，比如Grant SELECT,UPDATE,DROP等，如果想连接更多信息，欢迎点击Mysql账号管理查看

（2）删除用户

刚刚我们已经创建了一个admin账号并且没有为其设置任何权限，此时我们可以使用 DROP USER 命令删除某个用户，例如我们要删除之前创建的admin用户，那么我们可以使用命令:

DROP USER 'admin'@'%' -- 删除admin用户，用户名(User)和主机名(Host)构成FLUSH PRIVILEGES​; -- 刷新权限

此时user表中的admin用户就删除了。
在这里插入图片描述

（3）禁用用户、启用用户

有时候我们只想禁用某个用户的登录，并不想将该用户进行删除，那么可以使用MySQL的禁用功能。在演示之前我们现在创建一个admin用户，该用户拥有访问数据库(db_cztl_dev)的权限，请注意db_cztl_dev是在我MySQL中存在的一个数据库，您可以使用您自己的数据库。执行命令:

create user 'admin'@'%' identified by '123456' -- 创建用户GRANT SELECT ON `db_cztl_dev`.* TO 'admin'@'%' -- 将db_cztl_dev数据库的读权限赋予admin

此时user表会有admin账号的信息，我们可以看到user表里的admin的select_priv是为N，因为admin现在的权限只是能访问某一个数据库，而非全部数据库，所以user表里的select_pirv是为N，但是db表(数据库层级)会在db_cztl_dev那条数据的select_priv为N。
在这里插入图片描述
在这里插入图片描述
表示admin可以访问数据库db_cztl_dev，拥有该数据库下的所有SELECT权限，让我们用admin账号登录，就可以看到db_cztl_dev数据库。
在这里插入图片描述
如果我们要禁用admin账号，可以输入命令:

ALTER USER 'admin'@'%' ACCOUNT lock; -- 禁用admin账号FLUSH PRIVILEGES; -- 刷新权限

此时重新使用admin用户登录，就可以提示以下信息:
在这里插入图片描述
解除admin禁用可以使用命令:

ALTER USER 'admin'@'%' ACCOUNT UNLOCK; --解除admin用户FLUSH PRIVILEGES; -- 刷新权限

（4）重命名用户名

修改用户名的格式如下：

rename user '用户名'@'IP或者域名' to '新用户名'@'IP或者域名';

比如我们要将admin用户重名为king，那么可以这样写：

rename user 'admin'@'%' to 'king'@'%';

（5）修改用户密码

set password for '用户名'@'IP或者域名'=password('新密码')

（6）收回权限(revoke)

根据上面几小节我们指定如何给一个用户赋予权限可以用Grant命令，相对应的撤销用户的某个权限可以使用REVOKE。

（6.1）查看数据库中所有用户信息

我们知道用户的基本信息都存放在user表中，那么查看所有用户信息可以使用以下语句:

SELECT user,host FROM mysql.`user`;

在这里插入图片描述

（6.2）查看用户拥有的所有权限

查看某个用户的权限可以使用 show grants for 用户名，比如我们要查看admin用户的权限有哪些:

SHOW GRANTS FOR 'admin'@'%'

可以看到admin用户拥有查看数据库db_cztl_dev下所有资源的权限。
在这里插入图片描述

（6.3）使用REVOKE撤销权限

由4.2小节我们可以看到admin拥有访问db_cztl_dev数据库的权限，假如我们此时想撤销admin的权限，可以这样写:

REVOKE SELECT ON db_cztl_dev.* FROM 'admin'@'%'; -- 撤销admin在数据db_cztl_dev的权限

此时我们再使用SHOW GRANTS FOR 'admin'@'%'查看admin权限，可以看到admin访问db_cztl_dev数据库的权限已被撤销。
在这里插入图片描述

（7）修改用户权限(Grant 权限类型 ON 数据库.表 TO 用户)

通过6.3我们已经撤销了admin查看db_cztl_dev的权限，如果此时我们想赋予admin用户可以访问并且修改db_cztl_dev库下cztl_line表的权限，可以这样做:

GRANT SELECT,UPDATE ON db_cztl_dev.cztl_line TO 'admin'@'%';FLUSH PRIVILEGES;

重新使用admin账号登录数据库，就可以访问和修改db_cztl_dev库下的cztl_line表。
在这里插入图片描述
如果想让admin用户能访问整个db_cztl_dev库下所有资源，可以追加权限：

GRANT SELECT ON `db_cztl_dev`.* TO 'admin'@'%'

重新admin登录就可以查到所有表
在这里插入图片描述

（8） 设置MySQL用户密码过期策略

设置系统参数default_password_lifetime作用于所有的用户账户，可以通过show variables like ‘default_password_lifetime’;查看密码过期策略，0表示用不过期，可以通过 set global default_password_lifetime=180;设置密码过期时间为180天。
在这里插入图片描述

（8.1）手动强制某个用户密码过期

ALTER USER '用户名'@'IP或者域名' PASSWORD EXPIRE;

假如我们要将admin账号强制密码过期，可以这样做:

ALTER USER 'admin'@'%' PASSWORD EXPIRE; --强制admin用户密码过期

重启使用admin登录时，会弹出重置密码界面。
在这里插入图片描述

总结

以上就是我对MySQL用户权限分配的总结和分享，如果有任何意见或者错误欢迎大家及时提出，如果有疑问也欢迎留言一起讨论交流！

来源地址：https://blog.csdn.net/qq_43600166/article/details/129654829

您可能感兴趣的文档:

--结束END--

本文标题: Mysql用户权限分配详解

本文链接: https://lsjlt.com/news/407047.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

猜你喜欢
  • Mysql用户权限分配详解
    文章目录 MySQL 权限介绍一、Mysql权限级别分析（1）全局级别（1.1） USER表的组成结构（1.1.1） 用户列（1.1.2） 权限列（1.1.3） 安全列（1.1.4） 资源控制...
    99+
    2023-09-14
    mysql 数据库 java
  • 详解MySQL 用户权限管理
    前言：  不清楚各位同学对数据库用户权限管理是否了解，作为一名 DBA ，用户权限管理是绕不开的一项工作内容。特别是生产库，数据库用户权限更应该规范管理。本篇文章将会介绍下 MySQL 用户权限管理相关内容...
    99+
    2022-05-30
    MySQL 权限管理 MySQL 用户权限
  • 管理Mysql用户及分配权限介绍
    本文主要给大家简单讲讲管理Mysql用户及分配权限介绍，相关专业术语大家可以上网查查或者找一些相关书籍补充一下，这里就不涉猎了，我们就直奔主题吧，希望管理Mysql用户及分配权限介绍这篇文章可以给大家带来一...
    99+
    2024-04-02
  • MySQL用户与权限的管理详解
    本文实例讲述了MySQL用户与权限的管理。分享给大家供大家参考，具体如下： 用户连接到MySQL，可以做各种查询，这都是MySQL用户与权限功能在背后维持着操作。 用户与数据库服务器交互数据，分为两个阶...
    99+
    2024-04-02
  • Mongodb 用户权限管理及配置详解
    一、MongoDB命令 理解 admin 数据库 安装 MongoDB 时，会自动创建 admin 数据库，这是一个特殊数据库，提供了普通数据库没有的功能。 有些用户角色赋予用户操作多个数据库的权限，而这些角色只能在 a...
    99+
    2023-01-28
    Mongodb用户权限管理及配置详解 包括Mongodb访问控制配置 Mongodb开启权限验证 Mongodb账户创建 Mongodb账户管理命令
  • Mongodb用户权限管理及配置详解
    一、Mongodb命令 理解 admin 数据库 安装 MongoDB 时，会自动创建 admin 数据库，这是一个特殊数据库，提供了普通数据库没有的功能。 有些用户角色赋予用户操作...
    99+
    2023-01-28
    Mongodb 用户权限管理及配置详解 包括Mongodb访问控制配置 Mongodb开启权限验证 Mongodb账户创建 Mongodb账户管理命令
  • mysql用户权限
    读写、只读、DML、DDL 区别 在MySQL中，有不同的用户权限级别，包括读写权限、只读权限、DML权限和DDL权限。下面是它们之间的区别： 读写权限（Read/Write）：具有读写权限的用户可...
    99+
    2023-09-05
    mysql 数据库
  • MySQL用户和数据权限管理详解
    目录1、管理用户1.1、添加用户1.2、删除用户1.3、修改用户名1.4、修改密码2、授予权限和回收权限2.1、授予权限2.2、权限的转移和限制2.3、回收权限1、管理用户 1.1、添加用户 可以使用CREATE USE...
    99+
    2024-04-02
  • 详细介绍Mysql Grant用户权限
    下面一起来了解下Mysql Grant用户权限，相信大家看完肯定会受益匪浅，文字在精不在多，希望Mysql Grant用户权限这篇短内容是你想要的。MySQL权限图：很明显总共27个权限：下面是具体的权限介...
    99+
    2024-04-02
  • 创建用户，库及权限的分配
    --建数据库  create database zxlh default character set utf8; --建用户，只访问zxlh数据库,%表示不限制任何ip grant all PR...
    99+
    2024-04-02
  • linux怎么为用户组分配权限
    要为用户组分配权限，可以使用chmod命令。1. 找到要分配权限的文件或目录。可以使用ls命令来列出当前目录下的文件和目录。2. 使...
    99+
    2023-08-16
    linux
  • mysql中如何使用root用户分配其它用户访问权限
    这篇文章主要介绍mysql中如何使用root用户分配其它用户访问权限，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！使用root用户分配其它用户访问权限/usr/local/mysql...
    99+
    2024-04-02
  • mysql如何授权用户权限
    授权mysql用户权限的过程包括：登录mysql使用具有管理权限的用户。创建要授权的用户（如果不存在）。使用grant语句授予所需的权限。刷新权限表以使更改生效。 MySQL授权用户权...
    99+
    2024-06-15
    mysql
  • mysql用户权限管理实例分析
    本文实例讲述了mysql用户权限管理。分享给大家供大家参考，具体如下： 本文内容： 什么是用户权限 恰当的用户权限 查看权限 修改权限 删除权限 首发日期：2018-04-19 什么是用户...
    99+
    2022-05-15
    mysql 用户权限管理
  • mysql用户和权限的示例分析
    小编给大家分享一下mysql用户和权限的示例分析，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！ 用...
    99+
    2024-04-02
  • MySQL-5.7权限详解
    *.*代表所有数据库的权限 mysql> grant all on *.* to "test"@"%"; Query OK, 0 rows affected (0.00 sec) mysql> grant select, in...
    99+
    2018-12-01
    MySQL-5.7权限详解
  • MYSQL用户权限管理
    1.在MySQL中，用户是怎么定义的 #mysql中，定义一个用户是： 用户名@‘主机域‘ #用户名写法： 用户名如果是字符： mysql> create user [email protected]‘10.0.0.1‘; 用户...
    99+
    2015-02-26
    MYSQL用户权限管理 数据库入门 数据库基础教程
  • MySQL用户权限操作
    查看当前用户： select user,host from user; 或：   SELECT DISTINCT CONCAT(‘User: ‘‘‘,user,‘‘‘@‘‘‘,host,‘‘‘;‘) AS quer...
    99+
    2016-04-08
    MySQL用户权限操作 数据库入门 数据库基础教程 数据库 mysql
  • MySQL修改用户权限
    最近测试中台的存储服务，涉及到MySQL用户操作的命令，记录一下。 查看所有用户 select user from mysql.user; 查看当前登陆用户 select user();   创建用户 C...
    99+
    2016-08-30
    MySQL修改用户权限
  • mysql导出用户权限
    在对MySQL数据库进行迁移的时候，有时候也需要迁移源数据库内的用户与权限。对于这个迁移我们可以从mysql.user表来获取用户的相关权限来生成相应的SQL语句，然后在目标服务器上来执行生成的SQL语...
    99+
    2024-04-02
软考高级职称资格查询
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作