mysql中账户和权限的示例分析

2025-03-04 技术教程

小编给大家分享一下mysql中账户和权限的示例分析，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！

当客户端连接 MySQL 服务器时，必须提供有效的身份认证，例如用户名和密码。当用户执行任何数据库操作时，服务器将会验证用户是否具有相应的权限，例如查询表需要 SELECT 权限，删除对象需要 DROP 权限。

为了方便用户权限的管理，MySQL 8.0 提供了角色的功能。角色（Role）是一组权限的集合。

本篇我们讨论 MySQL 中的账户和权限的管理。

5.1 管理用户

5.1.1 创建用户

MySQL 使用 CREATE USER 语句创建用户，基本语法如下：

CREATEUSER[IFNOTEXISTS]account_nameIDENTIFIEDBY'password';

其中，account_name 是账户名称；账户名称分为两个部分：用户名（user_name）和主机名（host_name），使用 % 连接。IDENTIFIED BY 用于指定用户的密码。IF NOT EXISTS 用于避免创建重名账户时产生错误信息。

以下语句创建一个新的用户 dev01，它可以从本机登录（localhost）：

mysql>CREATEUSERdev01@localhostIDENTIFIEDBY'Dev01@mysql';QueryOK,0rowsaffected(0.21sec)

MySQL 中的账户由用户名和主机名共同决定，主机 office.example.com 上的 dev01 和主机 home.example.com 上的 dev01 是两个账户。如果不指定主机名，表示用户可以从任何主机登录：

user_nameuser_name@%

% 是通配符，表示任何字符串；另外，_ 表示任意单个字符。

如果用户名或主机名中包含特殊字符，例如空格或者 - ，需要使用引号分别引用这两部分内容：

'user-name'@'host-name'

除了单引号之外，也可以使用反引号（`）或者双引号（"）。

MySQL 中的账户信息存储在系统数据库 mysql 的 user 表中：

mysql>selecthost,userfrommysql.user;+-----------+------------------+|host|user|+-----------+------------------+|localhost|dev01||localhost|mysql.infoschema||localhost|mysql.session||localhost|mysql.sys||localhost|root|+-----------+------------------+5rowsinset(0.00sec)

除了 dev01@localhost 之外，其他 4 个用户都是初始化创建的系统用户。

除了基本语法之外，创建用户时还可以指定更多选项：

resource_option:{MAX_QUERIES_PER_HOURcount|MAX_UPDATES_PER_HOURcount|MAX_CONNECTIONS_PER_HOURcount|MAX_USER_CONNECTIONScount}

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>CREATEUSER'dev02'@'%'->WITHMAX_QUERIES_PER_HOUR1000MAX_UPDATES_PER_HOUR100;QueryOK,0rowsaffected(0.01sec)

注意第二行的->是客户端的提示符，不是输入的内容。查询系统用户表可以显示以上设置：

mysql>selecthost,user,max_questions,max_updatesfrommysql.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|+-----------+------------------+---------------+-------------+6rowsinset(0.00sec)

以下是密码管理选项：

password_option:{PASSWORDEXPIRE[DEFAULT|NEVER|INTERVALNDAY]|PASSWORDHISTORY{DEFAULT|N}|PASSWORDREUSEINTERVAL{DEFAULT|NDAY}|PASSWORDREQUIRECURRENT[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>selecthost,user,->password_expired,password_last_changed,->password_lifetime,password_reuse_history,->password_reuse_time,password_require_current->frommysql.user;+-----------+------------------+------------------+-----------------------+-------------------+------------------------+---------------------+--------------------------+|host|user|password_expired|password_last_changed|password_lifetime|password_reuse_history|password_reuse_time|password_require_current|+-----------+------------------+------------------+-----------------------+-------------------+------------------------+---------------------+--------------------------+|%|dev02|N|2019-09-2315:02:47|NULL|NULL|NULL|NULL||localhost|dev01|N|2019-09-2314:23:39|NULL|NULL|NULL|NULL||localhost|mysql.infoschema|N|2019-08-2810:07:39|NULL|NULL|NULL|NULL||localhost|mysql.session|N|2019-08-2810:07:39|NULL|NULL|NULL|NULL||localhost|mysql.sys|N|2019-08-2810:07:39|NULL|NULL|NULL|NULL||localhost|root|N|2019-08-2810:07:44|NULL|NULL|NULL|NULL|+-----------+------------------+------------------+-----------------------+-------------------+------------------------+---------------------+--------------------------+6rowsinset(0.00sec)

以下是账户锁定选项：

lock_option:{ACCOUNTLOCK|ACCOUNTUNLOCK}

该选项用于指定是否锁定账户，锁定的账户无法使用；默认为 ACCOUNT UNLOCK，不锁定账户。

5.1.2 修改用户

ALTER USER 语句可以修改用户的属性，修改用户的选项和创建用户相同。

首先是修改用户的密码。以下语句用于修改用户 dev01 的密码：

mysql>ALTERUSERdev01@localhostIDENTIFIEDBY'Dev01@2019';QueryOK,0rowsaffected(0.25sec)

MySQL 提供了 RENAME USER 语句，用于修改用户名：

mysql>RENAMEUSERdev02TOdev03;QueryOK,0rowsaffected(0.26sec)

用户 dev02 被重命名为 dev03。

RENAME USER 语句自动将旧用户的权限授予新用户，但是不会自动解决旧用户上的对象依赖。例如，某个存储过程的定义者为旧的用户名，并且使用定义者权限运行时，将会产生错误。

另一个常见的用户修改操作就是锁定账户和解锁账户：

mysql>ALTERUSERdev01@localhostACCOUNTLOCK;QueryOK,0rowsaffected(0.13sec)

用户 dev01 被锁定，此时无法使用该用户进行连接：

"C:\ProgramFiles\MySQL\MySQLServer8.0\bin\mysql.exe"-udev01-pEnterpassword:**********ERROR3118(HY000):Accessdeniedforuser'dev01'@'localhost'.Accountislocked.

系统变量 Locked_connects 用于记录锁定账户尝试登录的次数：

最后我们将 dev01 进行解锁：

mysql>ALTERUSERdev01@localhostACCOUNTUNLOCK;QueryOK,0rowsaffected(0.10sec)

5.1.3 删除用户

DROP USER 语句用于删除一个用户。以下语句将会删除用 dev03：

mysql>DROPUSERdev03;QueryOK,0rowsaffected(0.14sec)

如果被删除的用户已经连接到 MySQL 服务器，用户可以继续执行操作；但是无法建立新的连接。

5.2 管理权限

新创建的用户默认只有 USAGE 权限，只能连接数据库，而没有任何操作权限。使用 SHOW GRANTS 命令可以查看用户的权限：

mysql>SHOWGRANTSFORdev01@localhost;+-------------------------------------------+|Grantsfordev01@localhost|+-------------------------------------------+|GRANTUSAGEON*.*TO`dev01`@`localhost`|+-------------------------------------------+1rowinset(0.00sec)

使用 GRANT 语句可以为用户授予权限。

5.2.1 授予权限

GRANT 语句基本语法如下：

GRANTprivilege,...ONprivilege_levelTOaccount_name;

GRANT 语句支持一次授予多个权限，使用逗号进行分隔。

privilege_level 指定权限的作用级别，包括：

全局权限，作用于 MySQL 服务器中的所有数据库。全局权限使用*.*表示，例如，以下语句授予 dev01@localhost 用户查询所有数据库中的所有表的权限：

mysql>GRANTSELECT->ON*.*->TOdev01@localhost;QueryOK,0rowsaffected(0.01sec)mysql>SHOWGRANTSFORdev01@localhost;+--------------------------------------------+|Grantsfordev01@localhost|+--------------------------------------------+|GRANTSELECTON*.*TO`dev01`@`localhost`|+--------------------------------------------+1rowinset(0.00sec)

全局权限存储在 mysql.user 表中。

数据库权限，作用于指定数据库中的所有对象。数据库权限使用db_name.*表示，例如，以下语句授予 dev01@localhost 用户查询数据库 world 中的所有表的权限：

mysql>GRANTALL->ONworld.*->TOdev01@localhost;QueryOK,0rowsaffected(0.01sec)mysql>SHOWGRANTSFORdev01@localhost;+----------------------------------------------------------+|Grantsfordev01@localhost|+----------------------------------------------------------+|GRANTSELECTON*.*TO`dev01`@`localhost`||GRANTALLPRIVILEGESON`world`.*TO`dev01`@`localhost`|+----------------------------------------------------------+2rowsinset(0.00sec)

数据库权限存储在 mysql.db 表中。

表权限，作用于指定表的所有列。数据库权限使用db_name.table_name表示；如果不指定 db_name，使用默认数据库；如果没有默认数据库，将会返回错误。例如，以下语句授予 dev01@localhost 用户数据库 world 中country 表的增删改查权限：

mysql>GRANTSELECT,INSERT,UPDATE,DELETE->ONworld.country->TOdev01@localhost;QueryOK,0rowsaffected(0.01sec)mysql>SHOWGRANTSFORdev01@localhost;+----------------------------------------------------------------------------------+|Grantsfordev01@localhost|+----------------------------------------------------------------------------------+|GRANTSELECTON*.*TO`dev01`@`localhost`||GRANTALLPRIVILEGESON`world`.*TO`dev01`@`localhost`||GRANTSELECT,INSERT,UPDATE,DELETEON`world`.`country`TO`dev01`@`localhost`|+----------------------------------------------------------------------------------+3rowsinset(0.00sec)

表权限存储在 mysql.tables_priv 表中。

列权限，作用于指定表的指定列。每个列权限都需要指定具体的列名。例如，以下语句授予 dev01@localhost 用户在 world.country 表中 code 和 name 字段的查询权限，以及 population 字段的修改权限：

mysql>GRANTSELECT(code,name),UPDATE(population)->ONworld.country->TOdev01@localhost;QueryOK,0rowsaffected(0.01sec)mysql>SHOWGRANTSFORdev01@localhost;+----------------------------------------------------------------------------------------------------------------------------------+|Grantsfordev01@localhost|+----------------------------------------------------------------------------------------------------------------------------------+|GRANTSELECTON*.*TO`dev01`@`localhost`||GRANTALLPRIVILEGESON`world`.*TO`dev01`@`localhost`||GRANTSELECT,SELECT(`code`,`name`),INSERT,UPDATE,UPDATE(`population`),DELETEON`world`.`country`TO`dev01`@`localhost`|+----------------------------------------------------------------------------------------------------------------------------------+3rowsinset(0.00sec)

列权限存储在 mysql.columns_priv 表中。

存储例程权限，作用于存储例程（函数和过程）。存储例程权限可以基于全局、数据库或者单个例程进行指定。以下语句授予 dev01@localhost 用户在数据库 world.country 中创建存储例程的权限：

mysql>GRANTCREATEROUTINE->ONworld.*->TOdev01@localhost;QueryOK,0rowsaffected(0.02sec)mysql>SHOWGRANTSFORdev01@localhost;+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|Grantsfordev01@localhost|+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|GRANTSELECTON*.*TO`dev01`@`localhost`||GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,INDEX,ALTER,CREATETEMPORARYTABLES,LOCKTABLES,EXECUTE,CREATEVIEW,SHOWVIEW,ALTERROUTINE,EVENT,TRIGGERON`world`.*TO`dev01`@`localhost`||GRANTSELECT,SELECT(`code`,`name`),INSERT,UPDATE,UPDATE(`population`),DELETEON`world`.`country`TO`dev01`@`localhost`|+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+3rowsinset(0.00sec)

存储例程权限存储在 mysql.procs_priv 表中。

代理用户权限，允许用户作为其他用户的代理。代理用户拥有被代理用户的所有权限。以下语句将 dev01@localhost 用户设置为 root 用的代理：

mysql>GRANTPROXY->ONroot->TOdev01@localhost;QueryOK,0rowsaffected(0.01sec)mysql>SHOWGRANTSFORdev01@localhost;+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|Grantsfordev01@localhost|+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|GRANTSELECTON*.*TO`dev01`@`localhost`||GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,INDEX,ALTER,CREATETEMPORARYTABLES,LOCKTABLES,EXECUTE,CREATEVIEW,SHOWVIEW,ALTERROUTINE,EVENT,TRIGGERON`world`.*TO`dev01`@`localhost`||GRANTSELECT,SELECT(`code`,`name`),INSERT,UPDATE,UPDATE(`population`),DELETEON`world`.`country`TO`dev01`@`localhost`||GRANTPROXYON'root'@'%'TO'dev01'@'localhost'|+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+4rowsinset(0.00sec)

代理用户权限存储在 mysql.proxies_priv 表中。

5.2.2 撤销权限

REVOKE 语句执行与 GRANT 语句相反的操作，撤销授予用户的权限。

REVOKEprivilegee,..ONprivilege_levelFROMaccount_name;

撤销权限的参数与授予权限时类似，以下语句撤销用户 dev01@localhost 所有的权限：

mysql>REVOKEALL,GRANTOPTION->FROMdev01@localhost;QueryOK,0rowsaffected(0.01sec)mysql>SHOWGRANTSFORdev01@localhost;+--------------------------------------------------+|Grantsfordev01@localhost|+--------------------------------------------------+|GRANTUSAGEON*.*TO`dev01`@`localhost`||GRANTPROXYON'root'@'%'TO'dev01'@'localhost'|+--------------------------------------------------+2rowsinset(0.00sec)

代理用户权限需要单独撤销：

mysql>REVOKEPROXY->ONroot->FROMdev01@localhost;QueryOK,0rowsaffected(0.01sec)mysql>SHOWGRANTSFORdev01@localhost;+-------------------------------------------+|Grantsfordev01@localhost|+-------------------------------------------+|GRANTUSAGEON*.*TO`dev01`@`localhost`|+-------------------------------------------+1rowinset(0.00sec)

用户 dev01@localhost 又恢复了初始的权限。

对于全局级别的权限，REVOKE 的效果在用户下次登录时生效；对于数据库级别的权限，REVOKE 的效果在执行 USE 命令后生效；对于表级或者字段级别的权限，REVOKE 的效果随后的查询立即生效。

5.3 管理角色

当用户越来越多时，权限的管理也越来越复杂；而实际上，许多用户需要相同或类似的权限。为此，MySQL 8.0 引入了一个新的特性：角色（Role）。角色是一组权限的集合。

与账户类似，角色也可以授予权限；但是角色不能用于登录数据库。通过角色为用户授权的步骤如下：

创建一个角色；

为角色授权权限；

为用户指定角色。

5.3.1 创建角色

假设我们的应用需要使用 world 数据库。开发人员需要该数据库的完全访问权限，测试人员需要表的读写权限，业务分析人员需要查询数据的权限。

首先，使用CREATE ROLE语句创建 3 个角色：

mysql>CREATEROLEdevp_role,read_role,write_role;QueryOK,0rowsaffected(0.02sec)

角色名称和账户名称类似，也可以包含 role_name 和 host_name 两部分，使用 @ 连接。

此时如果查询用户表：

mysql>SELECThost,user,authentication_stringFROMmysql.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|+-----------+------------------+------------------------------------------------------------------------+8rowsinset(0.00sec)

可以看出，角色实际上也是一个用户，但是没有密码。

5.3.2 为角色授权

为角色授权和用户授权类似，也是使用 GRANT 语句。我们分别为上面的 3 个角色分配权限：

mysql>GRANTALLONworld.*TOdevp_role;QueryOK,0rowsaffected(0.01sec)mysql>GRANTSELECTONworld.*TOread_role;QueryOK,0rowsaffected(0.01sec)mysql>GRANTINSERT,UPDATE,DELETEONworld.*TOwrite_role;QueryOK,0rowsaffected(0.01sec)

查看角色的权限和查询用户的权限类似：

mysql>SHOWGRANTSFORdevp_role;+------------------------------------------------------+|Grantsfordevp_role@%|+------------------------------------------------------+|GRANTUSAGEON*.*TO`devp_role`@`%`||GRANTALLPRIVILEGESON`world`.*TO`devp_role`@`%`|+------------------------------------------------------+2rowsinset(0.00sec)

5.3.2 为用户指定角色

接下来我们创建几个用户，然后为他们分别指定角色。

mysql>CREATEUSERdevp1IDENTIFIEDBY'Devp1@2019';QueryOK,0rowsaffected(0.01sec)mysql>CREATEUSERread1IDENTIFIEDBY'Read1@2019';QueryOK,0rowsaffected(0.01sec)mysql>CREATEUSERtest1IDENTIFIEDBY'Test1@2019';QueryOK,0rowsaffected(0.04sec)

为用户指定角色和授予权限类似，也是使用GRANT语句：

mysql>GRANTdevp_roleTOdevp1;QueryOK,0rowsaffected(0.01sec)mysql>GRANTread_roleTOread1;QueryOK,0rowsaffected(0.01sec)mysql>GRANTread_role,write_roleTOtest1;QueryOK,0rowsaffected(0.01sec)

再次查询用户的权限：

mysql>SHOWGRANTSFORdevp1;+--------------------------------------+|Grantsfordevp1@%|+--------------------------------------+|GRANTUSAGEON*.*TO`devp1`@`%`||GRANT`devp_role`@`%`TO`devp1`@`%`|+--------------------------------------+2rowsinset(0.00sec)

如果想要知道用户通过角色获得的具体权限，可以使用USING选项：

mysql>SHOWGRANTSFORdevp1USINGdevp_role;+--------------------------------------------------+|Grantsfordevp1@%|+--------------------------------------------------+|GRANTUSAGEON*.*TO`devp1`@`%`||GRANTALLPRIVILEGESON`world`.*TO`devp1`@`%`||GRANT`devp_role`@`%`TO`devp1`@`%`|+--------------------------------------------------+3rowsinset(0.00sec)

另外，也可以通过将一个用户授予另一个用户，实现权限的复制：

mysql>GRANTread1TOtest1;QueryOK,0rowsaffected(0.09sec)

用户是具有登录权限的角色，角色是不能登录的用户。

5.3.4 设置默认角色

使用 devp1 连接数据库：

我们已经为用户 devp1 授予了 devp_role 角色，该角色拥有数据库 world 上的所有权限；错误的原因在于该角色没有自动激活。使用CURRENT_ROLE()函数查看当前启动的角色：

mysql>SELECTcurrent_role();+----------------+|current_role()|+----------------+|NONE|+----------------+1rowinset(0.00sec)

结果显示没有任何角色。SET DEFAULT ROLE命令可以设置用户默认的活动角色：

mysql>SETDEFAULTROLEALL->TOdevp1;QueryOK,0rowsaffected(0.01sec)

再次使用 devp1 连接数据库后，将会激活该用户所有的角色：

"C:\ProgramFiles\MySQL\MySQLServer8.0\bin\mysql.exe"-udevp1-pEnterpassword:**********WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis15Serverversion:8.0.17MySQLCommunityServer-GPLCopyright(c)2000,2019,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>SELECTcurrent_role();+-----------------+|current_role()|+-----------------+|`devp_role`@`%`|+-----------------+1rowinset(0.00sec)mysql>useworld;Databasechangedmysql>select*fromcitylimit1;+----+-------+-------------+----------+------------+|ID|Name|CountryCode|District|Population|+----+-------+-------------+----------+------------+|1|Kabul|AFG|Kabol|1780000|+----+-------+-------------+----------+------------+1rowinset(0.00sec)

另一种方式就是使用SET ROLE命令设置当前会话的活动角色：

SETROLENONE;SETROLEALL;SETROLEDEFAULT;

以上语句分别表示不设置任何角色、设置所有角色以及设置默认的角色。

5.3.5 撤销角色的权限

撤销角色的权限与撤销用户的权限类似，撤销角色的权限同时会影响到具有该角色的用户。

以下语句撤销角色 write_role 的 DELETE 权限：

mysql>REVOKEDELETE->ONworld.*->FROMwrite_role;QueryOK,0rowsaffected(0.14sec)

此时，用户 test1 上的相应权限也被撤销。

5.3.6 删除角色

DROP ROLE语句可以删除角色：

DROPROLErole_name,...;

删除角色的同时会撤销为用户指定的角色。以下语句将会删除角色 read_role 和 write_role：