【等保加固】MySQL5.7.38

【等保加固】MySQL5.7.38MySQL5.7.38密码复杂度配置和密码有效期#为空则未启用插件SHOWVARIABLESLIKEvalidate_password%;#查看是否启用插件为空未启用SELECTPLUGIN_NAME,PLUGIN_STATUSFROMINFORMATION_SCHEMA.PLUGINSWHEREPLUGIN_NAMEvalidate_password;#安装插件INSTALL PLUGIN validate_passwordSONAMEvalidate_password.so;#查看密码有效期SHOWVARIABLESLIKEdefault_password_lifetime;#设置密码有效期 90 天SETGLOBALdefault_password_lifetime90;登录失败处理功能和登录超时自动退出#安装登录失败处理 空闲登录超时#安装插件INSTALL PLUGIN CONNECTION_CONTROLSONAMEconnection_control.so;INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTSSONAMEconnection_control.so;#查看插件状态SHOWVARIABLESLIKEconnection_control%;#连续失败5次后触发延迟SETGLOBALconnection_control_failed_connections_threshold5;SETGLOBALconnection_control_min_connection_delay60000;SETGLOBALconnection_control_max_connection_delay2147483647;#设置登录超时SETGLOBALinteractive_timeout1800;SETGLOBALwait_timeout1800;#查看SHOWVARIABLESLIKEwait_timeout,interactive_timeout;查看用户和创建三权限用户#查看用户情况SELECTuser,host,account_lockedFROMmysql.user;#锁定账号ALTERUSERsysadminlocalhostACCOUNTLOCK;#解锁账号ALTERUSERsysadminlocalhostACCOUNTUNLOCK;#创建账户#1.系统管理员 sysadmin仅本地登录CREATEUSERsysadminlocalhostIDENTIFIEDBY你的强密码;#2.安全管理员 secadmin仅本地登录CREATEUSERsecadminlocalhostIDENTIFIEDBY你的强密码;#3.审计管理员 auditor仅本地登录CREATEUSERauditorlocalhostIDENTIFIEDBY你的强密码;#分配权限#sysadmin 系统管理员权限运维、建库、数据操作不能管账号安全GRANTCREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT,INDEX,REFERENCES,LOCKTABLES,PROCESS,RELOADON*.*TOsysadminlocalhost;#secadmin 安全管理员账号管理、安全策略不能随意删业务数据GRANTCREATEUSER,ALTERUSER,DROPUSER,GRANTOPTION,REVOKE,PROCESS,FLUSH,SHOWDATABASESON*.*TOsecadminlocalhost;# auditor 审计管理员纯只读只能查看禁止修改/删除GRANTSELECT,PROCESS,SHOWDATABASES,SHOWVIEWON*.*TOauditorlocalhost;#刷新权限立即生效FLUSHPRIVILEGES;#设置 3 个账号密码有效期 90 天ALTERUSERsysadminlocalhostPASSWORD EXPIREINTERVAL90DAY;ALTERUSERsecadminlocalhostPASSWORD EXPIREINTERVAL90DAY;ALTERUSERauditorlocalhostPASSWORD EXPIREINTERVAL90DAY;审计功能#查看审计功能SHOWVARIABLESLIKEgeneral_log%;#启用审计SETGLOBALgeneral_logON;配置地址限制#1、先创建账号替换成你的root密码CREATEUSERroot192.168.2.100IDENTIFIEDBY你的root密码;#2、再授权GRANTALLPRIVILEGESON*.*TOroot192.168.2.100WITHGRANTOPTION;#3、设置密码有效期90天ALTERUSERroot192.168.2.100PASSWORD EXPIREINTERVAL90DAY;#4、刷新权限FLUSHPRIVILEGES;#删除地址限制DROPUSERroot192.168.2.100;#创建地址段账户CREATEUSERroot192.168.2.%IDENTIFIEDBY你的强密码;GRANTALLPRIVILEGESON*.*TOroot192.168.2.%WITHGRANTOPTION;ALTERUSERroot192.168.2.%PASSWORD EXPIREINTERVAL90DAY;FLUSHPRIVILEGES;MySQL5.7.38加固密码复杂度配置和密码有效期#为空则未启用插件SHOWVARIABLESLIKEvalidate_password%;#查看是否启用插件为空未启用SELECTPLUGIN_NAME,PLUGIN_STATUSFROMINFORMATION_SCHEMA.PLUGINSWHEREPLUGIN_NAMEvalidate_password;#安装插件INSTALL PLUGIN validate_passwordSONAMEvalidate_password.so;#查看密码有效期SHOWVARIABLESLIKEdefault_password_lifetime;#设置密码有效期 90 天SETGLOBALdefault_password_lifetime90;登录失败处理功能和登录超时自动退出#安装登录失败处理 空闲登录超时#安装插件INSTALL PLUGIN CONNECTION_CONTROLSONAMEconnection_control.so;INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTSSONAMEconnection_control.so;#查看插件状态SHOWVARIABLESLIKEconnection_control%;#连续失败5次后触发延迟SETGLOBALconnection_control_failed_connections_threshold5;SETGLOBALconnection_control_min_connection_delay60000;SETGLOBALconnection_control_max_connection_delay2147483647;#设置登录超时SETGLOBALinteractive_timeout1800;SETGLOBALwait_timeout1800;#查看SHOWVARIABLESLIKEwait_timeout,interactive_timeout;查看用户和创建三权限用户#查看用户情况SELECTuser,host,account_lockedFROMmysql.user;#锁定账号ALTERUSERsysadminlocalhostACCOUNTLOCK;#解锁账号ALTERUSERsysadminlocalhostACCOUNTUNLOCK;#创建账户#1.系统管理员 sysadmin仅本地登录CREATEUSERsysadminlocalhostIDENTIFIEDBY你的强密码;#2.安全管理员 secadmin仅本地登录CREATEUSERsecadminlocalhostIDENTIFIEDBY你的强密码;#3.审计管理员 auditor仅本地登录CREATEUSERauditorlocalhostIDENTIFIEDBY你的强密码;#分配权限#sysadmin 系统管理员权限运维、建库、数据操作不能管账号安全GRANTCREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT,INDEX,REFERENCES,LOCKTABLES,PROCESS,RELOADON*.*TOsysadminlocalhost;#secadmin 安全管理员账号管理、安全策略不能随意删业务数据GRANTCREATEUSER,ALTERUSER,DROPUSER,GRANTOPTION,REVOKE,PROCESS,FLUSH,SHOWDATABASESON*.*TOsecadminlocalhost;# auditor 审计管理员纯只读只能查看禁止修改/删除GRANTSELECT,PROCESS,SHOWDATABASES,SHOWVIEWON*.*TOauditorlocalhost;#刷新权限立即生效FLUSHPRIVILEGES;#设置 3 个账号密码有效期 90 天ALTERUSERsysadminlocalhostPASSWORD EXPIREINTERVAL90DAY;ALTERUSERsecadminlocalhostPASSWORD EXPIREINTERVAL90DAY;ALTERUSERauditorlocalhostPASSWORD EXPIREINTERVAL90DAY;审计功能#查看审计功能SHOWVARIABLESLIKEgeneral_log%;#启用审计SETGLOBALgeneral_logON;配置地址限制#1、先创建账号替换成你的root密码CREATEUSERroot192.168.2.100IDENTIFIEDBY你的root密码;#2、再授权GRANTALLPRIVILEGESON*.*TOroot192.168.2.100WITHGRANTOPTION;#3、设置密码有效期90天ALTERUSERroot192.168.2.100PASSWORD EXPIREINTERVAL90DAY;#4、刷新权限FLUSHPRIVILEGES;#删除地址限制DROPUSERroot192.168.2.100;#创建地址段账户CREATEUSERroot192.168.2.%IDENTIFIEDBY你的强密码;GRANTALLPRIVILEGESON*.*TOroot192.168.2.%WITHGRANTOPTION;ALTERUSERroot192.168.2.%PASSWORD EXPIREINTERVAL90DAY;FLUSHPRIVILEGES;