视图和用户权限
1.视图(view)
-
看一个需求
emp表的列信息很多,有些信息是个人重要信息(比如:sal、comm、mgr、hiredate),如果我们希望某个用户只能查询emp表的empno、enamel、job和deptno信息,有什么办法呢?
答案是使用视图。
![image-20221009190711048](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221009190711048.png)
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表中
![image-20221010154107761](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221010154107761.png)
![image-20221010154209757](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221010154209757.png)
其中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后,根据相应的权限,可以操作的数据库和数据对象(表,视图,触发器)都不一样
![image-20221010161513484](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221010161513484.png)
- 用户修改密码
-- 修改自己的密码
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中的权限
![image-20221010164159592](https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20221010164159592.png)
-
给用户授权
# 基本语法: 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.%'
标签:day09,基表,--,用户,视图,emp,权限
From: https://www.cnblogs.com/liyuelian/p/16776961.html