需求:添加mysql用户,使得添加的用户只能用户操作某一个数据库的权限
- 创建用户:CREATE USER ‘testUser’@‘%’ IDENTIFIED BY ‘123456’;
此时可能会提示:
Your password does not satisfy the current policy requirements
- 查看完整密码规则
SHOW VARIABLES LIKE 'validate_password%';
validate_password_check_user_name OFF
validate_password_dictionary_file
validate_password_length 4
validate_password_mixed_case_count 1
validate_password_number_count 1
validate_password_policy LOW
validate_password_special_char_count 1
这里我已经修改过了,如果没有修改的情况下:validate_password_policy默认是medium;validate_password_length长度默认是8
- 需要输入两个命令,是设置的密码变得更加简单
SET GLOBAL validate_password_length = 4;
SET GLOBAL validate_password_policy=LOW;
- 设置完后再次输入创建用户的sql语句就不会报错了.
CREATE USER 'testUser'@'%' IDENTIFIED BY '123456';
- 指定用户用户某个数据库的权限
GRANT SELECT, INSERT, UPDATE, REFERENCES, DELETE, CREATE, DROP, ALTER, INDEX, TRIGGER, CREATE VIEW, SHOW VIEW, EXECUTE, ALTER ROUTINE, CREATE ROUTINE, CREATE TEMPORARY TABLES, LOCK TABLES, EVENT ON `mxc-contract`.* TO 'testUser'@'%';
- 刷新权限
FLUSH PRIVILEGES;
- 查看当前用户及权限
SELECT user,host FROM mysql.user;
- 如果想要删除创建的用户
DELETE FROM mysql.user WHERE user = 'testUser'