mysql用户及权限管理

创建用户

Create user命令创建

-- 命令格式
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

其中，'username’是要创建的用户名，'host’是允许连接的主机名或IP地址，'password’是要设置的密码。

--创建一个test_user
create user 'test_user'@'%' IDENTIFIED by "test_user";

create user命令创建的用户是没有任何权限的，还需要给用户授权才能正常使用。

grant命令创建

-- 命令格式
GRANT privileges ON database.table TO 'username'@'host' IDENTIFIED BY 'password';

其中，privileges是要授予的权限，database.table是要授权的数据库和表，'username’是要创建的用户名，'host’是要连接的主机名或IP地址，'password’是要设置的密码。

-- 使用grant创建一个test_user_01用户，并授予 mysql库的查询权限
grant SELECT on mysql.* to 'test_user_01'@'%' IDENTIFIED by "test_user_01"

Insert语句执行创建用户

-- 命令格式
INSERT INTO mysql.user (Host, User, Password) VALUES ('host', 'username', PASSWORD('password'));

其中，'host’是要连接的主机名或IP地址，'username’是要创建的用户名，'password’是要设置的密码。

-- 使用insert语句创建一个test_user_02的用户，没有任何权限
INSERT INTO mysql.user (Host, User, authentication_string,ssl_cipher,x509_issuer,x509_subject) VALUES ('%', 'test_user_02', PASSWORD('test_user_02'),'','','');
-- 通过insert语句创建用户后需要执行flush PRIVILEGES，目的时刷新内存，新用户才生效
flush PRIVILEGES;

需要注意的是，以上三种方式均需要具有CREATE USER或GRANT权限的用户才能执行。、

删除用户

drop user命令

-- 命令格式
DROP USER 'username'@'localhost';

这条命令会删除特定主机上的特定用户。

-- 删除test_user用户
DROP USER 'test_user_01'@'%';

直接删除user表记录

REVOKE命令

-- 命令格式
REVOKE ALL PRIVILEGES FROM 'username'@'localhost';

这条命令会撤销特定主机上特定用户的所有权限。

-- 删除test_user_03用户的所有权限（前提test_user_03要有权限才会执行成功）
REVOKE ALL PRIVILEGES on mysql.* FROM 'test_user_03'@'%';

REVEOKE命令只会回收权限，不会删除用户

delete语句

  -- 语句格式
  DELETE FROM mysql.user WHERE User='username' AND Host='localhost';

这条命令会从 mysql.user 表中删除特定主机上的特定用户。

-- 示例：删除test_user_03在主机'%'的权限
DELETE FROM mysql.user WHERE User='test_user_03' AND Host='%';
-- 注意：delete执行后不会立即生效，需要执行下面的语句后才会生效
flush PRIVILEGES

以上3个命令建议以用户+主机作为操作条件，因为这2个字段在user表中时双主键，如果只是用一个，可能会误操作其它记录导致异常。

权限管理

# 命令格式
GRANT 权限 ON 数据库对象 TO 用户 [IDENTIFIED BY 密码]

可授权的权限

• select: 查询数据权限
• insert: 插入数据权限
• update:更新/修改数据权限
• delete:删除数据的权限
• create:创建数据表权限
• alter:修改表结构权限
• drop:删除数据表权限
• references：外键权限
• create temporary：临时表权限
• index：索引权限
• create view:创建视图权限
• show view:查看视图权限
• create routine:创建存储过程的权限
• alert routine:修改存储过程的权限
• execute：执行存储过程的权限
• all [privileges]:所有权限

数据库对象

• . :表示给用户授权针对于所有数据库的所有表生效
• dbname.* :表示给用户授权只针对dbname数据库的所有表生效
• dbname.tablename :表示给用户授权只针对dbname数据库中的tablename数据表生效

用户

• username@“%” :表示允许username通过所有主机访问
• username@“localhost” ：表示只允许username通过所有本机访问
• username@“192.168.0.%” : 表示只允许username通过所有IP段为192.168.0.*的主机访问

密码

表示允许用户通过某个密码访问

存储

用户的权限信息存储在mysql库的user表和db表。user表存储了MySQL中所有用户的权限信息，而db表则存储了用户对每个数据库的权限信息

示例

1. 创建一个用户拥有所有数据库及数据表的数据增删改查权限的用户user_test,密码为user_test,允许通过所有主机链接

-- 创建同时授权
grant select,update,insert,delete on *.* to "test_user_04"@"%" IDENTIFIED by "test_user_04";
-- 查看权限
show grants for test_user_04;

2. 给test_user_04新增修改，删除，创建表权限。

-- 新增权限
grant create,drop,ALTER on *.* to "test_user_04"@"%";
-- 查看权限
show grants for test_user_04;

3. 给test_user_04 新增可授权权限(可给其他用户授权或创建用户)

-- 在授权语句后面加上"WITH GRANT OPTION"
grant select,update,insert,delete on *.* to "test_user_04"@"%" WITH GRANT OPTION;

使用update语句更新用户权限

可通过update语句更新user和db表，更新相关用户权限。执行后需要执行FLUSH PRIVILEGE 语句使修改生效。

撤销权限

撤销权限通删除用户，请查看[删除用户]章节