创建用户

CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';
-- 比如：
CREATE USER 'kimen'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'kimen'@'192.168.1.230' IDENTIFIED BY '123456';
CREATE USER 'kimen'@'%' IDENTIFIED BY '123456';

注意

创建完用户后，该用户只能连接数据库服务器，但是没有权限

授权

GRANT privilegesName ON databasename.tablename TO 'username'@'hostname'
-- 比如：
GRANT SELECT, INSERT ON test.tbL_user TO 'kimen'@'%';
GRANT ALL ON *.* TO 'kimen'@'%';
GRANT ALL ON test.* TO 'kimen'@'%';

用以上命令授权的用户不能给其它用户授权，加上WITH GRANT OPTION可让该用户获得授权权利：

GRANT ALL ON test.tbL_user TO 'kimen'@'%' WITH GRANT OPTION;

Tips

当数据库名称带有中横线-时，databasename需要增加反引号

GRANT ALL ON `test-a`.* TO 'kimen'@'%';

修改用户密码

SET PASSWORD FOR 'username'@'hostname' = PASSWORD('newpassword');
-- 如
SET PASSWORD FOR 'kimen'@'%' = PASSWORD("123456");
-- 如果是当前用户
SET PASSWORD = PASSWORD("newpassword");

撤销用户权限

REVOKE privilegesName ON databasename.tablename FROM 'username'@'hostname';
-- 比如
REVOKE SELECT ON *.* FROM 'kimen'@'%';

删除用户

DROP USER 'username'@'hostname';
-- 比如：
DROP USER 'kimen'@'%';

立即生效

flush privileges;

查看用户权限

show grants for username;