Mysql安全之权限用户管理参考手册

一、背景

日常Mysql维护过程中，基于安全要求和规定，需要对Mysql进行分权，接入金库、账户密码满足16位复杂度，对特定表授权，只读用户，最小化权限等处理；本文简要梳理下常用命令操作，以供有需者参考操作。

在这里插入图片描述

更多参看：MySQL用户管理；

二、安全权限配置

2.1、创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
#创建本地主机登录的用户
CREATE USER 'admin'@'localhost' IDENTIFIED BY '123456';
#创建指定主机登录的用户
CREATE USER 'monitor'@'192.168.10.3' IDENTIFIED BY 'Monitor123';
# 创建所有远程主机都可以登录的用户
CREATE USER 'admin'@'%' IDENTIFIED BY '123456';
#查看默认用户
SELECT user,host,account_locked FROM mysql.user;
#修改用户信息
RENAME USER user_01@localhost to user_01@'127.0.0.1';#删除角色monitor
drop role 'monitor'@'localhost';
#删除用户账号user_01、user_02
drop USER user_01@localhost,user_02@localhost;
#查看MySQL下所有用户账号列表
USE mysql;
SELECT * FROM USER;
#修改密码
set password for username @localhost = password(newpwd); //新密码必须使用 PASSWORD() 函数来加密，如果不使用 PASSWORD() 加密，也会执行成功，但是用户会无法登录
set password FOR user_01@localhost ='123456';
update mysql.user set authentication_string=password('新密码') where user='用户名' and Host ='localhost';
#如果是普通用户修改密码，可省略 FOR 子句来更改自己的密码
SET PASSWORD = PASSWORD('newpwd');
flush privileges;
#root密码
mysqladmin -u username -h hostname -p password "newpwd"
UPDATE mysql.user set authentication_string = PASSWORD ("rootpwd) WHERE User = "root" and Host="localhost";
#或
SET PASSWORD = PASSWORD ("rootpwd");
FLUSH PRIVILEGES;#创建本地角色admin
CREATE role 'admin'@'localhost';
#授予角色admin查询slot_info表的权限。
GRANT SELECT ON TABLE spms.slot_info TO 'admin'@'localhost';
#授予用户账号user_01角色admin的权限。
GRANT 'admin'@'localhost' TO user_01@'localhost';
SET GLOBAL activate_all_roles_on_login = ON;
#撤消用户账号user_02角色admin的权限。
revoke ALL PRIVILEGES ,GRANT OPTION FROM 'admin'@'localhost';
#删除角色student。
drop role 'admin'@'localhost';

注意：当忘记密码时，我们常在my.cnf中配置启用skip-grant-tables，使服务器不使用/绕过权限系统，给每个mysql用户完全访问所有数据库的权力。通过执行 mysqladmin flush-privileges或 mysqladmin reload或flush privileges语句，可以让一个正在运行的服务器再次开始使用授权表。

2.2、授权

grant all privileges on *.* to 'admin'@'%' identified by 'Admindb_123456';
#授予用户对admin数据库的读写权限
GRANT SELECT,INSERT ON admin.* TO 'localUser'@'%';
#授予用户对所有数据库数据表的所有权限
GRANT ALL ON *.* TO 'localUser'@'%';
#授予更新（update）权限
GRANT UPDATE ON TABLE spms.* TO user_01@localhost;
#授予用户账号admin修改表结构的权限
GRANT ALTER ON TABLE spms.slot TO user_01@localhost;
#授予用户账号user_01调用cn_proc存储过程的权限
GRANT EXECUTE on PROCEDURE spms.slot TO user_01@localhost;
#授予用户账号user_01在spms数据库上创建表、删除表、查询数据、插入数据的权限
GRANT CREATE,SELECT,INSERT,DROP ON spms.* TO user_01@localhost;
#授予全部权限
grant all privileges on spms.* to 'admin'@'%' identified by 'Admindb_123456';#撤消用户账号user_01在spms数据库上创建表、删除表、查询数据、插入数据的权限
revoke CREATE,SELECT,INSERT,drop on spms.* to 'admin'@'%' identified by 'Admindb_123456';
#撤消用户账号user_01所有权限
REVOKE ALL PRIVILEGES,GRANT OPTION FROM  user_01@localhost;
#查看权限
SHOW GRANTS FOR 'username'@'hostname';

2.3、中转

#创建新表slot_mir，与表slot_info完全相同,作为后者的副本进行中转操作
CREATE TABLE spms.slot_mir SELECT * FROM spms.slot_info;
#创建存储过程slot_proc，统计slot_info表中的行数。
DELIMITER@@
CREATE PROCEDURE  spms.slot_proc()
BEGIN
DECLARE n INT;
SELECT COUNT(*) INTO n FROM spms.slot_info;
SELECT n;
END@@

2.4、其他

1）mysql启用尽量使用mysql用户，且mysql用户无本地登录权限，当使用mysql用户启动数据库时，可以防止任何具有file权限的用户能够用root创建文件。而如果使用root用户启动数据库，则任何具有file权限的用户都可以读写root用户的文件。这样会做系统造成严重的安全隐患。
2）注意防止DNS欺骗：创建mysql用户时，user权限表的host可以指定域名或者ip地址，但是当使用域名时，就可能带来如下安全隐患：如域名对应的ip址址被恶意修改，则数据库就会被恶意的ip地址进行访问，导致安全隐患。
3）my.cnf配置文件进行严格的权限控制(改成mysql属主，设置权限为600)
4）不要给全部用户all privileges权限，只授予账号必须的权限；
5）除root外，任何用户不应有mysql库user表的写权限(update,insert,delete)；
6）除root外，不要把file, process,super权限授予管理员以处的账号；其中，file权限主要作用是:(1)将数据库的信息通过select … into outfile… 写到服务器上有写入权限的目录下。(2)可以将有读权限的文本文件通过load data infile… 命令写入数据库表；process 权限能被用来执行“show processlist” 命令，查看当前所有用户执行查询的明文文本。super权限能执行kill命令，终掉其它用户进程(show processlist的id进程值)。
7）drop table 命令并不收回以前的相关访问授权，导致重新创建同名的表时，以前其它用户对此表的权限会自动赋予，进而产生权限外流。因此在删除表时，要同时取消其它用户在此表的相应权限。
8）配置my.cnf启用ssl，配置前需要先运行mysql_ssl_rsa_setup生成证书，验证执行show global variables like 'have_%ssl';
9）为每个用户加上访问ip限制：创建用户时，指定user表host字段的ip或者hostname, 只有符合授权的ip或者hostname才可以进行数据库访问。
10）不需要从本地文件中Load数据到数据库中，就使用“–local-infile=0”禁用掉可以从客户端机器上Load文件到数据库中；
在这里插入图片描述