SQL-实验五

2012级计算机专业集美大学计算机工程学院20012~2013学年第一学期1一、实验使用环境：SQLServer2005二、实验内容与完成情况：实验题目：1.设计安全机制使得用户“王明”只能查询采购部门的职工。实验步骤：（1）打开SQLServerManagementStudio，连接到数据库服务器。（2）单击“新建查询”按钮，进入到命令行方式。（3）输入以下SQL语句：execsp_addlogin'wangming','1234'execsp_grantdbaccess'wangming','王明'createviewview_Employeesasselect*fromEmployeeswhere部门='采购部'grantselectonview_Employeesto王明完成情况：测试数据：select*fromview_Employees权限的体现：select*fromEmployees实验题目：2.设计角色“Role_Emp”，可以查看商品编码、商品名称、生产厂商、库存数量。并将用户王明作为成员加入角色“Role_Emp”，这样用户王明只能查看相关信息。实验步骤：（1）打开SQLServerManagementStudio，连接到数据库服务器。（2）单击“新建查询”按钮，进入到命令行方式。（3）输入以下SQL语句：execsp_addrole'Role_Emp'sp_addrolemember'Role_Emp','王明'createviewview_roleasselectPurchase.商品编码,商品名称,生产厂商,库存数量fromPurchase,库存表2012级计算机专业集美大学计算机工程学院20012~2013学年第一学期2wherePurchase.商品编码=库存表.商品编码andPurchase.进货时间=库存表.入库时间grantselectonview_roleto王明完成情况：测试数据：select*fromview_role权限的体现：select*fromPurchase实验题目：3.请进行安全设置，王老师拥有以下权力：王老师是C++课程的任课老师，他要能查到全校课程的信息及C++选课的信息，并拥有对C++课程成绩的修改，其他的信息王老师无权查看。实验步骤：（1）打开SQLServerManagementStudio，连接到数据库服务器。（2）单击“新建查询”按钮，进入到命令行方式，选取名字为SalaesDatabases的数据库（3）输入以下SQL语句：execsp_addlogin'wanglaoshi','1234'execsp_grantdbaccess'wanglaoshi','王老师'createviewview_2asselectStu_no,Cou_name,gradefromCourse,SCwhereCourse.Cou_no=SC.Cou_noandCourse.Cou_name='数据库原理'grantselectonCourseto王老师grantupdate,selectonview_2to王老师完成情况：测试数据：select*fromCourseselect*fromview_22012级计算机专业集美大学计算机工程学院20012~2013学年第一学期3updateview_2setgrade=95.0whereStu_no=20026101select*fromview_2权限体现：updateCoursesetCou_no='a03'whereCou_no='a01'dropviewview_2实验题目：4.如何使得全校老师都具有这样的权限：能查到全校课程的信息及所上课程的信息，并拥有对所上课程式成绩的修改，其他的信息无权查看。（要求：存储过程proc_stu_grant作用：输入参数为老师名字，从相应表中查找该老师所任教的课程，如果没有则返回，有的话则相应的在login1表中添加账号和密码。并且，创建相应的登录账号和数据库用户）实验步骤：（1）打开SQLServerManagementStudio，连接到数据库服务器。（2）单击“新建查询”按钮，进入到命令行方式。（3）输入以下SQL语句：--创建login1表createtablelogin1(Tnamevarchar(20),Tpasswordvarchar(20),)--创建存储过程createprocedureproc_stu_grant@namevarchar(20),@keyvarchar(10)asifexists(select*fromCoursewhereCou_teacher=@name)beginexecsp_addlogin@name,@keyexecsp_grantdbaccess@name,@name2012级计算机专业集美大学计算机工程学院20012~2013学年第一学期4insertintologin1(Tname,Tpassword)values(@name,@key)declare@sqlnvarchar(4000)set@sql='grantselectonCourseto'+@nameexec(@sql)set@sql='createview'+@name+'asselect*fromSCwherecou_no=(selectCou_nofromcoursewherecou_teacher='''+@name+''')'exec(@sql)set@sql='grantselect,update(grade)on'+@name+'to'+@nameexec(@sql)end测试数据：execproc_stu_grant'林红',123execproc_stu_grant'苏琴',123execproc_stu_grant'张勇明',123execproc_stu_grant'李琳',123execproc_stu_grant'王雄',123execproc_stu_grant'陈晓琳',123完成情况：select*fromlogin1举例：登录名为李琳老师的权限：select*fromCourseselect*fromSC登录名为苏琴老师的权限：select*from苏琴2012级计算机专业集美大学计算机工程学院20012~2013学年第一学期5select*from王雄update苏琴setgrade=90.0whereStu_no='20026101'实验题目：5.银行转账问题createtablebank--创建账户表，存放用户的账户信息(customerNamechar(10),--顾客姓名currentMoneymoney--当前余额)go--添加约束:根据银行规定,账户余额不能少于元,否则视为销户altertablebankaddconstraintck_currentMoneycheck(currentMoney=1)go--张红开户,开户金额为元;李明开户,开户金额为元insertintobank(customerName,currentMoney)values('张红',1000)insertintobank(customerName,currentMoney)values('李明',1)写出用事物解决银行转账的存储过程：实验步骤：（1）打开SQLServerManagementStudio，连接到数据库服务器。（2）单击“新建查询”按钮，进入到命令行方式，选取名字为SalaesDatabases的数据库（3）输入以下SQL语句：createprocBank_Proc@outchar(20),@inchar(20),@zhichumoneyasbegindeclare@xianyoumoneyselect@xianyou=currentMoneyfrombankwherecustomerName=@outif((@xianyou-@zhichu)=0)beginupdatebanksetcurrentMoney=currentMoney-@zhichuwherecustomerName=@outupdatebanksetcurrentMoney=currentMoney+@zhichuwherecustomerName=@in2012级计算机专业集美大学计算机工程学院20012~2013学年第一学期6print('转账成功')endelsebeginbegintranshibaiupdatebanksetcurrentMoney=currentMoney-@zhichuwherecustomerName=@outupdatebanksetcurrentMoney=currentMoney+@zhichuwherecustomerName=@inprint('转账失败')rollbacktranshibaiendend完成情况：测试数据:execBank_Proc'李明','张红',500select*frombankexecBank_Proc'张红','李明',500select*frombank三、实验结论通过本次实验，学会新的用户，登录名，和角色的建立，明白了如何设置用户权限，通过视图机制把要保密的数据对无权存取这些数据的用户隐藏起来，只允许某些用户访问数据，明白通过grant语句对用户进行赋权。掌握了权限的管理。四、出现的的问题和解决方案（列出遇到的问题和解决办法，列出没有解决的问题）1）遇到的问题和解决办法：1.在做第二题时导入的表格中没有C++的科目，用C语言代替，后发现学生成绩表中没有Ｃ2012级计算机专业集美大学计算机工程学院20012~2013学年第一学期7语言的成绩，最后用数据结构这一科目代替C语言科目执行程序结果。在建立银行转账存储过程的时候出现了转账不成功，结果接收人银行账户多出了转账金额的情况，一开始为程序加入了rollback语句，出现了如下的第二个错误，后为程序加入了begintranshibai语句，程序得以继续执行。2)未解决的问题1）在做第二题的时候用这个命令执行，结果出现了如下错误，不明白原因，后把select*fromCourse,SC改为selectStu_no,Cou_name,gradefromCourse,SC程序可以正常执行，但是在验证权限的时候，由于开始建立视图是因为将两张表的内容联接起来，导致不能插入信息来验证权限，插入验证权限的时候会出现如下提示错误，createviewview_2asselect*fromCourse,SCwhereCourse.Cou_no=SC.Cou_noandCourse.Cou_name='数据库原理'