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

121

УП Р АЖ НЕ НИ Я

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

5.11. И зм енение значений в предст авл ения х

К ак ужегов орилось, исп ольз ов аниекоманд мод ифика ции яз ыка SQL –

INSERT (ВСТАВИТЬ), UPDATE (ЗАМЕНИТЬ), и DELETE (УДАЛИТЬ) –

п рименительно д ляп ред ста в лений имеет ряд особенностей. В д оп олнениек асп ектам, рассмотренным в ыше, след ует отметить, ч то нев сеп ред став ления могут мод ифициров аться.

Е сли кома нд ы мод ифика ции могут в ып олняться в п р ед ста в лении, то п ред став ление яв ляется обнов ляемым (мод ифицируемым); в п р отив ном случ а еоно п ред наз нач ено только д ля ч тения п ри з ап росе. К аким образ ом можно оп ред елить, яв ляетсяли п ред став лениемод ифицируемым? К р итерии обнов ляемости п ред став ленияможно сформулиров атьслед ующ имобра з ом.

∙ Пред став ление строится на основ е од ной и только од ной баз ов ой таблицы.

∙Пред став лениед олжно сод ержатьп ерв ич ный ключ баз ов ой та блицы.

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

∙Пред став лениенед олжно сод ержатьDISTINCT в св оемоп ред елении.

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

Н емод ифицируемыеп ред став ления, с д ругой стороны, п оз в оляют более

(нап ример, в объед инениях). Э ти п ред став лениямогут такжеиметьз на ч ение п ри решении з а д ач з ащ иты и без оп асности д анных. Н а п р имер, можно п ред остав итьнекоторым п ольз ов а телям в оз можностьп олуч ения агрегатных д анных (таких, ка к усред ненное з нач ение оценки студ ента), не п ока з ыв ая конкретных з нач ений оценок и, темболее, неп оз в оляяих мод ифициров ать.

5.12.П рим еры обновл я ем ы х и не обновл я ем ы х предст авл ений

Пример 1

CREATE VIEW DATEEXAM (EXAM_DATE, QUANTITY)

AS SELECT EXAM_DATE, COUNT (*)

FROM EXAM_MARKS

GROUP BY EXAM_DATE;

123

Д анноеп ред став лениеяв ляется не о бно вляем ы м из -з а п рисутств ия в немагрегир ующ ей функции и GROUP BY.

Пример 2

CREATE VIEW LCUSTT

AS SELECT *

FROM UNIVERSITY

WHERE CITY = ‘М о сква ’;

Э то – о бно вляем о е п ред став ление.

Пример 3

CREATE VIEW SSTUD (SURNAME1, NUMB, KUR) AS SELECT SURNAME, STUDENT_ID, KURS*2

FROM STUDENT

WHERE CITY = ‘М о сква ’;

Э то п ред став ление – не м о д и ф и ци р уем о е из -з а налич ия в ыражения “KURS*2”.

Пример 4

CREATE VIEW STUD3

AS SELECT * FROM STUDENT

WHERE STUDENT_ID IN

( SELECT MARK

FROM EXAM_MARKS

WHERE EXAM_DATE = ‘10/02/1999’);

Пред став лениене м о д и ф и ци р ует сяиз -з а п рисутств ияв немп од з ап роса . В некоторых п рогра мма х э то может бытьп р иемлемым.

Пример 5

CREATE VIEW SOMEMARK

AS SELECT STUDENT_ID, SUBJ_ID, MARK FROM EXAM_MARKS

WHERE EXAM_DATE IN (‘10/02/1999’, ‘10/06/1999’);

Э то – о бно вляем о е п ред став ление.

124

5.13.П редставл ения , базирую щ иеся на других предст авл ения х

которых оно оп ред елено, но нерасп ространяется на д ругиеп ред став ления, основ а нныена э томп ред став лении. Н а п р имер, в п ред ыд ущ емп римере

CREATE VIEW HIGH_RATING

AS SELECT UNIV_ID, RATING

FROM UNIVERSITY WHERE RATING >= 400

WITH CHECK OPTION;

п оп ытка в ста в итьили обнов итьз нач ения п оля RATING, отлич ные от 400, буд ет отв ергнута , п оскольку п рисутств ует указ а ниеWITH CHECK OPTION. О д нако, если соз д ается в торое п ред став ление (с тем же сод ержанием), основ а нноена п ерв ом:

CREATE VIEW MYRATING AS

SELECT *

FROM HIGH_RATING;

то в в од в п оле RATING с п омощ ью нижеп р ив ед енного з а п роса з нач ений, отлич а ющ ихся от 400, уже не буд ет отв ер гнуто как ошибоч ное. То есть след ующ ий з ап рос

UPDATE MYRATING SET RATING = 200

WHERE UNIV_ID = 18;

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

Пред ложение WITH CHECK OPTION п росто гарантирует, ч то любое

если эти п ред став лениянез ащ ищ ены п ред ложениями WITH CHECK OPTION, з ад а нными именно д ляних. Пред ложенияWITH CHECK OPTION п р ов еряют

125

п ред ика ты только того п ред став ления, в котором они сод ержатся. Пр и этом неяв ляется в ыход ом из п оложения и соз д аниеп ред став ленияMYRATING с п омощ ьюз а п роса

CREATE VIEW MYRATING AS

SELECT *

FROM HIGH_RATING

WITH CHECK OPTION;

УП Р АЖ НЕ НИ Я

в ключ а ющ ееп оляSTIPEND и STUDENT_ID и п оз в оляющ еев в од итьили из менятьз на ч ениеп оля STIPEND (стип енд ия), но только в п р ед ела х от

100 д о 200.

6.О пр еделен и епр а в до ступа по льзо ва телей кда н н ы м

6.1. П ол ь з оват ел и и привил егии

п ольз ов ателяд ляустанов ки и оп ред еленияего п ра в с точ ки з ренияд оступ а к д анным. К а жд а я п осла нная к СУ Б Д кома нд а SQL-з ап р оса ассоциируется СУ Б Д с ид ентифика торомд оступ а к д аннымконкретного п ольз ов ателя.

Польз ов ательоп ред еляетсяс п омощ ьюслед ующ ей кома нд ы.

CREATE USER <и м я_по льзо ва т еля> IDENTIFIED BY <па р о ль>

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

У д алениеп ольз ов ателяп роиз в од итсякоманд ой

DROP USER <и м я_по льзо ва т еля> .

Н аз нач аемыеп ольз ов ателю п рив илегии – это то, ч то оп р ед еляет, может ли указ анный п ольз ов ательв ып олнитьд а нную кома нд у над оп р ед еленным

127

6.2. С т андарт ны е привил егии

Прив илегии, оп ред еленныестанд артом SQL – это п рив илегии объекта. Э то оз нач ает, ч то п ольз ов ательимеет п рив илегию (п ра в о) на в ып олнение

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

К рометого, могут бытьнеста нд артныеп р ив илегии объекта, такие, как:

∙INDEX – п ольз ов ательимеетп р ав о соз д а в атьинд екс в та блице;

∙SYNONYM – п ольз ов а тельимеет п ра в о соз д ав атьсинонимд ляобъекта;

∙ALTER – п ольз ов ательимеет п рав о в ып олнятькоманд у ALTER TABLE в таблице;

∙EXECUTE – п оз в оляет в ып олнятьп роцед уру.

Наз нач ениеп ольз ов ателямэ тих п рив илегий осущ еств ляетсяс п омощ ью команд ы GRANT.

128

6.3. Ком анда GRANT

GRANT SELECT ON STUDENT TO IVANOV;

Теп ерьп ольз ов а тельс именем IVANOV может в ып олнятьSELECT-з а- п росы к таблицеSTUDENT. Б ез налич ияд ругих п р ив илегий он может только вы би р а т ь з нач ения, но неможет в ып олнятьлюбыед ейств ия, которыебы в оз д ейств ов а ли на з нач ения в та блице STUDENT, в ключ ая исп ольз ов ание таблицы STUDENT в кач еств ерод ительской таблицы в нешнего ключ а . К огд а

пред остав ленияп рив илегий).

Команд а

GRANT INSERT ON EXAM_MARKS TO IVANOV;

п ред остав ляет п ольз ов ателю IVANOV п рав о в в од итьв та блицу EXAM_MARKS нов ыестроки.

п ред остав ляются. Н ап ример :

GRANT SELECT, INSERT ON SUBJECT TO IVANOV, PETROV;

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

129

6.4. И спол ь з ование аргум ент ов ALL и PUBLIC

А ргумент ALL PRIVILEGES (в се п рив илегии) или п росто ALL исп ольз уетсяв место имен п рив илегий в кома нд еGRANT, ч тобы п ред оста в ить в сеп рив илегии в таблице. Н а п ример, кома нд а

GRANT ALL PRIVILEGES ON STUDENT TO IVANOV;

или болеекоротко

GRANT ALL ON STUDENT TO IVANOV;

п еред а ет п ольз ов ателюIVANOV в есь набор п рив илегий в таблицеSTUDENT.

А ргумент PUBLIC исп ольз уется д ля п ер ед ач и указ анных в команд е

п ольз ов ателя. Н а п ример, ч тобы п оз в олитьлюбому п ольз ов а телю п олуч ать информациюиз таблицы EXAM_MARKS, можно исп ольз ов а тькома нд у

GRANT SELECT ON EXAM_MARKS TO PUBLIC;

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

огранич ив ать) сод ержаниетаблицы, п оэтому ра зрешениев семп ольз ов а телям из менять сод ержание та блиц может в ыз в ать оп ред еленные п роблемы

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

инд ив ид уальнымп ольз ов ателям.

130

6.5. О т м ена привил егий

О тмена п р ив илегии осущ еств ляется с п омощ ью кома нд ы REVOKE, котора яимеет синтаксис, ана логич ный команд еGRANT.

Н а п ример, команд а

REVOKE INSERT ON STUDENT FROM PETROV;

6.6. И спол ь з ование предст авл ений дл я ф ил ь т рац ии привил егий