Ulawulo lokufikelela kwiBasebenzisi kunye neMigangatho kwi-SQL

Ukhuseleko lubaluleke kakhulu kubalawuli beenkcukacha ezifuna ukukhusela iigigabyte zedatha yebalulekileyo ebalulekileyo kwizamehlo zokungabikho kwabangaphandle abangagunyaziwe kunye nabangaphakathi abazama ukugqithisa igunya labo. Zonke iinkqubo zolawulo lweenkcukacha zobudlelwane benkonzo zibonelela ngeendlela ezithile zokuphepha zangaphakathi ezilungiselelwe ukunciphisa le nkalo. Ziquka ukukhuseleka kwephasiwedi elula eyenziwa yi- Microsoft Access kwisakhiwo esiyinkimbinkimbi yomsebenzisi / inxaxheba esekelwe yimiqulu yolwazi oluphambili njenge-Oracle kunye ne- Microsoft SQL Server. Eli nqaku lijolise kwiindlela zokukhusela eziqhelekileyo kuzo zonke iinkcukacha ezizalisekisa uLwimi oluCwangcisiweyo lweSicelo (okanye iSQL ). Sonke, siya kuhamba kwinkqubo yokuqinisa ulawulo lokufikelela kwedatha nokuqinisekisa ukhuseleko lweedatha yakho.

Abasebenzisi

I-database-based database yolonke inkxaso nenkcazelo yomsebenzisi ofana neleyo esetyenziswa kwiinkqubo zokusebenza zekhompyutha. Ukuba uqhelana nomsebenzi womsebenzi weqela lomsebenzisi / we-Microsoft Windows NT kunye neWindows 2000, uya kufumana ukuba amaqela omsebenzisi / indima asekelwa yiSQL Server kunye ne-Oracle afana kakhulu.

Kunconywa kakhulu ukuba udale i-akhawunti yomsebenzisi ngamnye wedatha yomntu ngamnye oza kufumana i-database yakho. Kukwazi ukukwazi ukwabelana ngeakhawunti phakathi kwabasebenzisi okanye sebenzisa kuphela i-akhawunti yomsebenzisi ngamnye kumsebenzisi ngamnye ofuna ukufikelela kwisiseko sakho sedata, kodwa ndiyidambisa kakhulu le ndlela ngenxa yezizathu ezibini. Okokuqala, kuya kuphelisa ukuphendulwa komntu ngamnye-ukuba umsebenzisi wenza utshintsho kwi-database yakho (masithi ngokuzinikela i-$ 5,000 ukuphakamisa), awuyi kukwazi ukuyibuyisela kumntu othile ngokusetyenziswa kweengxelo zokuphicotha. Ukongezelela, ukuba umsebenzisi othile ushiya intlangano yakho kwaye unqwenela ukususa ukufikelela kwakhe kwiziko ledatha, uya kunyanzelwa ukuba utshintshe iphasiwedi bonke abasebenzisi abathembele kuyo.

Iindlela zokudala i-akhawunti yomsebenzisi ziyahluka ukusuka kwipulatifomu ukuya kwipulatifomu kwaye kufuneka uqhagamshelane namaxwebhu akho athileyo e-DBMS enkqubo ngqo. Abasebenzisi beMicrosoft SQL Server kufuneka baphonononge ukusetyenziswa kwenkqubo yokugcinwa kwe-sp_adduser. Abalawuli beenkcukacha ze-Oracle baya kufumana i-CREATE umyalelo we-USER uncedo. Nokuba ufuna ukuphanda ezinye iindlela zokuqinisekisa. Umzekelo, iMicrosoft SQL Server isekela ukusetyenziswa kwe-Windows NT Integrated Security. Ngaphantsi kwesi sicwangciso, abasebenzisi bachongwa kwiziko ledatha zabo ze-Windows NT kwaye abafunwa ukuba bangenise i-ID yomsebenzisi obongezelelweyo kunye nephasiwedi ukufikelela kwisiseko sedatha. Le ndlela ithandwa kakhulu phakathi kwabaphathi beenkcukacha kuba iguqulela umthwalo wokulawula i-akhawunti kubasebenzi bokulawula inethiwekhi kwaye inika ithuba lokungena komntu omnye kumsebenzisi wokugqibela.

Imisebenzi

Ukuba uhlala kwindawo enomncinci wabasebenzisi, mhlawumbi ufumanisa ukuba ukudala i-akhawunti yomsebenzisi nokunikezela iimvume ngokuzenzekelayo kubo kwanele iimfuno zakho. Nangona kunjalo, ukuba unabasebenzisi abaninzi, uya kuphazamiseka ngumthwalo wokugcina iakhawunti kunye neemvume ezifanelekileyo. Ukuphucula lo mthwalo, i-database yolwazi isekela ingcamango yendima. Iindima zedatha yeenkcukacha zisebenza ngokufanayo kwiqela leWindows NT. Iingxelo zomsebenzisi zinikezelwa kwiindima kunye neemvume zinikezelwa kwinxaxheba ngokubanzi kunokuba i-akhawunti yomsebenzisi ngamnye. Ngokomzekelo, singenza indima ye-DBA uze udibanise ii-akhawunti zomsebenzisi zabasebenzi bethu bolawulo kule nxaxheba. Emva kokuba senze oku, sinokwabela imvume ethile kubo bonke abakhoyo (kunye nabazayo) ngabalawuli ngokunikezela imvume yendima. Kwakhona, iinkqubo zokudala iindima ziyahluka ukusuka kwipulatifomu ukuya kwiqonga. Abalawuli be-MS SQL Server kufuneka baphononise inkqubo yokugcinwa kwe-sp_addrole ngelixa i-Oracle DBAs ifuna ukusebenzisa i-syntax YOKUKHALA.

Ukunika imvume

Ngoku ukuba sithenge abasebenzisi kwisiseko sethu, sekuyisikhathi sokuqala ukuqinisa ukhuseleko ngokufaka iimvume. Isinyathelo sethu sokuqala siya kuba neemvume ezifanelekileyo zolwazi kubasebenzisi bethu. Siza kufeza oku ngokusebenzisa ukusetyenziswa kwe-SQL GRANT statement.

Nantsi i-syntax yesitatimende:

UKUQALA
[KUNYE ]
KU
[NGOKUKHONA UKUQALA]

Ngoku, makhe sijonge kule ngxelo yomgca-ngomgca. Umgca wokuqala, I-GRANT

Umgca wesibini,

, isetyenziswe ukucacisa itheyibhile echaphazelekayo kwiimvume zamanqanaba etafile. Lo mgca awushiyiweyo ukuba sinikezela imvume ye-database-level. Umgca wesithathu uchaza umsebenzisi okanye indima enikwa imvume.

Ekugqibeleni, umgca wesine, NGOKUKHONA UKUQALA, kukhetha. Ukuba lo mgca ufakwe kwisitatimenti, umsebenzisi ochaphazelekayo uvumelekile ukuba anikwe ezi mvume ezifanayo kwabanye abasebenzisi. Qaphela ukuba I-OPTION YOKUQALA ayinakucaciswa xa iimvume zinikwe indima.

U mzekelo

Makhe sibone imizekelo embalwa. Kwiimeko zethu zokuqala, sandule ukuqesha iqela labangama-42 abasebenzisi bokungenisa idatha abaza kubongeza nokugcina iirekodi zabathengi. Bakudinga ukufikelela kwiinkcukacha kwitafile zabathengi, ukuguqula olu lwazi kwaye ungeze iirekhodi ezintsha kwitafile. Akufanele bakwazi ukususa ngokupheleleyo irekhodi kwiziko. Okokuqala, simele senze i-akhawunti yomsebenzisi kumsebenzisi ngamnye kwaye sibongezelele bonke kwinxaxheba entsha, iDathaEntry. Emva koko, simele sisebenzise le SQL isitatimende esilandelayo ukuwanika iimvume ezifanelekileyo:

UKUHLOLA, UKUQALA, NGAPHAMBILI
KWABASEBENZI
TO DataEntry

Kwaye konke oku kukho! Ngoku makhe sihlolisise imeko apho sinikezela iimvume zamanqanaba egciniweyo. Sifuna ukuvumela amalungu endima ye-DBA ukuba afake amatafula amatsha kwiziko lethu. Ngaphezu koko, sifuna ukuba bakwazi ukunika abanye abasebenzisi imvume yokwenza okufanayo. Nantsi isitatimende seSQL:

UKUQALA UKUHLA ITHEBHU
DBA
UKUQHUBEKA

Qaphela ukuba sifake umgca we-OPANTION line ukuqinisekisa ukuba i-DBAs yethu inokubanika le mvume kwabanye abasebenzisi.

Ukususwa kwemvume

Emva kokuba sinikeze imvume, kudla ukuba kubalulekile ukuba baxoshe ngomhla ozayo. Ngethamsanqa, i-SQL isinikela ngomyalelo we-REVOKE ukususa iimvume ezinikezelwe ngaphambili. Nantsi i syntax:

HLAWULA [UKUQALA UKUQALA]

KUSUKA

Uya kubona ukuba i-syntax yalo myalelo ifana neyomyalelo we-GRANT. Ukwahlukana kuphela kukuthi UKUQALA OKUQHELEKILEYO kukhankanyiweyo kumgca we-REVOKE umgca esikhundleni sokuphela komyalelo. Njengomzekelo, makhe sicinge ukuba sifuna ukuxosha uMariya ovunyelwe ngaphambili ukuba asuse iirekhodi kwiinkcukacha zabathengi. Sisebenzisa umyalelo olandelayo:

HLAWULA
KWABASEBENZI
KUSUKA ku Mariya

Kwaye konke oku kukho! Kukho indlela enye eyongezelelweyo exhaswa yi-Microsoft SQL Server efanelekileyo ukubhala-umyalelo we-DENY. Lo myalelo ungasetyenziselwa ukukhanyela ngokucacileyo imvume kumsebenzisi onokuthi mhlawumbi abe nayo kubulungu bendima. Nantsi i syntax:

KU

U mzekelo

Ukubuyela kumzekelo wethu wangaphambili, makhe sicinge ukuba uMariya wayelungu legalelo labaphathi ababenokufikelela kwitafile zabathengi. Isitatimende sangaphambili se REVOKE asiyi kubonelela ukukhanyela ukufikelela kwayo kwitafile. Iyakususa imvume eyinikwe yona nge-COMPANT statement ejolise kwiakhawunti yakhe yomsebenzisi, kodwa ayiyi kuphazamisa iimvume ezifunyenwe ngobulungu bakhe kwiNdima yabaphathi. Nangona kunjalo, ukuba sisebenzisa isitatimende se-DENY siya kuvimba lakhe ilifa lemvume. Nantsi umyalelo:

KUKHULULEKA
KWABASEBENZI
UMariya

Umyalelo we-DENY udala "imvume engavumelekanga" kwiinkcukacha zokufikelela kwiinkcukacha. Ukuba kamva senza isigqibo sokunika uMariya imvume yokususa imigca kwietafile zabaxhasi, asikwazi ukusebenzisa nje umyalelo we-GRANT. Lo myalelo wawuza kufakwa ngokukhawuleza yi-DENY ekhoyo. Kunoko, sizakuqala ukusebenzisa umyalelo we-REVOKE ukususa ukungena ngemvume okungenanto ngolu hlobo lulandelayo:

HLAWULA
KWABASEBENZI
KUSUKA ku Mariya

Uya kuphawula ukuba lo myalelo ufana neleyo esetyenzisiweyo ukususa imvume efanelekileyo. Khumbula ukuba i-DENY kunye ne-GRANT imiyalelo yomsebenzi ibini ngendlela efanayo * mdash; bobabini benza iziqinisekiso (ezintle okanye ezimbi) kwindlela yokulawula yokufikelela kwedatha. Umyalelo WOKUBUKELA ususa zonke iimvume ezilungileyo nezimbi zomsebenzisi ochaziweyo. Xa lo myalelo ukhishwe, uMariya uya kukwazi ukucima imigca esuka kwitheyibhile ukuba unelungu leendima enayo loo mvume. Ngaphandle koko, umyalelo WOKUQALA unokukhutshwa ukubonelela ukususa imvume ngqo kwiakhawunti yakhe.

Kuyo yonke le nqaku, ufunde into enhle malunga neendlela zokulawula ukufikelela ezixhaswe yiLwimi yoLwimi oluPhezulu. Oku kungeniswa kufuneka kukubonelele ngendawo efanelekileyo yokuqala, kodwa ndikukhuthaza ukuba ubhekisele kumaphepha akho e-DBMS ukuze ufunde amanyathelo okhuseleko ancedisiweyo yinkqubo yakho. Uza kufumana ukuba iinkcukacha ezininzi zixhasa iinkqubo eziphezulu zokulawula ukufikelela, ezifana nokunika imvume kwiikholamu ezithile.