mysql新建权限受控制的用户：支持正常增删改查及文件导出命令mysqldump

代码如下：

CREATE USER 'xxxx'@'localhost' IDENTIFIED BY '账户链接密码';
GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, CREATE, RELOAD, DROP, INDEX, LOCK TABLES, REFERENCES ON 数据库名.* TO 'xxxx'@'localhost';

CREATE USER 'xxxx'@'192.168.0.%' IDENTIFIED BY '账户链接密码';
GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, CREATE, RELOAD, DROP, INDEX, LOCK TABLES, REFERENCES ON 数据库名.* TO 'xxxx'@'192.168.0.%';

CREATE USER 'xxxx'@'%' IDENTIFIED BY '账户链接密码';
GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, CREATE, RELOAD, DROP, INDEX, LOCK TABLES, REFERENCES ON 数据库名.* TO 'xxxx'@'%';

CREATE USER 'xxxx'@'127.0.0.1' IDENTIFIED BY '账户链接密码';
GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, CREATE, RELOAD, DROP, INDEX, LOCK TABLES, REFERENCES ON 数据库名.* TO 'xxxx'@'127.0.0.1';
FLUSH PRIVILEGES;

 

其中以上命令创建一个账户叫做xxxx，针对不同的网络（localhost，127.0.0.1、内网192.168.0 c段，任意网段0.0.0.0)设置了权限

select 查询

insert 插入

update 更新

delete 产出

alter 修改表元数据信息

create 创建表、过程、函数等

drop 删除表、过程、函数等

index 索引功能

lock tables 梭镖

reload 导出数据功能

 

 数据库中实际数据如下：

 

使用上面的受限账户，只能看到以下数据库