账号权限

创建用户

1. 语法

WITH authentication_plugin，密码插件，默认是 caching_sha2_password 详细可以查看官方文档 (opens new window)

CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';

2. 实际操作

默认用的是caching_sha2_password

CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';

3. 修改账号

CREATE USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
ALTER USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

授权

1. 语法

权限列表可以查看官方文档 (opens new window)

GRANT PRIVILEGE ON database.table TO 'username'@'host';

2. 实际创建

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

取消权限

1. 语法

REVOKE type_of_permission ON database_name.table_name FROM 'username'@'host';

2. 显示用户权限列表

SHOW GRANTS FOR 'username'@'host';

3. 删除用户

DROP USER 'username'@'localhost';