DCL

创建用户

# 创建用户，该用户可以使用任意主机访问数据库
create user 'zhangsan'@'%' identified by 'zhangsan123456';


drop user if exists 'zhangsan'@'%';
create user 'zhangsan'@'localhost' identified by 'zhangsan123456';

授予权限

# 授予用户张三 student 数据表的查询权限
grant select on `school`.`student` to 'zhangsan'@'%';

# 授予用户张三 school 数据库中所有数据表的查询权限
grant select on `school`.* to 'zhangsan'@'%';

# 授予用户张三 school 数据库的 insert delete update 权限
grant insert,delete,update on `school`.* to 'zhangsan'@'%';

# 如果还想授予 DDL 权限
grant create,drop,alter on `school`.* to 'zhangsan'@'%';

# 赋予所有权限
grant all privileges on *.* to 'zhangsan'@'%';

召回权限

revoke insert, delete, update on `school`.* from 'zhangsan'@'%';

revoke all privileges on *.* from 'wangdachui'@'192.168.0.%';

由于数据库会缓存用户的权限，可以在授予或召回权限后，执行下面的语句，使权限立即生效

flush privileges;