лаба7

Федеральное государственное автономное образовательное учреждение

высшего профессионального образования

«ОМСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ»

Кафедра «Комплексная защиты информации»

Лабораторная работа

по курсу «Базы данных»

Лабораторная работа №7

Выполнили:

Студентки 2-го курса

гр. ……………..

Принял:

Самотуга А.Е.

Омск 2022

Ход работы

Часть 1. Выполнение заданий по созданию процедур и триггеров.

Код:

use eda;

create table Orders (

id_order INTEGER AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30),

cost INTEGER,

status INTEGER,

number INTEGER)

Рис. 1 Код и результат выполнения задания 1.

1. Создать таблицу логов

Код:

Use eda;

create table Logs (

id_log INTEGER AUTO_INCREMENT PRIMARY KEY,

data datetime,

last_operation varchar(10)

)

Рис. 2 Код и результат выполнения задания 2.

2. Создать триггер, который при вставке в таблицы заказы также логгирует информацию.

Код:

CREATE TRIGGER registrate

before insert on eda.Orders

for each row

insert into

eda.Logs(id_log, data, last_operation)

values (id_log, timestamp(now()), 'added')

Рис. 3 Код создания триггера задания 3.

Код:

CREATE TRIGGER deleting

before delete on eda.Orders

for each row

insert into

eda.Logs(id_log, data, last_operation)

values (id_log, timestamp(now()), 'del')

Рис. 4 Код создания триггера задания 4.

3. Вставить 6 заказов.

Код:

insert into Orders(id_order, name, cost, status, number) values (1, 'sdobnaya', 25, 1, 600);

insert into Orders(id_order, name, cost, status, number) values (2, 'pyshka', 30, 1, 600);

insert into Orders(id_order, name, cost, status, number) values (3, 'vishna', 45, 1,600);

insert into Orders(id_order, name, cost, status, number) values (4, 'cheburek', 50, 0, 600);

insert into Orders(id_order, name, cost, status, number) values (5, 'sloyenaya', 30, 0, 600);

insert into Orders(id_order, name, cost, status, number) values (6, 'abricos', 40, 1,600);

Рис. 5 Код заполнения таблицы задания 5.

Рис. 6 Заполненная автоматически таблица данных о заказах

4. Удалить 2 заказа из середины.

Код:

delete from eda.Orders where id_order=3;

delete from eda.Orders where id_order=4;

Рис. 7 Таблица заказов с отсутствующими данными по средним булочкам

Рис. 8 Таблица данных с автоматически изменёнными значениями удалённых булочек

5. Создать процедуру, которая всем заказам проставляет статус в исполнен при помощи курсора (например, 0 заменяет на 1).

Код:

CREATE definer=`root`@`localhost` procedure `changer` (in `status` int)

begin

declare a,b int;

declare cur1 cursor for select `status` from eda.Orders where `status`='0';

declare continue handler for not found set b=1;

open cur1;

set b=0;

while b=0 do

fetch cur1 into a;

if b=0 then

update eda.Orders set `status`=1;

end if;

end while;

close cur1;

end;

Рис. 9 Код создания процедуры задания 7.

Рис. 10 Вызов процедуры по изменению статуса

Рис. 11 Таблица заказов с изменённым статусом для булочки с id=5

6. Разработаем в рамках индивидуальной части работы свою процедуру

Процедура, которая «опустошает» количество всех изделий

Код:

CREATE DEFINER=`root`@`localhost` PROCEDURE `enpty`(in `number` int)

begin

declare a,b int;

declare curs3 cursor for select `number` from eda.Orders where `number`>'0';

declare continue handler for not found set b=1;

open curs3;

set b=0;

while b=0 do

fetch curs3 into a;

if b=0 then

update eda.Orders set `number`=0;

end if;

end while;

close curs3;

end

Рис. 12 Код создания процедуры задания 8 (с помощью конструктора процедур).

Рис. 13 Вызов процедуры по изменения числа изделий в наличии

Рис. 14 Таблица заказов с изменёнными значениями столбца number

Разработать в рамках индивидуальной части работы свой триггер

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

CREATE TRIGGER updating

after update on eda.Orders

for each row

insert into

eda.Logs(id_log, data, last_operation)

values (id_log, timestamp(now()), 'upd')

Часть 2. Создание базы данных, содержащую как минимум 1 таблицу с данными.

1. Создать базу данных test_фамилия_группа (1), содержащую как минимум 1 таблицу с данными.

Рис. 15 Создание базы данных

Рис. 16 Создание таблицы

2. Заполнить таблицу данными: вставить несколько строк.

Рис. 17 Заполнение таблицы данными

3. Создать 2 представления для таблицы/таблиц.

Код:

• create view `students_address` as select idstudents, address FROM `test_Pakusina_KZI-212`.`students`;

Рис. 18 Создание представления для вывода адресов

Код:

• create view `students_surname` as select idstudents, surname FROM `test_Pakusina_KZI-212`.`students`;

Рис. 19 Создание представления для вывода фамилий студентов

4. Сформировать запрос к одному из представлений.

Рис. 20 Вывод представления с адресами

5. Создать нового пользователя wet, который имеет права только на базу данных 1 и может там только читать.

Код:

• create user 'wet'@'%' identified by '123';

• grant select on `test_Pakusina_KZI-212`.* to 'wet'@'%';

Рис. 21 Создание пользователя и выделение ему прав

6. Создать пользователя abc, который может читать с любой базы данных, а базу данных 1 может редактировать (Update, create, drop и т.д.)

Код:

• create user 'abc'@'%' identified by '123';

• grant select on `test_Pakusina_KZI-212`.* to 'abc'@'%';

• grant select,alter,create,drop,update,insert,delete on `test_Pakusina_KZI-212`.* to 'abc'@'%';

Рис. 22 Создание пользователя и выделение ему прав

7. Выход из root и вход под первым пользователем wet

Рис. 23 Вход под пользователем wet

Рис. 24 Просмотр количества отображаемых баз данных

Рис. 25 Проверка возможности изменения

8. Выход из wet, выполнение входа под abc.

Рис. 26 Вход под пользователем abc

9. Вставить что-нибудь в 1 (создать таблицу или вставить строку)

Рис. 27 Добавление строки

10. Удалить у пользователя abc права на удаления базы данных 1.

Код:

• revoke drop on `test_Pakusina_KZI-212`.* from 'abc'@'%';

Рис. 28 Удаление у пользователя abc права на удаление базы данных

11. Удалить у пользователя wet права на чтение БД 1. Дать право на чтение одного из представлений. Проверить – попробовать вывести данные из представления, данные из таблиц БД 1.

Код:

• revoke select on `test_Pakusina_KZI-212`.* from 'wet'@'%';

• grant select on `test_Pakusina_KZI-212`.students_address to 'wet'@'%';

Рис. 29 Удаление права на просмотр базы данных и добавление права на просмотр представления

Код:

• select * from `test_Pakusina_KZI-212`.`students_address`

Рис. 30 Вывод данных из представления

Рис. 31 Проверка возможности вывода таблицы полностью

12. Выход из abc, вход под root и удаление обоих пользователей.

Код:

• drop user 'wet'@'%';

• drop user 'abc'@'%';

Рис. 32 Удаление пользователей

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

Создание 3 пользователей

Код:

• create user 'user1'@'%' identified by '123';

• create user 'user2'@'%' identified by '123';

• create user 'user3'@'%' identified by '123';

• grant select on *.* to 'user1'@'%';

• grant all on *.* to 'user2'@'%';

• grant select,alter,create,drop,update,insert,delete on `test_Pakusina_KZI-212`.* to 'user3'@'%';

Рис. 33 Создание пользователей и выделение им прав доступа

Создание группы пользователей

Код:

• create role users;

• grant select (`name`),

• insert (`name`),

• update (`name`) on `test_Pakusina_KZI-212`.`students` to users;

• grant users to 'user1'@'%','user2'@'%','user3'@'%';

Рис. 34 Создание группы и добавление туда пользователей

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