ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 12.04.2021
Просмотров: 1876
Скачиваний: 3
!
26
Пр
акт
ич
еск
ое
зан
ят
ие
4
Со
зда
ни
е
,
ис
по
лне
ни
е
и
отл
адк
а
хра
ни
мы
х
пр
оце
дур
и
тр
игг
еро
в
!
!
E-
:
mar
ket
@re
lex.r
u
ЗАО
execute direct "update SEQUENCES set CUR_VALUE = CUR_VALUE
+ “+itoa(change)+” where TAB_NAME = '"
Теперь
процедура
транслируется
удачно
,
о
чем
свидетельствует
исчезнувшее
окно
с
ошибками
.
Заметим
,
что
при
подаче
запроса
из
inl
мы
не
получим
полной
расшифровки
ошибок
,
только
общий
код
завершения
7200 –
ошибка
трансляции
хранимой
процедуры
.
Попробуйте
запустить
процедуру
с
разными
значениями
параметра
change.
Можно
запустить
процедуру
из
inl,
по
-
прежнему
указывая
всего
один
параметр
.
Второй
параметр
будет
обрабатываться
по
умолчанию
как
единица
.
4.3.
Обработка
ошибок
,
исключения
Наша
процедура
будет
некорректно
работать
,
если
в
качестве
значения
параметра
change
передать
значение
,
меньшее
единицы
,
или
NULL.
Мы
можем
вставить
соответствующую
проверку
в
начало
тела
процедуры
:
if change = NULL or change < 1 then
signal
INVPARAM
;
endif
Оператор
signal
вызывает
исключение
с
именем
INVPARAM
.
Это
исключение
необходимо
описать
в
секции
деклараций
процедуры
,
например
перед
ключевым
словом
code:
exception INVPARAM for BADPARAM;
Здесь
мы
назначили
исключению
имя
стандартного
и
критичного
исключения
BADPARAM,
т
.
е
.
оно
тут
же
завершает
исполнение
хранимой
процедуры
,
и
передает
состояние
исключения
.
В
качестве
результата
таким
образом
завершенной
процедуры
всегда
будет
NULL.
Попробуем
запустить
процедуру
и
указать
неправильное
значение
(
например
, 0)
в
качестве
параметра
change.
Отладчик
всегда
останавливается
в
строке
,
в
которой
произошло
исключение
.
Если
мы
запустим
процедуру
с
неверными
параметрами
из
inl,
то
тоже
получим
информацию
об
исключении
.
Запрос
execute,
завершившийся
с
исключением
,
всегда
имеет
код
возврата
7201.
Чтобы
обработать
исключение
внутри
процедуры
,
необходимо
использовать
блок
EXCEPTIONS,
как
было
рассмотрено
в
лекции
.
Например
,
если
присоединиться
к
БД
от
имени
пользователя
,
отличного
от
создателя
процедуры
,
то
при
попытке
выполнить
процедуру
(
ее
имя
в
запросе
execute,
кстати
,
должно
будет
уже
включать
имя
владельца
,
отделенной
точкой
от
собственно
имени
)
может
возникнуть
ошибка
доступа
к
таблице
SEQUENCES 2202 (
у
пользователя
скорее
всего
не
будет
таблицы
с
именем
SEQUENCES).
(
Замечание
:
конечно
,
мы
могли
бы
в
запросах
явно
написать
SYSTEM.SEQUENCES;
это
более
верно
,
но
тем
не
менее
,
может
возникнуть
ошибка
нарушения
прав
доступа
;
чтобы
процедура
реально
работала
для
нескольких
пользователей
,
обычно
удобно
создать
общие
(PUBLIC)
синонимы
на
используемые
таблицы
,
или
же
использовать
полное
имя
таблицы
,
и
в
любом
случае
надо
назначить
необходимые
права
grant execute on GENERATEPK to PUBLIC;
).
Если
мы
подадим
запрос
на
выполнение
SYSTEM.GENERATEPK
из
-
под
другого
пользователя
(
не
SYSTEM),
то
увидим
,
что
процедура
всегда
возвращает
1.
Чтобы
разобраться
,
в
чем
дело
,
можно
запустить
ожидание
процедуры
и
прийти
в
режим
ее
отладки
.
В
отладчике
мы
видим
,
что
при
попытке
исполнения
запросов
происходит
исключение
2202 (
нет
такой
таблицы
),
но
процедура
продолжает
исполняться
.
Это
происходит
из
-
за
того
,
что
исключение
некритичное
,
и
при
отсутствии
обработки
оно
просто
игнорируется
.
Значит
,
нам
необходимо
обработать
это
исключение
.
В
секции
определений
объявим
исключение
:
!
!
Пр
акт
ич
еск
ое
зан
ят
ие
4
Со
зда
ни
е
,
ис
по
лне
ни
е
и
отл
адк
а
хра
ни
мы
х
пр
оце
дур
и
тр
игг
еро
в
!
27
E-
:
mar
ket
@re
lex.r
u
ЗАО
exception NOTAB for 2202;
А
в
конец
процедуры
(
перед
end)
добавим
блок
обработки
исключения
:
exceptions
when NOTAB then
resignal;
Оператор
resignal
завершает
процедуру
и
передает
состояние
исключения
на
уровень
выше
.
Теперь
наша
процедура
корректно
реагирует
на
ошибку
2202
исполнения
запросов
.
4.4.
Упражнение
:
создание
еще
одной
процедуры
Заметим
,
что
в
нашей
задаче
с
генерациями
уникальных
значений
для
каждой
таблицы
не
учитывается
ситуация
,
когда
некоторая
таблица
будет
удалена
,
а
в
последствии
создана
другая
таблица
с
таким
же
именем
.
По
-
хорошему
,
надо
при
удалении
таблицы
удалять
и
соответствующую
запись
из
таблицы
SEQUENCE.
Это
логику
удобно
вынести
в
отдельную
хранимую
процедуру
,
назовем
ее
DropTabWithSequence.
Процедура
должна
сначала
удалить
таблицу
,
а
затем
,
в
случае
успеха
,
удалить
соответствующую
запись
из
таблицы
SEQUENCES.
spman
предоставляет
специальный
интерфейс
для
создания
процедур
:
по
сути
он
просто
подготовит
запрос
create procedure
для
Вас
.
Используйте
пункт
«
Новая
процедура
»
меню
«
Файл
».
Предупреждение
:
в
диалоге
создания
процедуры
регистр
символов
имени
имеет
значение
,
так
что
если
Вы
не
хотите
потом
точно
повторять
имя
процедуры
и
заключать
его
в
кавычки
,
имя
процедуры
надо
набрать
в
верхнем
регистре
.
В
результате
получаем
окно
с
шаблоном
текста
процедуры
.
Надо
дописать
входной
параметр
(
имя
таблицы
)
и
тело
процедуры
(
указание
:
для
проверки
любого
ненормального
кода
завершения
запроса
drop table
удобно
использовать
стандартную
функцию
errcode).
Примечание
:
Студенты
пытаются
написать
процедуру
сами
…
(
процедура
должна
иметь
примерно
такой
вид
:
create procedure "DROPTABWITHSEQUENCE"(in tabname char(64))
for debug
code
execute direct "drop table "+tabname+";";
if errcode() = 0 then
execute direct "delete from SEQUENCES where
tab_name='"+tabname+"';";
commit;
endif
end;
)
4.5.
Создание
триггера
Рассмотрим
пример
триггера
,
который
использует
созданную
нами
ранее
хранимую
процедуру
для
генерации
значений
первичного
ключа
при
вставке
записей
в
некоторую
таблицу
OBJECT:
create table object(id int primary key, value varchar(64));
Триггер
позволяет
принудительно
установить
значение
поля
ID
новой
записи
,
какое
бы
значение
не
задал
пользователь
в
исходном
запросе
:
create trigger INS_OBJECT before insert on object
!
28
Пр
акт
ич
еск
ое
зан
ят
ие
4
Со
зда
ни
е
,
ис
по
лне
ни
е
и
отл
адк
а
хра
ни
мы
х
пр
оце
дур
и
тр
игг
еро
в
!
!
E-
:
mar
ket
@re
lex.r
u
ЗАО
for each row execute for debug
code
call GeneratePk("OBJECT") into new.id; //
end;
Соответствующие
SQL-
операторы
содержатся
в
файле
trig_obj.sql.
Его
можно
исполнить
обычным
способом
,
через
inl.
Теперь
попробуйте
добавить
несколько
записей
в
таблицу
object.
Потом
подать
SELECT
из
этой
таблицы
–
мы
видим
сгенерированные
значения
колонки
ID.
Это
иллюстрация
только
одной
области
применения
триггеров
.
Допустим
теперь
,
что
мы
хотим
хранить
информацию
обо
всех
изменениях
,
сделанных
в
таблице
OBJECT
в
разные
моменты
времени
.
Для
этого
создадим
еще
одну
таблицу
:
create table object_history(id int, dt date, value
varchar(64), status char(1));
alter table object_history add primary key(id, dt);
Дополнительными
атрибутами
здесь
являются
dt –
дата
изменения
,
и
status –
статус
изменения
–
одна
из
букв
‘I’, ‘U’
или
‘D’.
Чтобы
обеспечить
занесение
в
эту
таблицу
записей
после
успешного
изменения
таблицы
OBJECT,
можно
создать
триггеры
на
различные
виды
DML-
запросов
аналогично
следующему
:
create trigger INS_OBJECT_HIST after insert on object
for each row execute for debug
code
execute direct
"insert into OBJECT_HISTORY(id, dt, value, status)
values(“ +
itoa(new.id) + “, sysdate, '” + new.value + “',
'I');"; //
end;
В
файле
obj_hist.sql
содержатся
запросы
на
создание
таблицы
OBJECT_HISTORY
и
двух
триггеров
на
добавление
и
на
изменение
данных
.
Прогоните
этот
файл
в
inl
и
попробуйте
добавить
и
обновить
несколько
записей
в
таблице
OBJECT.
Затем
сделайте
SELECT
из
таблицы
OBJECT_HISTORY.
Вы
должны
увидеть
историю
сделанных
изменений
.
Теперь
не
хватает
только
триггера
на
DELETE.
Создайте
его
самостоятельно
.
В
качестве
упражнения
используем
для
этого
утилиту
spman.
Утилита
spman
включает
диалоговое
окно
для
упрощения
создания
триггера
,
также
как
и
для
хранимых
процедур
.
Выберите
пункт
«
Новый
триггер
»
в
меню
«
Файл
».
Введите
все
характеристики
триггера
в
диалоговом
окне
.
У
вас
должно
открыться
окно
с
шаблоном
нового
триггера
,
примерно
такого
содержания
:
create trigger "DEL_OBJECT_HIST"
after delete on OBJECT for each row
execute for debug
!
!
Пр
акт
ич
еск
ое
зан
ят
ие
4
Со
зда
ни
е
,
ис
по
лне
ни
е
и
отл
адк
а
хра
ни
мы
х
пр
оце
дур
и
тр
игг
еро
в
!
29
E-
:
mar
ket
@re
lex.r
u
ЗАО
code
end;
Вставьте
соответствующий
оператор
между
code
и
end
и
сохраните
триггер
(
как
обычно
,
командой
сохранения
или
клавишей
<F2>).
При
сохранении
триггера
могут
возникнуть
ошибки
компиляции
,
которые
обрабатываются
так
же
,
как
и
для
хранимых
процедур
.
4.6.
Модификация
триггера
Для
модификации
триггера
необходимо
удалить
старый
триггер
(
запрос
drop trigger),
и
затем
пересоздать
новый
. spman
автоматически
делает
эти
операции
при
необходимости
.
Модифицируем
наш
самый
первый
триггер
(INS_OBJECT)
так
,
чтобы
он
запрещал
добавление
в
таблицу
OBJECTS
строк
,
у
которых
значение
value
равно
NULL (
простейшая
проверка
,
которая
может
быть
выполнена
и
декларативным
способом
,
но
потенциально
триггер
может
реализовать
любой
алгоритм
проверки
).
Выбираем
в
spman
команду
«
Открыть
триггер
»
меню
«
Файл
».
В
результате
откроется
окно
существующего
триггера
.
В
начало
тела
триггера
добавим
проверку
:
if new.value = NULL then
return false;
endif
Возврат
false
из
триггера
означает
запрет
текущей
операции
.
Сохраняем
триггер
,
и
пробуем
теперь
выполнить
в
inl
такой
запрос
:
insert into object(id) values(0);
Получаем
сообщение
о
том
,
что
добавлено
0
строк
–
это
результат
работы
триггера
.
4.7.
Отладка
триггера
Триггер
можно
отлаживать
точно
так
же
,
как
и
хранимую
процедуру
.
Отличие
в
том
,
что
можно
отладить
триггер
,
только
ожидая
его
инициирования
внешним
запросом
.
Откроем
в
spman
триггер
INS_OBJECT.
Включим
отладочную
сессию
(
клавиша
<F4>)
и
режим
ожидания
(
клавиши
<Alt>-<F9>).
Теперь
перейдем
в
inl
и
подадим
запрос
:
insert into object(id) values(0);
В
результате
попадаем
в
отладку
триггера
.
Можно
отследить
по
шагам
,
что
происходит
внутри
него
.
Повторим
то
же
самое
для
другого
,
допустимого
запроса
.
Видно
,
что
триггер
выполняется
по
другой
ветке
.
В
частности
,
можно
войти
внутрь
вызываемой
из
триггера
процедуры
.
!
!
!
30
E-mail: market@relex.ru
ЗАО
НПП
«
РЕЛЭКС
»
http://www.relex.ru
Практическое
занятие
5
Разграничение
доступа
в
СУБД
ЛИНТЕР
.
Дискреционный
доступ
Практика
2
часа
(
Лекция
5)
Целью
занятия
является
освоение
способов
разграничения
доступа
к
базе
данных
на
основе
дискреционного
доступа
.
При
выполнении
задания
будет
использовано
приложение
«
Интерактивный
SQL» (INL).
5.1.
Создание
нового
пользователя
.
Полномочия
пользователей
Различные
пользователи
являются
владельцами
данных
,
которые
находятся
в
созданных
ими
таблицах
.
Рассмотрим
на
следующей
задаче
.
Задача
:
Создать
двух
пользователей
A
и
B,
создать
от
имени
пользователя
A
таблицу
для
хранения
телефонной
книги
,
предоставить
пользователю
B
доступ
для
чтения
и
добавления
данных
.
Решение
:
Запускаем
утилиту
INL,
вводим
имя
и
пароль
администратора
базы
данных
или
пользователя
обладающего
привилегией
администратора
базы
данных
(DBA).
1.
Создаем
пользователей
A
и
B
с
паролями
‘1’
и
’2’:
create user A identified by ‘1’;
create user B identified by ‘2’;
2.
Даем
пользователю
A
привилегию
RESOURCE:
grant resource to A;
3.
Выходим
из
утилиты
INL
командой
exit
и
запускаем
утилиту
повторно
с
именем
пользователя
‘A’
и
паролем
‘1':
4.
Создаем
таблицу
для
хранения
телефонов
:
create table PHONES(ID integer autoinc,NAME char(20), PHONE char(12));
5.
Передаем
привилегии
на
чтение
и
вставку
пользователю
“B’:
grant select,insert on PHONES to B;
6.
Выходим
из
утилиты
INL
командой
exit
и
запускаем
утилиту
повторно
с
именем
пользователя
‘B’
и
паролем
‘2':
7.
Осуществляем
вставку
данных
:
insert into A.PHONES(NAME,PHONE) values(‘
Иванов
’,’8-095-167-34-80’);
8.
Осуществляем
выборку
данных
:
select * from A.PHONES;
Задачи
для
самостоятельной
работы
:
1.
Создать
пользователя
с
привилегией
DBA,
создать
от
имени
этого
пользователя
таблицы
для
хранения
почтовых
адресов
предприятий
(
название
,
почтовый
индекс
,
город
,
улица
,
дом
,
а
/
я
).
Созданным
ранее
пользователям
A,B
предоставить
возможность
заполнения
,
модификации
,
удаления
и
просмотра
данных
из
этой
таблицы
.
Всем
остальным
пользователям
предоставить
возможность
только
чтения
данных
из
созданной
таблицы
.
5.2.
Использование
представлений
для
разграничения
доступа
Для
«
вертикального
»
разграничения
доступа
к
таблице
используются
представления
.
Рассмотрим
это
на
следующей
задаче
.