day09-2视图和用户权限

时间：2022-10-10 20:09:00

1.视图（view）

  • 看一个需求

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

    答案是使用视图。

day09-2视图和用户权限

1.1基本原理

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

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

    day09-2视图和用户权限

  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
    day09-2视图和用户权限day09-2视图和用户权限

2.MySQL管理

2.1Mysql用户管理

  • MySQL用户

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

day09-2视图和用户权限day09-2视图和用户权限

其中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后，根据相应的权限，可以操作的数据库和数据对象（表，视图，触发器）都不一样

day09-2视图和用户权限
  • 用户修改密码
-- 修改自己的密码
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中的权限
day09-2视图和用户权限

  • 给用户授权

    # 基本语法：
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.%'

相关文章