MySQL用户也可以是个角色 _生活百科

前言角色（Role），可以认为是一些权限的集合，一直是存在各个数据库中，比如Oracle、SQL Server、OceanBase等，MySQL 自从 8.0 release 才引入角色这个概念。为用户赋予统一的角色，即把一个带有某些权限集合的角色分配给一个用户，那该用户就拥有了该角色所包含的所有权限，权限的修改直接通过角色来进行，无需为每个用户单独授权，大大的方便了权限管理。一、回顾MySQL 8.0之前的用户管理1、创建用户(默认缺省)从 CREATE USER 的 help 语法解释中发现，其不仅可以创建新的 MySQL 帐户，还支持为新帐户建立身份验证、SSL/TLS、资源限制和密码管理属性，并控制帐户最初是锁定还是解锁。那么，缺省选项默认创建的用户是怎么样的呢？mysql> CREATE USER kuzma IDENTIFIED BY 'iamkuzma';Query OK, 0 rows affected (0.07 sec)mysql> select * from mysql.user where User="kuzma"\G*************************** 1. row ***************************Host: %User: kuzmaSelect_priv: NInsert_priv: NUpdate_priv: NDelete_priv: NCreate_priv: NDrop_priv: NReload_priv: NShutdown_priv: NProcess_priv: NFile_priv: NGrant_priv: NReferences_priv: NIndex_priv: NAlter_priv: NShow_db_priv: NSuper_priv: N Create_tmp_table_priv: NLock_tables_priv: NExecute_priv: NRepl_slave_priv: NRepl_client_priv: NCreate_view_priv: NShow_view_priv: NCreate_routine_priv: NAlter_routine_priv: NCreate_user_priv: NEvent_priv: NTrigger_priv: NCreate_tablespace_priv: Nssl_type:ssl_cipher: 0xx509_issuer: 0xx509_subject: 0xmax_questions: 0max_updates: 0max_connections: 0max_user_connections: 0plugin: mysql_native_password authentication_string: *E2D9547BC3A2AD2EEC6BE9636489AC973656EEB9password_expired: N password_last_changed: 2022-10-05 17:41:06password_lifetime: NULLaccount_locked: N1 row in set (0.06 sec)如上，在 CREATE USER 后会在 mysql.user 系统表中注册相关用户信息，未指定的属性设置为其默认值：

帐户名的主机名部分，如果省略，则默认为'%'，即 'kuzma'@'%'，表示不限主机连接
Authentication：系统变量定义的认证插件 default_authentication_plugin = mysql_native_password ，空凭证
SSL/TLS：NONE
资源限制：无限制，max_questions最大查询数、max_updates最大更新数等都为0（默认值），即未做限制
密码管理：PASSWORD EXPIRE DEFAULT
密码过期：NONE，未设定，即永不过期
账户锁定：ACCOUNT UNLOCK，未锁定

首次创建的帐户没有权限，xxx_priv都为NONE，默认未设置用户权限，若需要分配权限，另外的通过 GRANT 语句为用户授权。若要删除用户，DROP USER 'kuzma'@'%'; 注意指定用户名和主机名。2、带选项创建用户2.1、设置密码过期# 将密码标记为过期，用户在第一次连接到服务器时必须选择一个新密码CREATE USER 'kuzma'@'localhost' PASSWORD EXPIRE;# 默认过期时间，应用 default_password_lifetime 系统变量指定的全局过期策略CREATE USER 'kuzma'@'localhost' PASSWORD EXPIRE DEFAULT;# 禁用密码过期，永不过期CREATE USER 'kuzma'@'localhost' PASSWORD EXPIRE NEVER;# 设定过期时间，每 180 天选择一个新密码CREATE USER 'kuzma'@'localhost' PASSWORD EXPIRE INTERVAL 180 DAY;2.2、身份验证及SSL/TLS# 指定身份验证插件，及明文密码值CREATE USER 'kuzma'@'localhost'IDENTIFIED WITH mysql_native_password BY 'iamkuzma';# CREATE USER 时指定 tls_option 值# 注：生产数据库通常不会做加密连接，减少不必要的连接限制tls_option: {SSL | X509 | CIPHER 'cipher' | ISSUER 'issuer' | SUBJECT 'subject'}# 默认缺省为REQUIRE NONE，即没有 SSL 或 X.509 要求，如果用户名和密码有效，则允许未加密的连接CREATE USER 'kuzma'@'localhost' REQUIRE NONE;# 加密连接限制，如果客户端无法建立安全连接，则连接尝试失败CREATE USER 'kuzma'@'localhost' REQUIRE SSL;2.3、资源限制resource_option: {MAX_QUERIES_PER_HOUR count# 允许该用户每小时多少查询| MAX_UPDATES_PER_HOUR count# 允许该用户每小时多少更新| MAX_CONNECTIONS_PER_HOUR count# 允许该用户每小时多少连接到服务器| MAX_USER_CONNECTIONS count# 限制该用户同时连接到服务器的最大数量}# 限制用户每小时的查询和更新数CREATE USER 'kuzma'@'localhost' WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100;【MySQL用户也可以是个角色】如果count为0（默认值），表示该用户没有限制。通常生产用户不会在数据库层做资源限制，但某些特殊用户特殊场景下，可通过进行资源限制实现限流。
2.4、账户锁定CREATE USER 和 ALTER USER 中使用 ACCOUNT LOCK 和 ACCOUNT UNLOCK 选项支持用户的锁定和解锁；常见场景：数据库、业务迁移，用户切新改造等，通过对用户的锁定和解锁，实现对业务连接控制。3、授权用户使用 GRANT 给用户授权，REVOKE 给用户撤权（需要操作者拥有 GRANT OPTION 权限）。支持用户对库、表、列、存储过程、代理用户# 授权[kuzma_write]用户{查询、插入、更新、删除}所有库表(*表示所有，db1.*表示db1库下的所有表)GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO kuzma_write;# 撤销[kuzma_write]用户的删除权限REVOKE DELETE ON *.* FROM 'kuzma_write'@'%';# 设置列权限-----虽然但是有时业务还就是有这样的需求# 1.列权限类型有且仅包括INSERT、REFERENCES(创建外键约束)、SELECT 和 UPDATE，权限后括号内给定表中的一个或多个列# 2.列权限存储在 mysql.columns_priv，其中的Column_priv列是一个枚举列，亦可说明为什么列权限"有且仅"4种权限GRANT SELECT (col1), INSERT (col1, col2) ON db1.tbl2 TO 'xxx_user'@'xxx_host';# 授权存储过程的创建和执行GRANT CREATE ROUTINE ON db1.* TO 'xxx_user'@'xxx_host';GRANT EXECUTE ON PROCEDURE db1.proc2 TO 'xxx_user'@'xxx_host';# 代理用户# 该PROXY权限使一个用户可以成为另一个用户的代理，代理用户冒充或冒用被代理用户的身份，实现权限的复制GRANT PROXY ON 'localuser'@'localhost' TO 'xxx_user'@'xxx_host';