mysql_账号_用户_角色_权限

1 新建账号

语法：create user 'usernamer'@'hostname' identified by 'password';

实例：create user 'dba'@'%' identified by 'pw_123456';

2 账号授权

语法：grant 权限 on db.table to 'username'@'hostname' identified by 'password' with grant option;

实例：grant all privileges on *.* to 'dba'@'%' identified by 'pw_123456' with grant option;

3 刷新权限，使之生效

flush privileges;

4 查看账号权限

show grants for 'dba'@'%';

5 回收权限

revoke create on *.* from 'dba'@'%';

flush privileges;

6 锁定账号

alter user 'dba'@'%' account lock; 

7 解锁账号

alter user 'dba'@'%' account unlocak; 

8 删除账号

drop user 'dba'@'%' ;

9 修改用户名和主机

rename user 'dba'@'%' to 'dba_old'@'192.168.3.2'

10 设置密码有效期 

create user 'dba'@'%' identified by 'pwd_123456' password expire interval 30 day

alter user 'dba'@'%' password expire interval 30 day;

11 设置密码（永不过期）

alter user 'dba'@'%' password expire never;

12 修改密码

set password for 'dba'@'%' = password('pwd_new');  

13 新建角色

create role 角色名

14 角色赋权

grant 某个权限 on *.* to 角色

15 给用户授权角色权限

grant 角色 to 'dba'@'%'