安全性語言實驗

山西大學軟件學院課程實驗報告實驗課程 數(shù)據(jù)庫課程實驗實驗名稱安全性語言實驗實驗時間第十三周3節(jié)到4節(jié)學生班級 學生學號 學生姓名 指導教師 2020年5月22日實驗目的:掌握自主存取控制權(quán)限的定義和維護方法實驗器材：計算機MySQLWorkbench實驗內(nèi)容:定義用戶，角色，分配權(quán)限給用戶，角色回收權(quán)限，以及相應的用戶名登錄數(shù)據(jù)庫驗證權(quán)限分配是否正確實驗步驟：一.自主存取控制實驗針對第一個實驗的數(shù)據(jù)庫，定義三種不同用戶，分別實現(xiàn)不同功能。分別為班長Tom學生Mike，教務處主任Jane，教務處老師Kathy和輔導員David，授課教師Jiffy。創(chuàng)建用戶為班長，教務處主任以及輔導員創(chuàng)建用戶標識，要求具有創(chuàng)建用戶或角色的權(quán)利。createuser'Tom'@'localhost'identifiedby'123456';createuser'Jane'@'localhost'identifiedby'123456';createuser'David'@'localhost'identifiedby'123456';\ s __i ■ . . . ( |IkhUhiLIferid |h |￥M |配|H K |if |&|K N *|1 |ID胃 11 1、， 1Hie InIn 1i■ 411| 01LU|RM.U..11KIHU.11K4IIM9I.IkM IN 1W■ 1S1N K 1F 1K￡ N ■1i 1K■ 1 1%5 |M1W SK1、 1、E I 4I1| 0|1udiiKiLdajJjiMnasri也而19?<n〔 帝i§uh"ML1時In 1w1NULL1iK-ilbail*<■ |b |￥M 1〈1H K |P |K1J |帖E 11 |hS 15為學生，教務處老師，授課教師創(chuàng)建用戶標識和用戶口令。createuser'Mike'@'localhost'identifiedby'123456';createuser'Kathy'@'localhost'identifiedby'123456';createuser'Jiffy'@'localhost'identifiedby'123456';■ ■一hvid I三 X f |K |K I肩 |?司 M 1 m 1冒 1h InN|N 1R :N 1K 1KQ SI 。] $$c Jm_ ptpi1.LIS3BH06-36L4：422a| MILLI￥ |N IH1MUIwIhMlJKM I， |M 1， |M |H |S I?% N |N 1K |M |K |圈1國I9, |pg IH IK IN0 哥I D C*1cudi3m_al'i^_paair?I：qI U mil,|M 1K 1KidJlLloc-LlhasiJlCCy |3 N I， |K |K 1K |?創(chuàng)建角色并分配權(quán)限1.為班長，輔導員，教務處主任分別創(chuàng)建一個查詢角色，并分配相應的查詢權(quán)限。createrole'class_leaderwn'@'localhost';grantselectontablesecond.studentto'class_leaderwn'@'localhost';grantselectontablesecond.courseto'class_leaderwn'@'localhost';Hrm事■ant(t>Eiecl”0MOTcxm-WKFiHLlMthfT/OilgZbMF!ChMflfi. 111Jl-HL IbMGUVTLUGEr*10 IUU.VTULHETUhJurivRc^luiiaidn-Bn.localhMtQUVT5ELKE皿iKionl.dCudiriiW IIwlhui]rt?iLriMEUEIiit)createrole'class_assistant'@'localhost';grantselectontablesecond.studentto'class_assistant'@'localhost';grantselectontablesecond.scto'classassistant'@'localhost';grantselectontablesecond.courseto'class_assistant'@'localhost';LSMi：的■產(chǎn)的rlMi_*Etli!Luil?dHCklli&itGtAXT5B_oTTW1*網(wǎng)沔?zhèn)?皿■:Jan_*MJarbi1!t'l4C?]hdir[，GUATSixmfflhRi-TEodx-TT1uLm?_n??i i&caJii&ar皿儲|1BL￡CIQn'?rcmd.iLuhiiLTQc.LiD3?KddLilanlILuEiJliail：mlidw1!iD.Ulm?]createrole'academic_officer'@'localhost';grantselectontablesecond.studentto'academic_officer'@'localhost';grantselectontablesecond.scto'academic_officer'@'localhost';grantselectontablesecond.courseto'academic_officer'@'localhost';*燮MVcumFM LmIImvl1:?OniMlfeir■BEa^w.ie_Dfflcw4J?v]|T3aLflVKTLSKEON■IQ■￡ l>Mt"，L-bcaJIcaLOW^T4a￡TT5'mcvd'.'i?yTE?'T9'Knd?lc_flnLwrt'imatNci'G￡aS-1SELECTOtiiwd-TQ tjaffTtitf■iM&llMrieiJIUlW5ME5wnwl.irudHirID Tlrtr?LcolWm4LiIQ4n2.為學生，教務處老師，授課教師創(chuàng)建角色，并合理賦予某項權(quán)限。createrole'class_studentyf@'localhost';grantselectontablesecond.studentto'class_studentyf@'localhost';grantselectontablesecond.courseto'class_studentyf@'localhost';■；i>q]>siofotwcrsradWi■阻LrJIbh'_|Lirantxforvl??H_nud?nLy*f4CD!=iJ&EaLInxivrusMOm?；.tof*LwvSkmR'iSaXTSELEjCZM■■caul.EDuriHTOclUi_iRtdvDLpf?IdcaLbail俺藍TSELECT糠'.Mudwir'TTi '1°L?4]h?-'i:3tomInliftft).Wcreaterole'class_tencherghj'@'localhost';grantselectontablesecond.studentto'class_tencherghj'@'localhost';grantselect,update,deleteontablesecond.scto'class_tencherghj'@'localhost';grantselectontablesecond.courseto'class_tencherghj'@'localhost';占pIHOTMTOFEW！ llaulafdrrI■”一OUhl1-yOEffi■EUslHalJindivf^iJflL-MHUtnl依妙了5ELKTWWEWrfMAfVfTOIfflrrwTlhff'iMfelh^lSLLKf.&冷KE.HCf,踞iKoid.i￡Fi:hix.trn-hFr^jtLnaLhutflRA^TW￡CTWwterri. Etliiifl.iv-gdwrtlij■kMUheti4IXAHliMK10.fOIK.*createrole'academic_tenchersm'@'localhost';grantselect,updateontablesecond.studentto'academic_tenchersm'@'localhost';grantselectontablesecond.scto'academic_tenchersm'@'localhost';grantselect,delete,updateontablesecond.courseto'academic_tenchersm'@'localhost';Nemsfardc&kflLc.ccncter^VlocbLtesr■am3*.■TOaEDilMic-tmchfrMIfIwLho我'以盛十主ELCtT,UMWlfLECLCtC TG IIcchIImi:'iiAMSELECT*0H k'W'■nFinlr.Vnclwrv■loc.UnH、..GIANTEELUT,L3WTECBKKwd'..iiuImieTD'■cu1mIc_tmic1sh-u''I'ScciJIkce'4TO?f11m時IE5M)班長，輔導員和教務處主任有權(quán)給其他人分配權(quán)限。createrole'class_studentlmz'@'localhost';grant'class_leaderwn'@'localhost'to'class_studentlmz'@'localhost';5HP1O/CTSME]Mi_nud*r^]az!?*iMiltwiic1:如aihfarc]aia_iLudvrriUal*]TC?]b<FFkffiJM ?.■TDelAldMv&nlhtt■Lnlhcdl.GRVnVblll.lMAflH'f 1D' lMV'LKikllMiiV!2EVUhhMl皿和RtK■rwl'createrole'class_tencherlzx'@'localhost';grant'class_assistant'@'localhost'to'class_tencherlzx'@'localhost';QL缶飴 tLua.tmclMrLBX1*4^,)MnLtnrL*:ternLaEcruLua.^ncbKrLxjLtLECtllhaal函LWL加L驚■*加uL?"_W■曲rrlxiflL?ilhca?CE.W V' IDe]A91_1*Mh*f]￡K*iKlUMhlFrfLii*ffl.Wcreaterole'academic_tenchernq'@'localhost';grant'academic_officer'@'localhost'to'academic_tenchernq'@'localhost';giurw4xMnl<_i4Mbe-n￡j*L-K&LliH：[UKr*ID IWlWTIQt'J?lJb(]G1uUlHK-d'kctrtL-KkLhKui:W心3=jMulHirTq*LocbLjemI:2nnilau-t(0.46ik)呻響.（3）給用戶分配權(quán)限。1.給班長Tom,輔導員David，教務處主任Jane分配權(quán)限。

grant'class_leaderwn'@'localhost'to'Tom'@'localhost';iriqLSWE3UKTSFW'Tm^iKiLkiif;中r虹tmiz心曲再r (KK.liTOM■如OLE Ili<i]bQ4il：TflTeafIwtlh^l'nnsineIIQ.0￡>3k|>grant'class_assistant'@'localhost'to'David'@'localhost';Druil:aIerriilMuLhniESlNtU￡iaCH■.事M?IruJhartiBjWFClUI*_AiAllRME?iMikllM'feTOIwilV]M-*Lk4i!!efGnirlID.IR3?kFgrant'academic_officer'@'localhost'to'Jane'@'localhost';mqP泌4CAM IMb"Gfmicxfeer uuIhamrawercs'uaminrjwtCL?mL|w!<.Q^AX" ■lacHlbaat10Jnv*Lonlhaat2fovaiaut?kJ■咧】：'2,給學生Mike,授課教師Jiffy，教務處老師Kathy分配權(quán)限。grant'class_studentyf@'localhost'to'Mike'@'localhost';If通L」SWTQUEJDt'yiU'l -!-:rMil-.pth.-ri.hj'UKW1LWKL項?.■*rt-Jiuhv1JccaLlaLIKW1 4LhaIUaiTO]<x-d：ludt2nnianHl?M.iOhe^ViqL}grant'class_tencherghj'@'localhost'to'Jiffy'@'localhost';Fg|)SAW fcifjEffT-#L-H-Hlhrct';linij.for」1刊ttwiiAntm■*Nili-rh*LwLM-i：OLFT■E.lAiiJ-tuMrihJ?iMtlhMi!T4 ImIImmimvlasvI:ftJ.-M?r)grant'academictenchersm'@'localhost'to'Kathy'@'localhost';WXT A0f'K>Lhv'l'L-klUkac*;ftvirsfarliihpVJiMilhJK!：CRPTW'.*TOKiSyfLhU帥"SA?{-hL*±Mlc_EiHdMr￡H42dcjL3k?i.IDKiEhy#JaciLtWiJi.2FME=nME(Q.40B?ci*岫-回收用戶權(quán)限或角色權(quán)限。1.回收授課教師權(quán)限。revokeupdateontablesecond.scfrom'class_tencherghj'@'localhost';"Ml?OUWT5FBH 】俄Lf:OttaCri!虹E]Ui?Rdi￡>?E|jLdLmillMi-lOWtUSAS??LEfi>'I心J4EGE機EE1BC]ffl'mid'.>wn+'W'eiiM.ctulwrikJV'LMkHMM-aWT4OK1.KTW' ：'-kTO(Iwp.ctnAiniv*IwvL'kviflbMfT￡BKtMMMtd.itadral10elMdJiratbi/血44ranLnMElOL明o±l2.回收學生Mike權(quán)限。revoke'class_studentyf@'localhost'from'Mike'@'localhost';stwflWOSm，MiSe，f，i?]lwn，.GT4M15rwlik-tflM-Llhflll眼IVTliAHPS皿■ltaf?jmLhsr1FO"lii1*11ftWMl)ifK|PH驗證權(quán)限分配的正確性。在用戶創(chuàng)建時，要執(zhí)行命令flushprivileges;1.以Jane登錄數(shù)據(jù)庫，驗證權(quán)限。if ~uJjirv-pEi.i?r[usikfI:ee??l?lraw>tB111-■mLknrI'onudE.ulvi<lh:err"ij.Eajt 穌Lc-vuiKtinidl!L4SFJYirvarciM.:1.0.3*HirStf.tp&arvrr~CM.fcfiTFL^iE上R2Mt.3K0,Mj:1*bfbIarlelj^flLiJkica.JlL3ri[biiiehtukLNxXI■土