MySQL实用教程(新体系.综合应用实例视频)第4版第10章 用户与权限

第10章

用户与权限——用户管理及实例MySQL+实用教程（第4版）01创建、删除用户1．创建用户2．删除用户创建、删除用户1．创建用户CREATEUSER用户名@主机名[IDENTIFIEDBY[PASSWORD]密码],...说明：（1）主机名是自己安装MySQL服务器的计算机全名或者IP地址。用户名和密码只能由字母和数字组成。如果一个用户名和主机名中包含特殊符号（如“_”）或通配符（如“%”），则需要用单引号将其括起。“%”表示一组主机。将本地主机作为MySQL服务器时，主机名为“localhost”。用户名@主机名称为帐户，为了简单，我们就称为用户。（2）如果两个用户具有相同的用户名但主机名不同，MySQL也将它们视为不同的用户，允许为这两个用户分配不同的权限集合。（3）IDENTIFIEDBY子句可以为用户设定一个密码。如果不想以明文发送密码且知道PASSWORD()函数返回给密码的混编值，则可以指定该混编值，但要加关键字PASSWORD。如果没有设定密码，那么MySQL允许该用户不使用密码登录。但是从安全的角度并不推荐这种做法。（4）由于新添加的用户记录需要写入系统数据库mysql中，创建用户就必须拥有mysql数据库的全局CREATEUSER权限或INSERT权限。如果用户已存在会出现错误。（5）对于每个帐户，CREATEUSER

在mysql.user系统表中创建一个新行。帐户行反映了语句中指定的属性。未指定的属性设置为其默认值。（6）每个帐户可以指定的属性很多，例如：角色、SSL/TLS、资源限制、密码管理、帐户锁定、二进制日志等。创建、删除用户【例10.1】创建新用户，测试初始权限。1）创建新用户。以root用户登录命令行，执行下列语句：CREATEUSER king@'localhost'IDENTIFIEDBY'queen', palo@'localhost'IDENTIFIEDBY'530415', liu@'localhost'IDENTIFIEDBY'lpwd', 'zhang'@'localhost'IDENTIFIEDBY'zpwd';CREATEUSER king@'OPPO'IDENTIFIEDBY'queen';CREATEUSER king@'0'IDENTIFIEDBY'queen';创建、删除用户说明：（a）用户名（'zhang'）的引号不是必须的。king包含本地、OPPO主机和IP地址'0'主机3个用户。除了使用命令行，也可以在Navicat环境root用户创建的本地连接（M8-Local）的查询窗口执行上述语句，如图10.1所示。（b）'localhost'指本地主机，笔者的当前本地主机名为“huawei”，故可以用“huawei”代替'localhost'，但引用用户时需要与定义的一致。创建、删除用户2）查看创建的用户。创建用户后，设置mysql系统数据库作为当前数据库，从user表中可查到刚刚添加的用户记录：USEmysql;SELECT*FROMuser;显示当前连接MySQL服务器系统默认用户和用户已经创建的用户及其包含的权限。从中可以看到root用户具有所有权限。也可以通过Navicat查看，在对应的连接下，单击“用户”，在用户对应“对象”页显示当前连接（笔者的为M8-Local，对应本机）下MySQL8服务器已经包含的所有用户，如图10.2所示，其中4个是MySQL安装后就存在的（图中框出），其余就是当前连接下用户已经创建的用户。创建、删除用户2．删除用户DROPUSER用户名@主机名,...说明：（1）该语句可用于删除一个或多个用户，并取消其权限。要使用DROPUSER，必须拥有mysql系统数据库的全局CREATEUSER或DELETE权限。（2）如果被删的用户之前创建了表、索引或其他数据库对象，它们将继续保留，因为MySQL并没有记录是由谁创建了这些对象。02修改用户名、密码1．修改用户名2．修改密码修改用户名、密码1．修改用户名RENAMEUSER用户名@主机名TO新用户名@主机名,...说明：要使用该语句，必须拥有全局CREATEUSER或MySQL系统数据库的UPDATE权限。如果语句中指定的用户名不存在或新用户名已经存在，则会出现错误。2．修改密码ALTERUSER用户名@主机名IDENTIFIEDBY新密码;【例10.2】将用户king的名字修改为ken，密码改为qen。RENAMEUSERking@'localhost'TOken@'localhost'; #（a）ALTERUSERken@'localhost'IDENTIFIEDBY'qen'; #（b）说明：（a）将用户king的名字修改为ken。（b）将用户ken的密码改为qen。第10章

用户与权限——权限控制及实例01授予权限语句1.授予表权限2.授予列权限3.授予数据库权限4.授予用户权限5.用户权限显示授予权限语句新的用户不允许访问属于其他用户的表，也不能立即创建自己的表，它必须被授权。在MySQL中可以被授予的权限分为以下几组。（1）列权限：与表中一个具体的列相关。例如，使用UPDATE语句更新表commodity的“商品编号”列的值的权限。（2）表权限：与一个具体表中的所有数据相关。例如，使用SELECT语句查询表commodity的所有数据的权限。（3）数据库权限：与一个具体的数据库中的所有表相关。例如，在已有的emarket数据库中创建新表的权限。（4）用户权限：与MySQL所有的数据库相关。例如，删除已有的数据库或者创建一个新数据库的权限。给用户授予权限语句：GRANT权限类型[(列,...)],... ON[对象类型]表名|*|*.*|数据库名.* TO用户名@主机名[IDENTIFIEDBY[PASSWORD]密码],... [WITH选项]授予权限语句说明：（1）ON给出的是要授予权限的对象类型、对象名称，授予表权限时，跟表名或视图名。（2）对象类型包括以下几种：TABLE、FUNCTION和PROCEDURE。不同的对象授予权限不相同。（3）若在TO子句中给存在的用户指定密码，则新密码将覆盖原密码。MySQL8.0不允许将权限授予一个不存在的用户，只有先创建用户，才能为其授予权限。（4）WITH子句用于权限的转移和限制，稍后专门介绍。（5）开始以root用户登录后执行该语句。在后面创建新用户，并赋予用户权限后，就可以用具有权限的用户登录，再授予其他用户权限。授予权限语句1.授予表权限授予表权限时，授权语句：GRANT表权限,... ON数据库名.表名 TO...其中，表名为“*”表示所有表。表常用权限如下：SELECT：SELECT语句访问特定的表的权力。用户必须对视图公式中指定的每个表（或视图）都有SELECT权限，才能访问该视图。INSERT：使用INSERT语句向一个特定表中添加行的权力。DELETE：使用DELETE语句在一个特定表中删除行的权力。UPDATE：使用UPDATE语句修改特定表中数据的权力。UPDATE(列,…)，对指定的列修改权限。REFERENCES：给予用户创建一个外键参照特定的表的权力。CREATE：使用特定的名字创建一个表结构的权力。ALTER：使用ALTERTABLE语句修改表结构的权力。INDEX：在表上定义索引的权力。DROP：删除表的权力。ALL或ALLPRIVILEGES：给予用户所有的权限。授予权限语句【例10.3】授予OPPO主机king用户查询commodity表的权限。授予用户liu和zhang查询和修改commodity表的权限，然后测试权限。1）授予权限。GRANTSELECT ONmodity TOking@'OPPO'; #（a）GRANTSELECT,UPDATE ONmodity TOliu@'localhost',zhang@'localhost'; #（b）说明：（a）授予用户king@'OPPO'在commodity表上的SELECT权限。（b）授予用户liu和zhang在commodity表上的SELECT和UPDATE权限。授予权限语句2）测试本地主机liu用户查询和修改commodity表的权限。（1）在Windows命令行窗口以用户liu登录MySQL，回答登录密码：lpwd。打开mydb数据库：USEmydb;系统显示出错信息如图10.3所示。因为没有给liu用户mydb数据库的任何权限，当然打不开该数据库。授予权限语句（2）操作emarket数据库打开emarket数据库；查询commodity表前2个记录；对商品编号='1A0201'商品库存量-1，操作成功，如图10.4所示，因为liu用户具有commodity表查询和修改权限。授予权限语句3）测试OPPO主机king用户查询commodity表的权限。本测试实验需要两台计算机，除了本地主机（huawei），还要准备一台名为OPPO的计算机，并安装好Navicat，与本地主机处于同一局域网。（1）之前已经在本地主机上以root用户创建了OPPO主机的king用户并授予了查询commodity表的权限。（2）在OPPO主机上用Navicat创建king用户连接。注意，主机名需要填本地主机名“huawei”。（3）在OPPO主机上打开myking连接，可以看到，除了显示一个系统数据库（information_schema）以外，还有一个emarket数据库。这是因为OPPO主机上的king用户当前还没有给它分配任何权限，而在本地“huawei”主机上只给OPPO主机上的king用户分配了emarket数据库的commodity表查询权限。继续在OPPO主机myking连接下单击emarket数据库，系统仅显示commodity表，打开commodity表，显示该表的所有记录，说明OPPO主机上的king用户确实具有查询commodity表的权限。但如果在commodity表中增加一条记录：1A0102，水果名称，5,100然后保存记录，系统显示没有权限出错信息。同样也不能对任何记录进行修改和删除操作。授予权限语句2.授予列权限授予列权限时，授权语句：GRANT权限类型(列名,...),... ON数据库名.表名 TO...对于列权限，“权限类型”值只能取SELECT、INSERT和UPDATE。【例10.4】授予ken在commodity表的“商品编号”和“商品名称”列上的UPDATE权限。GRANTUPDATE(商品编号,商品名称) ONmodity TOken@'localhost';授予权限语句3.授予数据库权限授予数据库权限时，授权语句：GRANT数据库权限,... ON[数据库名.]* TO...其中，“数据库名.*”对应数据库中的所有表，“*”表示当前数据库中的所有表。这个权限不仅适用于数据库中当前所有表，而且适用于以后添加到数据库中的表。数据库常用权限如下：SELECT：使用SELECT语句访问特定数据库中所有表和视图的权力。INSERT：使用INSERT语句向特定数据库中所有表添加行的权力。DELETE：使用DELETE语句删除特定数据库中所有表的行的权力。UPDATE：使用UPDATE语句更新特定数据库中所有表的数据的权力。REFERENCES：创建指向特定的数据库中的表外键的权力。CREATE：使用CREATETABLE语句在特定数据库中创建新表的权力。ALTER：使用ALTERTABLE语句修改特定数据库中所有表的权力。INDEX：在特定数据库中的所有表上定义和删除索引的权力。DROP：删除特定数据库中所有表和视图的权力。CREATETEMPORARYTABLES：在特定数据库中创建临时表的权力。CREATEVIEW：在特定数据库中创建新的视图的权力。SHOWVIEW：查看特定数据库中已有视图的定义的权力。授予权限语句【例10.5】授予ken在emarket数据库中的所有表的SELECT权限，授予ken在mydb数据库中所有的数据库权限。GRANTSELECT ONemarket.* TOken@'localhost';USEmydb;GRANTALL ON* TOken@'localhost';说明：与表权限类似，授予一个数据库权限也不意味着拥有另一个权限。如果用户被授予可以创建新表和视图，但是还不能访问它们。要访问它们，还需要单独被授予SELECT权限或更多权限。授予权限语句4.授予用户权限在MySQL所有的权限中，最有效率的权限就是用户权限，对于需要授予数据库权限的所有语句，也可以定义在用户权限上。授予用户权限时，授权语句：GRANT用户权限,... ON*.* TO...授予用户权限时，指定“CREATEUSER”权限给予用户创建和删除新用户的权力。指定“SHOWDATABASES”给予用户使用SHOWDATABASES语句查看所有已有的数据库定义的权力。【例10.6】创建新用户caddy，授予对所有数据库中所有表的CREATE、ALTER和DROP及创建新用户的权力。同时授予其在commodity表上的SELECT权限。CREATEUSER caddy@'localhost'IDENTIFIEDBY'cpwd';GRANTCREATE,ALTER,DROP,CREATEUSER ON*.* TOcaddy@'localhost';GRANTSELECT ONmodity TOcaddy@'localhost';授予权限语句表10.1列出SQL语句可授予权限。语

句用户权限数据库权限表

权

限列

权

限SELECTYesYesYesNoINSERTYesYesYesNoDELETEYesYesYesYesUPDATEYesYesYesYesREFERENCESYesYesYesYesCREATEYesYesYesNoALTERYesYesYesNoDROPYesYesYesNoINDEXYesYesYesYesCREATETEMPORARYTABLESYesYesNoNoCREATEVIEWYesYesNoNoSHOWVIEWYesYesNoNoCREATEROUTINEYesYesNoNoALTERROUTINEYesYesNoNoEXECUTEROUTINEYesYesNoNoLOCKTABLESYesYesNoNoCREATEUSERYesNoNoNoSHOWDATABASESYesNoNoNoFILEYesNoNoNoPROCESSYesNoNoNoRELOADYesNoNoNoREPLICATIONCLIENTYesNoNoNoREPLICATIONSLAVEYesNoNoNoSHUTDOWNYesNoNoNoSUPERYesNoNoNoUSAGEYesNoNoNo授予权限语句5.用户权限显示显示用户权限语句：SHOWGRANTSFOR用户;例如：SHOWGRANTSFORliu@'localhost';显示结果如图10.5所示。02权限转移和限制权限转移和限制GRANT语句使用WITH子句实现权限转移和限制：GRANT权限类型[(列表)],... ON... TO... WITHGRANTOPTION|WITH使用限制说明：（1）WITHGRANTOPTION如果指定为WITHGRANTOPTION，则表示TO子句中指定的所有用户都有把自己所拥有的权限授予其他用户的权力，而不管其他用户是否拥有该权限。（2）WITH使用限制WITH子句也可以对一个用户授予使用限制，有如下选项：MAX_QUERIES_PER_HOUR次数：每小时可以查询数据库的次数。MAX_UPDATES_PER_HOUR次数：每小时可以修改数据库的次数。MAX_CONNECTIONS_PER_HOUR次数：每小时可以连接数据库的次数。MAX_USER_CONNECTIONS次数：同时连接MySQL的最大用户数。权限转移和限制【例10.7】授予ken用户在orders和orderitems表上的SELECT权限，并允许其将orderitems权限授予新Jim用户。（1）在root用户下授予ken用户在orders和orderitems表上的SELECT权限，同时创建Jim用户。GRANTSELECT ONemarket.orders TOken@'localhost' WITHGRANTOPTION;GRANTSELECT ONemarket.orderitems TOken@'localhost' WITHGRANTOPTION;CREATEUSER Jim@'localhost'IDENTIFIEDBY'jpwd';（2）在Navicat中创建ken用户连接myken。权限转移和限制（3）在Navicat中单击myken连接，在查询分析器中，执行下列语句，将orderitems查询权限转移给Jim用户。GRANTSELECT ONemarket.orderitems TOJim@'localhost';运行结果如图10.6所示。权限转移和限制（4）在Navicat中以root用户连接MySQL，双击“用户”中的Jim，切换至“权限”选项页，显示其拥有的权限如图10.7所示。03权限撤销权限撤销要撤销一个用户的权限，但不从user表中删除该用户，可以使用REVOKE语句，这条语句和GRANT语句格式类似，但具有相反的效果。要使用REVOKE，用户必须拥有mysql数据库的全局CREATEUSER权限或UPDATE权限。REVOKE权限类型[(列表)],... ON表名|*|*.*|数据库名.* FROM用户名@主机名,...或者REVOKEALLPRIVILEGES,GRANTOPTIONFROM用户名@主机名,...说明：第一种格式用来撤销某些特定的权限，而第二种格式则撤销所有该用户的权限。【例10.8】撤销用户ken在orderitems表上的SELECT权限。以root用户登录，执行下列语句：REVOKESELECT ONemarket.orderitems FROMken@'localhost';由于ken用户对orderitems表的SELECT权限被撤销了，那么包括直接或间接地依赖于它的所有权限也被撤销了，在这个例子中，Jim也失去了对orderitems表的SELECT权限。04Navicat可视化权限操作1.显示用户和权限2.增加和撤销权限Navicat可视化权限操作1.显示用户和权限在root用户连接下，单击“用户”图标，显示出所有用户如图10.8所示。Navicat可视化权限操作双击用户名（例如ken@localhost），系统显示对应该用户的所有属性，初始为“常规”选项页，如图10.9（a）所示。切换到“权限”选项页，显示ken用户的所有权限，如图10.9（b）所示。

Navicat可视化权限操作2.增加和撤销权限在相应的权限上勾选或取消勾选，就能够增加和撤销用户的权限。单击“添加权限”按钮，系统列出当前连接下所有数据库及其对象，包含表、列等，右边为对应的权限，如图10.10（a）所示。如果要撤销用户对某项（例如mydb数据库）的权限，就选择对应的项，右击点“撤销”，然后单击“保存”按钮即可，如图10.10（b）所示。

第10章

用户与权限——角色和权限管理及实例角色和权限管理及实例角色是一组权限的集合。一组权限赋予某个角色，再把某个角色赋予某个用户，那用户就拥有该角色对应的权限，三者的关系如图10.11所示。01创建角色和分配权限创建角色和分配权限MySQL中常用的创建角色和分配权限的语句如下。（1）创建角色、删除角色CREATEROLE角色名,...DROPROLEIFEXISTS角色名,...（2）为角色分配相应权限GRANT权限名ON*.*TO角色名（3）为某个用户赋予角色的权限GRANT角色名ON*.*TO用户查看用户权限：SHOWGRANTSFOR用户创建角色和分配权限（4）启用角色SETDEFAULTROLE角色名TO用户注意：设置了角色，如果不启用，用户登录的时候依旧没有该角色的权限。如果一个用户有多个角色，需要进行设置：SETDEFAULTROLEALLTO用户（5）撤销角色权限REVOKEINSERT,UPDATEON*.*|数据库.*FROM角色名（6）用户角色权限查询SELECT*FROMMYSQL.DEFAULT_ROLES;SELECT*FROMMYSQL.ROLE_EDGES;02用户角色和权限分配举例用户角色和权限分配举例【例10.9】用户角色和权限分配过程演示。DROPUSERIFEXISTSuser1@'localhost',user2@'localhost'; #（a.1）CREATEUSERuser1@'localhost',user2@'localhost'; #（a.2）DROPROLEIFEXISTSrmydb_wirte,rmydb_read; #（b.1）CREATEROLErmydb_wirte,rmydb_read; #（b.2）GRANTINSERT