SQL 数据基础原理 12

用户和权限管理学习目标了解用户和权限，MySQL数据库的权限表掌握createuser创建用户的用法掌握alteruser设置密码的方法掌握grant授予用户权限的使用方法掌握revoke权限收回的使用掌握角色权限的授予及收回章节内容13.1用户与权限13.2用户管理 13.3权限管理 13.4MySQL8.0的新特性——角色管理 13.1用户与权限

13.1.1user表 13.1.2db表13.1.3tables_priv表和columns_priv表 13.1.4procs_priv表13.1.5访问控制过程 13.1用户与权限数据库的安全性是指只允许合法用户进行其权限范围内的数据库相关操作，保护数据库以防止任何不合法的使用所造成的数据泄露、更改或破坏。MySQL提供了用户认证、授权等来实现和维护数据的安全，以避免用户恶意攻击或者越权访问数据库中的数据对象，并能根据不同用户分配在数据库中的权限。也就是说数据库安全性措施主要涉及用户认证和访问权限两个方面的问题。13.1用户与权限MySQL用户主要包括系统用户和普通用户。root用户是系统管理员，拥有操作MySQL数据库的所有权限。普通用户只拥有创建该用户时赋予它的权限。在安装MySQL数据库时，系统会自动安装一个名为mysql的系统数据库，该数据库主要用于维护数据库的用户以及权限的控制和管理。mysql系统数据库中包含有关于用户帐户和用户持有的权限信息的授权表，分别是user（存储全局权限表）、db（存储数据库层级权限表）、tables_priv（存储表层级权限表）、columns_priv（存储列层级权限表）、procs_priv（存储存储过程和存储函数权限表）等。当MySQL服务启动时，会读取MySQL中的权限表，并将表中的数据加载到内存，当用户进行数据库访问操作时，MySQL会根据权限表中的内容对用户做相应的权限控制。13.1.1user表MySQL中的所有用户信息都保存在user表中。user表是mysql数据库中最重要的一个表。它记录了允许连接到服务器的账号信息及一些全局级的权限信息。user表账号字段确定是拒绝还是允许传入连接。对于允许的连接，用户表中授予的任何权限都表示用户的全局权限。此表中授予的任何权限都适用于服务器上的所有数据库。MySQL8.0.17中user表有51个字段，这些字段共分为4类，分别是账号字段、权限字段、安全字段和资源控制字段。可以通过“descuser;”命令来查看user表的结构。13.1.2db表db表账号字段（Host、DB、User）三个字段组合成复合主键，来确定哪些用户可以从哪些主机访问哪些数据库。剩下的权限字段决定允许的操作。在数据库级别授予的权限适用于数据库和数据库中的所有对象，如表和存储程序。输入命令descdb;可以查看db表的结构。13.1.3tables_priv表和columns_priv表tables_priv和columns_priv表，与db表类似，但具有更小的粒度：它们应用于表和列级别，而不是数据库级别。在表级别授予的权限适用于表及其所有列。在列级别授予的权限仅适用于特定列。tables_priv表可以对单个表进行权限设置，tables_priv表包含8个字段，前4个字段Host、Db、User、Table_name分别表示主机名、数据库名、用户名和表名。后4个字段Grantor、Timestamp、Table_priv和Column_priv。分别表示权限是谁设置的、修改权限的时间、对表进行操作的权限和对列的操作权限。columns_priv表可以对单个数据列进行权限设置，包含7个字段。前5个字段Host、Db、User、Table_name和Column_name分别表示主机名、数据库名、用户名、表名和列名。Timestamp和Column_priv表示修改权限的时间和对表中的数据列进行操作的权限。13.1.4procs_priv表procs_priv表适用于存储例程（存储过程和函数）。在例程级别授予的权限仅适用于单个过程或函数。输入命令：descprocs_priv;查看procs_pric表的结构。procs_priv表包含8个字段，分别是Host、Db、User、Routine_name、Routine_type、Grantor、Proc_priv和Timestamp。分别表示主机名、数据库名、用户名、例程的名称、例程的类型、存储权限是谁设置的、拥有的权限和更新的时间。13.1.5访问控制过程 当客户端连接到MySQL服务器时，它会经历两个访问控制阶段：连接验证阶段和请求验证阶段。1.连接验证阶段当用户尝试连接到MySQL服务器时，服务器会根据以下条件接受或拒绝连接：（1）用户的身份以及是否可以通过提供正确的密码来验证其身份。（2）用户的账号是锁定还是解锁。服务器首先检查凭证，然后检查账号锁定状态。任何一个步骤的失败都会导致服务器完全拒绝对用户的访问。否则，服务器接受连接，然后进入第2阶段并等待请求。使用三个执行凭证检查user表账号字段（Host，User和authentication_string）。锁定状态记录在user表account_locked列中。服务器仅在当某些user表行中的Host列和User列与客户端主机名和用户名匹配，客户端提供该行中指定的密码，并且account_locked值为“N”时，服务器才接受连接。13.1.5访问控制过程 2.请求验证阶段在请求验证阶段，服务器会检查用户是否有足够的权限执行每项操作。建立连接后，服务器进入访问控制的第2阶段。对于通过该连接发出的每个请求，服务器将确定要执行的操作，然后检查用户是否有足够的权限执行该操作。这就是权限表中的权限字段发挥作用的地方。这些权限可以来自user、db、tables_priv、columns_priv或procs_priv表中的任何一个。MySQL接受到用户的操作请求时，首先确认用户是否有权限。首先检查user表，即先检查全局权限表user，如果user中对应的权限为“Y”，则此用户对所有数据库的权限为“Y”，将不再检查db、tables_priv、columns_priv表；如果为“N”，则再从db表中检查此用户对应的具体数据库，并得到db中的“Y”的权限；如果db中为“N”，则检查tables_priv及columns_priv表中此数据库对应的具体表，取得表中的权限“Y”，以此类推。如果所有权限表都检查完毕，依旧没有找到允许的权限操作，MySQL服务器将返回错误信息，用户操作不能执行，操作失败。13.2用户管理

13.2.1添加普通用户13.2.2普通用户修改自己的密码 13.2.3root用户修改自己的密码和普通用户的密码13.2.4修改用户名 13.2.5删除普通用户 13.2.6MySQL8中root用户密码丢失的解决办法 13.2.用户管理MySQL用户包括普通用户和系统用户。这两种用户的权限是不一样的。root用户是系统管理员，拥有所有的权限。从MySQL8.0.16开始，MySQL引入了基于SYSTEM_USER权限的用户账号类别的概念。结合用户账号类别的概念，根据系统用户和普通用户是否具有SYSTEM_USER权限来区分它们。系统用户：具有SYSTEM_USER权限的用户。普通用户：没有SYSTEM_USER特权的用户。系统用户可以修改系统账号和普通账号。系统账号只能由具有适当权限的系统用户修改，而不能由普通用户修改。具有适当权限的普通用户可以修改普通账号，但不能修改系统账号。普通账号可以由具有适当权限的系统用户和普通用户修改。13.2.1添加普通用户 要使用CREATEUSER，必须具有全局CREATEUSER权限或mysql系统数据库的INSERT权限。启用只读系统变量时，CREATEUSER还需要CONNECTION_ADMIN或SUPER权限。基本语法格式为：CREATEUSER[IFNOTEXISTS]user[auth_option][,user[auth_option]]...DEFAULTROLErole[,role]...[REQUIRE{NONE|tls_option[[AND]tls_option]...}][WITHresource_option[resource_option]...][password_option|lock_option]...13.2.1添加普通用户 其中：（1）user：用户，由用户名和主机名组成，格式为'user_name'@'host_name'。（2）auth_option：用户身份验证选项，下面几种情况中选其一。IDENTIFIEDBY‘auth_string’：默认用户身份验证插件（caching_sha2_password。IDENTIFIEDWITHauth_plugin：使用指定的身份验证插件auth_plugin对空字符串（未设置用户密码）进行加密。IDENTIFIEDWITHauth_pluginBY'auth_string'IDENTIFIEDWITHauth_pluginAS'hash_string’（3）DEFAULTROLE：默认角色（无），role：角色。（4）tls_option:加密选项，默认是无。13.2.1添加普通用户 （5）resource_option:资源限制选项。默认是无限。可以从下列值中选取一个或多个。MAX_QUERIES_PER_HOURcount：每小时最大查询数。MAX_UPDATES_PER_HOURcount：每小时最大更新数。MAX_CONNECTIONS_PER_HOURcount：每小时最大连接数MAX_USER_CONNECTIONScount：最大用户连接数。（6）password_option：密码管理选项。PASSWORDEXPIRE[DEFAULT|NEVER|INTERVALNDAY]PASSWORDHISTORY{DEFAULT|N}PASSWORDREUSEINTERVAL{DEFAULT|NDAY}PASSWORDREQUIRECURRENT[DEFAULT|OPTIONAL]FAILED_LOGIN_ATTEMPTSNPASSWORD_LOCK_TIME{N|UNBOUNDED}（7）lock_option:账号锁定选项，有ACCOUNTLOCK（账号锁定）或ACCOUNTUNLOCK（账号解锁）两种情况。13.2.1添加普通用户 1.创建最简单的用户如果不指定主机地址，则将采用%（任意主机）。基本语法格式为：createuser'用户名'@'主机地址';【例13-1】添加一个新的用户xulixia，不指定主机和密码。SQL语句为：createuser'xulixia';执行语句后，可以在mysql.user表中进行查询，输入代码为：mysql>selecthost,user,plugin,authentication_stringfromuser->whereuser='xulixia’;注意：在创建用户时，若不指定主机地址、密码以及相关的用户选项，则表示此用户在访问MySQL服务器时，不限定客户端、不需要密码。13.2.1添加普通用户 2.创建含有密码的用户创建含有密码的用户，需要用到IDENTIFIEDBY子句。基本语法格式为：createuser'用户名'@'主机地址'identified[with‘身份验证插件类型’]by'密码';【例13-2】创建三个用户。用户zhang，不指定主机，密码为‘123456’。用户zhangsan，主机名为localhost，密码为‘123456’。用户wang，主机名为202.206.5.10，密码为‘123456’。（1）创建新用户zhang，SQL语句为：createuser'zhang'identifiedby'123456';（2）创建新用户zhangsan，SQL语句为：createuserzhangsan@localhostidentifiedby'123456';（3）创建新用户wang，主机名为202.206.5.10，密码为‘123456’。createuser'wang'@'202.206.5.10'identifiedby'123456';13.2.1添加普通用户 注意：创建用户时，用户主机名可以将引号省略。如果两个用户具有相同的用户名但主机不同，MySQL将其视为不同的用户，允许为这两个用户分配不同的权限集合。如果没有输入密码，那么MySQL允许相关的用户不使用密码登录。但是从安全的角度并不推荐这种做法。【例13-3】创建用户test1，指明验证密码的插件名为mysql_native_password，指定密码为‘123456’。指定密码验证插件需要用到IDENTIFIEDWITH语句。SQL语句为：createuser'test1'@'localhost'identifiedwith'mysql_native_password'by'123456';执行语句后，结果为“ERROR1524(HY000):Plugin'mysql_native_password'isnotloaded”。这是因为在MySQL8.4LTS版本中，默认情况下，mysql_native_password插件被禁用，如果需要使用该认证插件，就要先启用。如果需要启用该插件，需要在启动时指定相关参数或在配置文件中进行配置。13.2.1添加普通用户 设置方式为：如果只是在该版本中添加mysql_native_password插件，不改变原有默认的认证插件caching_sha2_password，可以通过修改MySQL配置文件并重启服务来实现。首先在D:\ProgramData\MySQL\MySQLServer8.4（本机）目录下，找到并打开配置文件my.ini，在[mysqld]部分添加一行：mysql_native_password=ON，保存文件。重启MySQL服务。重新登录MySQL后执行：SELECTplugin_name,plugin_statusFROMinformation_schema.pluginsWHEREplugin_name='mysql_native_password';可以看到如下结果，则表明mysql_native_password插件已经加载了。+-----------------------+---------------+|plugin_name|plugin_status|+-----------------------+---------------+|mysql_native_password|ACTIVE|+-----------------------+---------------+13.2.1添加普通用户 【例13-4】创建使用默认身份验证插件和给定密码的用户，将密码标记为过期，以便用户必须在第一次连接到服务器时设置新密码。SQL语句及执行结果为：mysql>createuseruser1@localhostidentifiedby'111111'passwordexpire;这种情况下，用户user1第一次登录到MySQL服务器，执行任何命令，都会出现如下提示：ERROR1820(HY000):YoumustresetyourpasswordusingALTERUSERstatementbeforeexecutingthisstatement.验证用户user1登录后，执行命令showdatabases；，要求用户必须修改密码。13.2.2普通用户修改自己的密码如果以非匿名用户身份连接，则可以使用USER()函数更改自己的密码。基本语法格式为：ALTERUSERUSER()IDENTIFIEDBY'password';要查看服务器验证当前登录用户，可以调用CURRENT_USER()函数。【例13-7】将当前用户test1的密码改为‘111111’。SQL语句为：selectcurrent_user();alteruseruser()identifiedby'111111';13.2.3root用户修改自己的密码和普通用户的密码修改用户信息的基本语法格式为：ALTERUSER[IFEXISTS]user[auth_option][,user[auth_option]]...[REQUIRE{NONE|tls_option[[AND]tls_option]...}][WITHresource_option[resource_option]...][password_option|lock_option]...其中参数的含义与CREATEUSER中的参数一样。使用ALTERUSER语句可以修改用户身份验证插件、用户密码、角色、SSL/TLS、资源限制和密码管理属性，账号锁定。13.2.3root用户修改自己的密码和普通用户的密码1.修改root账号密码root用户修改自己的密码，有两种方法。（1）使用ALTERUSERUSER()修改密码，基本语法格式为：ALTERUSERUSER()IDENTIFIEDBY'password';【例13-8】修改root账号的密码为123456。SQL语句为：alteruseruser()identifiedby'123456';13.2.3root用户修改自己的密码和普通用户的密码（2）使用mysqladmin命令修改root用户密码基本语法为：mysqladmin-uusername-hhostname-ppassword"newpassword"其中username通常是指root，password是关键字，"newpassword"是指新密码，此处只能用双引号。【例13-9】使用mysqladmin命令修改root用户密码，将密码改成root。在DOS命令行提示符下，输入命令：mysqladmin-uroot-ppassword“root”Enterpassword:******（此时输入root的旧密码123456）执行后，root用户的密码就修改成root。13.2.3root用户修改自己的密码和普通用户的密码2.修改普通用户的密码（1）使用root用户修改普通用户的密码，可以使用ALTERUSER命令进行修改。基本语法格式为：ALTERUSER用户名IDENTIFIEDBY'明文密码';【例13-10】修改用户test1的密码为test123456。SQL语句为：alterusertest1@localhostidentifiedby'test123456';13.2.3root用户修改自己的密码和普通用户的密码（2）使用SETPASSWORD命令修改用户密码root用户也可以使用SETPASSWORD来修改普通用户的密码。基本语法格式为：SETPASSWORDFOR用户=‘new_password’;其中，用户格式为用户名@主机名，new_password为用户设置的新密码。【例13-11】将用户zhangsan的密码改为‘111111’。SQL语句为：setpasswordforzhangsan@localhost='111111’;特别提醒：系统用户和普通用户，都不可以使用update命令修改密码。13.2.4修改用户名 可以使用RENAMEUSER语句来修改一个已经存在的MySQL用户的名字。基本语法格式：RENAMEUSER老用户TO新用户[,...]说明：要使用RENAMEUSER，必须拥有全局CREATEUSER权限或mysql数据库UPDATE权限。如果旧用户不存在或者新用户已存在，则会出现错误。【例13-12】将用户test1的名字修改为test。renameusertest1@localhosttotest@localhost;也可以通过使用update语句修改mysql.user表的记录的方式来修改用户名。【例13-13】使用update命令，将test用户名重新改为test1。输入命令及执行结果为：mysql>updatemysql.usersetuser='test1'whereuser='test';mysql>flushprivileges;注意：root用户也可以使用update来修改用户名。flushprivileges;语句用来刷新权限，此处不可省。如果省略当我们下一次用新用户名test1登录时，会发现无法登陆，这是因为我们没有刷新权限。13.2.5删除普通用户在MySQL数据库中，可以使用DROPUSER语句来删除普通用户，也可以直接在mysql.user表中删除用户。（1）使用DROPUSER语句来删除普通用户基本语法格式为：DROPUSER[IFEXISTS]user[,user]...注意：DROPUSER语句用于删除一个或多个MySQL用户，中间用逗号分隔，并取消其权限。【例13-14】删除用户wang。SQL语句为：dropuserwang@202.206.5.10;13.2.5删除普通用户注意：要使用DROPUSER，必须拥有mysql数据库的全局CREATEUSER权限或DELETE权限。（2）使用DELETE命令来删除普通用户【例13-15】删除用户zhang。SQL语句为：deletefrommysql.userwhereuser='zhang';注意：特别要提醒一点，DROPUSER语句删除用户，底层是修改权限表，和DELETE语句直接修改权限表的效果是一样的。但是，采用封装好的语句肯定不会出错，如果直接修改权限表，难免会漏掉某些表（全局权限表user、数据库级权限表db……）。所以推荐使用DROPUSER语句来删除用户。13.2.6MySQL8中root用户密码丢失的解决办法 在Windows操作系统中，使用以下过程重置MySQL数据库中“root”@“localhost”用户的密码。要更改具有不同主机名部分的root用户的密码，请修改主机名。步骤如下：（1）以管理员身份登录到系统。（2）如果MySQL服务器正在运行，请停止它。对于作为Windows服务运行的服务器，请转到“服务管理器”：从“开始”菜单中，选择“控制面板”，然后选择“管理工具”，再选择“服务”。在列表中找到MySQL服务并停止它。如果服务器未作为服务运行，则可能需要使用任务管理器强制其停止。（3）在单行上创建包含密码分配语句的文本文件。语句如下：ALTERUSER'root'@'localhost'IDENTIFIEDBY'MyNewPass';将MyNewPass替换成要使用的密码。（4）保存文件。假设将文件命名为C:\mysql-init.txt。13.2.6MySQL8中root用户密码丢失的解决办法 （5）打开控制台窗口进入DOS命令提示符：从“开始”菜单中选择“运行”，然后输入cmd作为要运行的命令。（6）启动MySQL服务器，并将init_file系统变量设置为文件名（请注意，选项值中的反斜杠是两个）：在DOS命令提示符输入下面两条命令：①C:\>cd"C:\ProgramFiles\MySQL\MySQLServer8.0\bin"②C:\>mysqld--init-file=C:\\mysql-init.txt注意：如果将MySQL安装到其他位置，请相应的调整cd命令。服务器在启动时执行由init_file系统变量命名的文件的内容，并更改“root”@“localhost”用户密码。13.2.6MySQL8中root用户密码丢失的解决办法 要使服务器输出显示在控制台窗口而不是日志文件中，请将--console选项添加到mysqld命令中，改为mysqld--console--init-file=C:\\mysql-init.txt。如果是使用MySQL安装向导安装的MySQL，则可能需要指定--defaults-file文件选项。将之改为：C:\>mysqld--defaults-file="C:\\ProgramData\\MySQL\\MySQLServer8.0\\my.ini"--init-file=C:\\mysql-init.txt可以使用服务管理器找到--defaults-file设置：从“开始”菜单中，选择“控制面板”，然后选择“管理工具”，再选择“服务”。在列表中找到MySQL服务，右键单击它，然后选择“属性”选项。在“常规”选项卡下的“可执行文件路径”字段包含--defaults-file设置。（7）服务器成功启动后，删除C:\mysql-init.txt。现在应该可以使用新密码以root身份连接到MySQL服务器。停止MySQL服务器并正常重启。13.2.6MySQL8中root用户密码丢失的解决办法 root用户密码丢失，也可以使用--skip-grant-tables参数跳过授权表，重新设置root用户密码来解决，步骤如下：（1）停止MySQL服务器首先,关闭MySQL服务器，可以使用netstopmysql80关闭服务,也可以在本地服务中关闭。（2）mysqld--skip-grant-tables启动MySQL服务以管理员权限打开DOS命令提示符，输入以下命令开启服务,并绕过权限检查。mysqld--console--skip-grant-tables--shared-memory13.2.6MySQL8中root用户密码丢失的解决办法 （3）mysql-uroot命令重新登录启动另一个DOS命令提示符,输入mysql-uroot直接登陆,接着输入以下命令,将root密码置为空。UPDATEmysql.userSETauthentication_string=''WHEREuser='root'andhost='localhost';（4）重新设置root用户密码关闭两个DOS命令提示符,重新启动MySQL服务,输入mysql-uroot直接登陆,通过以下命令设置root用户的密码。ALTERUSER'root'@'localhost'IDENTIFIEDBY'yourpassword';其中yourpassword为要设置的密码。13.3权限管理

13.3.1MySQL的各种权限 13.3.2授予权限和查看权限 13.3.3收回权限 13.3.1MySQL的各种权限 MySQL的权限类型分为全局级、数据库级、表级、列级和例程（存储过程、函数）级。用户权限都存储在mysql系统数据库的权限表中，如表13-2所示。表13-2mysql数据库中与权限相关的表13.3.1MySQL的各种权限 （1）全局级（用户级）权限：和MySQL所有的数据库相关。（2）数据库级权限：和一个具体的数据库中的所有表相关。（3）表级权限：和一个具体表中的所有数据相关。（4）列级权限：和表中的一个具体列相关。（5）例程级权限。这些权限可以被授予为全局级和数据库级，也可以被授予为例程级。13.3.2授予权限和查看权限 在MySQL中使用GRANT语句只能对已存在的用户授权，如果授权的用户不存在，则会出错。基本语法格式为：GRANTpriv_type[(column_list)][,priv_type[(column_list)]]...ON[object_type]priv_levelTOuser_or_role[,user_or_role]...[WITHGRANTOPTION]参数说明：priv_type：表示用户的权限，如select，update。column_list：列名。object_type：（如果存在）应指定为表、函数或过程。priv_level：表示用户的权限范围。user_or_role：用户或角色，用户由用户名和主机名组成。WITHGRANTOPTION：表示该用户可以将自己拥有的权限授权给其他用户。13.3.2授予权限和查看权限 1.查看新用户的权限用户可以通过SHOWGRANTS语句查看拥有哪些权限，当然如果有对mysql数据库的访问权限也可以直接查询权限表。基本语法格式为：SHOWGRANTS[FORuser_or_role];说明：user_or_role表示用户或角色。SHOWGRANTS;语句表示查看当前用户的权限，也可以表示成：SHOWGRANTSFORCURRENT_USER;或者SHOWGRANTSFORCURRENT_USER();【例13-16】创建新用户zhang，查看其权限并验证。（1）创建用户zhangcreateuserzhang@localhostidentifiedby'123456';（2）查看用户zhang的权限showgrantsforzhang@localhost;13.3.2授予权限和查看权限 （3）验证权限在DOS命令提示符下，输入：mysql-uzhang-p123456然后进行MySQL客户端，输入命令：usejwgl;，执行结果如下图所示。13.3.2授予权限和查看权限 2.授予表级权限和列级权限（1）授予表级权限授予表级权限时，priv_type的值具体可以查看相应的权限表。基本格式为：GRANT权限列表ON数据库名.表名TO用户[WITHGRANTOPTION];【例13-17】授予用户zhang在xsjbxxb表上的SELECT权限。SQL语句为：grantselectonjwgl.xsjbxxbtozhang@localhost;或者写成：usejwgl;grantselectonxsjbxxbtozhang@localhost;查看用户zhang的权限，输入代码及运行结果为：mysql>showgrantsforzhang@localhost;13.3.2授予权限和查看权限 （2）授予列级权限对于列级权限，priv_type的值只能取'Select','Insert','Update','References'。权限的后面需要加上列名column_list。基本格式为：GRANT权限类型(字段列表)[,…]ON数据库名.表名TO用户[WITHGRANTOPTION];【例13-18】授予用户zhang在xsjbxxb表上的姓名列的UPDATE权限，并验证。SQL语句为：grantupdate(xm)onjwgl.xsjbxxbtozhang@localhost;输入update和select命令验证，mysql>updatejwgl.xsjbxxb->setxm='张天'->wherexm='张天宇';mysql>selectxmfromjwgl.xsjbxxb->wherexm='张天';13.3.2授予权限和查看权限 3.授予数据库权限授予数据库权限时，priv_type的取值可以查看mysql.db表。基本格式为：GRANT权限列表ON数据库名.*TO用户[WITHGRANTOPTION];说明：授予数据库权限时ON关键字后面跟“*”和“数据库.*”。“*”表示当前数据库中的所有表；“数据库.*”表示某个数据库中的所有表。【例13-19】授予新用户wang在jwgl数据库中的所有表的select，update权限，并允许其将该权限授予其他用户，然后查看用户wang的权限。（1）创建新用户wang。createuserwang@localhostidentifiedby'111111';（2）授予用户wang权限。grantselect,updateonjwgl.*towang@localhostwithgrantoption;（3）查看用户wang的权限。mysql>showgrantsforwang@localhost;13.3.2授予权限和查看权限 【例13-20】用户wang将自己在jwgl数据库中所有的数据库权限授予新用户zhao。（1）创建新用户zhao。createuserzhao@localhostidentifiedby'111111';（2）打开DOS命令行窗口，以wang用户身份登录。mysql-uwang-p111111（3）授予用户zhao权限。grantselect,updateonjwgl.*tozhao@localhost;注意：和表权限类似，授予一个数据库权限也不意味着拥有另一个权限。如果用户被授予可以创建新表和视图，但是还不能访问它们。要访问它们，它还需要单独被授予SELECT权限或更多权限。13.3.2授予权限和查看权限 4.授予全局级权限授予全局权限时priv_type的取值可以查看mysql.user表。基本格式为：GRANT权限列表ON*.*TO用户[WITHGRANTOPTION];说明：ON子句中使用“*.*”，表示所有数据库的所有表。【例13-21】新建用户xlx，授予其所有的权限。（1）创建新用户xlxcreateuserxlx@localhostidentifiedby'xulixia123456';（2）授予权限grantallprivilegeson*.*toxlx@localhost;（3）查询用户xlx的权限showgrantsforxlx@localhost\G;13.3.2授予权限和查看权限 【例13-22】授予用户test1在所有数据库上create，alter和drop权限。SQL语句为：grantcreate,alter,dropon*.*totest1@localhost;查看用户权限，结果为：mysql>showgrantsfortest1@localhost;13.3.3收回权限 收回权限就是取消某个用户的某些权限。例如，管理员发现某个用户不应该具有update权限，就应该及时将其收回。收回用户不必要的权限在一定程度上可以保证数据的安全性。收回权限利用revoke语句来实现，语法格式有两种，一种是收回用户指定的权限，另一种是收回用户的所有权限。要使用REVOKE，用户必须拥有mysql数据库的全局CREATEUSER权限或UPDATE权限。13.3.3收回权限 基本语法格式：REVOKEpriv_type[(column_list)][,priv_type[(column_list)]]...ON[object_type]priv_levelFROMuser_or_role[,user_or_role]...或者：REVOKEALL[PRIVILEGES],GRANTOPTIONFROMuser_or_role[,user_or_role]说明：参数的含义与grant命令的参数含义相同。第一种格式用来回收某些特定的权限，第二种格式回收所有该用户的权限。13.3.3收回权限 【例13-23】收回用户zhang的所有权限。（1）首先查看用户zhang拥有的权限。执行语句为：mysql>showgrantsforzhang@localhost;（2）收回用户zhang的权限，执行语句为：mysql>revokeallon*.*fromzhang@localhost;（3）重新查询zhang的权限。mysql>showgrantsforzhang@localhost;【例13-24】收回用户wang在jwgl数据库上的SELECT权限。SQL语句为：revokeselectonjwgl.*fromwang@localhost;执行语句后，查看用户wang的权限mysql>showgrantsforwang@localhost;13.4MySQL8.0的新特性——角色管理

13.4.1创建角色 13.4.2授予及查看角色权限 13.4.3激活角色13.4.4收回角色或角色权限13.4.5删除角色 13.4.6角色和用户互换 角色 MySQL角色是权限的命名集合。像用户一样，角色可以拥有授予和撤销的权限。可以向用户授予角色，该角色将与每个角色相关联的权限授予该用户。这样就可以将权限集分配给用户，并为授予用户权限提供了一种方便的替代方法，既可以概念化所需的权限分配，也可以实现它们。13.4.1创建角色 创建角色使用CREATEROLE命令，基本语法格式为：CREATEROLE[IFNOTEXISTS]role[,role]...角色名的语法和语义同用户名：角色名称由用户名和主机名两部分组成。存储在授权表中时，它们具有与用户名相同的属性，这些属性在授权表账号列属性中有描述。角色名与用户名不同之处：角色名称的用户名不能为空。省略角色名的主机名默认为“%”。但与用户名中的“%”不同，角色名中“%”的主机部分没有通配符属性。角色名的主机名中的网络掩码没有意义。注意：（1）CREATEROLE一次可以创建一个或多个角色，这些角色被命名为权限集合。若要使用CREATEROLE语句，必须具有CREATEROLE或CREATEUSER权限。启用read_only系统变量时，CREATEROLE还需要CONNECTION_ADMIN或SUPER权限。（2）角色在创建时被锁定，没有密码，并且被分配默认的身份验证插件。13.4.1创建角色 【例13-25】创建三个角色：app_developer，app_read和app_write。SQL语句为：createrole'app_developer','app_read','app_write';执行语句后，查询mysql.user表，输入代码：mysql>selecthost,userfrommysql.userwhereuserlike'app_%’;执行结果如图13-21所示。

图13-21查看创建的角色13.4.2授予及查看角色权限1.授予角色权限使用GRANT将角色授予用户，即将权限的集合授予用户，语法格式为：GRANTrole[,role]...TOuser_or_role[,user_or_role]...[WITHADMINOPTION]注意：使用该语句，用户必须具有ROLE_ADMIN或SUPER权限，或是被授予了包含withADMINOPTION子句的GRANT语句的角色。若要授予具有SYSTEM_USER权限的角色，必须具有SYSTEM_USER权限。13.4.2授予及查看角色权限【例13-26】为上例创建的三个角色分配权限。将操作jwgl数据库的所有权限为授予app_developer角色。将对jwgl数据库的select权限授予app_read角色，将对jwgl数据库的insert、update和delete权限授予app_write。（1）为app_developer角色授予操作jwgl数据库的所有权限。grantallonjwgl.*toapp_developer;（2）为app_read角色授予操作jwgl数据库的select权限。grantselectonjwgl.*toapp_read;（3）为app_write角色授予操作jwgl数据库的insert、update和delete权限。grantinsert,update,deleteonjwgl.*toapp_write;13.4.2授予及查看角色权限【例13-27】创建一个开发人员用户，两个只读访问权限的用户，一个需要读/写访问权限的用户，请使用角色为每个用户分配权限。（1）创建用户。SQL语句为：mysql>CREATEUSER'dev1'@'localhost'IDENTIFIEDBY'dev111';mysql>CREATEUSER'read_user1'@'localhost'IDENTIFIEDBY'read111';mysql>CREATEUSER'read_user2'@'localhost'IDENTIFIEDBY'read222';mysql>CREATEUSER'rw_user1'@'localhost'IDENTIFIEDBY'rw111';（2）使用角色为每个用户分配所需的特权。SQL语句如下：mysql>GRANT'app_developer'TO'dev1'@'localhost';mysql>GRANT'app_read'TO'read_user1'@'localhost','read_user2'@'localhost';mysql>GRANT'app_read','app_write'TO'rw_user1'@'localhost';13.4.2授予及查看角色权限2.查看验证角色权限要验证角色分配给用户的权限，使用SHOWGRANTS查看权限。基本语法格式为：SHOWGRANTS[FORuser_or_role][USINGrole];【例13-28】查看角色app_developer和用户dev1的权限。（1）查看角色app_developer权限。输入SQL语句：showgrantsforapp_developer;（2）查看用户dev1的权限。输入SQL语句：

showgrantsfordev1@localhost;使用该语句，用户dev1的权限显示了每个授予的角色，而没有将其“扩展”到角色所代表的权限。要同时显示角色权限，需要添加一个USING子句来命名要为其显示权限的授予角色。SQL语句为：showgrantsfordev1@localhostusing'app_developer';13.4.3激活角色在用户会话中，授予用户的角色可以是活动的或非活动的。如果授予的角色在会话中处于活动状态，则应用其权限；否则，不应用。要确定当前会话中哪些角色处于活动状态，可以使用CURRENT_ROLE()函数。默认情况下，将角色授予用户或在mandatory_roles系统变量值中命名用户不会自动导致该角色在用户会话中变为活动角色。例如，由于到目前为止，在前面的示例中rw_us