视图和用户权限

• 看一个需求

  emp表的列信息很多，有些信息是个人重要信息（比如：sal、comm、mgr、hiredate），如果我们希望某个用户只能查询emp表的empno、enamel、job和deptno信息，有什么办法呢？

  答案是使用视图。

1.1基本原理

1. 视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，其数据来自对应的真实的表（基表）

   视图和基表关系的示意图：

2. 对视图的总结

   • 视图是根据基表（可以是多个基表）来创建的，视图是虚拟的表
   • 视图也有列，数据来自基表
   • 通过视图可以修改基表的数据
   • 基表的改变也会影响到视图的数据

1.2视图使用细节

• 语法

  1. create view 视图名 as select 语句 -- 创建一个视图
  2. alter view 视图名 as select 语句 -- 修改视图，更新成新的视图
  3. show create view 视图名 -- 显示创建视图的指令
  4. drop view 视图名1[,视图名2,…] -- 删除视图

例子

完成前面提出的需求

创建一个视图emp_view01，只能查询emp表的empno、ename、job和deptno列的信息

SELECT * FROM emp;
-- 创建一个视图emp_view01，只能查询emp表的empno、ename、job和deptno列的信息

-- 创建视图
CREATE VIEW emp_view01
    AS
    SELECT empno,ename,job,deptno FROM emp;

-- 查看视图
DESC emp_view01;
SELECT * FROM emp_view01;
SELECT empno, job FROM emp_view01;

-- 显示创建视图的指令
SHOW CREATE VIEW emp_view01; 

-- 删除视图
DROP VIEW emp_view01;

• 视图细节讨论

1. 创建视图后，到数据库去看，对应的视图只有一个视图结构文件（形式：视图名.frm）

   视图的数据只是来源于基表，本身是没有数据的（一个映射关系）

2. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图 [insert，delete，update]

3. 视图中可以再使用视图，数据仍然来自基表

   -- 视图细节讨论
   -- 1. 创建视图后，到数据库去看，对应的视图只有一个视图结构文件
   -- （形式：视图名.frm）

   -- 2. 视图的数据变化会影响到基表 [insert，delete，update]
   UPDATE emp_view01 SET job = 'MANAGER' WHERE empno=7369;-- 修改视图
   SELECT * FROM emp; -- 查询基表，，发现数据变了

   -- 基表的数据变化也会影响到视图
   UPDATE emp SET job = 'SALESMAN' WHERE empno=7369;-- 修改基表
   SELECT * FROM emp_view01; -- 查询视图，发现数据变了

   -- 3. 视图中可以再使用视图，比如从emp_view01视图中选出empno和ename做出新的视图
   CREATE VIEW emp_view02
   AS
   SELECT empno,ename FROM emp_view01;

1.3视图应用实例

• 视图最佳实践

1. 安全：

   一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。

2. 性能：

   关系数据库的数据常常会分表存储，使用外键建立这些表之间的关系。这时，数据库查询经常会用到连接（JOIN）。这样做不但麻烦，效率相对也比较低。如果建立一个视图（基于多个基表），将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。

3. 灵活：

   如果系统中有一张旧的表，这张表由于设计的问题即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新的表。这样就可以少做很多改动，也达到了升级数据表的目的。

• 练习

  针对emp，dept，salgrade三张表。创建一个视图emp_view03，可以显示雇员编号，雇员名，雇员部门名称和薪水级别。

  -- 针对emp，dept，salgrade三张表。
  -- 创建一个视图emp_view03，可以显示雇员编号，雇员名，雇员部门名称和薪水级别。
  /*
      分析：
      1.使用三张表联合查询，得到结果
      笛卡尔乘积 = 表1记录数*表2记录数*表3记录数
      利用过滤条件 emp.deptno= dept.deptno AND sal>=losal AND sal<=hisal
      2.将结果构建成视图
  */
  CREATE VIEW emp_view03
      AS
      SELECT empno,ename,dname,grade
          FROM emp,dept,salgrade
          WHERE emp.deptno= dept.deptno AND
          sal>=losal AND sal<=hisal
  
  DESC emp_view03
  SELECT * FROM emp_view03

  
  

2.1Mysql用户管理

• MySQL用户

mysql中的用户，都存储在系统数据库mysql中的user表中

其中user表的重要字段说明：

1. host：允许登录的“位置“，localhost表示该用户只允许本机登录，也可以指定ip地址，如：192.168.1.100
2. user：用户名
3. authentication_string：密码，是通过mysql的password()函数加密之后的密码。

• 创建用户

  create user '用户名'@'允许登录的位置' identified by '密码' -- 创建用户，同时指定密码

• 删除用户

  drop user '用户名'@'允许登录的位置';

例子

-- mysql用户管理
-- 原因：当我们做项目开发时，可以根据不同的开发人员，赋给他们相应的操作权限
-- 所以，mysql数据库管理人员（root），根据需要创建不同的用户，赋给相应的权限，供人员使用

-- 1.创建新用户
-- 1.1 'liyuelian'@'localhost' 表示用户的完整信息
--     'liyuelian' 用户名  'localhost' 登录ip

-- 1.2 123456 为密码，在mysql表存放的是password函数加密过后的密码
--  *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
CREATE USER 'liyuelian'@'localhost' IDENTIFIED BY '123456';

SELECT `host`,`user`,authentication_string FROM mysql.user;

-- 2.删除用户
DROP USER 'liyuelian'@'localhost';

-- 3.登录

不同的数据库用户，登录到DBMS后，根据相应的权限，可以操作的数据库和数据对象（表，视图，触发器）都不一样

• 用户修改密码

  -- 修改自己的密码
  set password = password('密码');
  -- 修改他人的密码（需要有修改用户密码权限）
  set password for'用户名'@'登录的位置' = password('密码');

例子

-- 修改本用户密码
SET PASSWORD= PASSWORD('abcdef');

-- 修改他人的密码（需要有修改用户密码权限）
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');-- 错误，提示权限不够

-- 切换到root用户，修改一般用户的密码
SET PASSWORD FOR 'olien'@'localhost' = PASSWORD('123456');-- 成功，root用户权限很高

2.2Mysql权限管理

• Mysql中的权限

• 给用户授权

  # 基本语法：
  grant 权限列表 on 库.对象名 to '用户名'@'登录位置' [identified by '密码']
  
  #说明：
  # 1. 权限列表，多个权限用逗号分开
  #   如：grant select on....
  #      grant select ,delete,create on....
  
  #2. 特别说明
  #  *.*   ：表示本系统中的所有数据库的所有对象（表，视图，存储过程）
  #   库.*  ： 表示某个数据库中的所有数据对象（表，视图，存储过程）
  
  # 3. identified by可以省略，也可以写出
  # (1)如果用户存在，就是修改该用户的密码
  # (2)如果该用户不存在，就是创建该用户

• 回收用户权限

  #基本语法
  revoke 权限列表 on 库.对象名 from '用户名'@'登录位置';

• 权限生效指令

  #如果权限没有生效，可以执行下面命令
  #基本语法
  flush privileges;

练习

1. 创建一个用户（用户名随意），密码为123，并且只可以从本地登录，不让远程登录mysql
2. 创建testdb库和表news，要求：使用root用户创建
3. 给用户分配查看news表和添加数据的权限
4. 测试看看用户是否只有这几个权限
5. 修改密码为abc，要求使用root用户完成
6. 重新登录
7. 回收新用户的权限
8. 使用root用户删除你的用户

root用户：

-- 演示用户权限的管理
-- 1. 创建一个用户（用户名随意），密码为123，并且只可以从本地登录，不让远程登录mysql
CREATE USER 'olien'@'localhost' IDENTIFIED BY '123'; 

-- 2. 创建testdb库和表news，要求：使用root用户创建
-- 使用root用户创建库和表
CREATE DATABASE testdb;
CREATE TABLE news(
    id INT ,
    content VARCHAR(32)
)
-- 添加一条测试数据
INSERT INTO news VALUES(100,'北京新闻');

-- 3. 给用户分配查看news表和添加数据的权限
-- 4. 测试看看用户是否只有这几个权限
GRANT SELECT,INSERT
    ON testdb.news
    TO 'olien'@'localhost';

-- 5. 修改olien用户的密码为abc，要求使用root用户完成
SET PASSWORD FOR'olien'@'localhost' = PASSWORD('abc');

-- 6. 重新登录

-- 7.回收新用户的权限
REVOKE SELECT,INSERT ON testdb.news FROM 'olien'@'localhost';

-- 8. 使用root用户删除你的用户
DROP USER 'olien'@'localhost';

新创建的用户：

-- 在默认情况下，olien用户只能看到一个默认的系统数据库

SELECT * FROM news;

INSERT INTO news VALUES(200,'上海新闻');

-- 测试看看用户是否只有这几个权限
-- 不能进行没有授权的操作，如：update
UPDATE news SET content ='广州新闻'
    WHERE id = 100; -- 错误，提示没有权限

2.3Mysql管理细节

细节说明：

1. 在创建用户的时候，如果不指定host，则默认为%，%表示所有的IP都有连接权限 。如create user xxx;
2. 也可以这样指定：create user 'xxx'@'192.168.1.%' ，表示xxx用户在192.168.1.*的ip地址都可以登录mysql
3. 在删除用户时，如果host不是%，需要明确指定'用户'@'host值'

例子

-- 细节说明：
-- 1. 在创建用户的时候，如果不指定host，则默认为%，%表示所有的IP都有连接权限.如create user xxx;
CREATE USER 'jack';

SELECT `host`,`user` FROM mysql.user;

-- 2. 也可以这样指定：create user 'xxx'@'192.168.1.%'
-- 表示xxx用户在192.168.1.*的ip地址都可以登录mysql
CREATE USER 'smith'@'192.168.1.%' 

-- 3. 在删除用户时，如果host不是%，需要明确指定'用户'@'host值'
DROP USER jack; -- 默认就是 drop user 'jack'@'%'
DROP USER smith@'192.168.1.%'