第四节 存储过程和用户管理
4.1 存储过程
1. 什么是存储过程
通过前面章节的学习，我们已经知道SQL是一种非常便利的语言。从数据库抽取数据，或者对特定的数据集中更新时，都能通过简洁直观的代码实现
但是这个所谓的“简洁”也是有限制，SQL基本是一个命令实现一个处理，是所谓的非程序语言。
在不能编写流程的情况下，所有的处理只能通过一个个命令来实现。当然，通过使用连接及子查询，即使使用SQL的单一命令也能实现一些高级的处理，但是，其局限性是显而易见的。例如，在SQL中就很难实现针对不同条件进行不同的处理以及循环等功能。
这个时候就出现了存储过程(Stored Procedure)这个概念，简单地说，存储过程就是数据库中保存(Stored)的一系列SQL命令（Procedure）的集合。也可以将其看作相互之间有关系的SQL命令组织在一起形成的一个小程序。
2. 存储过程的优点
1) 提高执行性能。存储过程执行效率之所高，在于普通的SQL语句，每次都会对语法分析，编译，执行，而存储过程只是在第一次执行语法分析，编译，执行，以后都是对结果进行调用。
2) 可减轻网络负担。使用存储过程，复杂的数据库操作也可以在数据库服务器中完成。只需要从客户端(或应用程序)传递给数据库必要的参数就行，比起需要多次传递SQL命令本身，这大大减轻了网络负担。
3) 可将数据库的处理黑匣子化。应用程序中完全不用考虑存储过程的内部详细处理，只需要知道调用哪个存储过程就可以了
【示例11】存储过程的定义、调用和删除
--
4.2 用户管理
1. 用户管理入门
MySQL中可创建不同的用户，并分配不同的权限，保证MySQL中数据的安全性。MySQL8中提供了角色，是权限的集合，可以直接赋予用户以角色。用户管理主要包括用户管理、权限管理、安全管理内容。
MySQL用户主要包括两种：root用户和普通用户。root用户为超级管理员，拥有MySQL提供的所有权限，而普通用户的权限取决于该用户在创建时被赋予的权限有哪些。实际开发中很少直接使用root用户，权限过大，操作不当会具有很大的危险性。
MySQL中有一个自带数据库mysql，其中有多个和用户权限有关的数据库表。
² user表中存储了允许连接到服务器的用户信息以及全局级（适用于所有数据库）的权限信息。这是最关键的表。
² db表中存储了某个用户对相关数据库的权限（数据库级权限）信息
² 表级权限表tables_priv，可以实现单张表的权限设置，
² 列级权限表columns_priv，可以实现单个字段的权限设计
MySQL用户通过身份认证后，会进行权限的分配，分配权限是按照user表、db表、tables_priv表、columns_priv表的顺序依次进行验证。即先检查全局级权限表user，如果user表中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db表、tables_priv表、columns_priv表；如果user表中对应的权限为N，则到数据库级权限表db中检查此用户对应的具体数据库的权限，如果得到db表中对应的权限为Y，将不再检查tables_priv表、columns_priv；如果db表中对应的权限为N，则检查表级权限表tables_priv中此数据库对应的具体表的权限，以此类推。
2. 用户管理
主要操作包括创建用户、修改用户、删除用户、查询用户。每个新建的用户都会在user表中有对应的记录。
其中创建用户的语法为：
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
l username：你将创建的用户名
l host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%；还可以指定的ip地址范围
l password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器
在MySQL5.7之前，user表中还有一个名为password的字段用于存储用户的密码，但是在MySQL5.7之后，密码存储authentication_string字段中。
【示例12】用户操作
1
3. 权限管理
MySQL通过权限管理机制可以给不同的用户授予不同的权限，从而确保数据库中数据的安全性。权限管理机制包括查看权限、授予权限以及收回权限。
其中授予权限的语法为：
grant privileges ON dbname.tablename TO 'username'@'host'
l privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL。新创建的用户默认权限为USAGE，除了可以登录数据库外，没有任何权限。
l databasename：数据库名
l tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*
【示例13】权限操作
1. -- 查询权限
2. -- 查询当前用户权限
3. show grants
4. -- 查询指定用户的权限
5. show grants for 'zhangsan'@'%'
6.
7. -- 特殊权限
8. -- USAGE :只有登录权限
9. -- ALL :所有权限
10.
11. -- 授予权限和撤销权限1
12. grant all on mydb.* to 'zhangsan'@'%'
13. revoke all privileges on mydb.* from 'zhangsan'@'%'
14.
15. -- 授予权限和撤销权限2
16. grant select,delete,update,insert on mydb.dept to 'zhangsan'@'%'
17. revoke delete on mydb.dept from 'zhangsan'@'%'
18.
19. -- 刷新权限
20. flush privileges;
MySQL中的权限有很多种，表中列出了MySQL中提供的权限以及每种权限的含义及作用范围。
4. 角色管理
MySQL 数据库中通常都会出现多个拥有相同权限集合的用户，在之前版本中只有分别向多个用户授予和撤销权限才能实现单独更改每个用户的权限。在用户数量比较多的时候，这样的操作是非常耗时的。
MySQL 8.0 为了用户权限管理更容易，提供了一个角色管理的新功能。角色是指定的权限集合，和用户帐户一样可以对角色进行权限的授予和撤消。如果用户被授予角色权限，则该用户拥有该角色的权限。
MySQL 8.0 提供的角色管理功能如下：
² CREATE ROLE 角色创建
² DROP ROLE 角色删除
² GRANT 为用户和角色分配权限
² REVOKE 为用户和角色撤销权限
² SHOW GRANTS 显示用户和角色的权限
² SET DEFAULT ROLE 指定哪些帐户角色默认处于活动状态
² SET ROLE 更改当前会话中的活动角色
² CURRENT_ROLE() 显示当前会话中的活动角色
【示例14】角色操作
本节作业
1. 存储过程的定义和优点
2. 完成示例11的视图和存储过程操作
3. 完成实力12，13，14的用户、权限、角色操作
