数据库MySQL用户创建、角色创建、权限授予

– 1.查询现有用户
use mysql;
select * from user;
– 2.创建用户
create user ‘zhengfan’@‘localhost’ identified by ‘2000’;
create user ‘wanglaoshi’@‘localhost’ identified by ‘1234’;
– 3.用户身份鉴定（DOS命令行）

– 4.删除用户
drop user ‘zhengfan’@‘localhost’;
drop user ‘wanglaoshi’@‘localhost’;
– 5.查询权限
show grants for ‘zhengfan’@‘localhost’;
show grants for ‘wanglaoshi’@‘localhost’;
– 6.授予权限
– (1)全部权限
grant all on mydb.student to zhengfan@localhost;
– (2)部分权限
grant select on mydb.student to zhengfan@localhost;
– (3)传递权限
grant update on mydb.student to zhengfan@localhost with grant option;
– 7.撤回权限
revoke all on mydb.student from zhengfan@localhost;
revoke select on mydb.student from zhengfan@localhost;
– 8.创建角色
create role teach;
– 给角色授予权限
grant select on mydb.student to teach;
– 给角色分配角色成员
grant teach to zhengfan@localhost;
grant teach to wanglaoshi@localhost;
– 激活角色，激活后需要重新连接用户
set default role all to ‘zhengfan’@‘localhost’;
set default role all to ‘wanglaoshi’@‘localhost’;
– 收回角色成员
revoke teach from zhengfan@localhost;
– 删除角色
drop role teach;