视图和用户权限

1.视图（view）

看一个需求

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

答案是使用视图。

1.1基本原理

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

视图和基表关系的示意图：
对视图的总结
- 视图是根据基表（可以是多个基表）来创建的，视图是虚拟的表
- 视图也有列，数据来自基表
- 通过视图可以修改基表的数据
- 基表的改变也会影响到视图的数据

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;

视图细节讨论

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

视图的数据只是来源于基表，本身是没有数据的（一个映射关系）
视图的数据变化会影响到基表，基表的数据变化也会影响到视图 [insert，delete，update]
视图中可以再使用视图，数据仍然来自基表

-- 视图细节讨论
-- 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视图应用实例

视图最佳实践

安全：

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

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

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

练习

针对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.MySQL管理

2.1Mysql用户管理

MySQL用户

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

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

host：允许登录的“位置“，localhost表示该用户只允许本机登录，也可以指定ip地址，如：192.168.1.100
user：用户名
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;

练习

创建一个用户（用户名随意），密码为123，并且只可以从本地登录，不让远程登录mysql
创建testdb库和表news，要求：使用root用户创建
给用户分配查看news表和添加数据的权限
测试看看用户是否只有这几个权限
修改密码为abc，要求使用root用户完成
重新登录
回收新用户的权限
使用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管理细节

细节说明：

在创建用户的时候，如果不指定host，则默认为%，%表示所有的IP都有连接权限。如create user xxx;
也可以这样指定：create user 'xxx'@'192.168.1.%' ，表示xxx用户在192.168.1.*的ip地址都可以登录mysql
在删除用户时，如果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.%'