项目3 管理MySQL权限与安全

数据库运维与管理项目3管理MySQL权限与安全教学目标知识目标了解MySQL权限管理原理掌握MySQL权限表掌握MySQL账户管理掌握MySQL权限管理能力目标能根据用户需求完成MySQL数据库的账户管理能根据应用场景完成MySQL数据库的权限管理能完成MySQL数据库的安全体系构建素养目标安全意识能力职业规范操作严谨细致思政目标民族自豪感国产软件振兴的使命担当项目3管理MySQL权限与安全项目场景：天天电器商场的售后服务部门最近通过外包的方式开发了一款售后服务登记系统，外包的软件开发商需要在公司的服务器上部署MySQL的数据库文件以提供数据共享服务。请你给外包开发公司建立一个访问MySQL数据库的账户，使其可以部署数据库文件，但是不能查看和改动部署在MySQL服务器上的其他数据文件。项目知识导入一、权限表5个常用的控制权限的表，分别为：user表，db表，tables_priv表，columns_priv表和procs_priv表。1、user表：

存放账户信息及全局级别（所有数据库）权限，决定了来自哪些主机的哪些账户可以访问数据库实例，如果有全局权限则意味着对所有数据库都有此权限。示例：查看user表结构mysql>usemysql;mysql>descuser;项目知识导入（1）账号字段User：账户名称；Host：允许访问的客户端IP地址或主机地址，为“%”时，表示所有客户端的账户；Host+Useruser表的主键，用于区分MySQL中的账户。（2）权限字段以“_priv”结尾的字段；字段保存了账户的全局权限，如“Select_priv”查询权限、“Insert_priv”插入权限等；数据类型都是ENUM枚举类型，取值为N或Y，N表示无对应权限，Y则表示拥有该权限；项目知识导入（3）安全连接字段ssl_type：用于保存安全连接的类型ssl_cipher：用于保存安全加密连接的特定密码x509_issuer：保存由CA签发的有效的X509证书x509_subject：保存包含主题的有效的X509证书（4）资源限制字段：保存账户对可使用的服务器资源的限制max_questions：每小时允许用户执行查询操作的最多次数。max_updates：每小时允许用户执行更新操作的最多次数。max_connections：每小时允许用户建立连接的最多次数。max_user_connections：允许单个用户同时建立连接的最多数量。项目知识导入（5）身份验证字段：保存账户的身份验证信息plugin：用于指定账户的验证插件名称；authentication_string：根据对应的验证插件对明文密码加密后的字符串；password_expired：密码是否过期；password_last_changed：密码最后修改时间；password_lifetime：密码的有效期；Password_reuse_history：修改密码不允许与最近几次使用的密码重复；Password_reuse_time：修改密码不允许与最近多少天使用的密码重复；Password_require_current：修改密码是否需要提供当前的登录密码；（6）账户锁定字段：account_locked：保存当前账户是否是锁定状态，当为Y时，即账户锁定，不能用于连接到服务器；项目知识导入二、账户管理1、建立账户

语法：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]...项目知识导入选项说明默认值user账户名，由“账户名@主机地址”组成

auth_option账户身份验证选项，即加密插件由default_authentication_plugin系统变量定义的插件进行身份验证role角色NONEtls_option加密连接协议选项NONEresource_option资源控制选项N(表示无限制)password_option密码管理选项PASSWORDEXPIREDEFAULTlock_option账户锁定选项ACCOUNTUNLOCK语法说明项目知识导入二、账户管理1、建立账户示例1：建立简单账户示例2：建立有密码的账户mysql>createuser'user1';mysql>CREATEUSER'user1'IDENTIFIEDby'123456';mysql>SELECTuser,hostFROMuser;mysql>SELECTplugin,authentication_stringFROMuserWHEREuser='user1';#查看明文密码加密后的效果#查询用户表中的用户信息项目知识导入二、账户管理1、建立账户示例3：同时创建多个账户示例4：建立账户并设置可操作资源范围mysql>CREATEUSER->'user3'IDENTIFIEDBY'333333',->'user4'IDENTIFIEDBY'444444';;mysql>CREATEUSER->'user5'IDENTIFIEDBY'555555'->WITHMAX_UPDATES_PER_HOUR2;#建立一个名为“user5”的账户，限制其每小时最多可以更新2次项目知识导入二、账户管理 2、修改账户语法：ALTERUSER[IFNOTEXISTS]user[auth_option][,user[auth_option]]...DEFAULTROLErole[,role]...[REQUIRE{NONE|tls_option[[AND]tls_option]...}][WITHresource_option[resource_option]...][password_option|lock_option]...项目知识导入二、账户管理 2、修改账户示例1：为登录的当前账户修改密码示例2：为指定的账户修改密码示例3：修改账户验证

将user1的账户加密插件修改为“caching_sha2_password”,密码修改“User1@111”,要求：每180天选择一个新密码；并启用登录失败跟踪（三个连续的错误密码将导致临时账户锁定两天）mysql>ALTERUSERUSER()IDENTIFIEDBY'Hello123.';mysql>ALTERUSER'user1'IDENTIFIEDby'HelloUser1.';mysql>ALTERUSER'user1'@'%'->IDENTIFIEDWITHcaching_sha2_passwordBY'User1@111'->PASSWORDEXPIREINTERVAL180DAY->FAILED_LOGIN_ATTEMPTS3PASSWORD_LOCK_TIME2;项目知识导入二、账户管理

2、修改账户示例4：修改账户状态之锁定账户示例5：修改账户状态之解锁账户示例6：设置账户的资源限制——设置user1账户最多可同时建立两个连接

mysql>ALTERUSER'user1'@'%'ACCOUNTLOCK;mysql>ALTERUSER'user1'@'%'ACCOUNTUNLOCK;mysql>ALTERUSER'user1'@'%‘

->WITHmax_user_connections2;

项目知识导入二、账户管理

3、删除账户

示例1：删除user5账户语法：DROPUSER[IFEXISTS]user[,user]…;mysql>DROPUSER'user5';

项目知识导入三、权限管理（一）MySQL的各种权限权限名称权限级别权限描述SELECT全局、数据库、表、列启用SELECTUPDATE全局、数据库、表、列启用UPDATEDELETE全局、数据库、表启用DELETEINSERT全局、数据库、表、列启用INSERTSHOWDATABASES全局启用SHOWDATABASES以显示所有数据库SHOWVIEW全局、数据库、表启用SHOWCREATEVIEWPROCESS全局使账户可以使用查看所有进程SHOWPROCESSLIST1.数据权限数据权限主要是针对数据库表中的数据行的操作权限项目知识导入权限名称权限级别权限描述DROP全局、数据库、表启用删除数据库、表和视图CREATE全局、数据库、表启用创建数据库、表CREATEROUTINE全局、数据库启用创建存储过程CREATETABLESPACE全局启用创建、修改或删除表空间和日志文件组CREATETEMPORARYTABLES全局、数据库启用CREATETEMPORARYTABLESCREATEVIEW全局、数据库、表启用创建或修改视图ALTER全局、数据库、表启用ALTERTABLEALTERROUTINE全局、数据库、存储过程启用删除或修改存储过程INDEX全局、数据库、表启用创建或删除索引TRIGGER全局、数据库、表启用创建、修改或删除触发器REFERENCES全局、数据库、表、列启用创建外键2.结构权限结构权限主要针对数据库表的结构或数据库的对象进行操作的权限项目知识导入3.管理权限这里的管理权限指的是通常只有管理员才有的管理权限权限名称权限级别权限描述SUPER全局能够使用如其他行政业务CHANGEMASTERTO，KILL，PURGEBINARYLOGS，SETGLOBAL，和中mysqladmin调试命令CREATEUSER全局允许使用CREATEUSER，DROPUSER，RENAMEUSER，和REVOKEALLPRIVILEGESGRANTOPTION全局、数据库、表、存储过程、代理允许授予或删除账户权限RELOAD全局启用FLUSH操作PROXY全局启用账户代理REPLICATIONCLIENT全局使账户能够询问主服务器或从服务器在哪里REPLICATIONSLAVE全局使复制从属服务器能够从主服务器读取二进制日志事件SHUTDOWN全局启用mysqladminshutdown使用LOCKTABLES全局、数据库启用LOCKTABLES对您拥有SELECT特权的on表的使用项目知识导入三、权限管理（二）查看权限语法：SHOWGRANTS[FOR账户]项目知识导入三、权限管理（三）授予权限GRANTpriv_type[(column_list)][,priv_type[(column_list)]]...ON[object_type]priv_levelTOuser_or_role[,user_or_role]...[WITHGRANTOPTION][ASuser[WITHROLEDEFAULT|NONE|ALL|ALLEXCEPTrole[,role]...|role[,role]...]]}priv_type：权限类型，即表3-3、表3-4、表3-5中所列权限名称。column_list：字段列表用于设置列权限object_type：权限作用的目标类型，默认为TABLE,其值还可以是FUNCTION(函数)、PROCEDURE(存储过程)。priv_level：权限级别，user_or_role：账户或角色WITHGRANTOPTION：表示当前账户可以为其他账户进行授权。项目知识导入三、权限管理（三）授予权限示例1：授予user1账户对test数据库中的student表的SELECT权限，以及对name和phone字段的插入权限。示例2：授予user1账户对test数据库所有的权限mysql>GRANTSELECT,INSERT(name,phone)ONtest.studenttouser1;mysql>GRANTALLONtest.*touser1;mysql>SHOWGRANTSFORuser1;项目知识导入三、权限管理（四）收回权限语法：REVOKEpriv_type[(column_list)][,priv_type[(column_list)]]...ON[object_type]priv_levelFROMuser_or_role[,user_or_role]...项目知识导入三、权限管理（四）收回权限示例1：收回user1账户对test数据库student表的插入权限。示例2：收回user1账户所有的权限mysql>REVOKEINSERT(name,phone)ONtest.studentFROMuser1;mysql>REVOKEALLONtest.studentFROMuser1;mysql>SHOWGRANTSFORuser1;项目知识导入三、权限管理（五）刷新权限

刷新权限指的是从系统数据库mysql中的权限表中重新加载账户的权限。这是因为GRANT、CREATEUSER等操作会将服务器的缓存信息保存到内存中，而REVOKE、DROPUSER操作并不会同步到内存中。因此可能会造成服务器内存的消耗，所以在REVOKE、DROPUSER操作后建议使用FLUSHPRIVILEGES重新加载账户的权限。具体的SQL语句如下。mysql>FLUSHPRIVILEGES;项目任务分解任务3-1：建立MySQL普通账户并用普通账户登录任务说明

安装配置完MySQL之后需要创建普通账户，尽管root账户具有最高权限，但还是需要普通账户进行管理。本任务要求创建MySQL普通账户并用普通账户登录。任务实施过程项目任务分解任务3-2：为MySQL普通账户授权数据库的远程访问任务说明

在上个任务中，创建了一个普通账户，但是这个账户只限于本地登录，不能用于远程访问。本任务要求为这个账户授权数据库的远程访问。任务实施过程项目任务分解任