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、其他

1mysql启用尽量使用mysql用户且mysql用户无本地登录权限当使用mysql用户启动数据库时可以防止任何具有file权限的用户能够用root创建文件。而如果使用root用户启动数据库则任何具有file权限的用户都可以读写root用户的文件。这样会做系统造成严重的安全隐患。
2注意防止DNS欺骗创建mysql用户时user权限表的host可以指定域名或者ip地址 但是当使用域名时就可能带来如下安全隐患 如域名对应的ip址址被恶意修改则数据库就会被恶意的ip地址进行访问导致安全隐患。
3my.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进程值)。
7drop 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文件到数据库中