Mysql创建新用户并指定权限

Mysql创建新用户并指定权限

root身份登录mysql

sudo mysql -u root -p 

创建新用户

create user '用户名'@'%' identified with mysql_native_password by '密码';
create user 'test'@'%' identified with mysql_native_password by '123456';#用户名test,密码123456

给该用户添加权限

grant all privileges on *.* to `用户名`@'%';#全部数据库
grant all privileges on *.* to `用户名`@'localhost';
grant all privileges on *.* to `test`@'localhost';

grant all privileges on 想授权的数据库.* to '用户名'@'%';#指定数据库
grant all privileges on student_message.* to 'test'@'%';

grant select privileges on 想授权的数据库.* to '用户名'@'%';#只查询权限
grant select privileges on student_message.* to 'test'@'%';

刷新权限缓存：flush privileges;

删除用户

delete from mysql.user where user='用户名';
delete from mysql.user where user='test';

重启mysql服务

#windows
net stop mysql
net start mysql

#ubuntu
sudo service mysql restart