MySQL用户权限配置示例

创建数据库

CREATE DATABASE `dbname` DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;

移除权限

REVOKE命令

只保留SELECT权限

命令说明： REVOKE [权限列表,] ON {库名}.{表名} FROM '{用户名}'@'{host}'

# 移除除SELECT外的所有其他权限
REVOKE INSERT,UPDATE,DELETE,CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON product.* FROM 'guest'@'localhost'

# 刷新权限
flush privileges; 

# 查看权限列表
SHOW GRANTS FOR 'guest'@'%';

授予权限

GRANT命令
命令说明: GRANT [权限列表,] ON {库名}.{表名} TO '{用户名}'@'{host}'

# 授予查询权限
GRANT SELECT ON `product`.* TO 'guest'@'%'
# 授予所有权限
grant all privileges on `product`.* TO 'guest'@'%'

示例：
通过授权解决Navicate报错
Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

授予用户PROCESS权限即可:
GRANT PROCESS ON *.* TO 'admin'@'%';

用户操作

创建用户

CREATE USER 'woo_local'@'%' IDENTIFIED BY '123123';

降低密码安全策略

set global validate_password_policy=LOW;
set global validate_password_length=6;

修改密码

set password for 'username'@'host' = password('newpassword');

-- 如果是设置当前用户的密码
set password = password('newpassword');

# 如果是MySQL8修改密码失败， 尝试下面命令:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '5LNKrDtHsR7$';
### 这是由于MySQL8推荐使用'caching_sha2_password'代替'mysql_native_password'