MySQL8.0 新加了很多功能, 其中在用户管理中增加了角色的管理, 默认的密码加密方式也做了调整, 由之前的 sha1 改为了 sha2, 同时加上 5.7 的禁用用户和用户过期的设置, 这样方面用户的管理和权限的管理, 也增加了用户的安全性.
MySQL8.0 中, MySQL 库中表的文件合并到数据根目录中的 mysql.ibd 中 (MySQL8.0 Innodb 引擎重构).
同时 MySQL8.0 可以使用 SET PERSIST 动态修改参数并保存在配置文件中 (mysqld-auto.cnf, 保存的格式为 JSON 串), 这个是 DBA 同学的福音, 不必担心设置之后忘记保存在配置文件中, 重启之后会被还原的问题了.
查阅了 MySQL8.0 的官方文档, 通过官方的示例来查看新的管理方式.
1,MySQL 用户管理
1.1, 验证插件和密码加密方式的变化
在 MySQL 8.0 中, caching_sha2_password 是默认的身份验证插件而不是之前版本的 mysql_native_password, 默认的密码加密方式是 sha2.
如果需要保持之前的验证方式并保持之前版本的密码加密方式需要在配置文件中修改, 暂不支持动态修改, 需要重启生效: default_authentication_plugin = mysql_native_password.
将 8.0 已有的 sha2 密码修改为 sha1 的模式:
- ALTER USER 'root'@'127.0.0.1' IDENTIFIED BY 'passowrd' PASSWORD EXPIRE NEVER; #修改加密规则为永不过期
- ALTER USER 'root'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY 'password'; #更新一下用户的密码加密方式为之前版本的方式
- FLUSH PRIVILEGES; #刷新权限
1.2 用户授权和修改密码
MySQL8.0 的用户授权和之前有所区别, 老版本的常用授权语句在 8.0 中会报错:
MySQL8.0 之前版本:
GRANT ALL ON *.* TO `wangwei`@`127.0.0.1` IDENTIFIED BY 'passowrd' WITH GRANT OPTION;
MySQL8.0 版本:
- CREATE USER `wangwei`@`127.0.0.1` IDENTIFIED BY 'passowrd';
- GRANT ALL ON *.* TO `wangwei`@`127.0.0.1` WITH GRANT OPTION;
MySQL8.0 中带过期时间用户的创建:
- CREATE USER `wangwei`@`127.0.0.1` IDENTIFIED BY 'wangwei' PASSWORD EXPIRE INTERVAL 90 DAY;
- GRANT ALL ON *.* TO `wangwei`@`127.0.0.1` WITH GRANT OPTION;
MySQL8.0 修改用户密码:
1.2, 密码过期时间管理
要全局建立自动密码到期策略, 请使用 default_password_lifetime 系统变量. 其默认值为 0, 禁用自动密码过期. 如果值 default_password_lifetime 正整数 N, 则表示允许的密码生存期, 以便密码必须每天更改 N. 可以加在配置文件中:
1: 要建立全局策略, 密码的使用期限大约为六个月, 请在服务器 my.cnf 文件中使用以下行启动服务器:
- [mysqld]
- default_password_lifetime=180
2: 要建立全局策略, 以便密码永不过期, 请将其设置 default_password_lifetime 为 0:
- [mysqld]
- default_password_lifetime=0
这个参数是可以动态设置并保存的:
- SET PERSIST default_password_lifetime = 180;
- SET PERSIST default_password_lifetime = 0;
创建和修改带有密码过期的用户, 帐户特定的到期时间设置示例:
要求每 90 天更换密码:
- CREATE USER 'wangwei'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
- ALTER USER 'wangwei'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
禁用密码过期:
- CREATE USER 'wangwei'@'localhost' PASSWORD EXPIRE NEVER;
- ALTER USER 'wangwei'@'localhost' PASSWORD EXPIRE NEVER;
遵循全局到期政策:
- CREATE USER 'wangwei'@'localhost' PASSWORD EXPIRE DEFAULT;
- ALTER USER 'wangwei'@'localhost' PASSWORD EXPIRE DEFAULT;
1.3 MySQL 用户密码重用策略设置
MySQL 允许限制重复使用以前的密码. 可以根据密码更改次数, 已用时间或两者来建立重用限制. 帐户的密码历史由过去分配的密码组成. MySQL 可以限制从此历史记录中选择新密码:
如果根据密码更改次数限制帐户, 则无法从指定数量的最新密码中选择新密码. 例如, 如果密码更改的最小数量设置为 3, 则新密码不能与任何最近的 3 个密码相同.
如果帐户因时间的限制而被限制, 则无法从历史记录中的新密码中选择新密码, 该新密码不会超过指定的天数. 例如, 如果密码重用间隔设置为 60, 则新密码不得在最近 60 天内选择的密码之间.
注意: 空密码不记录在密码历史记录中, 并随时可以重复使用.
要全局建立密码重用策略, 请使用 password_history 和 password_reuse_interval 系统变量. 要在服务器启动时指定变量值, 请在服务器 my.cnf 文件中定义它们.
示例:
要禁止重复使用最近 6 个密码或密码超过 365 天的任何密码, 请将这些行放入您的服务器 my.cnf 文件中:
- [mysqld]
- password_history=6
- password_reuse_interval=365
要动态设置和保存配置, 请使用如下所示的语句:
- SET PERSIST password_history = 6;
- SET PERSIST password_reuse_interval = 365;
2,MySQL8.0 的角色管理
MySQL 角色是指定的权限集合. 像用户帐户一样, 角色可以拥有授予和撤消的权限. 可以授予用户帐户角色, 授予该帐户与每个角色相关的权限. 用户被授予角色权限, 则该用户拥有该角色的权限.
以下列表总结了 MySQL 提供的角色管理功能:
CREATE ROLE 并 DROP ROLE 角色创建和删除;
GRANT 并 REVOKE 为用户和角色分配和撤销权限;
SHOW GRANTS 显示用户和角色的权限和角色分配;
SET DEFAULT ROLE 指定哪些帐户角色默认处于活动状态;
SET ROLE 更改当前会话中的活动角色.
CURRENT_ROLE() 功能显示当前会话中的活动角色.
2.1 创建角色并授予用户角色权限
考虑如下几种场景:
应用程序使用名为 app_db 的数据库 .
与应用程序相关联, 可以为创建和维护应用程序的开发人员以及管理员账户.
开发人员需要完全访问数据库. 有的用户只需要读取权限, 有的用户需要读取 / 写入权限.
为清楚区分角色的权限, 将角色创建为所需权限集的名称. 通过授权适当的角色, 可以轻松地为用户帐户授予所需的权限.
要创建角色, 请使用 CREATE ROLE:
CREATE ROLE 'app_developer', 'app_read', 'app_write';
角色名称与用户帐户名称非常相似, 由格式中的用户部分和主机部分组成. 主机部分, 如果省略, 则默认为 %. 用户和主机部分可以不加引号, 除非它们包含特殊字符. 与帐户名称不同, 角色名称的用户部分不能为空. 为角色分配权限, 使用与为用户分配权限相同的语法执行:
- GRANT ALL ON app_db.* TO 'app_developer';
- GRANT SELECT ON app_db.* TO 'app_read';
- GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';CREATE ROLE 'app_developer', 'app_read', 'app_write';
现在假设您最初需要一个开发人员帐户, 两个需要只读访问权的用户以及一个需要读取 / 写入权限的用户. 使用 CREATEUSER 创建用户:
- CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'dev1pass';
- CREATE USER 'read_user1'@'localhost' IDENTIFIED BY 'read_user1pass';
- CREATE USER 'read_user2'@'localhost' IDENTIFIED BY 'read_user2pass';
- CREATE USER 'rw_user1'@'localhost' IDENTIFIED BY 'rw_user1pass';
要为每个用户分配其所需的权限, 可以使用 GRANT 与刚才显示的形式相同的语句, 但这需要列举每个用户的个人权限. 相反, 使用 GRANT 允许授权角色而非权限的替代语法:
- GRANT 'app_developer' TO 'dev1'@'localhost';
- GRANT 'app_read' TO 'read_user1'@'localhost', 'read_user2'@'localhost';
- GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';
结合角色所需的读取和写入权限, 在 GRANT 中授权 rw_user1 用户读取和写入的角色.
在 GRANT 授权角色的语法和授权用户的语法不同: 有一个 ON 来区分角色和用户的授权, 有 ON 的为用户授权, 而没有 ON 用来分配角色.
由于语法不同, 因此不能在同一语句中混合分配用户权限和角色.(允许为用户分配权限和角色, 但必须使用单独的 GRANT 语句, 每种语句的语法都要与授权的内容相匹配.)
2.2 检查角色权限
要验证分配给用户的权限, 使用 SHOW GRANTS. 例如:
- mysql> SHOW GRANTS FOR 'dev1'@'localhost';
- +-------------------------------------------------+
- | Grants for dev1@localhost |
- +-------------------------------------------------+
- | GRANT USAGE ON *.* TO `dev1`@`localhost` |
- | GRANT `app_developer`@`%` TO `dev1`@`localhost` |
- +-------------------------------------------------+
但是, 它会显示每个授予的角色, 而不会将其显示为角色所代表的权限. 如果要显示角色权限, 添加一个 USING 来显示:
- mysql> SHOW GRANTS FOR 'dev1'@'localhost' USING 'app_developer';
- +----------------------------------------------------------+
- | Grants for dev1@localhost |
- +----------------------------------------------------------+
- | GRANT USAGE ON *.* TO `dev1`@`localhost` |
- | GRANT ALL PRIVILEGES ON `app_db`.* TO `dev1`@`localhost` |
- | GRANT `app_developer`@`%` TO `dev1`@`localhost` |
- +----------------------------------------------------------+
同样验证其他类型的用户:
- mysql> SHOW GRANTS FOR 'read_user1'@'localhost' USING 'app_read';
- +--------------------------------------------------------+
- | Grants for read_user1@localhost |
- +--------------------------------------------------------+
- | GRANT USAGE ON *.* TO `read_user1`@`localhost` |
- | GRANT SELECT ON `app_db`.* TO `read_user1`@`localhost` |
- | GRANT `app_read`@`%` TO `read_user1`@`localhost` |
- +--------------------------------------------------------+
- mysql> SHOW GRANTS FOR 'rw_user1'@'localhost' USING 'app_read', 'app_write';
- +------------------------------------------------------------------------------+
- | Grants for rw_user1@localhost |
- +------------------------------------------------------------------------------+
- | GRANT USAGE ON *.* TO `rw_user1`@`localhost` |
- | GRANT SELECT, INSERT, UPDATE, DELETE ON `app_db`.* TO `rw_user1`@`localhost` |
- | GRANT `app_read`@`%`,`app_write`@`%` TO `rw_user1`@`localhost` |
- +------------------------------------------------------------------------------+
2.3 撤消角色或角色权限
正如可以授权某个用户的角色一样, 可以从帐户中撤销这些角色:
REVOKE role FROM user;
REVOKE 可以用于角色修改角色权限. 这不仅影响角色本身权限, 还影响任何授予该角色的用户权限. 假设想临时让所有用户只读, 使用 REVOKE 从该 app_write 角色中撤消修改权限 :
REVOKE INSERT, UPDATE, DELETE ON app_db.* FROM 'app_write';
碰巧, 某个角色完全没有任何权限, 正如可以看到的那样 SHOW GRANTS (这个语句可以和角色一起使用, 而不仅仅是查询用户权限可用):
- mysql> SHOW GRANTS FOR 'app_write';
- +---------------------------------------+
- | Grants for app_write@% |
- +---------------------------------------+
- | GRANT USAGE ON *.* TO `app_write`@`%` |
- +---------------------------------------+
从角色中撤销权限会影响到该角色中任何用户的权限, 因此 rw_user1 现在已经没有表修改权限 (INSERT, UPDATE, 和 DELETE 权限已经没有了):
- mysql> SHOW GRANTS FOR 'rw_user1'@'localhost'
- USING 'app_read', 'app_write';
- +----------------------------------------------------------------+
- | Grants for rw_user1@localhost |
- +----------------------------------------------------------------+
- | GRANT USAGE ON *.* TO `rw_user1`@`localhost` |
- | GRANT SELECT ON `app_db`.* TO `rw_user1`@`localhost` |
- | GRANT `app_read`@`%`,`app_write`@`%` TO `rw_user1`@`localhost` |
- +----------------------------------------------------------------+
实际上, rw_user1 读 / 写用户已成为只读用户. 对于被授予 app_write 角色的任何其他用户也会发生这种情况, 说明修改使用角色而不必修改个人帐户的权限.
要恢复角色的修改权限, 只需重新授予它们即可:
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';
现在 rw_user1 再次具有修改权限, 就像授权该 app_write 角色的其他任何帐户一样.
2.4 删除角色
要删除角色, 请使用 DROP ROLE:
DROP ROLE 'app_read', 'app_write';
删除角色会从授权它的每个帐户中撤消该角色.
2.5 角色和用户在实际中的应用
假设遗留应用开发项目在 MySQL 中的角色出现之前开始, 因此与该项目相关联的所有用户都是直接授予权限 (而不是授予角色权限). 其中一个帐户是最初被授予权限的开发者用户, 如下所示:
- CREATE USER 'old_app_dev'@'localhost' IDENTIFIED BY 'old_app_devpass';
- GRANT ALL ON old_app.* TO 'old_app_dev'@'localhost';
如果此开发人员离开项目, 则有必要将权限分配给其他用户, 或者项目参与人增多, 则可能需要多个用户. 以下是解决该问题的一些方法:
不使用角色: 更改帐户密码, 以便原始开发人员不能使用它, 并让新的开发人员使用该帐户:
ALTER USER 'old_app_dev'@'localhost' IDENTIFIED BY 'new_password';
使用角色: 锁定帐户以防止任何人使用它来连接服务器:
ALTER USER 'old_app_dev'@'localhost' ACCOUNT LOCK;
然后将该帐户视为角色. 对于每个新开发项目的开发者, 创建一个新帐户并授予其原始开发者帐户:
- CREATE USER 'new_app_dev1'@'localhost' IDENTIFIED BY 'new_password';
- GRANT 'old_app_dev'@'localhost' TO 'new_app_dev1'@'localhost';
其效果是将原始开发者帐户权限分配给新帐户.
MySQL8.0 的用户和角色管理也越来越像 Oracle 了, 8.0 中有不少新的特性, 变化还是很大的, 需要 DBA 不断的学习和测试, 更新对 MySQL 新版的认知, 更好地运维 MySQL 数据库. 未来 MySQL 数据库自治和智能数据库是必然发展趋势, 对 DBA 来说是解放, 也是挑战.
同时也非常感谢好友知名 MySQL 数据库专家吴炳锡老师在百忙中抽空对本文进行校对.
来源: http://database.51cto.com/art/201805/572096.htm