實驗四 數(shù)據(jù)的完整性、安全性

1、實驗四 數(shù)據(jù)完整性安全性 徐龍琴、劉雙印設(shè)計制作實驗四 數(shù)據(jù)的完整性、安全性一、 實驗?zāi)康? 掌握數(shù)據(jù)安全性和完整性的概念，以及如何保證數(shù)據(jù)庫中數(shù)據(jù)安全及完整性。2 掌握 SQL Server中有關(guān)用戶、角色及操作權(quán)限的管理方法3 學(xué)會創(chuàng)建和使用規(guī)則、缺省。二、 實驗內(nèi)容1 數(shù)據(jù)庫的安全性實驗，通過SSMS設(shè)置 SQL Server的安全認(rèn)證模式實現(xiàn)對SQL Server的用戶和角色管理，設(shè)置和管理數(shù)據(jù)操作權(quán)限 2數(shù)據(jù)庫的完整性實驗。使用Transact-SQL設(shè)計規(guī)則、缺省、約束和觸發(fā)器。三、 實驗要求1數(shù)據(jù)的完整性實驗用SQL語句創(chuàng)建一學(xué)生成績數(shù)據(jù)庫(XSCJ)，包括學(xué)生(XSQK)、課程

2、(KC)和成績表(XS_KC)： 學(xué)生情況表（XSQK）列名數(shù)據(jù)類型長度是否允許為空值學(xué)號Char6N姓名Char8N性別Bit1N出生日期smalldatetime2專業(yè)名Char10所在系Char10聯(lián)系電話char11Ycreate database xscjuse xscjcreate table xsqk(學(xué)號 char(6) not null, 姓名 char(8) not null, 性別 bit not null, 出生日期 smalldatetime, 專業(yè)名 char(10), 所在系 char(10), 聯(lián)系電話 char(11) null)課程表（KC）列名數(shù)據(jù)類型長度

3、是否允許為空值課程號Char3N課程名Char20N教師Char10開課學(xué)期Tinyint1學(xué)時Tinyint1學(xué)分Tinyint1Ncreate table kc(課程號 char(3) not null, 課程名 char(20) not null, 教師 char(10), 開課學(xué)期 tinyint, 學(xué)時 tinyint, 學(xué)分 tinyint not null)成績表（XS_KC）列名數(shù)據(jù)類型長度是否允許為空值學(xué)號Char6N課程號成績CharSmallint32Ncreate table xs_kc(學(xué)號 char(6) not null, 課程號 char(3) not null

4、, 成績 smallint)數(shù)據(jù)的實體完整性實驗1 用SSMS分別將學(xué)生情況表（XSQK）的學(xué)號字段、課程表（KC）的課程號字段設(shè)置為主健用TSQL語句將成績表（XS_KC）的學(xué)號、課程號字段設(shè)置為主健alter table xs_kcadd primary key(學(xué)號,課程號)數(shù)據(jù)的參照完整性實驗用SSMS為成績表（XS_KC）創(chuàng)建外鍵FK_ XSQK_ID，外鍵FK_ XSQK_ID參照學(xué)生情況表（XSQK）表的學(xué)號用TSQL語句成績表（XS_KC）創(chuàng)建外鍵FK_ KC_ID，外鍵FK_ KC _ID參照課程表（KC）表的課程號alter table xs_kcadd constrain

5、t FK_KC_ID foreign key (課程號) references kc(課程號)數(shù)據(jù)的用戶定義完整性實驗 用TSQL語句為學(xué)生情況表（XSQK）的姓名列創(chuàng)建一個唯一約束alter table xsqkadd constraint ch_xsqk_unique unique(姓名)用SSMS為學(xué)生情況表（XSQK）的性別列創(chuàng)建一個檢查約束，使得性別的值為男或女用TSQL語句為成績表（XS_KC）的成績列創(chuàng)建一個檢查約束，使得成績的值在（0100）之間alter table xs_kcadd constraint ch_grade check(成績 between 0 and 100

6、) 用SSMS語句課程表（KC）的學(xué)時列創(chuàng)建一個缺省約束，缺省值為60用TSQL語句課程表（KC）的學(xué)分列創(chuàng)建一個缺省約束，缺省值為2alter table kcadd constraint default_kc_credit default 2 for 學(xué)分將如下數(shù)據(jù)分別轉(zhuǎn)換成文本數(shù)據(jù)或Excel的格式，并分別將其導(dǎo)入數(shù)據(jù)庫的各個表中：學(xué)號姓名性別出生日期專業(yè)所在系聯(lián)系電話020101楊穎01980-7-20計算機應(yīng)用計算機88297147020102方露露01981-1-15計算機應(yīng)用計算機88297147020103俞奇軍11980-2-20信息管理計算機88297151020104胡國

7、強11980-11-7信息管理計算機88297151020105薛冰11980-7-29水利工程水利系88297152020201秦盈飛01981-3-10電子商務(wù)經(jīng)濟系88297161020202董含靜01980-9-25電子商務(wù)經(jīng)濟系88297062020203陳偉11980-8-7電子商務(wù)經(jīng)濟系88297171020204陳新江11980-7-20房建水利系88297171學(xué)生情況表（XSQK）：課程表（KC）：課程號課程名教師開課學(xué)期學(xué)時學(xué)分101計算機原理陳紅2453102計算方法王頤3453103操作系統(tǒng)徐格2604104數(shù)據(jù)庫原理及應(yīng)用應(yīng)對剛3755105網(wǎng)絡(luò)基礎(chǔ)吳江江44531

8、06高等數(shù)學(xué)孫中文1906107英語陳剛1906108VB程序設(shè)計趙紅韋3705成績表（XS_KC）:學(xué)號課程號成績020101101850201011028702010110788020102101580201021026302010410776020202103550202021088002020310357020204103713. 理解默認(rèn)值的概念和作用用語句創(chuàng)建名為Xi_default，值為 “計算機系”的默認(rèn)值create default Xi_default as '計算機系'將默認(rèn)值Xi_default綁定到學(xué)生表中的所在系的屬性列上sp_bindefault

9、Xi_default,'xsqk.所在系' 解除學(xué)生表所在系的屬性列上的默認(rèn)值sp_unbindefault 'xsqk.所在系' 刪除默認(rèn)值Xi_defaultdrop default Xi_default注：創(chuàng)建默認(rèn)值的格式： create default默認(rèn)值名as 默認(rèn)值默認(rèn)值綁定的格式：sp_bindefault默認(rèn)值名, <表名.列名自定義數(shù)據(jù)類型名稱>解除默認(rèn)值綁定格式：sp_unbindefault<表名.列名自定義數(shù)據(jù)類型名稱>刪除默認(rèn)值格式：Drop default 默認(rèn)值名4.理解規(guī)則的概念和作用用語句創(chuàng)建

10、規(guī)則“rule_kkxq”，用以限制插入該規(guī)則所綁定的列中的值只能取1、2、3、4、5、6create rule rule_kkxq as ss in(1,2,3,4,5,6) 將“rule_ kkxq”規(guī)則綁定到課程表的開課學(xué)期屬性上，并執(zhí)行以下語句，看看能否正常執(zhí)行，為什么：Insert into 課程表Values(109,'C語言','李方',8,64,4)sp_bindrule rule_kkxq,'kc.開課學(xué)期'不能正常執(zhí)行因為數(shù)值8不在規(guī)則rule_kkxq所綁定的數(shù)據(jù)范圍內(nèi)若不解除規(guī)則，能否將規(guī)則rule_ kkxq直接刪除？不

11、能,需先解除規(guī)則才能刪除規(guī)則rule_ kkxq :sp_unbindrule 'kc.開課學(xué)期'drop rule rule_kkxq注：創(chuàng)建規(guī)則的格式： create rule規(guī)則名as 列名約束條件規(guī)則綁定的格式： sp_bindrule規(guī)則名, <表名.列名自定義數(shù)據(jù)類型名稱>解除規(guī)則綁定格式：sp_unbindrule <表名.列名自定義數(shù)據(jù)類型名稱>刪除規(guī)則格式：Drop rule 規(guī)則名5.數(shù)據(jù)的安全性實驗：(1)設(shè)置身份驗證模式 寫出查看當(dāng)前SQL Server身份驗證模式的過程，即查看當(dāng)前SQL Server系統(tǒng)到底

12、是采用Windows身份驗證還是混合身份驗證模式。服務(wù)器->右鍵"屬性"->選擇"安全性"將系統(tǒng)設(shè)置為Windows身份驗證模式，重啟SQL Server，使之生效并驗證將系統(tǒng)設(shè)置為混合身份驗證模式，重啟SQL Server，使之生效并驗證創(chuàng)建和管理登錄賬戶 1）用對象資源管理器創(chuàng)建、查看、刪除SQL Server登錄賬戶分別創(chuàng)建兩個登錄帳戶LoginA、LoginA1，其登錄密碼分別為：123456、456，并指定LoginA登錄默認(rèn)的數(shù)據(jù)庫為學(xué)生成績數(shù)據(jù)庫(XSCJ)，指定LoginA1登錄默認(rèn)的數(shù)據(jù)庫為master數(shù)據(jù)庫。查看登錄賬戶L

13、oginA、LoginA1刪除登錄賬戶LoginA12）用T-SQL語句創(chuàng)建、查看、刪除SQL Server登錄賬戶用系統(tǒng)存儲過程，分別創(chuàng)建兩個登陸帳戶LoginB、LoginB1，其登錄密碼分別為：666、888，登錄后然后所連接到的數(shù)據(jù)庫均為學(xué)生成績數(shù)據(jù)庫(XSCJ)。sp_addlogin 'LoginB','666','XSCJ'sp_addlogin 'LoginB1','888','XSCJ'注：創(chuàng)建新的SQL Server登錄賬號和登錄密碼的語句為： sp_addlogin login

14、ame = 'login' /登錄的名稱    , passwd = 'password' /登錄密碼。    , defdb = 'database' /登錄的默認(rèn)數(shù)據(jù)庫（登錄后登錄所連接到的 數(shù)據(jù)庫）。默認(rèn)設(shè)置為 master。   例：創(chuàng)建一個登陸帳戶Login，登錄密碼為：A，登錄后然后所連接到的數(shù)據(jù)庫 為master數(shù)據(jù)庫。 sp_addlogin 'Login','A','master' 或 sp_addlo

15、gin loginame ='Login', passwd ='A', defdb ='master'使用系統(tǒng)存儲過程sp_helplogins查看SQL Server登錄賬戶注：查看登錄賬戶的格式： exec sp_helplogins使用系統(tǒng)存儲過程sp_droplogin從SQL Server中將登錄賬戶LoginB1刪除sp_droplogin 'LoginB1'注：刪除登錄賬戶的格式：sp_droplogin loginame = '登錄名稱' 創(chuàng)建和管理數(shù)據(jù)庫用戶1）用對象資源管理器創(chuàng)建、查看、刪除數(shù)據(jù)

16、庫用戶 在學(xué)生成績數(shù)據(jù)庫(XSCJ)中創(chuàng)建數(shù)據(jù)庫用戶UserA，使其所對應(yīng)的帳號為LoginA 能否再創(chuàng)建一個數(shù)據(jù)庫用戶ABC，使其對應(yīng)登錄的帳號為LoginA，若不能寫出其原因？不能,同一個數(shù)據(jù)庫的數(shù)據(jù)庫用戶只能有一個登錄賬戶“該登陸已用另一開立賬戶用戶名”2）用語句創(chuàng)建、查看、刪除數(shù)據(jù)庫用戶 用系統(tǒng)存儲過程sp_grantdbaccess 在學(xué)生成績數(shù)據(jù)庫(XSCJ)中創(chuàng)建一個數(shù)據(jù)庫用戶UserB，使其所對應(yīng)的登錄帳號為LoginBsp_grantdbaccess 'LoginB','UserB'注：為SQL Server登錄在當(dāng)前數(shù)據(jù)庫中添加一個安全用戶的

17、語句為：sp_grantdbaccess loginame = 'login' /當(dāng)前數(shù)據(jù)庫中的登錄名稱    ,name_in_db = 'name_in_db' /數(shù)據(jù)庫中帳戶的名稱 用系統(tǒng)存儲過程sp_helpuser查看數(shù)據(jù)庫用戶sp_helpuser 'UserB' 注：查看數(shù)據(jù)庫用戶的格式： sp_helpuser name_in_db = 'security_account' 用系統(tǒng)存儲過程sp_ revokedbaccess刪除數(shù)據(jù)庫用戶ABC1無法對用戶'ABC1'

18、執(zhí)行刪除，因為它不存在，或者您沒有所需的權(quán)限。 注：刪除數(shù)據(jù)庫用戶的格式：sp_ revokedbaccess name_in_db = ' name '創(chuàng)建和管理角色1）服務(wù)器角色的管理 用對象資源管理器將登錄帳戶LoginA加到sysadmin的角色中。 用系統(tǒng)存儲過程sp_addsrvrolemember將登錄帳戶LoginB加到sysadmin的角色中。sp_addsrvrolemember 'LoginB',sysadmin注：為登錄賬戶指定服務(wù)器角色的系統(tǒng)存儲過程sp_addsrvrolemember的格式為： sp_addsrvrolemember

19、 '登錄賬號'，服務(wù)器角色名稱 用系統(tǒng)存儲過程sp_dropsrvrolemember刪除登錄賬戶LoginA的服務(wù)器角色sysadminsp_dropsrvrolemember 'LoginA',sysadmin注：刪除登錄賬戶的服務(wù)器角色的系統(tǒng)存儲過程sp_dropsrvrolemember的格式為：sp_dropsrvrolemember '登錄賬號'，服務(wù)器角色名稱2）數(shù)據(jù)庫角色的管理用對象資源管理器將數(shù)據(jù)庫用戶UserA添加到db_owner數(shù)據(jù)庫的角色中用系統(tǒng)存儲過程sp_addrolemember將數(shù)據(jù)庫用戶UserB添加到db_o

20、wner數(shù)據(jù)庫角色中。sp_addrolemember 'db_owner',UserB注：為數(shù)據(jù)庫用戶指定數(shù)據(jù)庫角色的系統(tǒng)存儲過程sp_addrolemember的格式為： sp_addrolemember rolename'role',/當(dāng)前數(shù)據(jù)庫角色的名稱      membername = security_account /*添加到角色的安全帳戶。security_account 可以是所有有效的 SQL Server 用戶、SQL Server 角色或是所有已授權(quán)訪問當(dāng)前數(shù)據(jù)庫的 Microsoft Windo

21、ws NT® 用戶或組。*/用系統(tǒng)存儲過程sp_droprolemember刪除數(shù)據(jù)庫用戶UserB的db_owner數(shù)據(jù)庫角色sp_droprolemember 'db_owner',UserB 注：刪除數(shù)據(jù)庫用戶的數(shù)據(jù)庫角色的系統(tǒng)存儲過程sp_droprolemember的格式為：sp_droprolemember ' 數(shù)據(jù)庫角色名稱 '，數(shù)據(jù)庫用戶賬戶3）用戶自定義角色的管理在學(xué)生成績數(shù)據(jù)庫(XSCJ)中創(chuàng)建一個角色RoleB create role RoleB 注：在當(dāng)前數(shù)據(jù)庫創(chuàng)建新的角色： sp_addrole rolename = '

22、;role' / 新角色的名稱      , ownername = 'owner' /*新角色的所有者，owner默認(rèn)值為 dbo。owner 必須是當(dāng)前數(shù)據(jù)庫中的某個用戶或角色*/ 將用戶UserB加入到自定義的RoleB這個角色中sp_addrolemember 'RoleB',UserB 注：將數(shù)據(jù)庫戶添加到自定義數(shù)據(jù)庫角色的格式為： sp_addrolemember rolename'role',/當(dāng)前數(shù)據(jù)庫角色的名稱      membername

23、 = security_account /*添加到角色的安全帳戶。security_account 可以是所有有效的 SQL Server 用戶、SQL Server 角色或是所有已授權(quán)訪問當(dāng)前數(shù)據(jù)庫的 Microsoft Windows NT® 用戶或組。*/ 將學(xué)生成績數(shù)據(jù)庫(XSCJ)中學(xué)生表的Select權(quán)限授予RoleBgrant select on xsqkto RoleB用帳戶LoginB身份登陸，并學(xué)生成績數(shù)據(jù)庫(XSCJ)中執(zhí)行Create Table Test (aa varchar(10)Select * from 學(xué)生情況表Select * from 成績表并

24、查看以上各語句能否正常執(zhí)行，為什么？如何才能使不能執(zhí)行的語句執(zhí)行？如果用帳號LoginA登陸，上面的語句能否執(zhí)行。不行,因為UserB沒有Create Table權(quán)限GRANT CREATE TABLE TO UserB如果用帳號LoginA登陸，上面的語句可以執(zhí)行。從角色RoleB中去除用戶UserBsp_droprolemember 'RoleB',UserB從學(xué)生成績數(shù)據(jù)庫(XSCJ)中刪除用戶UserBsp_dropuser UserB從學(xué)生成績數(shù)據(jù)庫(XSCJ)中刪除角色RoleBsp_droprole RoleB從SQL Server中刪除登陸帳戶LoginBsp_

25、droplogin LoginB注：在刪除登錄帳號之前，應(yīng)先將登錄帳號所對應(yīng)的用戶帳號全部刪除，不然將無法刪除登錄帳號。權(quán)限的授予與回收1）權(quán)限的授予 把查詢XSQK表的權(quán)限授予用戶UserAgrant selecton xsqkto UserA 把對KC表全部操作權(quán)限授予用戶UserAgrant select,insert,update,deleteon kcto UserA把對XS_KC表的查詢權(quán)限授予所有用戶grant selecton xs_kcto public 把刪除XSQK表和修改學(xué)生學(xué)號的權(quán)限授予用戶UserAgrant delete,update(學(xué)號)on xsqkto U

26、serA 把插入XSQK表的權(quán)限授予用戶UserA，并允許將此權(quán)限再授予其它用戶grant inserton xsqkto UserAwith grant option 把在學(xué)生成績數(shù)據(jù)庫(XSCJ)中建立表的權(quán)限授予用戶UserAgrant create tableto UserA2）權(quán)限的回收 撤銷用戶UserA修改學(xué)號的權(quán)限r(nóng)evoke update(學(xué)號)on xsqkfrom UserA 撤銷所有用戶對XS_KC表的查詢權(quán)限r(nóng)evoke selecton xs_kcfrom public 撤銷用戶UserA對XSQK表的插入權(quán)限r(nóng)evoke inserton xsqkfrom Use

27、rA CASCADE四、 實訓(xùn)思考題1 一個用戶要訪問數(shù)據(jù)庫須要經(jīng)過哪幾個安全認(rèn)證階段？ 答: 三個階段及相應(yīng)的安全認(rèn)證過程： 第一階段：用戶首先要登錄到 SQLServer 實例。在登錄時，系統(tǒng)要對其進行身份驗證，被認(rèn) 為合法才能登錄到 SQLServer 實例。 第二階段：用戶在每個要訪問的數(shù)據(jù)庫里必須獲得一個用戶賬號。SQL Server 實例將 SQL Server 登錄映射到數(shù)據(jù)庫用戶賬號上， 在這個數(shù)據(jù)庫的用戶賬號上定義數(shù)據(jù)庫的管理和 數(shù)據(jù)對象的訪問的安全策略。 第三階段：用戶訪問數(shù)據(jù)庫。用戶訪問數(shù)據(jù)庫對象時，系統(tǒng)要檢查用戶是否具有訪問數(shù) 據(jù)庫對象、執(zhí)行動作的權(quán)限，經(jīng)過語句許可權(quán)限

28、的驗證，才能實現(xiàn)對數(shù)據(jù)的操作。2 簡述登錄賬戶和用戶賬戶間區(qū)別與聯(lián)系 答: 登錄賬戶是登錄SQLServer 實例的賬戶;用戶賬戶是訪問數(shù)據(jù)庫的賬戶.3 用戶、角色和權(quán)限的職能，以及它們之間的關(guān)系權(quán)限系統(tǒng)的所有權(quán)限信息。權(quán)限具有上下級關(guān)系，是一個樹狀的結(jié)構(gòu)。 用戶應(yīng)用系統(tǒng)的具體操作者，用戶可以自己擁有權(quán)限信息，可以歸屬于0n個角色，它的權(quán)限集是自身具有的權(quán)限、所屬的各角色具有的權(quán)限、所屬的各組具有的權(quán)限的合集。它與權(quán)限、角色 之間的關(guān)系都是n對n的關(guān)系。 角色可以對許多擁有相似權(quán)限的用戶進行分類管理,具有上下級關(guān)系，可以形成樹狀視圖，父級角色的權(quán)限是自身及它的所有子角色的權(quán)限的綜合。父級角色的用戶、父級角色的組同理可推。4 服務(wù)器角色有哪些，并分別簡述其功能？用戶可以創(chuàng)建服務(wù)器角色嗎？答: 服務(wù)器角色有:sysadmin 可以在 SQL Server 中執(zhí)行任何活動。 serveradmin 可以設(shè)置服務(wù)器范圍的配置選項，關(guān)閉服務(wù)器。 setupadmin 可以管理鏈接服務(wù)器和啟動過程。 securityadmin 可以管理登錄和 CREATE DATABASE 權(quán)限，還可以讀取錯誤日志和更改密碼。 processadmin 可以管理在 SQL Server 中運行的進程。 dbcreator 可