修改 root 用户密码
ALTER USER 'root'@'%' IDENTIFIED BY 'password';创建用户
CREATE USER IF NOT EXISTS 'user_name'@'host_name' IDENTIFIED WITH mysql_native_password by 'password';创建用户（可远程登陆）
CREATR USER username@'%' IDENTIFIED BY 'password';创建 admin 账号，允许从任何主机进行登录并拥有所有数据库权限
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;为用户分配权限
GRANT privileges ON database.table TO username;分配查询权限
GRANT SELECT ON database.table TO username;| 权限 | 意义 |
|---|---|
| ALL [PRIVILEGES] | 设置除GRANT OPTION之外的所有简单权限 |
| ALTER | 允许使用ALTER TABLE |
| ALTER ROUTINE | 更改或取消已存储的子程序 |
| CREATE | 允许使用CREATE TABLE |
| CREATE USER | 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES |
| CREATE VIEW | 允许使用CREATE VIEW |
| DELETE | 允许使用DELETE |
| DROP | 允许使用DROP TABLE |
| INDEX | 允许使用CREATE INDEX和DROP INDEX |
| INSERT | 允许使用INSERT |
| SELECT | 允许使用SELECT |
| SHOW DATABASES | SHOW DATABASES显示所有数据库 |
| SHOW VIEW | 允许使用SHOW CREATE VIEW |
| SHUTDOWN | 允许使用mysqladmin shutdown |
| UPDATE | 允许使用UPDATE |
| GRANT OPTION | 允许授予权限 |
分配dbadmin级权限(单库)
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, CREATE TEMPORARY TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, EVENT, TRIGGER ON database_.* TO 'username'@'host';
GRANT FILE ON *.* TO 'username'@'host';FILE 是全局权限，无法下发到指定数据库/表刷新权限
FLUSH PRIVILEGES;重命名用户
RENAME USER 'username'@'hostname' TO 'newname'@'newhost';删除用户
DROP USER 'username'@'hostname';
[1]: https://blog.olimc.top/usr/uploads/2023/05/538021772.png