一文看尽MySQL用户权限管理,真香!

举报
_陈哈哈 发表于 2022/01/22 22:17:29 2022/01/22
【摘要】 关于MySQL的权限简单的理解就是MySQL允许你做你权利以内的事情,不可以越界。MySQL服务器通过权限表来控制用户对数据库的访问,权限表存放在mysql数据库中,由mysql_install_db脚本初始化。 存储账户权限信息表主要有:user、db、tables_priv、columns_priv、procs_priv、...

关于MySQL的权限简单的理解就是MySQL允许你做你权利以内的事情,不可以越界。MySQL服务器通过权限表来控制用户对数据库的访问,权限表存放在mysql数据库中,由mysql_install_db脚本初始化。

存储账户权限信息表主要有:user、db、tables_priv、columns_priv、procs_priv、proxies_priv这六张表(5.6之前还有host表,现在已经把host内容整合进user表)

小伙伴想精准查找自己想看的MySQL文章?喏 → MySQL专栏目录 | 点击这里

目录

 

权限管理概念

1. MySQL连接权限

2. 操作执行权限

3. MySQL执行权限检查顺序

实际操作

创建/授权用户:

一、创建用户格式

二、授权格式及示例:

1. 授予管理员权限

2. 授予数据库、表权限

3. 授予列权限

三、修改用户配置:

1. 回收 mysql 权限

2. 删除 mysql 用户

3. 修改 mysql 用户密码

4. 忘记密码

5. 设置MySQL用户密码过期策略

6. mysql 用户 lock

7. 用户重命名

 


权限管理概念

MySQL用户权限管理主要有以下作用:

  1. 可以限制用户访问哪些库、哪些表
  2. 可以限制用户对哪些表执行SELECT、CREATE、DELETE、DELETE、ALTER等操作
  3. 可以限制用户登录的IP或域名
  4. 可以限制用户自己的权限是否可以授权给别的用户

用户操作MySQL数据库的权限管理,主要分为两个阶段:

  • 有没有权限连接上来
  • 有没有权限执行本操作

1. MySQL连接权限

服务器如何判断用户有没有权连接上来?

依据:

  • 你从哪里来?host
  • 你是谁?user
  • 你的密码是多少?password

用户的这三个信息,存储在mysql库中的user表中。


2. 操作执行权限

mysql数据库(系统数据库)下的表:user、db、tables_priv、columns_priv、proce_priv、proxies_priv共同构成授权表;

  • 1)user表

user表列出可以连接服务器的用户及其口令,并且它指定他们有哪种全局(超级用户)权限。在user表启用的任何权限均是全局权限,并适用于所有数据库。例如,如果你启用了DELETE权限,在这里列出的用户可以从任何表中删除记录,所以在你这样做之前要认真考虑。

  • 2)db表

db表列出数据库,而用户有权限访问它们。在这里指定的权限适用于一个数据库中的所有表。

  • 3)tables_priv表

tables_priv表指定表级权限,在这里指定的一个权限适用于一个表的所有列。

  • 4)columns_priv表

columns_priv表指定列级权限。这里指定的权限适用于一个表的特定列。

  • 5)proce_priv

columns_priv表指定存储过程权限。这里代表允许使用某个存储过程的权限。

  • 6)proxies_priv

利用 MySQL proxies_priv(模拟角色)实现类似用户组管理。角色(Role)可以用来批量管理用户,同一个角色下的用户,拥有相同的权限。
注:MySQL5.7.X以后可以模拟角色(Role)的功能,通过mysql.proxies_priv模拟实现
 

3. MySQL执行权限检查顺序

mysql执行权限检查顺序


开始查询

  1. 校验user表,对于全局权限是ok → 直接执行
  2. 检验DB表,对于某个有特定的数据库有权限 → 执行
  3. 检验tables_priv,对于特定数据库下的某些表是有权限 → 执行
  4. 检验columns_priv,对于特定表中的某些列有权限 → 执行

权限分布

可能的设置的权限

表权限

'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter'

列权限

'Select', 'Insert', 'Update', 'References'

过程权限

'Execute', 'Alter Routine', 'Grant'

 

 

 

 

 

用户登录,user表首先能限制用户登录,其次还保存了该用户的全局权限,如果该用户没有任何权限,那么将从db表中查找该用户是否有某个数据库的操作权限,如果都没有,将从table_priv表中查找该用户是否有某个表的操作权限,如果有,则该用户可以按照已有的权限来操作该表。


实际操作

在我们安装完数据库后,只能在本地登录数据库,当进行远程登录时,会提示我们错误。

æä¹è§£å³mysqlä¸å许è¿ç¨è¿æ¥çé误

1130 - Host'xxx.xxx.xxx.xxx' is not allowed to connect to this MySQL server

这时,我们就要进行相应的配置,给特定的用户赋予特定的操作权限。

MySQL中使用GRANT命令和REVOKE命令来管理用户的权限。

 

创建/授权用户:


有两种方式创建MySQL授权用户

方法一、通过insert语句直接操作MySQL系统权限表(此处不再赘述)

方法二、执行create user/grant命令(推荐)

注意:

  • 在试用grant命令创建用户时要注意了,在MySQL5.7.7版本前,如果被授权的用户不存在,那么grant语句会自动创建新的账户,除非设置参数sql_mode 包含 “NO_AUTO_CREATE_USER” 。
  • 但从5.7.7版本开始,默认的sql_mode就包含“NO_AUTO_CREATE_USER” (grant语句不再创建新的账户
  • 因此,如果你是用的MySQL版本较新,要注意语法了,需要先用create user创建用户,再用grant命令赋权限;


一、创建用户格式

创建用户命令一般格式:

create user [用户名]@[访问地址] identified by [密码]
 


举例1:创建zhangsan用户,只是创建用户并没有权限,'localhost'表示只能在本地登录,无法通过远程连接;密码是 password

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

举例2:创建lisi用户,只是创建用户并没有权限,密码为 password,%通配符表示任何主机都可以连接(可以远程连接),

CREATE USER 'lisi'@'%' IDENTIFIED BY 'password';
 

举例3:(5.7.7版本前)的创建用户并赋予RELOAD,PROCESS权限 ,在所有的库和表上;*.*:代表所有的库表

GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost' identified by '123456';
 

二、授权格式及示例:

grant [权限1,权限2,权限3] on *.* to user@'host' identified by 'password'
 

常用权限:all privileges、create、drop、select、insert、delete、update

例如,给"zhangsan" 用户管理员权限,并且允许该用户继续给别的用户赋权限

grant all privileges on *.* to 'zhangsan'@'192.168.1.%' with grant option;
 
  • all privileges:表示将所有权限授予给用户。也可指定具体的权限,如:SELECT、CREATE、DROP等。
  • on:表示这些权限对哪些数据库和表生效,格式:数据库名.表名,这里写“*”表示所有数据库,所有表。如果我要指定将权限应用到test库的user表中,可以这么写:test.user
  • to:将权限授予哪个用户。格式:”用户名”@”登录IP或域名”。%表示没有限制,在任何主机都可以登录。比如:'zhangsan'@'192.168.1.%',表示zhangsan这个用户只能在192.168.0.* IP段登录
  • with grant option:通过在grant语句的最后使用该子句,就允许被授权的用户把得到的权限继续授给其它用户

注:使用GRANT添加权限,权限会自动叠加,不会覆盖之前授予的权限,比如你先给用户添加一个SELECT权限,后来又给用户添加了一个UPDATE权限,那么该用户就同时拥有了SELECT和UPDATE权限。
 

1. 授予管理员权限

举例4:把zhangsan 变成管理员用户,拥有root权限


  
  1. mysql> GRANT ALL PRIVILEGES ON *.* TO 'zhangsan'@'localhost' WITH GRANT OPTION;
  2. #刷新权限命令
  3. mysql> flush privileges;

2. 授予数据库、表权限

举例5:授予用户zhangsan可以对test数据库中的所有表进行查询

grant select on test.* to zhangsan;
 


举例6:授予lisi在test数据库中创建、修改、删除表的权限以及创建视图的权限

grant createalterdropcreate view on test.* to lisi;
 


举例7:授予lisi可以对当前数据库中的所有表进行查询, * :表示当前数据库

grant select on * to lisi; 
 


举例8:授予lisi可以创建、修改、删除数据库以及对所有数据库中的所有表进行create、alter和drop

grant create,alter,drop on . to lisi;
 


举例9:授予lisi可以创建新用户

grant create user on . to lisi;
 

3. 授予列权限


# 例10:给zhangsan用户赋权限,设置为在test库,shop表,上的id、name、price列只有select 权限


  
  1. mysql> grant select(id,name,price) on test.temp to zhangsan@'localhost' WITH GRANT OPTION;
  2. #刷新权限
  3. mysql> flush privileges;

# 举例11:授予用户zhangsan可以对test.shop表的id和name列进行更新

grant update(id,name) on test.shop to zhangsan@'localhost';
 


# 举例12:查看自己的权限:

show grants;
 

 

三、修改用户配置:

1. 回收 mysql 权限


通过 revoke 命令收回用户权限,回收的时候看一下这个用户有哪些权限然后回收 

show grants for admin@'localhost';
 

  
  1. mysql> show grants \G
  2. *************************** 1. row ***************************
  3. Grants for admin@localhost: GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION
  4. 1 row in set (0.00 sec)


revoke :回收权限


  
  1. mysql> revoke PROCESS ON *.* FROM admin@'localhost';
  2. #刷新权限
  3. mysql> flush privileges;

2. 删除 mysql 用户

drop user admin@'localhost';
 

3. 修改 mysql 用户密码

修改用户密码的方式包括以下三种:


  
  1. -- 第一种
  2. ALTER USER 'chenhh'@'localhost' IDENTIFIED BY 'mypassword';
  3. -- 第二种
  4. SET PASSWORD FOR 'chenhh'@'localhost' = PASSWORD('mypassword');
  5. -- 第三种
  6. GRANT USAGE ON *.* TO 'chenhh'@'localhost' IDENTIFIED BY 'mypassword';


如在数据库外命令行中,通过:mysqladmin -u用户名 -p旧的密码 password 新密码

shell> mysqladmin -u user_name -h host_name password "new_password"
 

 

# 修改当前会话本身用户密码的方式包括:


  
  1. -- 第一种
  2. ALTER USER USER() IDENTIFIED BY 'mypassword';
  3. -- 第二种
  4. SET PASSWORD = PASSWORD('mypassword');

4. 忘记密码

1> 添加登录跳过权限检查配置

修改my.cnf,在mysqld配置节点添加skip-grant-tables配置


  
  1. [mysqld]
  2. skip-grant-tables

2> 重新启动mysql服务

shell> service mysqld restart
 

3> 修改密码


  
  1. -- 第一种
  2. ALTER USER 'chenhh'@'localhost' IDENTIFIED BY 'mypassword';
  3. -- 第二种
  4. SET PASSWORD FOR 'chenhh'@'localhost' = PASSWORD('mypassword');
  5. -- 第三种
  6. GRANT USAGE ON *.* TO 'chenhh'@'localhost' IDENTIFIED BY 'mypassword';

5. 设置MySQL用户密码过期策略

设置系统参数default_password_lifetime作用于所有的用户账户,修改my.cnf系统文件重启MySQL即可


  
  1. [mysqld]
  2. default_password_lifetime=90
  3. #default_password_lifetime=180 设置180天过期
  4. #default_password_lifetime=0 设置密码不过期 

如果为每个用户设置了密码过期策略,则会覆盖上述系统参数,设置方式如下:


  
  1. -- 90天过期
  2. ALTER USER 'chenhh'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
  3. -- 密码不过期
  4. ALTER USER 'chenhh'@'localhost' PASSWORD EXPIRE NEVER;
  5. -- 默认过期策略
  6. ALTER USER 'chenhh'@'localhost' PASSWORD EXPIRE DEFAULT;

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

ALTER USER 'chenhh'@'localhost' PASSWORD EXPIRE;
 

6. mysql 用户 lock

通过执行create user/alter user命令中带account lock/unlock子句设置用户的lock状态

Create user语句默认的用户是unlock状态

创建的时候给用户锁定方式

create user chenhh@localhost identified by 'mysql' account lock;
 

Alter user语句默认不会修改用户的lock/unlock状态

# 修改用户为unlock

 alter user chenhh@'localhost' account unlock;
 

当客户端使用lock状态的用户登录MySQL时,会收到如此报错 

Access denied for user ‘user_name’@’host_name’. 
Account is locked.

7. 用户重命名

rename user 'chenhh'@'%' to 'zhoujielun'@'%';
 

 

 

文章来源: chensj.blog.csdn.net,作者:_陈哈哈,版权归原作者所有,如需转载,请联系作者。

原文链接:chensj.blog.csdn.net/article/details/105793810

【版权声明】本文为华为云社区用户转载文章,如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

举报
请填写举报理由
0/200