实例:
sql>CREATE USER canal IDENTIFIED BY 'canal';
sql>GRANT SELECT,INSERT,DELETE,UPDATE,REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'canal'@'%';
sql>FLUSH PRIVILEGES;
权限未生效可能需要重启连接数据库的应用
CREATE USER username IDENTIFIED BY 'password';--创建用户
drop user 用户名@"客户端地址";--删除用户
GRANT SELECT,REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'username'@'%';
grant 逗号分隔的权限名字列表 on 库名.表名 to 用户名@"客户端地址" identified by "密码" with grant option;//有授权权限,可转授权
revoke 权限列表 on 库名.表名 from 用户名@"客户端地址";//撤销权限
flush privileges; //刷新权限表,保证修改生效
权限名字 all select update insert delete ...
*.*所有库所有表
db.tableName 某库某表客户端地址 %通配符
show grants;查看当前用户权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*81F5E21E3531AEBFB6AF209E1B' WITH GRANT OPTION相关数据库和表
mysql库
user用户表
db数据库权限表
tables_priv
columns_priv
procs_priv
proxies_priv