安全性语言实验

山西大学软件学院课程实验报告实验课程 数据库课程实验实验名称安全性语言实验实验时间第十三周3节到4节学生班级 学生学号 学生姓名 指导教师 2020年5月22日实验目的:掌握自主存取控制权限的定义和维护方法实验器材：计算机MySQLWorkbench实验内容:定义用户，角色，分配权限给用户，角色回收权限，以及相应的用户名登录数据库验证权限分配是否正确实验步骤：一.自主存取控制实验针对第一个实验的数据库，定义三种不同用户，分别实现不同功能。分别为班长Tom学生Mike，教务处主任Jane，教务处老师Kathy和辅导员David，授课教师Jiffy。创建用户为班长，教务处主任以及辅导员创建用户标识，要求具有创建用户或角色的权利。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为学生，教务处老师，授课教师创建用户标识和用户口令。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 |?创建角色并分配权限1.为班长，辅导员，教务处主任分别创建一个查询角色，并分配相应的查询权限。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：的■产的rlMi_*Etli!Luil•dHCklli&itGtAXT5B_oTTW1*网沔侦"皿■: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.为学生，教务处老师，授课教师创建角色，并合理赋予某项权限。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)班长，辅导员和教务处主任有权给其他人分配权限。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）给用户分配权限。1.给班长Tom,辅导员David，教务处主任Jane分配权限。

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分配权限。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*岫-回收用户权限或角色权限。1.回收授课教师权限。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权限。revoke'class_studentyf@'localhost'from'Mike'@'localhost';stwflWOSm，MiSe，f，i™]lwn，.GT4M15rwlik-tflM-Llhflll眼IVTliAHPS皿■ltaf・jmLhsr1FO"lii1*11ftWMl)ifK|PH验证权限分配的正确性。在用户创建时，要执行命令flushprivileges;1.以Jane登录数据库，验证权限。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■土