用户管理

尽量为不同的人物分配不同的用户及权限来操作数据库 提高安全性

  1. ##### 查用户
  2. SELECT *
  3. FROM mysql.user;
  4. SELECT Host, User
  5. FROM mysql.user;
  8. ##### 创建用户
  9. CREATE USER 'query' IDENTIFIED BY 'query';
  10. # 创建用户并指定访问ip为本地
  11. CREATE USER 'query' @'localhost' IDENTIFIED BY 'query';
  12. # 由此知 user 表的主键是Host+User 即 端口+账号
  14. ##### 查看当前用户的权限
  15. SHOW GRANTS ;
  18. ##### 修改用户基础信息 [不常用]
  19. UPDATE mysql.user
  20. SET User = 'queryUser'
  21. WHERE User = 'query' AND Host = 'localhost';
  22. # 修改后必须刷新权限 否则不生效
  23. FLUSH PRIVILEGES ;
  25. ##### 删除用户 1
  26. DROP USER 'queryUser' @'localhost'; # 没有指定Host时默认是%
  28. # 删除用户 2 需要刷新权限 `且不推荐使用` 因为并没有删除此用户的其他相关内容
  29. # 比如用户权限[mysql.user+mysql.db]等,即删除并没有删除干净
  30. DELETE
  31. FROM mysql.user
  32. WHERE User = 'query';
  33. # 删除后必须刷新权限 否则不生效
  34. FLUSH PRIVILEGES ;
  36. ##### 修改当前用户密码
  37. ALTER USER USER() IDENTIFIED BY 'root';
  38. # 或
  39. SET PASSWORD = 'root';
  40. ##### 修改其他用户密码
  41. ALTER USER 'query' @'localhost' IDENTIFIED BY 'query';
  42. # 或
  43. SET PASSWORD FOR 'query' @'localhost' = 'query';
  44. ##### 不要使用UPDATE 因为加密密码的函数在8.0之后删除
  45. UPDATE mysql.user SET authentication_string = password('queyr') WHERE User = 'query';
  46. FLUSH PRIVILEGES ;

密码过期及重用策略

  1. #### 设置指定用户密码过期
  2. ALTER USER 'query' @'localhost' PASSWORD EXPIRE INTERVAL 100 DAY ; # 100天过期后可以登录 但是没有其他操作
  3. CREATE USER 'lisi' @'localhost' IDENTIFIED BY 'lisi' PASSWORD EXPIRE INTERVAL 100 DAY ;
  4. ALTER USER 'query' @'localhost' PASSWORD EXPIRE NEVER ; # NEVER永不过期
  5. ALTER USER 'query' @'localhost' PASSWORD EXPIRE DEFAULT ; # DEFAULT使用全局设置
  6. ### 设置全局密码过期时间 sql、配置文件
  7. SET PERSIST default_password_lifetime = 100; # 每过100天密码过期 若设置0则不会过期

密码重用策略:
限制已经使用过的密码 -密码历史改动数量 -使用时间 ， 可以设置全局 单独账号
1、账号历史密码包含过去该账号的密码。MySQL以以下规则限制密码使用
a、限制：基于密码史改数量，则新密码不能从最近限制的密码数量中选择。例：设置为3，则新密码不能与最近3个密码重复
b、限制：基于时间，则新密码不能从规定时间内选择。例：设置30天，则新密码不能与30天内的密码重复
2、MySQL使用password_history和password_reuse_interval系统变量设置密码重用策略
a、password_history : 密码重用数量
b、password_reuse_interval : 密码重用周期
3、也可以在 服务器配置文件中维护，也能在 运行中使用 sql语句修改该变量的值并持久化
4、手动设置：全局
a、sql修改 ：
SET PERSIST password_history = 6
SET PERSIST password_reuse_interval = 30
b、配置文件:
[mysql]
SET PERSIST password_history = 6
SET PERSIST password_reuse_interval = 30
c、单独用户设置 :
例如:

  1. CREATE USER 'limingming' @'localhost' PASSWORD HISTORY 5; # 不能与近5个重复
  2. ALTER USER 'limingming' @'localhost'  PASSWORD HISTORY 5;
  4. CREATE USER 'limingming' @'localhost' PASSWORD REUSE INTERVAL 30 DAY ; # 不能与30天内重复
  5. ALTER USER 'limingming' @'localhost' PASSWORD REUSE INTERVAL 30 DAY ;
  7. CREATE USER 'limingming' @'localhost' PASSWORD HISTORY 5 
  8.   PASSWORD REUSE INTERVAL 30 DAY ; # 不能与近5个和30天内重复

权限管理

仅仅在MySQL中创建了用户 对于新用户来说是没有意义的 因为创建的用户默认是只有登录权限和访问information_schema库的权限 需要对用户进行授权或者授予角色操作 用户才能进一步操作数据库

  1. # 查看全部权限
  2. SHOW PRIVILEGES ;
  5. # 查看本人权限
  6. SHOW GRANTS ;

查看权限.png
常用权限解释:
（1） CREATE和DROP权限 ，可以创建新的数据库和表，或删除（移掉）已有的数据库和表。如果将MySQL数据库中的DROP权限授予某用户， 用户就可以删除MySQL访问权限保存的数据库。
（2） SELECT、INSERT、UPDATE和DELETE权限 允许在一个数据库现有的表上实施操作。
（3） SELECT权限只有在它们真正从一个表中检索行时才被用到。
（4） INDEX权限 允许创建或删除索引，INDEX适用于已有的表。如果具有某个表的CREATE权限，就可以在CREATE TABLE语句中包括索引定义。
（5） ALTER权限 可以使用ALTER TABLE来更改表的结构和重新命名表。
（6） CREATE ROUTINE权限 用来创建保存的程序（函数和程序），ALTER ROUTINE权限用来更改和删除保存的程序， EXECUTE权限 用来执行保存的程序。
（7） GRANT权限 允许授权给其他用户，可用于数据库、表和保存的程序。
（8） FILE权限 使用户可以使用LOAD DATA INFILE和SELECT … INTO OUTFILE语句读或写服务器上的文件，任何被授予FILE权限的用户都能读或写MySQL服务器上的任何文件（说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）。
权限分布.png
权限控制主要是出于安全因素，因此需要遵循以下几个 经验原则 ：
1、只授予能 满足需要的最小权限 ，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。
2、创建用户的时候 限制用户的登录主机 ，一般是限制成指定IP或者内网IP段。
3、为每个用户 设置满足密码复杂度的密码 。
4、 定期清理不需要的用户 ，回收权限或者删除用户。

授权

  1. # 直接授权 如果没有这个用户则会直接创建此用户
  2. # GRANT 权限,权限 ON 数据库.表[数据库.*表示整个库] TO 用户名 @IP IDENTIFIED BY 密码
  3. GRANT SELECT ON test_db.test_p TO 'admin' @'%' IDENTIFIED BY 'admin';

GRANT 授权 并不存在 覆盖 即 后面执行的授权和前面执行的授权同时存在 -> 权限叠加
用户进行自己没有权限的的操作将报错:
1142 (42000): DELETE command denied to user 'admin' @'%' for table 'test_p'

  1. # 授权 所有权限 到 所有库 的 所有表 给 admin用户, 
  2. # 但是无法和root相同权限root用户可以给别的用户授权 ALL PRIVILEGES 用户则不能
  3. GRANT ALL PRIVILEGES ON *.* TO 'admin' @'%' IDENTIFIED BY 'admin';
  1. # 如果想要让 ALL PRIVILEGES 用户给别人授权自己拥有的权限则需要增加参数 WITH GRANT OPTION
  2. GRANT ALL PRIVILEGES ON *.* TO 'admin' @'%' IDENTIFIED BY 'admin' WITH GRANT OPTION ;

我们在开发应用的时候，经常会遇到一种需求，就是要根据用户的不同，对数据进行横向和纵向的分组。
所谓横向的分组，就是指用户可以接触到的数据的范围，比如可以看到哪些表的数据；
所谓纵向的分组，就是指用户对接触到的数据能访问到什么程度，比如能看、能改，甚至是删除。

查看权限

  1. # 查看当前用户权限
  2. SHOW GRANTS;
  3. # 或
  4. SHOW GRANTS FOR CURRENT_USER;
  5. # 或
  6. SHOW GRANTS FOR CURRENT_USER();
  8. # 查看某用户的全局权限
  9. SHOW GRANTS FOR 'user'@'主机地址' ;

回收权限

收回权限就是取消已经赋予用户的某些权限。
收回用户不必要的权限可以在一定程度上保证系统的安全性。
MySQL中使用 REVOKE语句 取消用户的某些权限。
使用REVOKE收回权限之后，用户账户的记录将从db、host、tables_priv和columns_priv表中删除，但是用户账户记录仍然在user表中保存（删除user表中的账户记录使用DROP USER语句）。
注意：在将用户账户从user表删除之前，应该收回相应用户的所有权限。

  1. # 收回权限命令
  2. # REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;
  4. #例:
  5. #收回全库全表的所有权限
  6. REVOKE ALL PRIVILEGES ON *.* FROM 'admin' @'%';
  7. #收回mysql库下的所有表的插删改查权限
  8. REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM 'admin' @'localhost';
  10. ### 注意： 须用户重新登录后才能生效 重启后查看权限
  11. SHOW GRANTS FOR 'admin' @'%';

不建议仅仅使用root访问数据库 完全把权限控制放在应用层面实现 即使可以实现相同的效果。
建议使用用户 角色机制控制访问权限 不要轻易使用root用户 避免root用户密码再应用层面泄漏
MySQL功能已经十分完善 尽量使用 提高效率和安全性

权限表

MySQL服务器通过 权限表 来 控制用户对数据库的访问 权限表存放再mysql库中 MySQL数据库会根据这些权限表的内容为每个用户授权 其中mysql库中最重要的是user表、db表、table_priv表、column_priv表、procs_priv表等 在MySQL服务启动时，服务器将这些数据库表中的权限信息读入内存其中user表中由用户信息
user表: 用户名:User ,加密密码串:authentication_string ,ip:Host ,和是否拥有所有权限:xxxx_priv ,安全校验字段:ssl ,和其他字段组成

db表记录了: 用户名:User ,ip:Host ,和对于各个库的访问权限:Db + xxxx_priv

table_priv表记录了 : 用户名:User ,ip:Host ,和对于各个库中各个表的访问权限:Db + Table_Name + xxxx_priv

column_priv表: 用户名:User ,ip:Host ,和对于各个库中各个表的各个字段的访问权限:Db + Table_Name + Column_Name + xxxx_priv

procs_priv表 : 用户名:User ,ip:Host ,对于各个库中 存储过程操作权限

访问控制流程:

访问控制.png

MySQL8.0 角色

MySQL8之后引入了角色的概念 角色拥有一部分权限(角色=权限的集合) 然后用户作为角色 简化了大部分重复授权操作
引入角色的好处.png

角色操作

  1. # 创建角色
  2. # CREATE ROLE '角色名'[@'访问ip'] [,'角色名'[@'访问ip']]...
  3. CREATE ROLE 'dmin'@'localhost','manin'@'localhost';
  6. # 角色授权
  7. # GRANT 权限,权限,权限 ON 库.表 TO '角色名'[@'访问ip'] ;
  8. GRANT SELECT ON test_db.* TO 'dmin'@'localhost';
  9. GRANT ALL PRIVILEGES ON *.* TO 'dmin'@'localhost' WITH GRANT OPTION ;
  11. # 查看角色权限
  12. SHOW GRANTS FOR 'dmin'@'localhost';
  1. # 回收权限
  2. # REVOKE privileges ON tablename FROM 'rolename';
  3. REVOKE ALL PRIVILEGES ON *.* FROM 'dmin'@'localhost' ;
  5. # 删除角色
  6. DROP ROLE 'dmin'@'localhost','manin'@'localhost';

角色以用户的形式存在于mysql.user表
SELECT * FROM mysql.user;

赋予角色

  1. ## 给用户授予角色
  2. # GRANT 角色名,角色名 TO 用户名,用户名;
  3. # 1 创建角色
  4. CREATE ROLE main@localhost ;
  5. # 2 给角色权限
  6. GRANT SELECT ON test_db.staff TO main@localhost;
  7. SHOW GRANTS FOR main@localhost;
  8. # 3 创建用户
  9. CREATE USER liming@loclahost IDENTIFIED BY 'liming';
  10. # 4 给用户授予角色
  11. GRANT main@localhost TO liming@loclahost;
  12. # 再次查看用户权限
  13. SHOW GRANTS FOR liming@loclahost;

SHOW GRANTS 查看登录用户的角色 未激活的 显示为 NONE
注意: 角色默认未激活 需要对用户激活角色

  1. # 激活用户全部角色
  2. SET DEFAULT ROLE ALL TO liming@loclahost;
  3. # 激活指定角色
  4. SET DEFAULT ROLE main@localhost TO liming@loclahost;
  7. ## 或者修改session 和 配置文件的 配置 使权限默认生效
  8. SET GLOBAL activate_all_roles_on_login = ON;
  9. # 这条 SQL 语句的意思是，对 所有角色永久激活 。
  10. # 运行这条语句之后，用户才真正拥有了赋予角色的所有权限。
  1. ## 撤销用户角色 撤销后要重新登录
  2. # REVOKE 角色名 FROM 用户名;
  3. REVOKE main@localhost FROM liming@loclahost;

强制角色

强制角色是 创建用户时给账号的默认角色 不用手动设置 无法 回收 和 删除
1 服务启动前设置
[mysqld]
mandatory_roles=’role1,role2@localhost,r3@%.atguigu.com’
2 运行时设置

  1. SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; 
  2. #系统重启后仍然有效
  5. SET GLOBAL mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; 
  6. #系统重启后失效