MySql权限设置

1:总体描述
grant all privileges on . to root@’%’ identified by “root”;
新插入一个mysql user其Host为%的用户
操作后show grants会看到:
GRANT ALL PRIVILEGES ON . TO ‘root’@‘localhost’ IDENTIFIED BY PASSWORD ‘*E6CC90B878B948C35E92B003C792C46C58C4AF40’ WITH GRANT OPTION
GRANT PROXY ON ‘’@’’ TO ‘root’@‘localhost’ WITH GRANT OPTION
在user表中可以看到生成了一条已%为HOST的记录

2:刷新MYSQL配置
flush privileges;// 刷新privileges

3:设置所有权限(grant 权限 on 数据库.(.*代表所有库) to 用户名@‘登录主机’ identified by “密码”😉
grant all privileges on . to root@‘192.168.1.50’ identified by “1”;
grant all privileges on . to root@‘192.168.1.56’ identified by “1”;
grant all privileges on . to root@‘192.168.1.42’ identified by “1”;
grant all privileges on . to root@‘192.168.1.69’ identified by “1”;
grant all privileges on . to root@‘192.168.1.236’ identified by “1”;
grant all privileges on . to root@‘192.168.1.124’ identified by “1”;
grant all privileges on . to root@‘192.168.1.123’ identified by “1”;
grant all privileges on . to root@‘192.168.1.188’ identified by “1”;// 内网测试服务器
注意:同上,只是HOST变为了192.168.1.124,其中identified by后的1为访问密码,需要flush privileges;这样show grants for ts@192.168.1.124;才能更新

4:撤销HOST为指定值的所有权限,即只能登录,该操作完成后可以看到USER表中的大部分字段都被设为了N(revoke是grant的反向,使用时注意from和to)
revoke all on . from root@192.168.1.50;
revoke all on . from root@192.168.1.56;
revoke all on . from root@192.168.1.42;
revoke all on . from root@192.168.1.69;
revoke all on . from root@192.168.1.236;
revoke all on . from root@192.168.1.124;
revoke all on . from root@192.168.1.123;
revoke all on . from root@192.168.1.188;// 内网测试服务器

5:查看指定HOST用户的操作权限
show grants;// 查看本机grants
show grants for root@192.168.1.50;
show grants for root@192.168.1.56;
show grants for root@192.168.1.42;
show grants for root@192.168.1.69;
show grants for root@192.168.1.236;
show grants for root@192.168.1.124;
show grants for root@192.168.1.123;
show grants for root@192.168.1.188;// 内网测试服务器

6:设置只能查询,插入,修改,删除指定表的权限
// (192.168.1.50)
grant select, insert, update, delete on ts.content to root@192.168.1.50;
grant select, insert, update, delete on ts.logentity to root@192.168.1.50;
grant select, insert, update, delete on ts.content_lx to root@192.168.1.50;

revoke select, insert, update, delete on ts.module from root@192.168.1.50;

// (192.168.1.42)
grant select, insert, update, delete on ts.shuser to root@192.168.1.42;
grant select, insert, update, delete on ts.logentity to root@192.168.1.42;
grant select, insert, update, delete on ts.posjiesuan to root@192.168.1.42;
grant select, insert, update, delete on ts.posbankdata to root@192.168.1.42;
grant select, insert, update, delete on ts.poszhongduan to root@192.168.1.42;
grant select, insert, update, delete on ts.posjiesuan_cd to root@192.168.1.42;
grant select, insert, update, delete on ts.posjiesuan_fksqcd to root@192.168.1.42;
grant select, insert, update, delete on ts.posjiesuan_fksqhd to root@192.168.1.42;

revoke select, insert, update, delete on ts.module from root@192.168.1.42;

// (192.168.1.69)
grant select, insert, update, delete on ts.content to root@192.168.1.69;
grant select, insert, update, delete on ts.logentity to root@192.168.1.69;
grant select, insert, update, delete on ts.content_lx to root@192.168.1.69;

revoke select, insert, update, delete on ts.module from root@192.168.1.69;

// (192.168.1.236)
revoke select, insert, update, delete on ts.module from root@192.168.1.236;

// (192.168.1.124)
grant select, insert, update, delete on ts.game to root@192.168.1.124;
grant select, insert, update, delete on ts.hyuser to root@192.168.1.124;
grant select, insert, update, delete on ts.game_lx to root@192.168.1.124;
grant select, insert, update, delete on ts.game_area to root@192.168.1.124;
grant select, insert, update, delete on ts.logentity to root@192.168.1.124;
grant select, insert, update, delete on ts.game_zone to root@192.168.1.124;
grant select, insert, update, delete on ts.game_company to root@192.168.1.124;

revoke select, insert, update, delete on ts.module from root@192.168.1.124;

// (192.168.1.123)
grant select, insert, update, delete on ts.game to root@192.168.1.123;
grant select, insert, update, delete on ts.game_lx to root@192.168.1.123;
grant select, insert, update, delete on ts.game_area to root@192.168.1.123;
grant select, insert, update, delete on ts.logentity to root@192.168.1.123;
grant select, insert, update, delete on ts.game_zone to root@192.168.1.123;
grant select, insert, update, delete on ts.game_company to root@192.168.1.123;

revoke select, insert, update, delete on ts.module from root@192.168.1.123;

// 内网测试服务器192.168.1.120
revoke select, insert, update, delete on ts.module from root@192.168.1.120;