SQL в вопросах и задачах

REFERENCES никогд а неисп ольз уетсяп ри соз д а нии п ред ста в лений.

6.6.1.О граничение привил егии SELECT дл я определ енны х ст ол бц ов

CREATE VIEW STUDENT_VIEW AS SELECT STUDENT_ID, SURNAME FROM STUDENT;

и п ред остав ить п ольз ов ателю PETROV п рив илегию SELECT в соз д анном п ред став лении, а нев са мой таблицеSTUDENT:

GRANT SELECT ON STUDENT_VIEW TO PETROV;

Д ля столбцов можно соз д атьраз лич ные п рив илегии, од нако, след ует иметьв в ид у, ч то д лякома нд ы INSERT это буд ет оз нач атьв став ку з нач ений п о умолч анию, а д лякоманд ы DELETE огра нич ениестолбца небуд ет в ообщ е иметьз на ч ения.

6.6.2. О граничение привил егий дл я определ енны х ст рок

CREATE VIEW MOSC_UNIVERSITY AS

SELECT * FROM UNIVERSITY

WHERE CITY = ‘М о сква ’

WITH CHECK OPTION;

6.6.3.П редост авл ение дост упа т ол ь кок из вл еченны м данны м

Д ругаяв оз можностьсостоит в том, ч тобы устана в лив атьп ольз ов а телям п рив илегии на д оступ к уже из в леч енным д анным, а не к з на ч ениям таблице. Д ляэ того уд обно исп ольз ов а тьа грегирующ иефункции. Н а п ример, соз д а д им п ред став ление, которое д ает информацию о колич еств е оценок, сред неми общ емба лла х д лястуд ентов на кажд ый д ень:

CREATE VIEW DATETOTALS AS

SELECT EXAM_DATE, COUNT (*) AS KOL, SUM (MARK) AS SUMMA, AVG (MARK) AS TOT

FROM EXAM_MARKS GROUP BY EXAM_DATE;

Теп ерьможно п еред атьп ольз ов а телю PETROV п рив илегию SELECT в соз д а нномп ред ста в лении DATETOTALS с п омощ ьюз а п роса :

GRANT SELECT ON DATETOTALS TO PETROV;

133

6.6.4.И спол ь з ование предст авл ений в качест ве ал ь т ернат ивы ограничения м

Пред став ленияс WITH CHECK OPTION могут исп ольз ов атьсяв кач еств е альтернатив ы огра нич ениям. Н а п р имер, необход имо уд остов ериться, ч то в се з нач ения п оля CITY в та блице STUDENT ра в ны наз в аниям конкретных город ов . Д ляэтого можно уста нов итьогра нич ениеCHECK неп осред ств енно на столбец CITY. О д нако п оз жеего из менениебуд ет з атр уд нено. В кач еств е альтернатив ы можно соз д атьп ред став ление, исключ ающ ее неп ра в ильные з нач енияCITY:

CREATE VIEW CURCITYES AS SELECT *

FROM STUDENT

WHERE CITY IN (‘М о сква ’, ‘Во р о н еж ’)

WITH CHECK OPTION;

Теп ерь, в место того, ч тобы п ред остав лятьп ольз ов ателям п рив илегии

п ред став ление, инач еего собств енныекоманд ы такженебуд ут п риняты.

6.7. Д ругие т ипы привил егий

Д о сих п ор не рассмотрены в оп росы уста нов ки целого ряд а д ругих п рив илегий, а именно:

∙К то имеет п р ав о соз д а в атьта блицы?

∙К то имеет п р ав о из менять, уд алять, или огранич ив а тьтаблицы?

в сегд а имеютсянекоторыетип ы суп ер п ольз ов а телей, то естьп ольз ов ателей, которыеимеют большинств о или в сеп рив илегии, и которыемогут п еред ать св ой статус суп ерп ольз ов ателя кому-либо с п омощ ью п рив илегии или

6.8. Т ипичны е привил егии сист ем ы

При общ емп од ход еимеетсятри ба з ов ых п рив илегии системы:

∙CONNECT (Подключить),

∙RESOURCE (Ресурс) и

∙DBA (Администратор Базы Данных).

Прив илегия CONNECT состоит из п ра в а з а регистриров а ться и п рав а соз д а в атьп ред став ленияи синонимы, если п еред аны п р ив илегии объекта.

Прив илегияRESOURCE состоит из п ра в а соз д ав атьбаз ов ыетаблицы.

Прив илегияDBA – это п рив илегияад министратора баз ы д анных, то есть суп ер п ольз ов ателя, которому п ред остав ляются самые в ысокие п олномоч ия п ри ра ботес баз ой д анных. Э ту п рив илегию может иметьод ин или более п ольз ов ателей с функциями а д министра тора баз ы д анных. К ома нд а GRANT (в из мененной форме) может п р именятьсякак с п р ив илегиями объекта, так и с системными п рив илегиями.

135

6.9. С оз дание и удал ение пол ь з оват ел ей

CONNECT. В этом случ а е обыч но д обав ляется п ред ложение IDENTIFIED BY, указ ыв а ющ ееп ар ольд ляэтого п ольз ов ателя. Н ап р имер, кома нд а

GRANT CONNECT TO PETROV IDENTIFIED BY ‘PETROVPASSWORD’;

п рив ед ет к соз д аниюп ольз ов ателяс именемPETROV, п ред остав ит ему п рав о

К огд а п ольз ов атель A п ред остав ляет п рив илегию CONNECT д ругому п ольз ов ателюB, гов орят, ч то п ольз ов а тельA “со зд а ет ” п ольз ов ателяB. При этом п ольз ов атель A обяз ательно д олжен иметь п р ив илегию DBA. Е сли

RESOURCE. Н о п ри э томв оз никает д ругаяп роблема . Пр и п оп ыткеуд а ления п ольз ов ателем A п р ив илегии CONNECT п ольз ов ателя B, который ужеимеет соз д а нныеим таблицы, эта кома нд а уд а ления п рив илегии буд ет отклонена ,

соз д а нные этим п ольз ов ателем таблицы. Прив илегию RESOURCE уд а лять отд ельно не требуется, д остаточ но уд алить CONNECT, ч тобы уд а лить п ольз ов ателя.

УП Р АЖ НЕ НИ Я

136

п ольз ов ателямп рав а на осущ еств лениез ап росов к та блицеEXAM_MARKS.

118. О тменитеп рив илегию INSERT п о отношению к таблицеSTUDENT у

119. Перед айтеп ольз ов ателю SIDOROV п рав о в ып олнятьоп ерации в став ки или обнов ления д ля таблицы UNIVERSITY, но только д ля з ап исей об унив ерситетах, з нач ениярейтингов которых лежат в д иа п аз онеот 300 д о

400.

120. Ра з решите п ольз ов ателю PETROV д ела ть з а п росы к та блице EXAM_MARKS, но з ап ретитеему из менятьв этой та блицез нач енияоценок студ ентам, имеющ имнеуд ов летв орительные(=2) оценки.

6.10. С оз дание синоним ов (SYNONYM)

система несможет оп ред елитьместона хожд ениетаблицы, так ка к у раз ных п ольз ов ателей могут оказ а ться таблицы с од инаков ыми именами. Исп ольз ов а ние д линных имен с п рефикса ми может ока з аться неуд обным. Поэтому большинств о реа лиз а ций SQL п оз в оляют соз д ав а ть д ля таблиц синонимы (ч то неяв ляетсяста нд артомANSI). Си но ни м – это альтернатив ное

таблицы д лянего отп а д ает. Польз ов а тельимеет п ра в о соз д ав атьсинонимд ля таблицы, если он имеет, п о кра йней мере, од ну п рив илегию в од ном или болеестолбцах этой та блицы.

С п омощ ью кома нд ы CREATE SYNONYM п ольз ов ательIVANOV может д ля таблицы с именем PETROV.STUDENT соз д а ть синоним с именем CLIENTS след ующ имобраз ом.

CREATE SYNONYM CLIENTS FOR PETROV.STUDENT;

137

Теп ерьп ольз ов ательIVANOV может исп ольз ов атьтаблицу с именем CLIENTS в кома нд еточ но та к же, как имяPETROV.STUDENT.

п ольз ов ателя могут соз д а в ать д в е п олностью нез а в исимые таблицы с од инаков ыми именами, но это та кжеоз нач ает, ч то од ин п ольз ов ательможет соз д а тьп ред став ление, основ а нноена имени, стоящ емп ослеимени та блицы, и исп ольз уемымд ругимп ольз ов ателем. Э то иногд а д елаетсяв случ аях, когд а п ред став лениеисп ольз уетсякак з амена самой исход ной таблицы, на п ример,

138

6.11.С иноним ы общ егопол ь з ования

(PUBLIC)

од ного и того же имени. Э то д аст в оз можность, на п ример, исп ольз ов ать

п ольз ов ателями с п рив илегиями ад министратора баз ы д анных (п ольз ов а тель

д оступ на. Д ажеесли имяяв ляетсяобщ им, сама таблица общ ей неяв ляется.

6.12. Удал ение синоним ов

О бщ иеи д ругиесинонимы могут уд алятьсякома нд ой DROP SYNONYM. Синонимы могут уд алятьсятолько их в лад ельца ми, кр омеобщ их синонимов ,

DROP SYNONYM CLIENTS;

139

7. Упр а влен и етр а н за кци ями

В п роцессев ып олненияп ослед ов ательности команд SQL таблицы баз ы

целостности д анных в баз е. Д ляобесп еч енияцелостности д анных логич ески

п ред шеств ующ ий нач а лу транз а кции. Э то наз ыв а ют о т ка т о м транз а кции.

нез а в ершенной транз акции, в сегд а буд ут а в томатич ески “откатаны” обра тно.

Имеется в оз можностьуста нов ки режима AUTOCOMMIT а в томатич ески п ри р егистрации. Е сли сеанс п ольз ов а теляз ав ерша етсяав а рийно, на п ример, п роиз ошел сбой системы или в ып олнена п ер ез агруз ка п ольз ов ателя, то текущ ая транз акция в ып олнит а в томатич еский откат из менений. Э то – од на из в оз можностей уп ра в ления в ып олнением д иа логов ой обработки з ап росов п утем ра з д еления кома нд на большое колич еств о ра з лич ных тр анз акций. О д иноч ная транз а кция не д олжна сод ержатьслишком много несв яз анных команд , на п рактикеона ч а сто состоит из ед инств енной команд ы. Х орошее п рав ило, которому можно след ов ать– это соз д ав атьтранз а кции из од ной команд ы или нескольких близ ко св яз анных команд .

Н а п ример, требуетсяуд а литьсв ед енияо студ ентеп о фамилии ‘Ива н о в’ из баз ы д анных. Прежд е, ч емсв ед енияиз таблицы STUDENT буд ут уд а лены,

140

требуетсяосущ еств итьоп ред еленныед ейств ияс д анными об этомстуд ентев д ругих таблица х, в ч астности с д а нными о его оценках. Н еобход имо уста нов итьсоотв етств ующ ее этому студ енту п оле STUDENT_ID в та блице EXAM_MARKS в NULL. Послеэтого можно уд алитьз ап исьоб этом студ енте из таблицы STUDENT. Э ти д ейств ияв ып олняютсяс п омощ ьюд в ух з ап р осов

UPDATE EXAM_MARKS

SET STUDENT_ID = NULL

WHERE STUDENT_ID = 1004;

DELETE FROM STUDENT

WHERE STUDENT_ID = 1004;

Е сли в оз никает п роблема с уд а лением з ап иси о студ ентес фа милией ‘Ива н о в’ (в оз можно, имеетсяд ругой в нешний ключ , ссылающ ийсяна него, о котором небыло из в естно, и, соотв етств енно, неуч тено п ри уд алении), можно было бы отменитьв сесд ела нныеиз менения, п о кра йней мере, д о тех п ор, п ока п роблема небуд ет решена . Д ляэ того п рив ед еннуюгр уп п у команд след ует обрабатыв ать как од иноч ную тра нз акцию, п ред усматрив ая ее з ав ершениес п омощ ью команд ы COMMIT или ROLLBACK – в з ав исимости от рез ультата.

УП Р АЖ НЕ НИ Я

121. Вы п еред али п ра в о SELECT в таблице EXAM_MARKS п ольз ов ателю IVANOV. Вв ед итекома нд у так, ч тобы в ы могли ссылатьсяк э той таблице, как к EXAM_MARKS, неисп ольз уяимяIVANOV в кач еств еп рефикса .

122. Е сли п роиз ойд ет сбой п итания, ч то случ ится со в семи из менениями, сд еланными в о в ремятекущ ей тра нз акции?