SQLServer2008實(shí)驗(yàn)四

1、安全性管理（2學(xué)時(shí)）實(shí)驗(yàn)?zāi)康模?1) 學(xué)習(xí)使用SSMS中數(shù)據(jù)維護(hù)功能(2) 掌握使用Insert、Update、Delete命令維護(hù)數(shù)據(jù)表的數(shù)據(jù)的方法(3) 了解SQL Server 的認(rèn)證模式，安全管理的主要內(nèi)容和方法(4) 掌握用戶管理、訪問權(quán)限管理的基本方法實(shí)驗(yàn)內(nèi)容：第一部分 數(shù)據(jù)維護(hù)1. INSERT命令輸入數(shù)據(jù)使用Insert命令分別向OrderMag數(shù)據(jù)庫中的三個表中輸入如下3條數(shù)據(jù)，注意插入數(shù)據(jù)的順序，store表和customer表，再插入引用其主鍵為外鍵的orders表。1) Store (Pno, Pname, Ptype, Pnum)-庫存（零件號，零件名稱，零件類別，零

2、件數(shù)量）PnoPnamePtypePnumP1齒輪傳動200P2蝸桿傳動30P3螺栓標(biāo)準(zhǔn)120insert into Store values('P1','齒輪','傳動',200);insert into Store values('P2','蝸桿','傳動',30);insert into Store values('P3','螺栓','標(biāo)準(zhǔn)',120);2) Customer (Cno, Cname, Ctel, Caddr, Czip)-顧客（

3、顧客號，顧客名稱，電話，地址，郵編）cnoCnameCtelCaddrCzipC1北京聯(lián)合大京100101C2上海貝海C3L圳412100insert into Customer values('C1','北京聯(lián)合大學(xué)','#39;,'北京',100101);insert into Customer values('C2','上海貝爾','#39;,'上海&#

4、39;,' ');insert into Customer values('C3','LG','#39;,'深圳','412100');3) Orders (Ono, Cno, Pno, Onum, Osum, Odate)-訂單（訂單號，顧客號，定購零件號，定購數(shù)量，訂單金額，簽訂日期）OnoCnoPnoOnumOsumOsumO1C2P210010002002-4-8O2C3P3101002003-5-2O3C1P2202002007-4-5insert into Orders

5、 values('O1','C2','P2',100,1000,'2002-4-8');insert into Orders values('O2','C3','P3',10,100,'2003-5-2');insert into Orders values('O3','C1','P2',20,200,'2007-4-5');2. insert語句插入部分內(nèi)容向customer表中插入一條數(shù)據(jù)，插入列cn

6、o,cname,ctel，插入內(nèi)容為C4，南陽理工學(xué)院nsert into Customer (Cno,Cname,ctel)values('C4','南陽理工學(xué)院','#39;);3update語句把store表中所有零件數(shù)量增長一倍update Store set Pnum=Pnum*2把store表中零件類型為傳動類型的零件數(shù)量增加100update Store set Pnum=Pnum+100 where Ptype='傳動'4delete語句把order表中訂購零件號等

7、于P3的數(shù)據(jù)刪除。delete Orders where Pno='P3'第二部分 安全性管理1設(shè)置身份驗(yàn)證模式1）在SSMS中，選擇服務(wù)器實(shí)例2）單擊右鍵，選擇“屬性”，在屬性對話框中選擇“安全性”選項(xiàng)卡3）將身份驗(yàn)證模式設(shè)定為“僅Windows ”4）單擊“確定”，SQL SERVER重新啟動服務(wù)后更改驗(yàn)證模式5）重新啟動服務(wù)，登陸SSMS時(shí)選擇SQL SERVER驗(yàn)證，然后輸入sa用戶登錄6）能否登錄？為什么？不可以登錄，因?yàn)榇诉x項(xiàng)允許登錄的權(quán)限是windows，sa不是Windows的用戶7）重復(fù)1-3步驟，將身份驗(yàn)證模式設(shè)定為混合模式。注意：實(shí)驗(yàn)完成后應(yīng)該將其重新設(shè)定

8、為混合認(rèn)證模式；只有重新啟動服務(wù)，驗(yàn)證模式才生效。2. 添加SQL SERVER登錄帳號(1)用SSMS添加windows登錄帳號TestSQL1。1）在windows系統(tǒng)中創(chuàng)建新用戶TestSQL1，密碼TestSQL1（控制面板-管理工具-本地用戶和組-用戶-新增用戶） 2）在SSMS中，展開“安全性”，選擇“登錄名”，選擇“新建登錄名”3）選擇windows身份驗(yàn)證4）搜索用戶TestSQL1，單擊“確定” (2)用SSMS添加SQL SERVER登錄帳號TestSQL2。1）在SSMS中，展開“安全性”，選擇“登錄名”，選擇“新建登錄名”2）選擇SQL Server身份驗(yàn)證3）輸入用戶

9、名TestSQL2及密碼TestSQL2，單擊“確定”4）打開數(shù)據(jù)庫引擎查詢頁，輸入登錄帳號TestSQL2和密碼TestSQL2，單擊“確定”，此時(shí)數(shù)據(jù)庫引擎查詢頁是以TestSQL2身份登錄5）輸入并執(zhí)行查詢語句SELECT * FROM master.dbo.sysDatabases6）在查詢頁中輸入“Use OrderMag”T-SQL語句，切換當(dāng)前數(shù)據(jù)庫。7）切換是否成功? 為什么?切換數(shù)據(jù)庫失敗，因?yàn)閯?chuàng)建用戶的時(shí)候沒有授權(quán)給TestSQL2訪問數(shù)據(jù)庫Use OrderMag權(quán)限。如下圖，(3)使用CREATE LOGIN語句創(chuàng)建登錄名1）創(chuàng)建Windows驗(yàn)證模式登錄名TestSQ

10、L3（TestSQL3用戶在windows系統(tǒng)中創(chuàng)建），指定其默認(rèn)數(shù)據(jù)庫為master首先要在機(jī)器上創(chuàng)建windows用戶并按如下圖所示更改其屬性。然后執(zhí)行use mastergo create login "ZD-PCTestSQL3" from windows with default_database = mastergo2）SQL Server驗(yàn)證模式登錄名TestSQL4，密碼TestSQL4，并指定其默認(rèn)數(shù)據(jù)庫為master數(shù)據(jù)庫，要求使用密碼策略，在首次使用新登錄名時(shí)提示用戶輸入新密碼而且對該登錄名實(shí)施密碼過期策略（注意對應(yīng)的密碼策略關(guān)鍵字）。use mast

11、ergo create login TestSQL4 with password = 'TetsSQL4'must_change,default_database = master,check_expiration = on,check_policy = ongo3、添加數(shù)據(jù)庫用戶帳號（1）用SSMS授權(quán)SQL SERVER登錄帳號TestSQL2訪問數(shù)據(jù)庫OrderMag。1）在SSMS中，展開數(shù)據(jù)庫OrderMag中的“安全性”，選擇右鍵單擊“用戶”，選擇“新建用戶”，用戶名為TestSQL22）在“登錄名”中選擇TestSQL2，然后單擊“確認(rèn)”3）打開數(shù)據(jù)庫引擎查詢頁，

12、輸入登錄帳號TestSQL2和密碼TestSQL2，單擊“確認(rèn)”。4）此時(shí)查詢就是以TestSQL2身份登錄5）在查詢頁中輸入“Use OrderMag”T-SQL語句，切換當(dāng)前數(shù)據(jù)庫。Use OrderMag6）切換是否成功？為什么？可以切換到數(shù)據(jù)庫OrderMag，因?yàn)樵谟脩鬞estSQL2中已經(jīng)映射其權(quán)限。7）在查詢頁中輸入”select * from Customer”，查詢是否成功，為什么？不能訪問數(shù)據(jù)表，因?yàn)橛脩鬞estSQL2，還沒有被授予訪問數(shù)據(jù)表的權(quán)限。注意：User Name與Login的名稱可以相同，也可以不同，但是建議采用相同的名稱以方便維護(hù)管理。（2）用CREATE

13、USER語句創(chuàng)建用戶要求使用SQL Server登錄名TestSQL4在OrderMag數(shù)據(jù)庫中創(chuàng)建數(shù)據(jù)庫用戶TestSQL4，默認(rèn)架構(gòu)名使用dbo。CREATE LOGIN TestSQL4 WITH PASSWORD='123456',DEFAULT_DATABASE= master, DEFAULT_LANGUAGE=簡體中文,DEFAULT_SCHEMA=dbo, CHECK_EXPIRATION=ON, CHECK_POLICY=ONGO4、給登錄帳號分配固定服務(wù)器角色（1）在SMSS中給帳號分配固定服務(wù)器角色，讓帳號有管理SQL Server權(quán)限。1）雙擊登錄帳號T

14、estSQL2，選擇“服務(wù)器角色”選項(xiàng)卡2）選擇Security Administrators固定服務(wù)器角色，“確定”（2）用sql語句實(shí)現(xiàn)以下要求將Windows登錄名* TestSQL1添加到sysadmin固定服務(wù)器角色中。ALTER SERVER ROLE sysadmin ADD MEMBER ZD-PCTestSQL1GO5、給用戶帳號分配固定數(shù)據(jù)庫角色（1）在SMSS中給用戶帳號分配固定數(shù)據(jù)庫角色。1）雙擊登陸賬號TestSQL22）常規(guī)頁選擇db_owner固定數(shù)據(jù)庫角色，單擊“確定”。3) 在查詢頁中輸入”select * from Customer”,查詢是否成功，為什么？

15、查詢成功，因?yàn)橛脩鬞estSQL2的權(quán)限變?yōu)閿?shù)據(jù)庫的擁有者，可以完全擁有數(shù)據(jù)庫的權(quán)限。（2）用sql語句實(shí)現(xiàn)以下要求將OrderMag數(shù)據(jù)庫上的數(shù)據(jù)庫用戶TestSQL4添加為固定數(shù)據(jù)庫角色db_owner的成員。USE masterGOALTER ROLE db_owner ADD MEMBER TestSQL4GO6、給用戶賬戶分配自定義角色（1）在SMSS中給用戶帳號TestSQL2分配自定義角色role1。1）新建數(shù)據(jù)庫角色，名稱為role1，所有者選擇dbo2）在角色成員中添加用戶TestSQL25、6的界面操作方法均可以實(shí)現(xiàn)為用戶賬戶分配角色（2）用sql語句實(shí)現(xiàn)以下要求CREAT

16、E ROLE語句創(chuàng)建自定義角色，創(chuàng)建名為ROLE2的新角色，并指定dbo為該角色的所有者；將SQL Server登錄名創(chuàng)建的OrderMag的數(shù)據(jù)庫用戶TestSQL4添加到數(shù)據(jù)庫角色ROLE2中USE masterGOCREATE SERVER ROLE role2 AUTHORIZATION zd-PCzdGOALTER SERVER ROLE role2 ADD MEMBER TestSQL4GO7、創(chuàng)建應(yīng)用程序角色并激活用sql語句創(chuàng)建應(yīng)用程序角色（名稱approle，密碼123，默認(rèn)架構(gòu)dbo）并激活CREATE APPLICATION ROLE approle WITH PASSW

17、ORD = '123' DEFAULT_SCHEMA = dbo8、權(quán)限，用sql語句實(shí)現(xiàn)以下要求：1）給OrderMag數(shù)據(jù)庫上的用戶TestSQL4授予創(chuàng)建表的權(quán)限。USE OrderMagGOALTER ROLE db_datareader ADD MEMBER TestSQL4GO2）授予用戶TestSQL4，使用戶有對customer表的所有操作權(quán)限use OrderMagGOGRANT TAKE OWNERSHIP ON dbo.Customer TO TestSQL4GO3）取消以前對TestSQL4授予或拒絕的在customer表上的SELECT權(quán)限。use O

18、rderMagGOREVOKE INSERT ON dbo.Customer TO TestSQL4 AS dboGOuse OrderMagGOREVOKE VIEW DEFINITION ON dbo.Customer TO TestSQL AS dboGOuse OrderMagGOREVOKE VIEW CHANGE TRACKING ON dbo.Customer TO TestSQL CASCADE AS dboGOuse OrderMagGOREVOKE ALTER ON dbo.Customer TO TestSQL4 CASCADE AS dboGOuse OrderMagG

19、OREVOKE UPDATE ON dbo.Customer TO TestSQL4 CASCADE AS dboGOuse OrderMagGOREVOKE SELECT ON dbo.Customer TO TestSQL4 AS dbGO9、架構(gòu)，用sql語句實(shí)現(xiàn)以下要求：創(chuàng)建架構(gòu)test_schema，其所有者為用戶TestSQL4CREATE SCHEMA test_schema test_schema | AUTHORIZATION TestSQL4 | test_schema AUTHORIZATION TestSQL4 請同學(xué)注意所有涉及到用T-SQL語句實(shí)現(xiàn)的操作必須將SQL

20、語句按照題目的順序保存到一個文檔并提交，希望大家養(yǎng)成寫注釋的習(xí)慣，適當(dāng)?shù)脑赟QL語句中增加注釋思考實(shí)驗(yàn)部分中提到的問題，理解登錄名、用戶、角色、權(quán)限之間的關(guān)系SQL語句insert into Store values('P1','齒輪','傳動',200);insert into Store values('P2','蝸桿','傳動',30);insert into Store values('P3','螺栓','標(biāo)準(zhǔn)',120);insert int

21、o Customer values('C1','北京聯(lián)合大學(xué)','#39;,'北京',100101);insert into Customer values('C2','上海貝爾','#39;,'上海',' ');insert into Customer values('C3','LG','#39;,'深圳','412100

22、9;);insert into Orders values('O1','C2','P2',100,1000,'2002-4-8');insert into Orders values('O2','C3','P3',10,100,'2003-5-2');insert into Orders values('O3','C1','P2',20,200,'2007-4-5');insert into Custom

23、er (Cno,Cname,ctel)values('C4','南陽理工學(xué)院','#39;);update Store set Pnum=Pnum*2update Store set Pnum=Pnum+100 where Ptype='傳動'delete Orders where Pno='P3'use mastergo create login "ZD-PCTestSQL3" from windows with default_database = mastergouse

24、 mastergo create login TestSQL4 with password = 'TetsSQL4'must_change,default_database = master,check_expiration = on,check_policy = ongoCREATE LOGIN TestSQL4 WITH PASSWORD='123456',DEFAULT_DATABASE= master, DEFAULT_LANGUAGE=簡體中文,DEFAULT_SCHEMA=dbo, CHECK_EXPIRATION=ON, CHECK_POLICY=ONGOALTER SERVER ROLE sysadmin ADD MEMBER ZD-PCTestSQL1GOUSE masterGOALTER ROLE db_owner ADD MEMBER TestSQL4GOUSE masterGOCREATE SERVER ROLE role2 AUTHORIZATION zd-PCzdGOALTER SERVER ROLE role2 ADD MEMBER TestSQL4GOCREATE APPLI