MySQL用户创建及授权

1.创建和删除用户

注：mysql用户由用户名及其来源主机构成。

1.1 创建用户

#8.0以上版本

mysql>  create user 'jeames'@'%' identified with mysql_native_password by 'jeames';

#8.0以下版本

mysql> create user 'jeames'@'%' identified by 'jeames';

1.2 删除用户

mysql> drop user 'jeames'@'%';

2.赋权及回收权限

mysql> show grants for jeames@'%';

该权限只能用于数据库登陆，不能执行任何操作；且usage权限不能被回收

2.1 赋权限

##给普通用户授予操作数据库表的权限

root@mysql8020:/# mysql -uroot -p

mysql> grant select,insert,update,delete on db1.student to jeames@'%;'

mysql> select * from db1.student;

mysql> show grants for jeames@'%';

mysql> flush privileges;

##创建、修改、删除数据库表结构的权限

mysql> grant create on db1.* to `jeames`@`%`;

mysql> grant alter on db1.* to `jeames`@`%`;  

mysql> grant drop on db1.* to `jeames`@`%`;  

##操作数据库存储过程、函数的权限

mysql> grant create routine on db1.* to `jeames`@`%`;

mysql> grant alter routine on db1.* to `jeames`@`%`;

mysql> grant execute on db1.* to `jeames`@`%`;

2.2 回收权限

revoke select,insert,update,delete on db1.student to jeames@'%;';