MySQL中授权(grant)和撤销授权(revoke)的使用方法

创建一个用户tom，密码为tom：

insert into user(host,user,password) values('localhost','tom',password('tom'));
create user 'tom'@'localhost' identified by 'tom';

创建数据库tom_db：

GBK: create database `tom_db` default character set gbk collate gbk_chinese_ci;
UTF8: create database `tom_db` default character set utf8 collate utf8_general_ci;
UTF8MB4: create database `tom_db` default character set utf8mb4 collate utf8mb4_unicode_ci;

给用户tom赋予所有库的所有权限：

grant all privileges on *.* to tom@localhost identified by 'tom';
grant all privileges on *.* to tom@"%" identified by 'tom';

授权用户tom拥有数据库tom_db的所有权限：

grant all privileges on tom_db.* to tom@localhost identified by "tom";
grant all privileges on tom_db.* to tom@"%" identified by "tom";

授权用户tom拥有数据库tom_db的部分权限：

grant select,insert,update,delete on tom_db.* to tom@localhost identified by 'tom';
grant select,insert,update,delete on tom_db.* to tom@"%" identified by 'tom';

重新载入赋权表：

flush privileges;

查看当前用户(自己)权限：

show grants;

查看其他 MySQL 用户权限：

show grants for tom@localhost;

撤销已经赋予给 MySQL 用户权限的权限:

revoke all on *.* from tom@localhost;
revoke all on tom_db.* from tom@localhost;

如果想让授权的用户，也可以将这些权限 grant 给其他用户，需要选项 "grant option"：

grant select on tom_db.* to tom@localhost with grant option;

(资源库 www.zyku.net)