數(shù)據(jù)庫(kù)實(shí)驗(yàn)五SQL-Server安全性實(shí)習(xí)報(bào)告

1、實(shí)驗(yàn)四 SQL SERVER安全性一、實(shí)驗(yàn)?zāi)康恼莆誗QL SERVER數(shù)據(jù)訪問控制策略和技術(shù)，SQL SERVER數(shù)據(jù)庫(kù)管理系統(tǒng)使用安全帳戶認(rèn)證控制用戶對(duì)服務(wù)器的連接，使用數(shù)據(jù)庫(kù)用戶和角色等限制用戶對(duì)數(shù)據(jù)庫(kù)的訪問。 二、實(shí)驗(yàn)內(nèi)容1在服務(wù)器級(jí)別上創(chuàng)建三個(gè)以SQL Server身份驗(yàn)證的登錄名，登錄名稱自定。1在服務(wù)器級(jí)別上創(chuàng)建三個(gè)以SQL Server身份驗(yàn)證的登錄名，登錄名稱自定。create login T with password = '123456'create login M with password = '123456'create login J

2、 with password = '123456'2分別為三個(gè)登錄名在“Project”數(shù)據(jù)庫(kù)映射三個(gè)數(shù)據(jù)庫(kù)用戶，數(shù)據(jù)庫(kù)用戶名為Tom，Mary和John，使這三個(gè)登錄名可以訪問“Project”數(shù)據(jù)庫(kù)。use Projectgocreate user Tom for login Tcreate user Mary for login Mcreate user John for login J3授予用戶John創(chuàng)建表和視圖的權(quán)限。grant alter on schema : dbo to Johngrant create table,create viewto John4完成

3、以下授權(quán)：（1）把對(duì)表S的INSERT權(quán)力授予用戶Tom，并允許他再將此權(quán)限授予其他用戶。grant inserton Sto Tomwith grant optionuse Projectinsert into S (sno,sname,city)values ('S9','國(guó)安','北京')（2）用戶Mary對(duì)S，P，J三個(gè)表有SELECT和INSERT權(quán)力grant select,inserton Sto Marygrant select,inserton Pto Marygrant select,inserton Jto Maryuse

4、Projectinsert into S (sno,sname,city)values ('S10','華安','北京')insert into P(pno,pname,pweight)values ('P10','螺絲刀',12)insert into J(jno,jname,city)values ('J20','項(xiàng)目','北京')select * from S select * from Pselect * from J （3）用戶Tom對(duì)SPJ表有DELETE

5、權(quán)力，對(duì)QTY字段具有UPDATE權(quán)力。（）用戶Tom對(duì)SPJ表有DELETE權(quán)力，對(duì)QTY字段具有UPDATE權(quán)力。grant delete,update(qty)on SPJto Tomupdate SPJ set qty=qty+50where Sno = 's5'delete from SPJwhere sno='s5' and pno='p6' and jno='j4'（4）把對(duì)用戶Tom授予的所有權(quán)力收回revoke delete,update(qty)on SPJfrom Tomrevoke inserton Sfr

6、om Tom cascade（5）對(duì)用戶Mary增加SPJ表的SELECT權(quán)力。（）對(duì)用戶Mary增加SPJ表的SELECT權(quán)力。grant selecton SPJto Maryselect * from SPJ where sno='s1'（6）拒絕Mary對(duì)表P的INSERT權(quán)限。（）拒絕Mary對(duì)表P的INSERT權(quán)限。deny inserton Pto Maryinsert into P(pno,pname,pweight)values ('P11','螺絲刀',12)5在student數(shù)據(jù)庫(kù)中創(chuàng)建角色STUDBA，將查詢和更新SC表的

7、權(quán)限授予角色STUDBA。5在student數(shù)據(jù)庫(kù)中創(chuàng)建角色STUDBA，將查詢和更新SC表的權(quán)限授予角色STUDBA。use Studentgocreate role STUDBAgrant select,update on SCto STUDBAuse Studentgocreate user Tom for login Texec sp_addrolemember STUDBA ,Tomuse Studentselect * from SC update SCset Grade=Grade+5where Sno='101101'6兩個(gè)同學(xué)為一組（自由搭配），在自己的數(shù)據(jù)庫(kù)

8、服務(wù)器上分別為對(duì)方創(chuàng)建一個(gè)登錄名，在student數(shù)據(jù)庫(kù)中為登錄名映射數(shù)據(jù)庫(kù)用戶，將數(shù)據(jù)庫(kù)用戶加入STUDBA角色中。然后用對(duì)方為自己創(chuàng)建的登錄名和對(duì)方的數(shù)據(jù)庫(kù)服務(wù)器建立連接，對(duì)對(duì)方的數(shù)據(jù)庫(kù)進(jìn)行操作，驗(yàn)證所授予的權(quán)限。6兩個(gè)同學(xué)為一組（自由搭配），在自己的數(shù)據(jù)庫(kù)服務(wù)器上分別為對(duì)方創(chuàng)建一個(gè)登錄名，在student數(shù)據(jù)庫(kù)中為登錄名映射數(shù)據(jù)庫(kù)用戶，將數(shù)據(jù)庫(kù)用戶加入STUDBA角色中。然后用對(duì)方為自己創(chuàng)建的登錄名和對(duì)方的數(shù)據(jù)庫(kù)服務(wù)器建立連接，對(duì)對(duì)方的數(shù)據(jù)庫(kù)進(jìn)行操作，驗(yàn)證所授予的權(quán)限。create login LIU with password = '123456'use studentcreate user liu for login LIUEXE