LAB19 (Задания по бд), страница 4

Отчет будет представлен в нижней области окна.

Замечание. Если во время набора команды create procedure будут допущены синтаксические ошибки, то после нажатия [Ctrl]+[Enter] прекомпилятор в нижней половине окна выдаст сообщение об ошибке.

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

Варианты заданий на выполнение лабораторной работы № 5 приведены в приложении IV. Задание необходимо выполнить двумя способами: 1) в диалоговом SQL; 2) с помощью хранимой процедуры.

ЛАБОРАТОРНАЯ РАБОТА № 6

Цель:

1. Практическое освоение операторов grant/revoke, union и create view для получения доступа к защищенным данным.

Справочный материал:

1. Конспект курса.

Используемые технические средства:

1. SQL-сервер, работающий на компьютере Pentium 120/32/2,1 под управлением операционной системы Windows NT.

2. Open-Client, работающий на компьютере PC 486/DX4/100/8/1,08/SVGA1MPCI под управлением операционной системы Windows95.

Упражнения:

Перед выполнением лабораторной работы необходимо войти в сеть, затем в систему SYBASE for WINDOWS95 и диалоговый SQL, а также установить связь пользователя с базой данных (аналогично описанному сценарию в лабораторной работе № 1).

1. Создать входные имена SQL-сервера VAF и APB из БД master (имя sa). Для этого в верхней области окна необходимо ввести:

sp_addlogin VAF, teacher, MPEI1 [Ctrl]+[Enter]

sp_addlogin APB, freedom, MPEI1 [Ctrl]+[Enter]

1. Создать пользователей БД MPEI1 из БД MPEI1 (имя STUD1). Для этого в верхней области окна необходимо ввести:

sp_adduser VAF [Ctrl]+[Enter]

sp_adduser APB [Ctrl]+[Enter]

3) Предоставить командное полномочие на выполнение команды create table пользователю VAF. Для этого в верхней области окна необходимо ввести:

grant create table to VAF [Ctrl]+[Enter]

4) Предоставить объектное полномочие на выполнение команд select, update, insert в таблице, созданной пользователем STUD1, пользователю АРВ. Для этого в верхней области окна необходимо ввести:

grant select, update, insert, delete

on poss

to APB [Ctrl]+[Enter]

5) Выполнить запрос по двум информационным таблицам (например: poss и ukr): выдать данные по учащимся России и Украины, заканчивающим учебу в 95, 97 и 98 годах.

Для того, чтобы выполнить этот запрос, например пользователю АРВ, который имеет объектное полномочие на работу с таблицей poss, но не имеет полномочий на таблицу ukr, необходимо:

а) владельцу БД MPEI1 (STUD1) создать и загрузить таблицу с именем ukr (по аналогии с таблицей poss);

б) предоставить объектное полномочие на выполнение команды select в таблице ukr пользователю АРВ, т.е. ввести команду:

grant select on ukr APB [Ctrl]+[Enter]

в) выйти из WISQL32 (disconnect) и войти в WISQL32 с именем APB;

г) так как таблицы poss и ukr имеют одинаковую структуру, то для этого запроса можно использовать оператор union, т.е. ввести команду:

select * from poss where gok in ('95', '97', '98')

union

select * from ukr where gok in ('95', '97', '98') [Ctrl]+[Enter]

В нижней области окна будет предоставлен отчет:

<окно>

д) отменить предоставленное пользователю АРВ полномочие на таблицу ukr, т.е. выйти из WISQL32 (disconnect) , войти в WISQL32 с именем STUD1 и ввести команду:

revoke select on ukr from APB [Ctrl]+[Enter]

6) Создать представление с именем pr1 на основе таблицы ukr.

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

create view pr1

(nomer1, fio1, data_rogden1, pol1, sem_polog_k1,

spec1, kat_obuch_k1, vuz_k1, mp1, gp1, mo1, gok1)

as select * from ukr [Ctrl]+[Enter]

Замечание. Предварительно пользователю АРВ владельцем БД STUD1 должны быть предоставлены полномочия на использование команды create view и команды select в таблице ukr:

grant create view to APB [Ctrl]+[Enter]

grant select on ukr APB [Ctrl]+[Enter]

7) Предоставить вспомогательную таблицу vuz_gorod и четыре таблицы словаря БД в общее пользование. Для этого необходимо ввести следующие команды grant для каждой таблицы (команды вводятся владельцем БД STUD1):

grant all on vuz_gorod to public [Ctrl]+[Enter]

grant all on vuz to public [Ctrl]+[Enter]

grant all on family to public [Ctrl]+[Enter]

grant all on kat_obuch to public [Ctrl]+[Enter]

grant all on gorod to public [Ctrl]+[Enter]

Задание:

1. Из БД master (входное серверное имя sa, пароль compute) создать 2 входных серверных имени, имена произвольны;

2. Используя эти имена, в своей БД (выйти из БД master и войти в свою) создать двух пользователей вашей БД;

3. Предоставить первому пользователю командное полномочие (произвольное), а второму – объектное (тоже произвольное);

4. Создать представление на основе сложного оператора SELECT (типа оператора SELECT из лабораторной работы №3 или №4).

ЛАБОРАТОРНАЯ РАБОТА № 7

Цель:

1. Знакомство с использованием контрольных ограничений и правил для поддержки целостности данных в БД.

Справочный материал:

1. Конспект курса.

Используемые технические средства:

1. SQL-сервер, работающий на компьютере Pentium 120/32/2,1 под управлением операционной системы Windows NT.

2. Open-Client, работающий на компьютере PC 486/DX4/100/8/1,08/SVGA1MPCI под управлением операционной системы Windows95.

Упражнения:

Перед выполнением лабораторной работы необходимо войти в сеть, затем в систему SYBASE for WINDOWS95 и диалоговый SQL, а также установить связь пользователя с базой данных (аналогично описанному сценарию в лабораторной работе № 1).

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

- на уровне поля;

- на уровне таблицы.

(см. конспект курса).

1) Создание контрольных ограничений на уровне поля.

Рассмотрим структуру таблицы poss:

а) поле номер (nomer) - контрольным ограничением для него является промежуток чисел от 1 до 700000;

б) поле фамилия, имя, отчество (fio) - контрольным ограничением для него является запрет использования символов "." (точка) и "-" (минус);

в) поле дата рождения (data_rogden) разбивается на три подполя:

1. день рождения (d_rogd) - контрольным ограничением является промежуток символов от "01" до "31";

2. месяц рождения (m_rogd) - контрольным ограничением является промежуток символов от "01" до "12";

3. год рождения (g_rogd) - контрольным ограничением является промежуток символов от "00" до "99";

г) поле пол (pol) - контрольным ограничением для него является использование либо символа "М", либо символа "Ж";

д) поле код семейного положения (sem_polog_k) - контрольным ограничением для него является список из четырех значений "0", "1", "2", "3";

е) поле специальность (spec) - контрольным ограничением для него является промежуток символов от "000000" до "190046";

ж) поле код категории обучения (kat_obuch_k) - контрольным ограничением является промежуток символов от "01" до "17";

з) поле код учебного заведения (vuz_k) - контрольным ограничением является промежуток чисел от 128955 до 8199999;

и) поле месяц приема (mp) - контрольным ограничением является промежуток символов от "01" до "12";

к) поле год приема (gp) - контрольным ограничением является промежуток символов от "00" до "99";

л) поле месяц окончания (mo) - контрольным ограничением является промежуток символов от "01" до "12";

м) поле год окончания (gok) - контрольным ограничением является промежуток символов от "00" до "99".

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

create table poss1

(nomer integer check (nomer between 1 and 700000),

fio char (40) not null check (fio not like "%.%" or fio not like "%-%"),

d_rogd char(2) check (d_rogd between "01" and "31"),

m_rogd char(2) check (m_rogd between "01" and "12"),

g_rogd char(2) check (g_rogd between "00" and "99"),

pol char(1) check (pol like "M" or pol like "Ж"),

sem_polog_k char(1) check (sem_polog_k in ("0", "1", "2", "3")),

spec char(6) check (spec between "000000" and "190046"),

kat_obuch_k char(2) check (kat_obuch_k between "01" and "17"),

vuz_k integer check (vuz_k between 128955 and 8199999),

mp char(2) check (mp between "01" and "12"),

gp char(2) check (gp between "00" and "99"),

mo char(2) check (mo between "01" and "12"),

gok char(2) check (gok between "00" and "99")) [Ctrl]+[Enter]

Проверка действий контрольных ограничений осуществляется оператором insert (или update), т.е. при вводе записей в таблицу.

2) Создание контрольных ограничений на уровне таблицы.

Рассмотрим некоторые значения поля "категория обучения":

а) значения "п/ф(вуз)" и "п/ф(техн.)" (коды "01" и "02") обозначают обучаемого на подготовительных факультетах (в вузе и техникуме), срок обучения которого не превышает одного года;

б) значения "студент" и "студент-заочник" (коды "03" и "04") обозначают обучаемого в высшем учебном заведении, срок обучения которого не превышает 5 или 6 лет;

в) значения "учащийся" и "бакалавр" (коды "05" и "17") обозначают учащегося либо среднего учебного заведения, либо высшего учебного заведения, срок обучения которого не превышает 4 или 5 лет;

г) значение "аспирант" (код "07") обозначает обучаемого в аспирантуре, срок обучения которого 3 года;

д) значение "аспирант-заочник" (код "08") обозначает обучаемого в аспирантуре, срок обучения которого 4 года;

е) значения "повышение квалификации" и "стажер" (код "10" и "11") обозначают обучаемого в высшем учебном заведении, срок обучения которого не превышает 1 или 2 года.

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

convert (integer, gok) - convert (integer, gp)

Для создания рассмотренных контрольных ограничений воспользуемся возможностью создания контрольных ограничений на уровне таблицы в команде create table таблицы с именем poss2:

create table poss2

(nomer integer check (nomer between 1 and 700000),

fio char (40) not null check (fio not like "%.%" or fio not like "%-%"),

d_rogd char(2) check (d_rogd between "01" and "31"),

m_rogd char(2) check (m_rogd between "01" and "12"),

g_rogd char(2) check (g_rogd between "00" and "99"),

pol char(1) check (pol like "M" or pol like "Ж"),

sem_polog_k char(1) check (sem_polog_k in ("0", "1", "2", "3")),

spec char(6) check (spec between "000000" and "190046"),

kat_obuch_k char(2) check (kat_obuch_k between "01" and "17"),

vuz_k integer check (vuz_k between 128955 and 8199999),

mp char(2) check (mp between "01" and "12"),

gp char(2) check (gp between "00" and "99"),

mo char(2) check (mo between "01" and "12"),