mysql 8 创建用户并赋予改用户指定数据库权限

1. 创建用户

-- 低版本数据库
create user '用户名'@'%' identified by '密码';
-- 高版本数据库
create user '用户名'@'%' identified with mysql_native_password by '密码';
-- 示例1：
create user 'test'@'%' identified with mysql_native_password by '123456';
-- 示例2：
create user 'test'@'localhost' identified with mysql_native_password by '123456';

2. 设置数据库权限

-- 指定数据库
grant all privileges on 想授权的数据库.* to '用户名'@'%';
-- 全部数据库
grant all privileges on *.* to '用户名'@'%';
-- 示例
grant all privileges on test_table.* to 'test'@'%';

3. 修改密码

alter user '用户名'@'%' identified by '密码';

-- 示例
alter user 'test'@'%' identified by 'test123'

4. 刷新生效

flush privileges;

5. 删除用户

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