关于mysql的用户策略的一些基本控制

user control in mysql

user create and update password

1.create

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

2.delete

DROP USER 'username'@'host';

3.update

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

set password expire

ALTER USER 'username'@'host' PASSWORD EXPIRE;

4.change cert type

ALTER USER 'your_username'@'%' IDENTIFIED WITH mysql_native_password BY 'your_password';
FLUSH PRIVILEGES;

example :

ALTER USER 'dev_user'@'192.168.1.%' 
PASSWORD EXPIRE INTERVAL 90 DAY
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME 1;

5.read

-- get all user info
SELECT user, host FROM mysql.user;

-- get user detail info 
SELECT * FROM mysql.user WHERE user='username'\G

6.lock and unlock

-- lock user 
ALTER USER 'username'@'host' ACCOUNT LOCK;

-- unlock user 
ALTER USER 'username'@'host' ACCOUNT UNLOCK;

grant

1.grant privilege

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


ALL PRIVILEGES 
SELECT
INSERT 
UPDATE 
DELETE 
CREATE 

2.revoke privilege

REVOKE privilege_type ON database.table FROM 'username'@'host';

3.show privilege

SHOW GRANTS FOR 'username'@'host';

Example:

-- 创建开发人员账户
CREATE USER 'dev_user'@'192.168.1.%' IDENTIFIED BY 'Dev@Pass123';

-- 授予特定数据库的读写权限
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE 
ON project_db.* TO 'dev_user'@'192.168.1.%';

-- 设置密码策略
ALTER USER 'dev_user'@'192.168.1.%' 
PASSWORD EXPIRE INTERVAL 90 DAY
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME 1;