MySQL用户管理

MySQL

MySQL中的用户分为root用户和普通用户，两种用户的权限是不同的。

一、权限表

MySQL安装完成后会自动生成一个名为mysql的数据库，该库里面存储的都是权限表。

1. User表

字段有45个，可分为类别为：

• 用户列

• 权限列

• 安全列

• 资源控制列

（1）用户列
用户列包括Host（Key）、User（Key）、authentication_string（v5.7）；
（2）权限列
a. 权限列包括Select_priv、Insert_priv等以priv结尾的字段；
b. 这些字段值只有Y/N，一般可以使用grant语句给用户赋予一些权限，也可通过update语句更新User表来设置相应的权限（注：更新User表之后，一定要执行FLUSH PRIVILEGES来重载权限表）
（3）安全列
a. 安全列包括ssl_type、ssl_cipher、x509_issuer、x509_subject；
b. ssl用于加密，x509标准可以用来标识用户；
c. show variables like 'have_openssl'查看是否具有ssl功能
（4）资源控制列
资源控制列包括max_questions、max_updates、max_connections和max_user_connections，默认值为0，表示无限制。

2. DB表

db表存储了某个用户对一个数据库的权限。
（1）用户列
用户列包括Host、Db和User三个字段；
（2）权限列
除了用户列剩下的都是权限列，如Create_routine_priv和Alter_routine_priv等
注：User表中的权限是针对所有数据库的，用户获取内容是先根据User表中的权限获取，然后再根据db表的内容获取权限。

3. tables_priv表和columns_priv表

1. tables_priv表中记录的是对单个表进行权限设置；
   有8个字段：Host、DB、User、Table_name、Table_priv、Column_priv、Timestamp（字段存储更新的时间）、Grantor（权限的设置者）

2. columns_priv表中记录的是对单个数据列进行权限设置；
   有7个字段：Host、Db、User、Table_name、Column_name、Column_priv、Timestamp

注：

• MySQL权限分配是按照user表-> db表 -> table_priv表 -> columns_priv表的顺序进行分配的
• 在数据库系统中，先判断user表中的值是否为'Y'，如果user表中的值是'Y'，就不需要检查后面的表；如果user表为N，则再一次检查后面的表。

4. procs_priv表

procs_priv表可以对存储过程和存储函数进行权限设置；
有8个字段：Host、DB、User、Routine_name（存储过程或函数名称）、Routine_type（类型[取值有：FUNCTION/PROCEDURE]）、Proc_priv（拥有的权限[Execute：执行；Alter Routine：修改；Grant：权限赋予]）、Timestamp、Grantor

二、账户管理

1. 新建用户

1. 使用CREATE USER语句来创建新的用户（无权限）
   CREATE USER user [IDENTIFIED BY [PASSWORD] 'password'];
   eg：create user 'admin'@'192.168.189.%' identified by '123456';
   （注：其中user由User@Host构成，如“'admin'@'%'”[%表示对所有非本地主机授权，不包括localhost,一定要用单引号括起]，另可用IP方式如'192.168.189.%'[localhost为'127.0.0.1']；另外无需password()函数加密，数据库自动密码加密）

2. 直接在mysql.user表中INSERT用户（对user表的INSERT权限）
   eg：insert into user(host,user,authentication_string,select_priv,insert_priv,update_priv,ssl_cipher,x509_issuer,x509_subject) values ('localhost','guest',PASSWORD('guest123'),'Y','Y','Y','','','');
   flush privileges;

3. 使用GRANT语句来新建用户（可创建多个，创建的同时授权，但只能创建带有密码的账户）
   GRANT priv_type ON database.table TO user IDENTIFIED BY 'password';
   eg：GRANT SELECT ON *.* TO admin@127.0.0.1 identified by '123';
   （注：*.* 与 db.* 的区别在于， *.* 对所有数据库生效，所以user表的SELECT会变为'Y'，而db.*使得user表为'N'，更改的是DB表）

2. 删除用户

1. DROP USER语句
   DROP USER user [,user]...
   eg：drop user admin@'%'; / drop user admin @127.0.0.1;

2. DELETE语句
   eg：DELETE FROM mysql.user WHERE Host = '%' and User = 'username';
   flush privileges;

3. 修改密码

1. 使用mysqladmin命令来修改root用户的密码（不进入数据库）
   eg：mysqladmin -u -root -p password "new_password";

2. 修改user表
   eg：UPDATE user SET Password = PASSWORD('123') WHERE USER = 'username';（flush privileges）

3. 使用SET语句（root用户登录）

   • 修改root：eg SET PASSWORD = PASSWORD("123");
   • 修改其它：eg SET PASSWORD FOR 'username'@'%'=PASSWORD('123');

4. GRANT语句
   GRANT priv_type ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password'];
   eg：GRANT SELECT ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD('123');

三、权限管理

1. MySQL权限列表REF

2. 授权（GRANT）

GRANT priv_type [(column_list)] ON database.table
TO user [IDENTIFIED BY [PASSWORD] 'password']
[,user [IDENTIFIED BY [PASSWORD] 'password']]...
WITH with_option [with_option]...;
【with_option】：GRANT OPTION、MAX_QUERIES_PER_HOUR count、MAX_UPDATES_PER_HOUR count、MAX_CONNECTIONS_PER_HOUR count和MAX_USER_CONNECTIONS count（5个）
eg：GRANT SELECT,UPDATE ON *.*
TO 'username'@'%'
WITH GRANT OPTION;

3. 取消权限（REVOKE）

1. 某些权限
   REVOKE priv_type[(column_list)] ON database.table FROM user[,user];
   eg：REVOKE SELECT ON *.* FROM 'username'@'%';

2. 全部权限
   REVOKE ALL PRIVILEGES,GRANT OPTION FROM user[,user]...;
   eg：REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'username'@'%';

4. 查看权限

SHOW GRANTS;