日志

视图、存储过程以及权限控制练习

 来源    2021-01-13    0  

视图、存储过程以及权限控制

导读:

该文章为视图、存储过程、用户权限练习;

如果有不对的地方欢迎指出与补充;

该基础练习基于MySQL5.0以上;

语句格式:

1. 视图格式:

create view view_name[列名,列名.....] as select 子查询 with check option

其中with check option 作用是:在视图进行update\delete\insert操作时要保证更新、插入、删除的行满足视图定义中的谓词公式(既子查询中的条件表达式);

视图操作如正常表一样可以增删改查。

2.存储过程格式:

create procedure pro_name(in 参数名 类型,out 参数名 类别)
begin
      过程体                     
end

3.用户权限格式:

create  user 用户名 indentified by '密码'
--设置权限
grant 权限 on 数据库名.表名 to 用户@登录主机 identified by "用户密码";

视图:

(1)建立视图IS_STUDENT,视图中包含信息系全体学生的基本信息。(要求限制更新)

Create view IS_STUDENT as select * from student
Where sdept=’信息系’ with check option;

(2)建立视图CJ_STUDENT,视图中包含所有成绩不及格的学生的学号,姓名,课程名,成绩。

Create view CJ_STUDENT as select student.sno,sname,cname,grade from student,sc,course

 Where student.sno=sc.sno and sc.cno=course.cno and (grade <60 or grade is null);

(3)建立视图AVG_CJ,视图包括学生的学号以及他们的平均成绩,按成绩降序排列。

Create view AVG_CJ as select sno ,avg(grade) 

from sc  where grade is null group by  sno  order by avg(grade) desc;

(4)根据视图IS_STUDENT,修改该视图中年龄都增加1。观察基本表Student中相应的哪些数据发生了变化。

Update IS_STUDENT set sage=sage+1;

select * from IS_STUDENT;

(5)在视图IS_STUDENT中插入新的记录,学号为9531103,姓名为张玉,女,21岁,计算机系。(是否能执行?若不行,为什么?)

insert into IS_STUDENT values('9531103','张玉','女',21,'计算机系');

不能执行,因为,违反了视图创建规则,计算机系不满足sdept中的信息系要求;

(6)根据视图CJ_STUDENT创建视图CJ_TJ,包含课程名,不及格人数,不及格人姓名列表,按照不及格人数降序排列。

Create view CJ_TJ as select cname,count(*),GROUP_CONSCAT(sname SEPARATOR ',')  from CJ_STUDENT  group by cname order by count(*) desc;

存储过程:

(1)建立存储过程course_grade:根据课程名参数,查询该课程的成绩表,包括学号,姓名,成绩,按学号升序排序。

CREATE PROCEDURE course_grade (in course_name varchar(20))

SELECT student.sno,sname,grade from student,sc,course WHERE student.sno=sc.sno and sc.cno =course.cno and cname=course_name ORDER BY sno;

DROP PROCEDURE course_grade;

 

CALL course_grade("计算机导论");

(2)建立存储过程search_grade:根据姓名和课程名参数,查询该学生相应的课程成绩,若存在成绩,则返回成绩值,否则返回NULL。

create procedure search_grade(in student_name char(10), in course_name varchar(20), out re_grade smallint, out rname char(10)) 
	Begin 
	If( not exists
			(
				select * from student, sc, course
				where student.sno = sc.sno
				and sc.cno = course.cno
				and sname = student_name
				and cname = course_name) )
then set re_grade = null;
	else 
		select grade into re_grade from student, sc, course
		where student.sno = sc.sno
		and sc.cno = course.cno
		and sname = student_name
		and cname = course_name ;
	end if;
end ;
#创建成功
-- 执行存储过程
call search_grade('王大力','数据库原理',@grade, @rname);
select @grade, @rname;
call search_grade('王大力','高等数学',@grade, @rname);
select @grade, @rname;

(3)创建存储过程take_course:根据参数学号和课程号,完成选课功能(要求能够根据选课人数加以限制)。

要求:

§ 检查该学生选课门数是否超过3门,若超过,不插入记录;

§ 检查该课程选课人数是否已满(在course表里添加一个属性列,记录课程人数限制);

ü 选课人数已满:不插入记录;

ü 选课人数未满且未插入过:完成数据插入操作;

ü 选课人数未满,已插入过:不插入记录

§ 最后返回执行结果信息,例如:

ü 超过3门,返回-1;

ü 选课人数已满。返回0;

ü 选课人数未满且未插入过:返回1;

ü 选课人数未满已插入过:返回2

create procedure take_course (in student_no char(7), in course_no char(3), out i tinyint )
begin 
	if(select count(*) from sc where sno= student_no) >= 3 
		then set i = 1;
	else 
		if(select count(*) from sc where cno = course_no) >= 5 
		then set i= 0;
		else 
				if exists (select * from sc where sno = student_no and cno = course_no) 
				then set i = 2;
				else 
					insert into sc(sno, cno) values(student_no, course_no);
					set i = 1;
				end if ;
		end if ;
	end if ;
end ;

权限控制:

(1)创建超级用户admin,具有对所有数据库的所有访问权限,仅限本机连接(密码自行设置),并验证用户和权限设置是否正确。

CREATE user admin @localhost

IDENTIFIED by '123';

(2)创建管理员teacher_liu,具有对SC表、IS_STUDENT视图(实验5创建)的增删改查权限,并且具有授权的权限,允许从任意位置连接(密码自行设置),并验证用户和权限设置是否正确。

CREATE USER teacher_liu @'%' IDENTIFIED by '123456';

GRANT SELECT ,INSERT, UPDATE, DELETE on studentTable.sc TO teacher_liu @'%' WITH GRANT OPTION;

总结:

期末考试结束,开始陆续更新。。。

以上练习如果有不对的地方欢迎指正。。。。

相关文章
sql-server-2008 – 授予存储过程,函数和视图的执行权限
问答我正在使用只具有读权限的用户.它基本上有权限 >连接 >执行 但是,它没有函数和存储过程的执行权限.在我的部分报告中,我正在调用自己的功能.我想授予readonlyuser执行SP,函数和 ...
1
MySQL数据库(六) —— SQL注入攻击、视图、事物、存储过程、流程控制
日志SQL注入攻击.视图.事物.存储过程.流程控制 一.SQL注入攻击 1.什么是SQL注入攻击 import pymysql conn = pymysql.Connect( user="roo ...
1
Hive 文件格式 & Hive操作(外部表、内部表、区、桶、视图、索引、join用法、内置操作符与函数、复合类型、用户自定义函数UDF、查询优化和权限控制)
日志本博文的主要内容如下: Hive文件存储格式 Hive 操作之表操作:创建外.内部表 Hive操作之表操作:表查询 Hive操作之表操作:数据加载 Hive操作之表操作:插入单表.插入多表 Hive语 ...
1
将存储过程的权限授予另一个Oracle用户
问答我是本科学习的学生,在向用户A授予对数据库Oracle 10g mode = xe中用户B拥有的存储过程的所有权时,我面临的问题很小. 请帮我编写sql命令,以便将存储过程xyz的所有权授予另一个用户 ...
1
sql – 如何从数据库中删除所有内容(表,视图,存储过程,函数)?
问答我想删除数据库,表,视图,存储过程和函数中的所有内容.我找到了一些链接,但它们不适用于复杂的数据库.它没有删除表.这是我在本网站上发现的脚本的自己版本并进行了调整. /* Drop all non-s ...
1
sql – 如何将视图的所有权限授予任意用户
问答您如何授予任意用户的所有功能和视图的读/选访问权? 我使用psql –user = postgres -d mydb -f myview.sql创建几个函数和视图,然后我运行: GRANT ALL P ...
2
获取sql server 2005中存储过程的权限
问答如何在sql server 2005中获取存储过程的授予权限?::SELECT OBJECT_NAME(major_id), USER_NAME(grantee_principal_id), perm ...
1
sql-server – 错误消息 – 视图服务器状态权限被拒绝 – 使用SQL Server 2012 Management Studio连接到SQL Server 2014时
问答右键单击表并选择"选择前1000行"时,我收到此错误: 除了明显升级到SQL Server 2014之外,还有其他解决方法吗?::仅供参考,此问题似乎与此Connect错误有关: ...
1
安全性 – 用于权限控制的LDAP?
问答如果LDAP可用于控制用户权限,我一直在想.例如,如果我有UNIX和Web登录,是否有一种简单的方法可以授予用户访问UNIX(或者甚至两者)的权限? 我目前解决这个问题的尝试是创建"登录&q ...
1
entity-framework-4.1 – 您可以使用Entity Framework 4.1代码第一种方法创建sql视图/存储过程
问答实体框架4.1代码首先工作伟大创建表和关系.是否可以使用Code第一种方法创建sql视图或存储过程?任何指针,将高度赞赏.非常感谢!::EF代码优先方法期望在数据库中没有逻辑.这意味着没有存储过程和数 ...
2
c# – 从视图访问用户权限的最佳方式
问答在我的ASP.NET MVC站点中,我的设置允许用户拥有角色,角色拥有权限.通常,为控制器设置这些权限.在我的网站的主导航菜单中,经过身份验证的用户可以查看所有项目,即使他们无权访问该页面. 目前,我 ...
2
sql-server – SQL Server 2005 – 授予创建存储过程的权限(但没有其他对象)
问答我想授予用户创建,更改和执行存储过程的权限,但是无法创建其他数据库对象.我将它们从角色db_ddladmin中排除,但显式授予了"创建过程"和"执行"权限.这些 ...
2
SQL Server存储过程和权限
问答是否可以拒绝SQL Server中所有内容的权限(例如select,insert等),并通过存储过程提供临时权限.我想只使用存储过程来访问sql数据库中的数据. 这可能还是有另一种方式?::这是一个支 ...
1
django – DRF视图集中的权限检查无法正常工作
问答我正在实现一个API,我有嵌套结构.让我们说这是一个动物园,我可以打电话给GET / api / cage /得到一个笼子GET / api / cage / 1 /列表来获得笼子ID 1,但是我可以 ...
1
可以在django中使用装饰器来根据视图参数进行权限吗?
问答我不确定这是可能的,但有没有办法写出类似的东西 @group_required('group_id') def myview(request, group_id): ..... 我在哪里查看参数gro ...
1
sql-server – db-role的存储过程执行权限
问答我想赋予db_datareader角色执行特定存储过程的权限. 这是可能的,如果可以,我该如何实现?我试图在存储过程的权限选项卡中添加该角色,但这似乎不起作用,该角色不被接受为有效对象. 编辑 根据c ...
1
python – Django – 某些视图的用户权限?
问答从管理员看,您可以向用户或用户组分配权限:允许从模型添加,更改或删除数据. 这很棒,但是我还需要允许用户或用户组访问或不访问一组视图.我在我的网站上有一些类型的服务,所以我想允许一些用户访问某些服务( ...
3
sql-server – 存储过程和权限 – EXECUTE足够了?
问答我有一个SQL Server 2008数据库,其中对底层表的所有访问都是通过存储过程完成的.一些存储过程只是从表中选择SELECT记录,而其他UPDATE,INSERT和DELETE. 如果一个存储过 ...
1
sql-server – SQL Server视图存储过程完整关系
问答我正在使用SQLSEVER 2012 Management Studio在SQLSERVER 2012 Express数据库上处理一堆非常复杂的存储过程. 例如我有一个存储过程SP1调用另一个存储过程 ...
4
python – Django – 基于函数的视图的自定义权限
问答如何为基于功能的视图编写自定义权限?我正在使用REST框架,我编写了一个密文加密/解密API.我为每个密钥(密钥详细信息)提供了一个基于函数的视图,我只想将其提供给该密钥的所有者.我知道在使用基于类的 ...
1