mysql 用户相关操作

用户账号的crud

# create user account
## 只允许从本地连接
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
## 哪里连都行
CREATE USER 'username'@'%' IDENTIFIED BY 'password';

# delete user account
DROP USER 'username'@'host';

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

# select
select user, host from mysql.user;

用户权限的crud

# create

# read 
SHOW GRANTS FOR 'username'@'host';

# update
GRANT PRIVILEGE_TYPE ON database_name.table_name TO 'username'@'host';

## add all privilege
GRANT SELECT on testdb.* TO 'user1'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost';

# delete
REVOKE privilege_type ON database_name.table_name FROM 'username'@'host';

REVOKE SELECT ON testdb.* FROM 'user1'@'localhost';
REVOKE ALL PRIVILEGES ON *.* FROM 'user1'@'localhost';