Хранимые процедуры для выдачи требуемых разрешений каждому пользователю
Были составлены процедуры для выдачи требуемых разрешений (привилегий) пользователям.
Реализация выдачи привилегий для пользователя Системный администратор (admin) в процедуру представлена в листинге 23.
Листинг 23 – Процедура выдачи привилегий для пользователя Администратор (sysadmin)
DELIMITER // CREATE PROCEDURE pr_priv_admin() BEGIN GRANT ALL PRIVILEGES ON *.* TO 'ADMIN'; END // |
Реализация выдачи привилегий для пользователя Менеджер по организации рейсов (flight_manager) в процедуру представлена в листинге 24.
Листинг 24 – Процедура выдачи привилегий для пользователя Менеджер (flight_manager)
DELIMITER // CREATE PROCEDURE pr_priv_flmanager() BEGIN GRANT SELECT ON airport.* TO 'flight_manager'; GRANT INSERT, UPDATE ON airport.flights TO 'flight_manager'; GRANT INSERT, UPDATE ON airport.airlines TO 'flight_manager'; END // |
Реализация выдачи привилегий для пользователя Кассир (cachier) в процедуру представлена в листинге 25.
Листинг 25 – Процедура выдачи привилегий для пользователя Консультант (consultant)
DELIMITER // CREATE PROCEDURE pr_priv_cashier() BEGIN GRANT SELECT, INSERT, UPDATE ON airport.passangers TO 'cashier'; GRANT SELECT, INSERT, UPDATE ON airport.flights TO 'cashier'; GRANT SELECT, INSERT, UPDATE ON airport.tikets TO 'cashier';END // |
6.3 Триггеры для работы пользователей с таблицами базы данных
Были разработаны триггеры для работы пользователей с таблицами базы данных:
Для таблицы "Сотрудники" был создан триггер, представленный в листинге 26. Он проверяет, является ли атрибут "pos" (позиция) одной из разрешенных должностей (командир корабля, второй пилот, штурман, бортинженер, стюардесса) перед вставкой или обновлением новой записи сотрудника. Если введенная должность не является одной из допустимых, триггер выдаст ошибку с сообщением "Недопустимая должность для сотрудника". Результат работы триггера представлен на рисунке 34.
Листинг 26 – Триггер на проверку позиций сотрудников при обновлении или добавлении
DELIMITER $$ CREATE TRIGGER check_employee_position_insert BEFORE INSERT ON employees FOR EACH ROW BEGIN IF NEW.pos NOT IN ('командир корабля', 'второй пилот', 'штурман', 'бортинженер', 'стюардесса') THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Недопустимая должность для сотрудника'; END IF; END$$ DELIMITER ;
DELIMITER $$ CREATE TRIGGER check_employee_position_update BEFORE UPDATE ON employees FOR EACH ROW BEGIN IF NEW.pos NOT IN ('командир корабля', 'второй пилот', 'штурман', 'бортинженер', 'стюардесса') THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Недопустимая должность для сотрудника'; END IF; END$$ DELIMITER ; |
Рисунок
34 – Вывод сообщения об ошибке при
некорректной позиции
Для таблицы "Самолеты" был создан триггер, представленный в листинге 27, который будет проверять, находится ли год выпуска в определенном диапазоне (например, 1950-2023) перед вставкой или обновлением новой записи о самолете. Если год не входит в этот диапазон, триггер выдает ошибку с сообщением " Неверный год выпуска самолета". Результат работы триггера представлен на рисунке 35.
Листинг 27 – Триггер на проверку года выпуска самолёта при обновлении или добавлении
DELIMITER $$ CREATE TRIGGER check_plane_year_ins BEFORE INSERT ON planes FOR EACH ROW BEGIN IF NEW.year NOT BETWEEN 1950 AND 2023 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Неверный год выпуска самолета'; END IF; END$$ DELIMITER ;
DELIMITER $$ CREATE TRIGGER check_plane_year_upd BEFORE UPDATE ON planes FOR EACH ROW BEGIN IF NEW.year NOT BETWEEN 1950 AND 2023 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Неверный год выпуска самолета'; END IF; END$$ DELIMITER ; |
Рисунок
35 – Вывод сообщения об ошибке при
некорректном годе выпуска самолёта
Для таблицы "рейсы" можно был создан триггер, представленный в листинге 28, который будет проверять, соответствует ли атрибут "airline_code" существующей авиакомпании, а атрибуты "plane_id" и "crew_id" - существующим самолетам и экипажам, прежде чем вставлять или обновлять новую запись о рейсе. Если один из перечисленных атрибутов не существует, триггер выдает ошибку с сообщением "Неверный airline_code, plane_id или crew_id". Результат работы триггера представлен на рисунке 36.
Листинг 28 – Триггер на проверку существующих атрибутов в таблице «Рейсы» при обновлении или добавлении
DELIMITER $$ CREATE TRIGGER check_valid_code_ins BEFORE INSERT ON flights FOR EACH ROW BEGIN DECLARE airline_exists INT; DECLARE plane_exists INT; DECLARE crew_exists INT; SELECT COUNT(*) INTO airline_exists FROM airlines WHERE airline_code = NEW.airline_code; SELECT COUNT(*) INTO plane_exists FROM planes WHERE plane_id = NEW.plane_id; SELECT COUNT(*) INTO crew_exists FROM crews WHERE crew_id = NEW.crew_id;
IF (airline_exists = 0 OR plane_exists = 0 OR crew_exists = 0) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Неверный airline_code, plane_id или crew_id'; END IF; END $$ DELIMITER ;
DELIMITER $$ CREATE TRIGGER check_valid_code_upd BEFORE UPDATE ON flights FOR EACH ROW BEGIN DECLARE airline_exists INT; DECLARE plane_exists INT; DECLARE crew_exists INT; SELECT COUNT(*) INTO airline_exists FROM airlines WHERE airline_code = NEW.airline_code; SELECT COUNT(*) INTO plane_exists FROM planes WHERE plane_id = NEW.plane_id; SELECT COUNT(*) INTO crew_exists FROM crews WHERE crew_id = NEW.crew_id;
IF (airline_exists = 0 OR plane_exists = 0 OR crew_exists = 0) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Неверный airline_code, plane_id или crew_id'; END IF; END $$ DELIMITER ; |
Рисунок
36 – Вывод сообщения об ошибке при
некорректном вводе
Для таблицы "Сотрудники" был создан триггер, представленный в листинге 30, который будет автоматически устанавливать атрибут "created" на текущую дату и время при вставке новой записи о сотруднике. Атрибут "created" не существует в таблице "employees". Чтобы добавить столбец "created" с временной меткой в таблицу "employees", можно использовать следующий код SQL, представленный в листинге 29:
Листинг 29 – Добавление «created» колонки с временной меткой в таблицу «employees»
ALTER TABLE employees ADD created TIMESTAMP DEFAULT CURRENT_TIMESTAMP; |
А затем без проблем был создан триггер для автоматической установки атрибута "created" в текущую временную метку, когда вставляется новая запись о сотруднике:
Листинг 30 – Триггер на автоматическое добавление атрибута создания нового сотрудника
DELIMITER $$ CREATE TRIGGER set_created_employee BEFORE INSERT ON employees FOR EACH ROW BEGIN SET NEW.created = NOW(); END; $$ DELIMITER ; |
Результат работы триггера представлен на рисунке 37.
Рисунок
37 – Результат работы триггера на
добавление даты создания сотрудника в
таблице «employees»
Для таблицы "tikets" был создан триггер, представленный в листинге 31, для автоматического обновления атрибута "book_date" на текущую дату и время при вставке новой записи о билете.
Листинг 31 – Триггер для автоматического обновления атрибута «book_date» при добавлении новой записи
DELIMITER $$ CREATE TRIGGER set_book_date BEFORE INSERT ON tikets FOR EACH ROW BEGIN SET NEW.book_date = NOW(); END; $$ DELIMITER ; |
Результат работы триггера представлен на рисунке 38.
Рисунок
38 – Автоматическая вставка текущей
даты в «book_date»
при добавлении записи
Был создан триггер, который не даёт купить билет пассажиру, если осталось 3 и менее часа до вылета. Код триггера представлен в листинге 32.
Листинг 32 – Триггер, который не даёт купить билет за 3 часа до вылета
DELIMITER $$ CREATE TRIGGER check_ticket_before_insert BEFORE INSERT ON tikets FOR EACH ROW BEGIN DECLARE departure_time DATETIME; SELECT dep_time INTO departure_time FROM flights WHERE flight_id = NEW.flight_id; IF TIMESTAMPDIFF(HOUR, NOW(), departure_time) <= 3 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Невозможно купить билет, до отправления осталось менее 3 часов'; END IF; END $$ DELIMITER ; |
Этот триггер использует оператор BEFORE INSERT для выполнения перед вставкой нового билета в таблицу tikets. Он использует оператор SELECT для получения времени вылета рейса, связанного с новым вставляемым билетом. Затем он использует оператор IF и функцию TIMESTAMPDIFF для проверки того, что разница между текущим временем и временем вылета меньше или равна 3 часам. Если это так, триггер выдает ошибку с помощью оператора SIGNAL, предотвращая вставку билета и выдавая пользователю сообщение об ошибке.
На момент проверки триггера точная дата и время – 25.01.2023, 14:10. Добавим полёт на сегодняшний день в 16:30. Код добавления представлен в листинге 33.
Листинг 33 – Добавление полёта на сегодняшний день
INSERT INTO flights (dep_city, des_city, dep_time, time, plane_id, airline_code, crew_id) VALUES ('Москва', 'Санкт-Петербург', '2023-01-25 16:30', 60, 1, 'DP', 1); |
При попытке создать билет на этот полёт вылезает ошибка «Невозможно купить билет, до отправления осталось менее 3 часов», результат работы триггера представлен на рисунке 39.
Рисунок
39 – Вывод сообщения об ошибке при попытке
купить билет за 3 и менее часа до вылета