【补充篇】MySQL权限管理

  MySQL 的权限系统主要作用有两个，一是拒绝非法用户，防止数据泄露；二是给不同合法的用户控制和管理某些表的权限，以防机密被所有人查看或者数据被不相干的人修改。

MySQL权限管理

1. 工作原理

  通过以上说明，我们已经知道，MySQL 权限系统通过两个阶段进行认证：

（1）对连接的用户进行身份认证，合法的用户通过认证，不合法的用户拒绝连接；
（2）对通过认证的合法用户赋予相应的权限，用户可以在这些权限范围内对数据库做相应的操作。

  对于身份的认证，MySQL 是通过 IP 地址和用户名联合进行确认的，例如 MySQL 安装后默认创建的用户 root@localhost 表示用户 root 只能从本地（localhost）进行连接才可以通过认证，此用户从其他任何主机对数据库进行的连接都将被拒绝。也就是说，同样的一个用户名，如果来自不同的 IP 地址，则 MySQL 将其视为不同的用户。
  MySQL 的权限表在数据库启动的时候就载入内存，当用户通过身份认证后，就在内存中进行相应权限的存取，这样，此用户就可以在数据库中做权限范围内的各种操作了。

2. 权限表的存取

  在上面介绍的两个过程中，必须使用到权限表，而MySQL的权限表在“mysql”数据库（数据库名叫“mysql”）中，里面包含三个重要的权限表，分别是user、host、db。其中最重要的表是 user 表，其次是 db 表，host 表在大多数情况下并不使用。user中的列主要分为 4 个部分：用户列、权限列、安全列和资源控制列。

表定义如下所示：

  同样的，通过分解两个步骤，了解权限表的使用：
（1）通过user表的用户列（host、user 和 password）判断用户是否合法
（2）合法用户按照以下顺序得到数据库权限 ：user->db->tables_priv->columns_priv。权限范围依次递减。

例如：用户 z1@localhost，有所有表的 select 权限。

第一步验证，用户合法
第二步：因为它有所有表的select权限，所以user表的Select_priv权限为Y，也是因为是所有表，所以用不到db表

z2@localhost 只对 test1 数据库上所有表的 select 权限

第一步验证，用户合法
第二步：因为它只有test1库，所以user表的Select_priv权限为N，db表的Db列值为test1,且db表的Select_priv权限为Y

3. 账号管理

  通过上面的介绍和演示，了解了权限系统的原理后，再接着看怎么样给创建账号、权限变更、和删除账号等DBA 日常工作。

1. 创建账号

  有两种方法可以用来创建账号：使用 GRANT 语法创建或者直接操作授权表，但更推荐使用第一种方法，因为操作简单，出错几率更少。GRANT 的常用语法如下：

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] …
ON [object_type] {tbl_name | * | . | db_name.*}
TO user [IDENTIFIED BY [PASSWORD] ‘password’]
[, user [IDENTIFIED BY [PASSWORD] ‘password’]] …
[WITH GRANT OPTION]
object_type =
TABLE
| FUNCTION
| PROCEDURE
 
例如1：创建用户 z1，权限为可以在所有数据库上执行所有权限，只能从本地进行连接
grant all privileges on *.* to ‘z1’@‘localhost’;–该语句执行后z1没有 grant 权限
 
例如2：创建用户 z1，权限为可以在所有数据库上执行所有权限且有grant权限。只能从本地进行连接
grant all privileges on *.* to ‘z1’@‘localhost’ with grant option;
 
例如3：在2的基础上设置密码为123
grant all privileges on *.* to ‘z1’@‘localhost’ identified by ‘123’ with grant option;
 
例如4：创建z2，任何IP进行连接，权限为对test1数据库里的所有表进行SELECT、UPDATE、INSERT 和 DELETE 操作，初始密码为“123”。
grant select,update,insert,delete on test.* to ‘z2’@‘%’ identified by ‘123’;
 
例 5：授予 SUPER、PROCESS、FILE 权限给用户 z3@%。
grant super,process,file on *.* to ‘z3’@‘%’;
 
例 6：只授予登录权限给 z4@localhost。
grant usage on *.* to ‘z4’@‘localhost’;

注意：

• SUPER、PROCESS、FILE 权限都属于管理权限，因此不能够指定某个数据库，on 后面必须跟 “*.*”
• usage 权限只能用于数据库登录，不能执行任何操作
• 直接操作权限表也可以实现上述，但这里不做讲解，读者自行搜索

2. 查看、修改权限

  创建完账号后，很大可能需要查看账号权限，和重新给予权限，下面将介绍查看和更改这两种常用操作的命令。

查看：

常用方法：
show grants for user@host;

show grants for ‘z1’@‘localhost’;

  
新方法：
information_schema 数据库进行权限的查看

select * from SCHEMA_PRIVILEGES where grantee=“‘z1’@‘localhost’”;

收回：
  权限的修改分两种，一种是新增（grant），一种是收回（revoke）。若新增grent，则新权限和已有的 权限进行合并，使用语法同上；REVOKE 语句可以回收已经赋予的权限，语法如下：

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] …
ON [object_type] {tbl_name | * | . | db_name.*}
FROM user [, user] …
  
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] …

-- 收回 z2@localhost 上的 INSERT 和 SELECT 权限：
revoke select,insert on *.* from 'z2'@'localhost';
-- 注意：usage 权限不能被回收，也就是说，REVOKE 用户并不能删除用户。

修改密码：

方法 1：可以用 mysqladmin 命令在命令行指定密码。
mysqladmin -u user_name -h host_name password “newpwd”
  
方法 2：执行 SET PASSWORD 语句。
SET PASSWORD FOR ‘jeffrey’@‘%’ = PASSWORD(‘biscuit’);
  
方法 3：还可以在全局级别使用 GRANT USAGE 语句(在*.*)来指定某个账户的密码而不影响账户当前的权限。
GRANT USAGE ON . TO ‘jeffrey’@‘%’ IDENTIFIED BY ‘biscuit’;
  
方法 4：直接更改数据库的 user 表。
shell> mysql -u root mysql
mysql> INSERT INTO user (Host,User,Password) VALUES(‘%’,‘jeffrey’,PASSWORD(‘biscuit’));
mysql> FLUSH PRIVILEGES;

注意：更改密码时候一定要使用 PASSWORD 函数（mysqladmin 和 GRANT 两种方式不用写，会自动加上）。

3.删除账号

  要彻底删除账号，同样也有两种方法：DROP USER 命令和修改权限表。
DROP USER 语法非常简单，具体如下：

DROP USER user [, user] …

drop user ‘z2’@‘localhost’;

  修改权限表方法只要把 user 用户中的用户记录删除即可。