Средства шифрования данных. 3 Функции шифрования субд mysql

• Название: 3 Функции шифрования субд mysql
• Анкор: Средства шифрования данных
• Дата: 24.10.2021
• Размер: 385.53 Kb.
• Имя файла: isibd.docx
• Тип: Документы; #255058

Средства шифрования данных, Скрыть данные от неуполномоченных пользователей можно, зашифровав их. В СУБД MySQL имеются функции шифрования данных. Некоторые из них представлены в таблице З З.

Таблица 3.3 — Функции шифрования СУБД MySQL

ф нкция	Описание
AES ENCRYPT(str, Кеу str)	Подвергает строку str шифрованию, используя официальный алгоритм AES. В качестве ключа используется значение Ке str.
AES key_str)	Принимает в качестве первого аргумента зашифрованную с помощью функции AES ENCRYPT() строку и расшифровывает ее. При этом ключ key_str должен совпадать как в пе вом, так и во вто ом сл чае.
DES ENCRYPT(str, кеу)	Подвергает строку str шифрованию, используя официальный алгоритм 3DES. В качестве ключа используется значение К str.
DES DECRYPT(crypt str, Кеу)	Принимает в качесгве первого аргумента зашифрованную с помощью функции DES ENCRYPT() строку и расшифровывает ее. При этом ключ Кеу str должен совпадать как впе вом, таки во вто мс ае.
ENCRYPT(str)	Подвергает строку str необратимому шифрованию, использ я вызов системной ии
md5(str)	Подвергает строку str необратимому шифрованию, использ я алго итмМГ)5.
SHAl(str)	Вычисляет контрольную сумму для строки str по алгоитм SHA.

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

INSERTINTO employees(surname, id_depart, salary_hour)



А выборка данных:

SELECT id_depart, AES id_depart) ПОМ ernployees;

Следует обратить внимание, что для использования приведенных выше функций шифрования столбец «salary hour» должен иметь символьный тип данных, например VARCHAR

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

1. 
   Назовите средства СУБД MySQL, предназначенные для ограничения доступа к данным.
   
2. 
   Объясните, что такое привилегии. Назовите операторы языка SQL для работы с ними.
   

83

З. Дайте определение представления. Назовите операторы языка SQL для работы с ними.

4. Объясните принцип работы функций шифрования. Приведите примеры функций шифрования в СУБД MySQL.

4. 
   ПРОГРАММИРОВАНИЕ НА SQL
   

4.1 Хранимые процедуры и функции

Для реализации бизнес-логики или бизнес-сценариев на языке SQL можно создавать хранимые объекты: процеДуры, функции, триггеры, события. Под хранимым объектом будем понимать последовательность запросов, хранящихся на сервере и реализующих определенный алгоритм. В результате КЛИ€зНтам не нужно ПОСЫЛсгЬ серверу последовательность запросов, достаточно послать один запрос на вызов хранимого объекта. Преимущества такого подхода:

— уменьшается риск ошибок при выполнении бизнесс-процедур, а также уменьшается время разработки прикладного ПО;

— сокращается сетевой трафик;

— безопасность. Алгоритм реализован на сервере;

— снижается сложность кода в прикладном ПО;

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

Хранимые функции. Хранимые функции принимают входные параметры, выполняют какой-либо алгоритм и возвращают результат. Создание производится с помощью оператора CREATE FUNCTION, который в СУБД MYSQL имеет следующий синтаксис:

CREATE FUNCTION f_name

([parameter [ ...]l)

RETURNS оре

[characteristics.. ..l function_body, где Кпате — имя функции;

Оре — тип данных возвращаемого значения; function body — программный код тела функции, состоящий из операторов языка SQL

Вызываются хранимые функции с помощью оператора

SELECT f_name (parametrs);

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

Тело хранимого объекта состоит из составного оператора BEGIN...END, внутри которого могут располагаться другие операторы, в том числе и BEGIN...END. Объявление переменной осуществляется с помощью оператора DECLARE, который имеет следующий синтаксис:

84

DECLARE оре [DEFAULT valuel;, где var_name — имя переменной; Оре — тип данных; vclue — значение.

Присвоить значение переменной можно при помощп оператора SET, который имеет следующий синтаксис:

SET var_name = value К var_name 1 value 1]

Каждый оператор в SQL заканчивается «;». Этот символ воспринимается консольным клиентом как сигнал к отправке запроса на сервер, поэтому для то го, чтобы хранимый объект был воспринят целиком, перед его написанием переопределяют разделитель запросов с ПОМОШЬЮ оператора DELIMITER:

DELIMITER новый_разделптель запросов.

Хранимый объект автоматически привязывается к выбранной с помощью оператора USE для работы в данный момент БД. Внутри хранимых объектов использование данного оператора запрещено.

Пример. Написать функцию, которая подсчитывает количесгво заданий, зарегистрированных на заданного сотрудника. USE enterprise; #выбирали базу Данных enterprise

DELIMITER $$#переопреДелили разДелитель запросов на «$3»

CREATEFUNCTIONcount_empl

RETURNS int

BEGW

DECLARE п ЛТ DEFA ШТ О;

SELECT WTO п ПОМ employees JOIN tasks ОЛ^Т (employees.id enmloyee tasks.id empl) WHERE employees.surname= ,surname_par GROUP ВУ employees.id employee;

REIVPu№n;

ЕЛО,

$$#разДелшпель, Даст понять серверу, что функция закончена

DELIMITER ; #возвращаем прежний разделитель

Предложение SELECT имя столбца INTO имя переменной заносит значение, выбранное из соответствующего столбца, в переменную.

Теперь, когда требуется посчитать, сколько заданий зарегистрировано на сотрудника, пользователю не нужно писать сложный SQL-3arrpoc, а можно просто вызвать функцию:

SELECT cotmt_empl

Хранимые процедуры. Хранимые процедуры, в отличие от функций, результата не возвращают. Хранимые процедуры создаются с помощью оператора CREATE PROCEDURE, который в СУБД MySQL имеет следующий син таксис:

CREATE PROCEDURE р_пате

(parameters [...l)

[characteristic] routine body, где р пате— имя процедуры, routine_body — тело процедуры.

85

Вызываются хранимые процедуры с помощью оператора:

CALL р_патефаштetrs) ;

Синтаксис описания параметров:

[IN OUT INOUT]

Модификатор означает, что параметр рассматривается как входной, т. е. значение параметра при выходе из процедуры остается неизменным, 0UT означает, что параметр рассматривается как выходной, т. е. значение параметра при выходе из процедуры будет то, которое параметру было присвоено в процедуре.

parameter пате — имя параметра. paranwter Оре — тип данных, characteristic: LANGUAGE SQL [NOT] DETERMNISTIC SQL SECURITY [DEFNED INVOkERl

Пример. Пусть нам необходимо уволить сотрудника. Для решения этой задачи, прежде чем удалить запись о нем из таблицы «employees», во избежание нарушения целостности данных, необходимо принять решение, что делать с заданиями, которые на него зарегистрированы. Предположим, для данного случая на предприятии установлено следующее правило: незавершенные зада ния передать другому сотруднику, а записи о завершенных — удалить из таблицы «tasks». Пользователям в качестве входных параметров удобно использовать фамилии сотрудников, а не их идентификаторы. Тогда решение данной задачи будет происходить по следующему алгорътгму:

Узнать из таблицы «employees» идентификатор удаляемого сотрудника.

1. 
   Узнать из таблицы «employees» идентификатор нового сотрудника, которому будем передавать задания.
   
2. 
   Выполнить UPDATE-3anpoc для таблицы «tasks», который передаст находящиеся в разработке задания от удаляемого сотрудника новому.
   

З. Удалить записи о задания увольняемого сотрудника, которые он уже сдал, из таблицы «tasks».

4. Удалить из таблицы «employees» запись об увольняемом сотруднике. Запишем данный алгоритм в виде процедуры на языке SQL. DELIMITER

CREATE PROCEDURE empl пате text, text)

BEGIN

DECLARE лТ DEFA ЛТ О;

DECLAREid new WTDEFAULTO;

SELECT employee шТО id_del empl МОМ employees WHERE surname=otd empl пате;

SELECT id ernployee ШТО U под, empl ПОМ employees WHERE surnante =new empl пате;

УТТЕ tasks SET id new_empl ИНЕЕ id_del АЛО date_turn IS NULL; DELETE ПОМ tasks WHERE

D ELETE ПОМ employees ИНЕЕ

86

Теперь, для того чтобы модифицировать БД в связи с увольнением сотрудника, достаточно просто вызвать процедуру, указав, в качестве параметров фамилию увольняемого сотрудника и фамилию сотрудника, которому следует передать находящиеся в разработке задания:

CALL del етрЩ'Петрова”, ”Колос ”);

В СУБД MySQL объекты хранятся в таблице «proc» системной базы дан ных «mysql».

Существует 4 способа, чтобы просмотреть данные относительно хранимых процедур или функций:

1. 
   Оператор
   

SuOW [PROCEDURE FUNCTION] STATUS [L1kEpatternl; возвращает список хранимых процедур.

2. 
   Оператор
   

SuOW CREATE [PROCEDURE FUNCTIONl object_name•, выводит структуру оператора CREATE, с помощью которого была создана процедура/функция, З. Оператор

SELECT тОМ PROC WHERE пате=оЬјесцпатс, извлечет данные о хранимой процедуре таблицы «рос» системной БД «mysql».

4. С помощью оператора SELECT можно извлечь данные из информаци онной схемы (information_schema).

Для удаления хранимых объектов используется оператор:

DROP [PROCUDURElFUNCTIONl [IF EXISTSl object_name•,

Пример. Удалить процедуру del empl().

DROP PROCEDUREdel empl;

Имя процедуры уникально в пределах БД. Поэтому, чтобы не удалить ошибочно процедуру из другой БД, перед выполнением данного запроса необ ходимо убедиться, что в данный момент выбрана нужная БД.

Для редактирования хранимой процедуры используется оператор:

ALTER [PROCUDUREiFUNCTIONl object_nmne lcharacteristicsl;

Хранимые объекты привязаны к БД. При удалении БД они будут удалены

автоматически.

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

Оператор IF. Данный оператор позволяет реализовать ветвление программы и имеет следующий синтаксис:

IF search condition Then statement_list1

[ELSE IF search condition Then statement_list21



[ELSE IF...l

END IF;

Если search condition истинно, то выполняется statement list1.

87

Пример, Напишем программный код, который определяет, работает ли в отделе с идентификатором 1 сотрудник по фамилии Гук:

DECLARE н ТТ DEFAULT О;

SELECT id employee Л^УТО п ПОМ employees WHERE

AND id depart=l;

IF THENSELE'CT

ELSE SELECT ”YES”

ЕЛО IF;

В данном примере объявили переменную п, по умолчанию ПРИСВОИЛИ ей значение 0, так как пользователя с идентификатором 0 в БД нет (нумерация начинается с 1). Далее пытаемся выбрать идентификатор пользователя с заданной фамилией. Если такой пользователь в таблице существует, то его идентификатор запишется в переменную п. Если такого пользователя не существует, то значение переменной п останется равным О, Далее с помощью оператора 1F THEN ELSE анализируем значение переменной п.

Рассмотрим другой пример. Пример.

IF EXISTS (SELECT id_project FROMprojects WHERE = (@project id)

ТНТ

INSERT INTO tasks (id empl, id_project, task_number, date delivery)

VALUES NOW()),•

ENDIF;

В данном примере проверяем, существует ли идентификатор проекта, указанный в переменной @project_id в таблице «projects». Если существует, то регистрируем задание, а если нет, то выводим сообщение о том, что идентификагор проекта указан неверно.

Оператор CASE. Позволяет реализовать множественный выбор и имеет 2 формы:

CASE case_value

WHEN when value then statement list

[ELSE statement_listl

END CASE;

CASE

WHEN search condition then statement list

[ELSE statement_listl

END CASE;

В первой форме CASE значение case value сравнивается со значением when value; как только соответствие найдено, выполняется соответствующий statement list. Если ни одного соотношения не найдено, то выполняется statement list после else.

Во второй форме сравнение осуществляется в конструкции when; как только будет найдено первое истинное значение, выполняется соответствую-

88

щий statement list и происходит выход из оператора CASE. Если statement_list состоит из нескольких запросов, то необходимо использовать блок BEGlN...END;.

Пример. Напишем программный код, в котором анализируется заработная плата сотрудника с фамилией Гук.

DECLARE ТТ

SELECTsalary_hour ПТО wage ПОМ employees WHERE тук“; CASE

WHEN wage>8 ТНТ“ ,SELECT ”EXcEllent. ”

WHEN SELECT ”GOOD. ”

ELSE SELECT ^f'Very bad. “

ЕЛО CASE;

Оператор WHILE. Оператор WHILE имеет следующий синтаксис:

WHILE search condition DO statement list

END WHILE llabell

При каждой итерации проверяется условие search condition, и до тех пор, пока оно истинно, выполняется statement list,

Оператор LEAVE уекращает выполнение блока, помеченного меткой label.

Оператор ITERATE прекращает текущую итерацию цикла.

Пример. Разработать хранимую функцию, которая сгенерирует строку размером 20 символов, состоящую на 40 % из «0» и на 60 % из «1», CREATEFUNCTIONbin string()

RETURN

BEGIN

DECLARE i INTDEFAULT О;

DECLARE bin tinytext DEFA („ШТ

WHILE ДО

IF (rand() TfIENSETbin=concattbin, Т);

ELSE SET Т);

ЕЛО

ЕМ) WHILE w,•

RETURN bin„

ЕЛО;

Оператор REPEAT. Оператор REPEAT имеет следующий синтаксис:

: КЕРЕАТ statement list

UNTIL search condition

ЕМ) КЕРЕАТ [labell;

В отличие от WHILE, условие цикла проверяется в конце оператора, т. е. выполняется хотя бы одна итерация, после чего проверяется условие search condition. Цикл выполняется, пока условие ложно. Перепишем выше приведенную функцию с использованием оператора REPEATE.

89

CREATEFUNCTIONbin stringO

RETURN

ENDIF,• (JNTIL

ЕЛО REPEAT;

RETURN bin,

ЕМ),

Оператор ШОР. Данный оператор используется для реализации циклов, не имеющих условий выхода, и имеет следующий синтаксис:

[label : LOOP statement list

END LOOP [label

Перепишем функцию bin string() с использованием оператора LOOP• CREATEFUNCTIONbin string()

RETURN

BEGIN

DECLARE i ТТ DEFA ШТ О, DECLARE tinytext DEFA ШТ

w.• ТОР



ЕЛО IF;

IF i>-20 THENLEAVEw;

END IF,•

END ТОР w,•

RETURN bin,

ЕМ);

Курсоры. Практическое применение операторы циклов находят при работе с курсорами. Если запрос возвращает несколько записей, то использование такого запроса совместно с оператором SELECT INTO FROM приводит к возникновению ошибки. Избежать этого можно, прибегнув к использованию курсоров, которые позволяют в цикле просмотреть и обработать каждую строку результирующего набора, Таким образом, курсор — это специальная конструкция, которая позволяет задать имя области в памяти, в которой хранится результирующий набор, и осуществить доступ к хранящейся там информации. Работа с курсором происходит по следующему алгоритму:

1. При помощи оператора DECLARE CURSOR связывается имя курсора с выполняемым запросом:

DECLARE cursor пате CURSOR FOR select statment•, 90

Оператор объявляет курсор с именем cursor_name для SELECT-3m-rpoca select statment. В рамках хранимого объекта имя cursor_name должно быть уникальным. В момент объявления курсора при помощи DECLARE CURSOR SEГ.ЕСТ-запрос select statment не выполняется.

2. Оператор 0PEN выполняет запрос, связанный с курсором, и устанавливает курсор перед первой записью результирующей таблицы. Синтаксис:

OPEN cursor пате;

3. Оператор FETCH помещает курсор на первую запись результирующей таблицы и извлекает данные из записи в локальные переменные хранимого объекта. Повторный вызов оператора FETCH приводит к перемещению курсора к следующей записи, и так до тех пор, пока записи в результирующей таблице не будут исчерпаны.

ЕЕТСН cursor пате INTO var, var1

После ключевого слова INTO должно быть приведено столько локальных переменных, сколько полей возвращает Ш-ЕСТ-запрос select statment.

4. Оператор CLOSE прекращает доступ к результирующей таблице и ликвидирует связь между результирующей таблицей и курсором. Синтаксис:

CLOSE cursor пате;

Пример. Создать хранимую функцию, которая вернет строку, содержащую фамилии работников, участвующих в реализации заданного проекта, разделенные «,», USE enterprise;

DELWITER

DROP FUNCTION IF EXI,ws

CREA ТЕ FUNCTION (id_project int) RETURNS tinytat BEGIN

DECLARE strname ТЛУТЕХГ;

#итогован строка с фамилиями

DECLARE string TINYTEXTDEFAULT

DECLARE is end INT DEFAULT ();#флаг окончания работы

/*объявляем курсор Для запроса, который выбирает из таблицы фамилии сотруДников, участвующих в выполнении проекта У

DECLARE cur_pr CURSOR FOR select distinct employees.swnarne from employees .i0in tasks оп (employees.id_enployee=tasks.id_empl) where id_project=project id;

/*объявляем обработчик для ситуации, когДа курсор Достигает конца результирующего набора*/

DECLARE CONTINUE HANDLER FOR лоТ FOUND SET is end=I; 0PENcur_pr surnames;

/*в цикие читаем Данные курсора и собираем фамшши в строку*/ lbl: LOOP

/*извлекаем очередную фамилию из результирующего набора*/ FETCH cur_pr surnames INTO шпате;

#если фалпиии больше нет — выходим из цикла

IFis endTHENLEAVElbl;

91

ЕАО

# Добаюием ; в конце итоговой строки

SET surname ”; ^т);

Добавляем следующую фамилию в итоговую строку

SETswnames string, surname);

END ТОР [Ы;

CLOSE cur_pr surnames,'

RETURN surnames string;

DELIMITER ,

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

1. 
   Назовите операторы языка SQL, с помощью которых создаются хранимые процедуры и функции. Каков синтаксис данных операторов?
   
2. 
   Назовите операторы языка SQL, с помощью КОТОРЫХ можно объявить переменную и присвоить ей значение. Каков синтаксис данных операторов?
   

З. Перечислите операторы управления потоками данным, реализованные в языке SQL.

4. 
   Приведите отличия между процедурами и функциями.
   
5. 
   Раскройте суть понятия «курсор».
   
6. 
   Приведите в виде блок-схема алгоритм работы с курсорами.
   

4.2 Триггеры. События

Триггеры. Триггер — последовательность операторов, сохраненных на сервере, выполнение которых происходит автоматически при наступлении события, связанного с изменением содержимого таблицы. В СУБД MySQL возможны три события изменения данных таблицы, к которым можно привязать триггер - это выполнение операторов: INSERT, DELETE, UPDATE. Например, при оформлении нового заказа при добавлении записи в таблицу можно создать триггер, который будет автоматически вычитать число заказанных товаров. Создается новый триггер с помощью оператора CREATE TRIGGER, который в СУБД MySQL имеет следующий синтаксис:

CREATE TRIGGER trigger_name trigger_time trigger_event 0N tbl_name FOR ЕАСН ROW trigger_stmt

Оператор CREATE TRIGGER создает триггер с именем trigger_name, привязанный к таблице tbl пате. Таблица должна существовать физически, т. е. не допускается привязка триггера к временной таблице или к представлению. В СУБД MySQL trigger_time указывает момент выполнения триггера и может принимать два значения:

— BEFORE — действие триггера производится до операции изменения таблицы;

— AFTER — действие триггера производится после операции изменения таблицы.

92