202108091043 - mysql权限控制

mysql库下

• 权限表：user表，db表和table_priv
  • user表：用户 + IP 能否连接数据库
  • db表：哪些用户对哪些数据库有哪些权限
  • tables_priv表：用户对哪些表有哪些权限

示例: 给用户hehe操作test库的goods表的insert，select，update的权限

-- 定义给用户hehe操作test库的goods表的insert，select，update的权限
grant insert,select,update on test.goods to hehe@'localhost' identified by '123456';

-- 回收权限
revoke update on test.* from hehe@'localhost';

-- 刷新权限
flush privileges;

实战

-- 测试
CREATE USER 'nau'@'60.12.1.19' IDENTIFIED BY 'Bo@123';

grant insert,select on toess.q_j_mum_flo_h_nau to nanhu@'6.12.1.19' identified by 'Bo@123' with grant option;

flush privileges;

--- 

-- 应用
CREATE USER 'nau'@'16.62.15.10' IDENTIFIED BY 'Nau@123' ;

grant insert,select on tou_as.q_j_me_flow_h_na to na@'11.62.15.10' identified by 'Nau@123' with grant option;

flush privileges;

grant all privileges on dam.* to'jr'@'60.12.1.1' IDENTIFIED BY 'nc@123' with grant option;