本篇文章给大家带来了mysql中账户的权限的相关问题，当用户执行任何数据库操作时，服务器将会验证用户是否具有相应的权限，例如查询表需要 select 权限，删除对象需要 drop 权限。希望对大家有帮助。
当客户端连接 mysql 服务器时，必须提供有效的身份认证，例如用户名和密码。当用户执行任何数据库操作时，服务器将会验证用户是否具有相应的权限，例如查询表需要 select 权限，删除对象需要 drop 权限。
为了方便用户权限的管理，mysql 8.0 提供了角色的功能。角色（role）是一组权限的集合。
本篇我们讨论 mysql 中的账户和权限的管理。
5.1 管理用户
5.1.1 创建用户
mysql 使用 create user 语句创建用户，基本语法如下：
create user [if not exists] account_nameidentified by 'password';
其中，account_name 是账户名称；账户名称分为两个部分：用户名（user_name）和主机名（host_name），使用 % 连接。identified by 用于指定用户的密码。if not exists 用于避免创建重名账户时产生错误信息。
以下语句创建一个新的用户 dev01，它可以从本机登录（localhost）：
mysql> create user dev01@localhost identified by 'dev01@mysql';query ok, 0 rows affected (0.21 sec)
mysql 中的账户由用户名和主机名共同决定，主机 office.example.com 上的 dev01 和主机 home.example.com 上的 dev01 是两个账户。如果不指定主机名，表示用户可以从任何主机登录：
user_nameuser_name@%
% 是通配符，表示任何字符串；另外，_ 表示任意单个字符。
如果用户名或主机名中包含特殊字符，例如空格或者 - ，需要使用引号分别引用这两部分内容：
'user-name'@'host-name'
除了单引号之外，也可以使用反引号（`）或者双引号（"）。
mysql 中的账户信息存储在系统数据库 mysql 的 user 表中：
mysql> select host, user from mysql.user;+-----------+------------------+| host | user |+-----------+------------------+| localhost | dev01 || localhost | mysql.infoschema || localhost | mysql.session || localhost | mysql.sys || localhost | root |+-----------+------------------+5 rows in set (0.00 sec)
除了 dev01@localhost 之外，其他 4 个用户都是初始化创建的系统用户。
除了基本语法之外，创建用户时还可以指定更多选项：
resource_option: { max_queries_per_hour count | max_updates_per_hour count | max_connections_per_hour count | max_user_connections count}
resource_option 用于限制该用户对系统资源的使用：
max_queries_per_hour，每小时允许执行的查询次数。默认为 0 ，表示没有限制；
max_updates_per_hour，每小时允许执行的更新次数。默认为 0 ，表示没有限制；
max_connections_per_hour，每小时允许执行的连接次数。默认为 0 ，表示没有限制；
max_user_connections，该用户并发连接的数量。默认为 0 ，表示没有限制；此时用户的并发连接数由系统变量 max_user_connections 决定。
以下语句创建一个新的账户 dev02，允许从任何主机登录。同时限制该用户每小时最多执行 1000 次查询和 100 次更新：
mysql> create user 'dev02'@'%' -> with max_queries_per_hour 1000 max_updates_per_hour 100;query ok, 0 rows affected (0.01 sec)
注意第二行的->是客户端的提示符，不是输入的内容。查询系统用户表可以显示以上设置：
mysql> select host, user, max_questions, max_updates from mysql.user;+-----------+------------------+---------------+-------------+| host | user | max_questions | max_updates |+-----------+------------------+---------------+-------------+| % | dev02 | 1000 | 100 || localhost | dev01 | 0 | 0 || localhost | mysql.infoschema | 0 | 0 || localhost | mysql.session | 0 | 0 || localhost | mysql.sys | 0 | 0 || localhost | root | 0 | 0 |+-----------+------------------+---------------+-------------+6 rows in set (0.00 sec)
以下是密码管理选项：
password_option: { password expire [default | never | interval n day] | password history {default | n} | password reuse interval {default | n day} | password require current [default | optional]}
密码管理选项可以用于设置密码的过期策略、重用策略和修改密码时的验证：
password expire，将密码立即设置为过期；password expire default，使用全局的密码过期策略，由系统变量 default_password_lifetime 决定；password expire never，密码永不过期；password expire interval n day 密码每隔 n 天过期；
password history default，使用全局的密码重用策略，由系统变量 password_history 决定；password history n，新密码与最近 n 次密码不能重复；
password reuse interval default，使用全局的密码重用策略（按照时间间隔指定），由系统变量 password_reuse_interval 决定；password reuse interval n day，新密码与最近 n 天内的密码不能重复；
password require current，用户修改密码时需要输入当前密码；password require current optional，用户修改密码时不需要输入当前密码；password require current default，使用全局策略，由系统变量 password_require_current 决定。
账户的密码选项同样可以通过 mysql.user 表查看：
mysql> select host,user, -> password_expired, password_last_changed, -> password_lifetime, password_reuse_history, -> password_reuse_time, password_require_current -> from mysql.user;+-----------+------------------+------------------+-----------------------+-------------------+------------------------+---------------------+--------------------------+| host | user | password_expired | password_last_changed | password_lifetime | password_reuse_history | password_reuse_time | password_require_current |+-----------+------------------+------------------+-----------------------+-------------------+------------------------+---------------------+--------------------------+| % | dev02 | n | 2019-09-23 15:02:47 | null | null | null | null || localhost | dev01 | n | 2019-09-23 14:23:39 | null | null | null | null || localhost | mysql.infoschema | n | 2019-08-28 10:07:39 | null | null | null | null || localhost | mysql.session | n | 2019-08-28 10:07:39 | null | null | null | null || localhost | mysql.sys | n | 2019-08-28 10:07:39 | null | null | null | null || localhost | root | n | 2019-08-28 10:07:44 | null | null | null | null |+-----------+------------------+------------------+-----------------------+-------------------+------------------------+---------------------+--------------------------+6 rows in set (0.00 sec)
以下是账户锁定选项：
lock_option: { account lock | account unlock}
该选项用于指定是否锁定账户，锁定的账户无法使用；默认为 account unlock，不锁定账户。
5.1.2 修改用户
alter user 语句可以修改用户的属性，修改用户的选项和创建用户相同。
首先是修改用户的密码。以下语句用于修改用户 dev01 的密码：
mysql> alter user dev01@localhost identified by 'dev01@2019';query ok, 0 rows affected (0.25 sec)
mysql 提供了 rename user 语句，用于修改用户名：
mysql> rename user dev02 to dev03;query ok, 0 rows affected (0.26 sec)
用户 dev02 被重命名为 dev03。
rename user 语句自动将旧用户的权限授予新用户，但是不会自动解决旧用户上的对象依赖。例如，某个存储过程的定义者为旧的用户名，并且使用定义者权限运行时，将会产生错误。
另一个常见的用户修改操作就是锁定账户和解锁账户：
mysql> alter user dev01@localhost account lock;query ok, 0 rows affected (0.13 sec)
用户 dev01 被锁定，此时无法使用该用户进行连接：
"c:\program files\mysql\mysql server 8.0\bin\mysql.exe" -u dev01 -penter password: **********error 3118 (hy000): access denied for user 'dev01'@'localhost'. account is locked.
系统变量 locked_connects 用于记录锁定账户尝试登录的次数：
mysql> show global status like 'locked_connects';+-----------------+-------+| variable_name | value |+-----------------+-------+| locked_connects | 1 |+-----------------+-------+1 row in set (0.00 sec)
最后我们将 dev01 进行解锁：
mysql> alter user dev01@localhost account unlock;query ok, 0 rows affected (0.10 sec)
5.1.3 删除用户
drop user 语句用于删除一个用户。以下语句将会删除用 dev03：
mysql> drop user dev03;query ok, 0 rows affected (0.14 sec)
如果被删除的用户已经连接到 mysql 服务器，用户可以继续执行操作；但是无法建立新的连接。
5.2 管理权限
新创建的用户默认只有 usage 权限，只能连接数据库，而没有任何操作权限。使用 show grants 命令可以查看用户的权限：
mysql> show grants for dev01@localhost;+-------------------------------------------+| grants for dev01@localhost |+-------------------------------------------+| grant usage on *.* to `dev01`@`localhost` |+-------------------------------------------+1 row in set (0.00 sec)
使用 grant 语句可以为用户授予权限。
5.2.1 授予权限
grant 语句基本语法如下：
grant privilege, ... on privilege_level to account_name;
grant 语句支持一次授予多个权限，使用逗号进行分隔。
privilege_level 指定权限的作用级别，包括：
全局权限，作用于 mysql 服务器中的所有数据库。全局权限使用*.*表示，例如，以下语句授予 dev01@localhost 用户查询所有数据库中的所有表的权限：
mysql> grant select -> on *.* -> to dev01@localhost;query ok, 0 rows affected (0.01 sec)mysql> show grants for dev01@localhost;+--------------------------------------------+| grants for dev01@localhost |+--------------------------------------------+| grant select on *.* to `dev01`@`localhost` |+--------------------------------------------+1 row in set (0.00 sec)
全局权限存储在 mysql.user 表中。
数据库权限，作用于指定数据库中的所有对象。数据库权限使用db_name.*表示，例如，以下语句授予 dev01@localhost 用户查询数据库 world 中的所有表的权限：
mysql> grant all -> on world.* -> to dev01@localhost;query ok, 0 rows affected (0.01 sec)mysql> show grants for dev01@localhost;+----------------------------------------------------------+| grants for dev01@localhost |+----------------------------------------------------------+| grant select on *.* to `dev01`@`localhost` || grant all privileges on `world`.* to `dev01`@`localhost` |+----------------------------------------------------------+2 rows in set (0.00 sec)
数据库权限存储在 mysql.db 表中。
表权限，作用于指定表的所有列。数据库权限使用db_name.table_name表示；如果不指定 db_name，使用默认数据库；如果没有默认数据库，将会返回错误。例如，以下语句授予 dev01@localhost 用户数据库 world 中country 表的增删改查权限：
mysql> grant select, insert, update, delete -> on world.country -> to dev01@localhost;query ok, 0 rows affected (0.01 sec)mysql> show grants for dev01@localhost;+----------------------------------------------------------------------------------+| grants for dev01@localhost |+----------------------------------------------------------------------------------+| grant select on *.* to `dev01`@`localhost` || grant all privileges on `world`.* to `dev01`@`localhost` || grant select, insert, update, delete on `world`.`country` to `dev01`@`localhost` |+----------------------------------------------------------------------------------+3 rows in set (0.00 sec)
表权限存储在 mysql.tables_priv 表中。
列权限，作用于指定表的指定列。每个列权限都需要指定具体的列名。例如，以下语句授予 dev01@localhost 用户在 world.country 表中 code 和 name 字段的查询权限，以及 population 字段的修改权限：
mysql> grant select(code, name), update(population) -> on world.country -> to dev01@localhost;query ok, 0 rows affected (0.01 sec)mysql> show grants for dev01@localhost;+----------------------------------------------------------------------------------------------------------------------------------+| grants for dev01@localhost |+----------------------------------------------------------------------------------------------------------------------------------+| grant select on *.* to `dev01`@`localhost` || grant all privileges on `world`.* to `dev01`@`localhost` || grant select, select (`code`, `name`), insert, update, update (`population`), delete on `world`.`country` to `dev01`@`localhost` |+----------------------------------------------------------------------------------------------------------------------------------+3 rows in set (0.00 sec)
列权限存储在 mysql.columns_priv 表中。
存储例程权限，作用于存储例程（函数和过程）。存储例程权限可以基于全局、数据库或者单个例程进行指定。以下语句授予 dev01@localhost 用户在数据库 world.country 中创建存储例程的权限：
mysql> grant create routine -> on world.* -> to dev01@localhost;query ok, 0 rows affected (0.02 sec)mysql> show grants for dev01@localhost;+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| grants for dev01@localhost |+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| grant select on *.* to `dev01`@`localhost`|| grant select, insert, update, delete, create, drop, references, index, alter, create temporary tables, lock tables, execute, create view, show view, alter routine, event, trigger on `world`.* to `dev01`@`localhost` || grant select, select (`code`, `name`), insert, update, update (`population`), delete on `world`.`country` to `dev01`@`localhost` |+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+3 rows in set (0.00 sec)
存储例程权限存储在 mysql.procs_priv 表中。
代理用户权限，允许用户作为其他用户的代理。代理用户拥有被代理用户的所有权限。以下语句将 dev01@localhost 用户设置为 root 用的代理：
mysql> grant proxy -> on root -> to dev01@localhost;query ok, 0 rows affected (0.01 sec)mysql> show grants for dev01@localhost;+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| grants for dev01@localhost |+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| grant select on *.* to `dev01`@`localhost` || grant select, insert, update, delete, create, drop, references, index, alter, create temporary tables, lock tables, execute, create view, show view, alter routine, event, trigger on `world`.* to `dev01`@`localhost` || grant select, select (`code`, `name`), insert, update, update (`population`), delete on `world`.`country` to `dev01`@`localhost` || grant proxy on 'root'@'%' to 'dev01'@'localhost' |+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+4 rows in set (0.00 sec)
代理用户权限存储在 mysql.proxies_priv 表中。
5.2.2 撤销权限
revoke 语句执行与 grant 语句相反的操作，撤销授予用户的权限。
revoke privilegee, ..on privilege_levelfrom account_name;
撤销权限的参数与授予权限时类似，以下语句撤销用户 dev01@localhost 所有的权限：
mysql> revoke all, grant option -> from dev01@localhost;query ok, 0 rows affected (0.01 sec)mysql> show grants for dev01@localhost;+--------------------------------------------------+| grants for dev01@localhost |+--------------------------------------------------+| grant usage on *.* to `dev01`@`localhost` || grant proxy on 'root'@'%' to 'dev01'@'localhost' |+--------------------------------------------------+2 rows in set (0.00 sec)
代理用户权限需要单独撤销：
mysql> revoke proxy -> on root -> from dev01@localhost;query ok, 0 rows affected (0.01 sec)mysql> show grants for dev01@localhost;+-------------------------------------------+| grants for dev01@localhost |+-------------------------------------------+| grant usage on *.* to `dev01`@`localhost` |+-------------------------------------------+1 row in set (0.00 sec)
用户 dev01@localhost 又恢复了初始的权限。
对于全局级别的权限，revoke 的效果在用户下次登录时生效；对于数据库级别的权限，revoke 的效果在执行 use 命令后生效；对于表级或者字段级别的权限，revoke 的效果随后的查询立即生效。
5.3 管理角色
当用户越来越多时，权限的管理也越来越复杂；而实际上，许多用户需要相同或类似的权限。为此，mysql 8.0 引入了一个新的特性：角色（role）。角色是一组权限的集合。
与账户类似，角色也可以授予权限；但是角色不能用于登录数据库。通过角色为用户授权的步骤如下：
创建一个角色；
为角色授权权限；
为用户指定角色。
5.3.1 创建角色
假设我们的应用需要使用 world 数据库。开发人员需要该数据库的完全访问权限，测试人员需要表的读写权限，业务分析人员需要查询数据的权限。
首先，使用create role语句创建 3 个角色：
mysql> create role devp_role, read_role, write_role;query ok, 0 rows affected (0.02 sec)
角色名称和账户名称类似，也可以包含 role_name 和 host_name 两部分，使用 @ 连接。
此时如果查询用户表：
mysql> select host,user,authentication_string from mysql.user;+-----------+------------------+------------------------------------------------------------------------+| host | user | authentication_string |+-----------+------------------+------------------------------------------------------------------------+| % | devp_role | || % | read_role | || % | write_role | || localhost | dev01 | $a$005$lw58qcu;qi|l`ktulchfhivfxy5dsyrymehjkjqko4mezqefufyt0zgye2 || localhost | mysql.infoschema | $a$005$thisisacombinationofinvalidsaltandpasswordthatmustneverbrbeused || localhost | mysql.session | $a$005$thisisacombinationofinvalidsaltandpasswordthatmustneverbrbeused || localhost | mysql.sys | $a$005$thisisacombinationofinvalidsaltandpasswordthatmustneverbrbeused || localhost | root | $a$005$kdqbw(
q*0uev;tykgue56d9kxifzptrsgvxkjvm23cyn5pge9dlro0et8 |+-----------+------------------+------------------------------------------------------------------------+8 rows in set (0.00 sec)
可以看出，角色实际上也是一个用户，但是没有密码。
5.3.2 为角色授权
为角色授权和用户授权类似，也是使用 grant 语句。我们分别为上面的 3 个角色分配权限：
mysql> grant all on world.* to devp_role;query ok, 0 rows affected (0.01 sec)mysql> grant select on world.* to read_role;query ok, 0 rows affected (0.01 sec)mysql> grant insert, update, delete on world.* to write_role;query ok, 0 rows affected (0.01 sec)
查看角色的权限和查询用户的权限类似：
mysql> show grants for devp_role;+------------------------------------------------------+| grants for devp_role@% |+------------------------------------------------------+| grant usage on *.* to `devp_role`@`%` || grant all privileges on `world`.* to `devp_role`@`%` |+------------------------------------------------------+2 rows in set (0.00 sec)
5.3.2 为用户指定角色
接下来我们创建几个用户，然后为他们分别指定角色。
mysql> create user devp1 identified by 'devp1@2019';query ok, 0 rows affected (0.01 sec)mysql> create user read1 identified by 'read1@2019';query ok, 0 rows affected (0.01 sec)mysql> create user test1 identified by 'test1@2019';query ok, 0 rows affected (0.04 sec)
为用户指定角色和授予权限类似，也是使用grant语句：
mysql> grant devp_role to devp1;query ok, 0 rows affected (0.01 sec)mysql> grant read_role to read1;query ok, 0 rows affected (0.01 sec)mysql> grant read_role, write_role to test1;query ok, 0 rows affected (0.01 sec)
再次查询用户的权限：
mysql> show grants for devp1;+--------------------------------------+| grants for devp1@% |+--------------------------------------+| grant usage on *.* to `devp1`@`%` || grant `devp_role`@`%` to `devp1`@`%` |+--------------------------------------+2 rows in set (0.00 sec)
如果想要知道用户通过角色获得的具体权限，可以使用using选项：
mysql> show grants for devp1 using devp_role;+--------------------------------------------------+| grants for devp1@% |+--------------------------------------------------+| grant usage on *.* to `devp1`@`%` || grant all privileges on `world`.* to `devp1`@`%` || grant `devp_role`@`%` to `devp1`@`%` |+--------------------------------------------------+3 rows in set (0.00 sec)
另外，也可以通过将一个用户授予另一个用户，实现权限的复制：
mysql> grant read1 to test1;query ok, 0 rows affected (0.09 sec)
用户是具有登录权限的角色，角色是不能登录的用户。
5.3.4 设置默认角色
使用 devp1 连接数据库：
"c:\program files\mysql\mysql server 8.0\bin\mysql.exe" -u devp1 -penter password: **********welcome to the mysql monitor. commands end with ; or \g.your mysql connection id is 14server version: 8.0.17 mysql community server - gplcopyright (c) 2000, 2019, oracle and/or its affiliates. all rights reserved.oracle is a registered trademark of oracle corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners.type 'help;' or '\h' for help. type '\c' to clear the current input statement.mysql> use world;error 1044 (42000): access denied for user 'devp1'@'%' to database 'world'
我们已经为用户 devp1 授予了 devp_role 角色，该角色拥有数据库 world 上的所有权限；错误的原因在于该角色没有自动激活。使用current_role()函数查看当前启动的角色：
mysql> select current_role();+----------------+| current_role() |+----------------+| none |+----------------+1 row in set (0.00 sec)
结果显示没有任何角色。set default role命令可以设置用户默认的活动角色：
mysql> set default role all -> to devp1;query ok, 0 rows affected (0.01 sec)
再次使用 devp1 连接数据库后，将会激活该用户所有的角色：
"c:\program files\mysql\mysql server 8.0\bin\mysql.exe" -u devp1 -penter password: **********welcome to the mysql monitor. commands end with ; or \g.your mysql connection id is 15server version: 8.0.17 mysql community server - gplcopyright (c) 2000, 2019, oracle and/or its affiliates. all rights reserved.oracle is a registered trademark of oracle corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners.type 'help;' or '\h' for help. type '\c' to clear the current input statement.mysql> select current_role();+-----------------+| current_role() |+-----------------+| `devp_role`@`%` |+-----------------+1 row in set (0.00 sec)mysql> use world;database changedmysql> select * from city limit 1;+----+-------+-------------+----------+------------+| id | name | countrycode | district | population |+----+-------+-------------+----------+------------+| 1 | kabul | afg | kabol | 1780000 |+----+-------+-------------+----------+------------+1 row in set (0.00 sec)
另一种方式就是使用set role命令设置当前会话的活动角色：
set role none;set role all;set role default;
以上语句分别表示不设置任何角色、设置所有角色以及设置默认的角色。
5.3.5 撤销角色的权限
撤销角色的权限与撤销用户的权限类似，撤销角色的权限同时会影响到具有该角色的用户。
以下语句撤销角色 write_role 的 delete 权限：
mysql> revoke delete -> on world.* -> from write_role;query ok, 0 rows affected (0.14 sec)
此时，用户 test1 上的相应权限也被撤销。
5.3.6 删除角色
drop role语句可以删除角色：
drop role role_name, ...;
删除角色的同时会撤销为用户指定的角色。以下语句将会删除角色 read_role 和 write_role：
mysql> drop role read_role, write_role;query ok, 0 rows affected (0.10 sec)
推荐学习：mysql视频教程
以上就是一起聊聊mysql中的账户和权限的详细内容。