一.#查询mysql所有用户
select * from mysql.`user`;
二.#创建用户
#username:你将创建的用户名
#host:登录地址,如果是本地用户可用localhost,如果想让该用户可以从任意远程主机登陆,可以使用通配符%
#password:该用户的登陆密码
CREATE USER 'username'@'ip地址' IDENTIFIED BY 'password';
#本地用密码登录
CREATE USER 'username'@'localhost' IDENTIFIED BY '123456';
#IP地址且用密码登录
CREATE USER 'username'@'IP地址' IDENDIFIED BY '123456';
#通配符%表示主机不限制用密码登录
CREATE USER 'username'@'%' IDENTIFIED BY '123456';
#密码可以为空
CREATE USER 'username'@'%' IDENTIFIED BY '';
#不用密码denglu
CREATE USER 'username'@'%';
#例子:
CREATE USER 'mmc'@'localhost' IDENTIFIED BY '123456';
三.#授权
#privileges:用户的操作权限,如SELECT,INSERT,UPDATE等
#如果要授予所的权限则使用ALL
#databasename:数据库名称
#tablename:表名,如果要授予该用户对所有数据库和表的相应操作权限则可用*表示,例如:*.*
GRANT privileges ON databasename.tablename TO 'username'@'host'
#GRANT SELECT, INSERT ON test.user TO 'mmc'@'%';
#GRANT ALL ON *.* TO 'mmc'@'%';
#例子:
GRANT SELECT ON test.userinfo TO 'mmc'@'localhost';
#备注:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:
GRANT privileges ON databasename.tablename TO 'username'@'ip地址' WITH GRANT OPTION;
#3.设置与更改用户密码
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
SET PASSWORD FOR 'cat'@'localhost' = PASSWORD('123456');
#如果是当前登陆用户用:
SET PASSWORD = PASSWORD("newpassword");
四. 撤销用户权限
REVOKE privilege ON databasename.tablename FROM 'username'@'host';
REVOKE SELECT ON *.* FROM 'mmc'@'%';
#查看所赋予用户的权限
SHOW GRANTS FOR 'mmc'@'%';
#例如:
SHOW GRANTS FOR 'mmc'@'localhost';
五.删除所创建无用的用户
DROP USER 'username'@'host';
#例如:
DROP USER 'mmc'@'localhost';