ОБЕСПЕЧЕНИЕ БЕЗОПАСНОСТИ СИСТЕМ БАЗ ДАННЫХ MYSQL - Студенческий научный форум

VII Международная студенческая научная конференция Студенческий научный форум - 2015

Личный портфель

ОБЕСПЕЧЕНИЕ БЕЗОПАСНОСТИ СИСТЕМ БАЗ ДАННЫХ MYSQL

Брюхомицкий Ю.А. 1, Кобилев М.А. 1
1Институт Компьютерных технологий и Информационной безопасности Южный Федеральный Университет г.Таганрог
 Комментарии
Текст работы размещён без изображений и формул.
Полная версия работы доступна во вкладке "Файлы работы" в формате PDF

1.1. Пользователи

В MySQL пользователей можно создать двумя способами: С помощью операторов предназначенных для создания пользователей, таких как CREATE USER или GRANT. Эти операторы позволяют вносить в таблицу привилегий соответствующие изменения. И прямыми манипуляциями в MySQL таблицах привилегий с помощью операторов INSERT, UPDATE, или DELETE.

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

Также есть возможность добавлять пользователей с помощью программ сторонних производителей, позволяющих производить администрирование в MySQL. Одной из таких программ является phpMyAdmin.

Команды GRANT и REVOKE позволяют системным администраторам создавать пользователей MySQL, а также предоставлять права пользователям или лишать их прав на четырех уровнях привилегий:

Глобальный уровень. Глобальные привилегии применяются ко всем базам данных на указанном сервере. Эти привилегии хранятся в таблице mysql.user.

Уровень базы данных. Привилегии базы данных применяются ко всем таблицам указанной базы данных. Эти привилегии хранятся в таблицах mysql.db и mysql.host.

Уровень таблицы. Привилегии таблицы применяются ко всем столбцам указанной таблицы. Эти привилегии хранятся в таблице mysql.tables_priv.

Уровень столбца. Привилегии столбца применяются к отдельным столбцам указанной таблицы. Эти привилегии хранятся в таблице mysql.columns_priv.

Если привилегии предоставляются пользователю, которого не существует, то этот пользователь создается.

Глобальные привилегии можно задать, воспользовавшись синтаксисом ON *.*, а привилегии базы данных - при помощи синтаксиса ON db_name.*. Если указать ON * при открытой текущей базе данных, то привилегии будут заданы для этой базы данных.

Для того чтобы можно было определять права пользователям с конкретных компьютеров, в MySQL обеспечивается возможность указывать имя пользователя (user_name) в форме user@host. Если необходимо указать строку user, в которой содержатся специальные символы (такие как `-') или строку host, в которой содержатся специальные или групповые символы (такие как`%'), можно заключить имя удаленного компьютера или пользователя в кавычки (например, 'test-user'@'test-hostname').

В имени удаленного компьютера также можно указывать групповые символы. Например, user@"%.loc.gov" относится к user всех удаленных компьютеров домена loc.gov, аuser@"144.155.166.%" относится к user всех удаленных компьютеров подсети 144.155.166 класс C.

Простая форма user является синонимом для user@"%".

В MySQL не поддерживаются групповые символы в именах пользователей. Анонимные пользователи определяются вставкой записей User='' в таблицу mysql.user или созданием пользователя с пустым именем при помощи команды GRANT.

На данный момент команда GRANT поддерживает имена удаленных компьютеров, таблиц, баз данных и столбцов длиной не более 60 символов. Имя пользователя должно содержать не более 16 символов.

Привилегии для таблицы или столбца формируются при помощи логического оператора OR из привилегий каждого из четырех уровней. Например, если в таблице mysql.user указано, что у пользователя есть глобальная привилегия SELECT, эта привилегия не отменяется на уровне базы данных, таблицы или столбца.

Привилегии для столбца могут быть вычислены следующим образом:

  • глобальные привилегии;

  • OR (привилегии базы данных AND привилегии удаленного компьютера);

  • OR привилегии таблицы;

  • OR привилегии столбца.

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

Если привилегии предоставляются сочетанию пользователь/удаленный компьютер, которое отсутствует в таблице mysql.user, то в последнюю добавляется запись, которая остается в таблице до тех пор, пока не будет удалена при помощи команды DELETE. Иначе говоря, команда GRANT может создавать записи user в таблице, но команда REVOKE не может их удалить. Это необходимо делать при помощи команды DELETE.

Если нежелательно отправлять пароль открытым текстом, можно воспользоваться параметром PASSWORD с зашифрованным паролем, полученным при помощи функции SQL PASSWORD().

Если у пользователя привилегии для базы данных, то при необходимости в таблице mysql.db создается запись. Данная запись удаляется после удаления всех привилегий для этой базы данных командой REVOKE.

Если у пользователя нет никаких привилегий для таблицы, то таблица не отображается, когда пользователь запрашивает список таблиц (например, при помощи оператора SHOW TABLES).

Оператор WITH GRANT OPTION предоставляет пользователю возможность наделять других пользователей любыми привилегиями, которые он сам имеет на указанном уровне привилегий. При предоставлении привилегии GRANT необходимо проявлять осторожность, так как два пользователя с разными привилегиями могут объединить свои привилегии!

Пользователь не может предоставить другому пользователю привилегию, которой нет у него самого. Привилегия GRANT позволяет предоставлять только те привилегии, которыми обладает данный пользователь.

Если пользователю назначена привилегия GRANT на определенном уровне привилегий, то все привилегии, которыми этот пользователь уже обладает (или которые будут ему назначены в будущем!) на этом уровне, также могут назначаться этим пользователем. Предположим, пользователю назначена привилегия INSERT в базе данных. Если потом в базе данных назначить привилегию SELECT и указать WITH GRANT OPTION, пользователь сможет назначать не только привилегию SELECT, но также и INSERT. Если затем в базе данных предоставить пользователю привилегию UPDATE, пользователь сможет после этого назначать INSERT, SELECT и UPDATE.

Не следует назначать привилегии ALTER обычным пользователям. Это дает пользователю возможность разрушить систему привилегий путем переименования таблиц!

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

При запуске mysqld все привилегии считываются в память. Привилегии базы данных, таблицы и столбца вступают в силу немедленно, а привилегии уровня пользователя – при следующем подсоединении пользователя. Изменения в таблицах назначения привилегий, которые осуществляются при помощи команд GRANT и REVOKE, обрабатываются сервером немедленно. Если изменять таблицы назначения привилегий вручную (используя команды INSERT, UPDATE и т.д.), необходимо запустить оператор FLUSH PRIVILEGES или mysqladminflush-privileges, чтобы указать серверу на необходимость перезагрузки таблиц назначения привилегий.

Имена пользователей MySQL и пароли

Между MySQL и Unix или Windows существует несколько различий в использовании имен пользователей и паролей:

  • Имена пользователей, которые применяются в MySQL для авторизации, не имеют ничего общего с именами пользователей Unix (аккаунты Unix) или именами пользователей Windows. Большинство клиентов MySQL по умолчанию пытаются войти в систему, используя текущее имя пользователя Unix в качестве имени пользователя MySQL, но это сделано только для удобства. Программы клиентов позволяют указывать различные имена при помощи параметров -u или -user. Это означает, что невозможно обеспечить безопасность базы данных, если не все имена пользователей MySQL снабжены паролями: ведь можно попытаться подсоединиться к серверу, используя любое имя, а если воспользоваться именем, которому не назначен пароль, то удастся войти в систему.

  • Имена пользователей MySQL могут содержать до 16 символов. Имена пользователей Unix обычно ограничены 8 символами.

  • Пароли MySQL не имеют никакого отношения к паролям Unix. Не существует связи между паролем, который используется для входа в Unix, и паролем, необходимым для доступа к базе данных.

  • MySQL шифрует пароли при помощи своего алгоритма, который отличается от алгоритма Unix, используемого во время входа в систему. Следует иметь ввиду: даже если пароль хранится в зашифрованном виде, то знания этого зашифрованного пароля будет достаточно, чтобы подсоединиться к серверу MySQL!

Пользователи MySQL и их привилегии обычно создаются при помощи команды GRANT.

Если подсоединение к серверу MySQL осуществляется с клиента командной строки, необходимо указать пароль при помощи параметра --password=your-password

>>mysql --user=monty --password=guess database_name

Если необходимо, чтобы клиент запрашивал пароль, то следует указать --password без каких-либо аргументов

mysql --user=monty --password database_name

или сокращенный вариант этого параметра:

mysql -u monty -p database_name

Обратим внимание на то, что в последнем примере database_name не является паролем.

Если необходимо указать пароль при помощи параметра -p, то запись будет следующей:

mysql -u monty -pguessdatabase_name

В некоторых системах вызов библиотеки, которуюMySQL использует для запроса пароля, автоматически обрезает пароль до 8 символов. В самомMySQL не существует никаких ограничений на длину пароля.

Вступление в силу привилегий

При запуске mysqld все таблицы назначения привилегий загружаются в память и с этого момента привилегии вступают в силу.

Изменения, которые вносятся в таблицы назначения привилегий при помощи команд GRANT, REVOKE или SET PASSWORD, учитываются сервером немедленно.

Если вносить изменения в таблицы назначения привилегий вручную (при помощи команд INSERT, UPDATE и т.д.), необходимо запускать оператор FLUSH PRIVILEGES, mysqladminflush-privilegesили mysqladminreload, чтобы указать серверу на необходимость перезагрузить эти таблицы. В противном случае изменения не вступят в силу, пока сервер не будет перезагружен. Если внести изменения вручную, но не перезагрузить таблицы назначения привилегий, то останется только удивляться, почему внесенные изменения не действуют!

Когда сервер замечает, что были внесены изменения в таблицы назначения привилегий, он обрабатывает установленные соединения клиентов следующим образом:

  • изменения привилегий таблиц и столбцов вступают в силу при следующем запросе клиента;

  • изменения привилегий баз данных вступают в силу при следующем использовании команды USE db_name;

  • изменения глобальных привилегий и изменения пароля вступают в силу при следующем подсоединении пользователя.

Ограничение ресурсов пользователя

До данной версии единственным возможным методом ограничения использования ресурсов сервера MySQL была установка переменной запуска max_user_connections в значение, отличное от нуля. Но этот метод действует только на глобальном уровне и не позволяет управлять отдельными пользователями. Он может представлять определенный интерес только для провайдеров услуг Internet.

На уровне отдельного пользователя теперь введено управление следующими тремя ресурсами:

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

  • количество всех обновлений в час: любая команда, которая изменяет таблицу или базу данных;

  • количество соединений, сделанных за час: новые соединения, открытые за час.

Пользователь в упомянутом выше контексте представляет собой отдельную запись в таблице user, которая уникальным образом идентифицируется своими столбцами user и host.

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

mysql>>GRANT ... WITH MAX_QUERIES_PER_HOUR N1

-> MAX_UPDATES_PER_HOUR N2

-> MAX_CONNECTIONS_PER_HOUR N3;

Можно указать любое сочетание приведенных выше ресурсов. N1, N2 и N3 являются целыми числами, представляющими собой значения количеств запросов/обновлений/соединений в час.

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

Текущие значения для определенного пользователя могут быть сброшены (установлены в нуль), если воспользоваться оператором GRANT с любым из приведенных выше пунктов, включая оператор GRANT с текущими значениями.

Кроме того, текущие значения для всех пользователей сбрасываются, если производится перезагрузка привилегий (на сервере или при использовании команды mysqladminreload) или если выполняется команда FLUSH USER_RESOURCES.

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

Необходимым условием для включения данной функции является наличие в таблице user базы данных mysql дополнительного столбца, как это определено в скриптахсозданиятаблицmysql_install_db и mysql_install_db.sh в подкаталоге scripts.

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

Приведенные ниже примеры показывают, как использовать программу MySQL клиента, для создания новых учетных записей. В этих примерах предполагается, что привилегии пользователей были созданы в соответствии с настройками по умолчанию. Это означает, что для внесения изменений необходимо подключится к серверу MySQL от имени root пользователя, при этом root должен иметь привилегии INSERT для базы данных mysql и административные привилегии RELOAD.

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

>> mysql --user=rootmysql

Если пользователь root защищен паролем, необходимо передать также и парольс помощью параметра --password или -p.

После подключения к MySQL серверу можно создавать новых пользователей. Следующие операторы используют команду GRANT для создания и настройки 4 пользователей:

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';

mysql> GRANTALLPRIVILEGESON *.* TO 'monty'@'localhost'

-> WITHGRANTOPTION;

mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';

mysql> GRANTALLPRIVILEGESON *.* TO 'monty'@'%'

-> WITHGRANTOPTION;

mysql> CREATE USER 'admin'@'localhost';

mysql> GRANT RELOAD, PROCESS ON *.* TO 'admin'@'localhost';

mysql> CREATE USER 'dummy'@'localhost';

Пользователи, созданные этими операторами имеют следующие характеристики.

Два из созданных пользователя имеют имя monty и пароль some_pass. Оба эти пользователя являются супер-пользователями. Они имеют все привилегии и могут делать что угодно. Аккаунт 'monty'@'localhost' может использоваться только при подключении с localhost. Аккаунт 'monty'@'%' использует специальный параметр '%' вместо адреса хоста, благодаря этому он может подключатся к серверу с любого адреса.

Аккаунту 'admin'@'localhost' не задан пароль. Этот аккаунт может использоваться только администратором и только с локального хоста. Ему разрешены административные операторы RELOAD и PROCESS. Эти привилегии позволяют администратору выполнять команды: mysqladminreload, mysqladminrefresh, и mysqladminflush-xxx, а также mysqladminprocesslist. Но этот пользователь не имеет никаких привилегий для управления любой из баз данных. Эти привилегии можно добавитьпозже, используя немного другие GRANT-операторы.

Аккаунту dummy'@'localhost не задан пароль. Это значит, что он может быть использован при подключении только с localhost. Ему не даны никакие привилегии. Эти привилегии будут присвоены ему чуть позже.

Для того чтобы проверить права или привилегии аккаунта следует использовать команду SHOW GRANTS:

mysql> SHOW GRANTS FOR 'admin'@'localhost';

Grants for admin@localhost

GRANT RELOAD, PROCESS ON *.* TO 'admin'@'localhost'

Как альтернативу к CREATE USER и GRANT можно создать те же аккаунты вручную, используя оператор INSERT, при этом в конце обязательно нужно вызвать команду перегрузки привилегий используя FLUSH PRIVILEGES:

shell> mysql --user=root mysql

mysql> INSERTINTO user

-> VALUES('localhost','monty',PASSWORD('some_pass'),

-> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

mysql> INSERTINTO user

-> VALUES('%','monty',PASSWORD('some_pass'),

-> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',

-> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',

-> '','','','',0,0,0,0);

mysql> INSERTINTO user SET Host='localhost',User='admin',

-> Reload_priv='Y', Process_priv='Y';

mysql> INSERTINTO user (Host,User,Password)

-> VALUES('localhost','dummy','');

mysql> FLUSH PRIVILEGES;

Функция PASSWORD() была использована в операторе INSERT для шифрования пароля. Оператор CREATE USER автоматически шифрует пароли, так что функция PASSWORD() для него не нужна.

Значение Y включает привилегии для аккаунта, в зависимости от версии MySQL, можно иметь разное количество значений Y в первых двух операторах INSERT. Оператор INSERT для администратора работает с более читабельным расширением INSERT синтаксиса, используя оператор SET. Оператор INSERT для пользователя dummy имеет значения только для полей Host, User, и Password. Для него не заданы поля с привилегиями, так что MySQL добавит их автоматически со значением по умолчанию N. Для создания супер-пользователя, необходимо выполнить INSERT с значениями Y во всех полях что отвечают за привилегии пользователя. В следующих примерах создаются три пользователя и им дается доступ только к определенным базам данных.

Для создания пользователей с помощью операторов CREATE USER и GRANT, используются следующие команды:

shell> mysql --user=root mysql

mysql> CREATE USER 'custom'@'localhost' IDENTIFIED BY 'obscure';

mysql> GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,DROP

-> ON bankaccount.*

-> TO 'custom'@'localhost';

mysql> CREATE USER 'custom'@'host47.example.com' IDENTIFIED

BY 'obscure';

mysql> GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,DROP

-> ON expenses.*

-> TO 'custom'@'host47.example.com';

mysql> CREATE USER 'custom'@'server.domain' IDENTIFIED BY 'obscure';

mysql> GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,DROP

-> ON customer.*

-> TO 'custom'@'server.domain';

Первый аккаунт имеет доступ к базе данных bankaccount, но только с localhost. Второй аккаунт имеет доступ к базе данных expenses, но только с хоста host47.example.com. Третий аккаунт имеет доступ к базе данных customer, но только с хоста server.domain.

Для создания этих же аккаунтов без операторов GRANT используется оператор INSERT:

shell> mysql --user=root mysql

mysql> INSERTINTO user (Host,User,Password)

-> VALUES('localhost','custom',PASSWORD('obscure'));

mysql> INSERTINTO user (Host,User,Password)

-> VALUES('host47.example.com','custom',PASSWORD('obscure'));

mysql> INSERTINTO user (Host,User,Password)

-> VALUES('server.domain','custom',PASSWORD('obscure'));

mysql> INSERTINTO db

-> (Host,Db,User,Select_priv,Insert_priv,

-> Update_priv,Delete_priv,Create_priv,Drop_priv)

-> VALUES('localhost','bankaccount','custom',

-> 'Y','Y','Y','Y','Y','Y');

mysql> INSERTINTO db

-> (Host,Db,User,Select_priv,Insert_priv,

-> Update_priv,Delete_priv,Create_priv,Drop_priv)

-> VALUES('host47.example.com','expenses','custom',

-> 'Y','Y','Y','Y','Y','Y');

mysql> INSERTINTO db

-> (Host,Db,User,Select_priv,Insert_priv,

-> Update_priv,Delete_priv,Create_priv,Drop_priv)

-> VALUES('server.domain','customer','custom',

-> 'Y','Y','Y','Y','Y','Y');

mysql> FLUSH PRIVILEGES;

Три первых оператора INSERT добавляют записи в таблицу пользователей, которые позволят пользователю custom подключатся с разных хостов с заданным паролем. Но он не имеет никаких глобальных привилегий (все привилегии были выставлены по умолчанию в значение N). Следующие три оператора INSERT добавляют записи в таблицу db, они изменят привилегии для пользователя custom в базах данных bankaccount, expenses, и customer, но только при подключении с определенных хостов. Всегда когда меняются привилегии пользователей вручную, необходимо выполнить в конце команду обновления прав пользователей FLUSH PRIVILEGES, только после этого изменения вступят в силу. Для создания пользователя который может подключатся со всех машин определенного домена (например: mydomain.com), можно использовать параметр “%” для части имени в которой указывается хост:

mysql> CREATE USER 'myname'@'%.mydomain.com' IDENTIFIED BY 'mypass';

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

mysql> INSERTINTO user (Host,User,Password,...)

-> VALUES('%.mydomain.com','myname',PASSWORD('mypass'),...);

mysql> FLUSH PRIVILEGES;

Установка, изменение и сброс пароля root в MySQL

mysqladmin -команда, при помощи которой меняется пароль root вMySQL.

Установка пароля root в первый раз

Если пользователь никогда не устанавливал пароль root в MySQL, сервер не будет требовать пароля root для подключения к его базам данных. Чтобы впервые установить пароль MySQL следует использовать в консоли команду mysqladmin,

как показано ниже:

mysqladmin -urootpasswordnewpass

где newpass будет устанавливаемый пароль. Для изменения (обновления) пароля rootиспользуется команда:

mysqladmin -uroot -poldpasswordnewpass

где oldpassword – старый пароль, а newpasswordсоотвественно– новый. Еслижевответполученосообщение:

mysqladmin: connect to server at 'localhost' failederror: 'Access denied for user 'root'@'localhost' (using password: YES)'

то это означает, что введен неправильныйпароль, либо его сменил кто-то другой. В этом случае необходимо воспользоваться следующей инструкцией для восстановления пароля к вашему MySQL.

Изменения пароля MySQL для других пользователей

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

mysqladmin -u user-name -p oldpasswordnewpass

где user-name – имя пользователя, для которого изменяется пароль.

Обновление или изменение пароля

MySQL хранит имена пользователей и пароли в таблице пользователей внутри базы данных. Пароль можно обновить, используя следующий метод:

1. Ввести свой логин в MySQL, а затем – следующую команду:

mysql -u root –p

2. Можно начинать работу с базой данных. В качестве приглашения для ввода команд вначале строки должно бытьmysql>usemysql.

3. Сменитьпарольпользователя

mysql> update user set password=PASSWORD("newpass") where User='ENTER-USER-NAME-HERE'.

4. Перегрузитьпривилегииилогин

mysql> flush privileges;mysql> quit.

Восстановлениепароля root в MySQL

Можно восстановить пароль баз данных MySQL если выполнить следующие 5 шагов:

  • Остановить демон MySQL.

  • Запустить демон MySQL (mysqld) с опцией --skip-grant-tables, т.к. в этом случае пароль не запрашивается.

  • Подключиться к серверу MySQL c root-привилегиями.

  • Ввести новый пароль.

  • Выйти и перегрузить демон MySQL.

Далее приводятся команды, которые необходимо использовать для каждого шага, при условии, что осуществлен вход в систему с root-привилегиями.

1. Останавливаетсяслужба MySQL:

/etc/init.d/mysql stopStopping MySQL database server: mysqld.

2. Запускается служба с опцией --skip-grant-tables

mysqld_safe --skip-grant-tables&

Долженбытьследующийвывод:

[1] 5988Starting mysqld daemon with databases from /var/lib/mysqlmysqld_safe[6025]: started.

3. Производится подключение к серверу MySQL при помощи клиента mysql:

mysql -u root

Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-logType 'help;' or 'h' for help. Type 'c' to clear the buffer.mysql>

4. Вводитсяновыйпарольдля root:

mysql> use mysql;mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';mysql> flush privileges;mysql> quit

5. Останавливаетсясервер MySQL:

/etc/init.d/mysql stop

Stopping MySQL database server: mysqldSTOPPING server from pid file /var/run/mysqld/mysqld.pidmysqld_safe[6186]: ended[1]+ Done mysqld_safe --skip-grant-table

6. Запускается MySQL-сервер и вводится логин с новым паролем:

/etc/init.d/mysqlstart mysql -u root -p.

Задание:

1. Ознакомиться с теоретическим материалом.

2. Создать минимум 3-х пользователей с разным уровнем привилегий (на выбор). Пользователям разрешается обращаться только с локального/удаленного/определенного хоста.

Обращение пользователя к базе с любого хоста

Обращение пользователя к базе с локального хоста

Обращение пользователя к базе с определенного хоста (192.168.176.129)

3. Забыть и сбросить парольroot.

ЗапуститьMySQLс особыми параметрами, отменяющими проверку авторизации

Выполнение команды смены пароля, применение изменений привилегий и перезапуск демона.

1.2 Роли. Утилита администрирования MySQL-Securich

Основные особенности утилиты:

  • Поддержка концепции динамических ролей для пользователей MySQL;

  • Временная блокировка и разблокировка аккаунтов;

  • Сохранение даты/времени создания и модификаций паролей всех пользователей;

  • Возможность задать возраст пароля, с автоматическим уведомлением на e-mail пользователя о необходимости сменить старый пароль и приближении срока блокировки его аккаунта;

  • При установке пароля проводится проверка по словарю (его вы можете создать сами или скачать уже готовый из общеупотребительных слов);

  • Настройка контроля сложности нового пароля (только пользователю root разрешается самостоятельно устанавливать любые пароли);

  • Мониторинг и хронология смены ролей и привилегий у пользователей;

  • Безопасная схема переименования пользователей;

  • Возможность автоматически сбрасывать все текущие сетевые подключения к СУБД при изменении привилегий/ролей пользователя;

  • Запрет и дополнительный контроль любого изменения (в обход этой утилиты) в служебной базе mysql.

В целом можно уверенно констатировать, что на данный момент Securich предлагает очень цельную и логичную систему по расширенному пользовательскому администрированию СУБД MySQL, нивелирующей почти все слабые стороны MySQL и заметно упрощающей его аккаунт-менеджмент.

Список команд имеющих отношение к администрированию учетных записей:

  • show_user_list() – получить список всех пользователей в системе с полными данными по ним;

  • show_reserved_usernames() – получить список всех зарезервированных (запрещенных администратором для регистрации) имен пользователей. Добавление и удаление этих имен осуществляется соответственно через командыadd_reserved_username() и remove_reserved_username();

  • clone_user() – клонировать уже существующие учетные записи (то есть настройки и права будут взяты за основу при создании новойучетной записи);

  • block_user() – временно заблокировать пользователя (при этом все его настройки и сам пользователь в базе сохраняются);

  • unblock_user() – разблокировать указанного пользователя (восстановление возможности подключаться и работать с БД), операция противоположна по смыслу block_user().

УстановкаSecurich

>svn checkout http://securich.googlecode.com/svn/trunk/ securich-read-only > cd securich>mysql-u root --execute="drop database if exists securich">mysql-u root securichmysql-u root securichmysql-u root securichforprocin`ls procedures/`>do>mysql-u root securichd

Примеркомандыблокированияпользователяна серверечерез Securich:

mysql> call block_user ('savgor');

Query OK, 1 row affected (0.08 sec)

Со стороны клиента savgor, подключенного к серверу БД в момент его блокировки, эффект применения к нему этой команды будет выглядеть так:

mysql> show processlist;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

ERROR 1045 (28000): Access denied for user 'savgor'@'localhost' (using password: YES)

ERROR:

Can't connect to the server

mysql>

Вернуть доступ к БД можно так:

mysql> call unblock_user ('savgor');

Концепции групп и ролей могут значительно упростить и стандартизировать раздачу назначения привилегий. Первоначально реализацию ролей разработчики MySQL обещали в 5-ой версии, но после её выхода, поддержку этой важной особенности в очередной раз перенесли в планы на реализацию в будущую 7-ую версию.

Управление ролями через Securich

Расширенное управление аккаунтами MySQL – только один аспект возможностей Securich.Рассмотрим его возможности по созданию ролей и контролю над правами пользователей БД, для чего сначала приведем список всех команд, ответственных за это:

  • create_update_role() – запуск этой процедуры приведет к созданию (или обновлению уже имеющейся) роли, drop_role() – удаляет роль;

  • show_privileges_in_role() – показ всех привилегий присвоенных указанной роли;

  • show_roles() – выдать список всех ролей доступных в системе;

  • show_users_with_privilege() – показ списка пользователей, имеющих любые права доступа к указанной в аргументах таблице и/или базе данных;

show_user_entries() – выдача списка всех ролей для указанного пользователя со всеми деталями: в каких базах данных и хостах эти привилегии имеют силу на текущем сервере;

  • grant_privileges() – используется для задания привилегий для указанного пользователя, в том числе через регулярные выражения (например, для выделения диапазона таблиц, к которым получит доступ пользователь). Попытка дать права на несуществующие таблицы/БД приведет к их автоматическому созданию перед применением всех установок;

  • rename_user() – переименовывает логин пользователя сохраняя все его текущие привилегии и роли, при этом также дается возможность сменить его пароль и контактный e-mail;

  • password_check() – внутренняя проверка согласованности данных о паролях между служебными таблицами securichи mysql;

  • mysql_reconciliation() – используется совместно с аналогичной командой grant_privileges_reverse_reconciliation () для принудительной синхронизации всех данных по привилегиям междутаблицами mysql и securich

(вторая команда выполняет такую же синхронизацию, но в обратном направлении);

  • sеt_pаssword_expirable() – присвоение паролю уже существующего, указанного в аргументах пользователя статуса «ограниченный по времени действия» (или его отмена);

  • sеt_passwоrd() – изменение своего пароля пользователем (или принудительная установка root-ом). Автоматически анализируется история и запрещаются предыдущие 5 вариантов, также сохраняется дата и время всех проводимых операций. Позволяет автоматически проверять структуру и сложность создаваемого пароля по маске значений, задаваемых в таблице seccоnfig:

    • Проверка минимальной и/или максимальной разрешенной длины пароля;

    • Проверка пароля по словарю из слов;

    • Контроль наличия разных регистров;

    • Контроль наличия цифр в пароле;

    • Контроль наличия специальных символов;

    • Контроль совпадения или модификаций пароля, коррелирующего с именем пользователя (например, пароль получен путем написания логина задом наперед).

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

$ mysql -u savgor -p -h 127.0.0.1 -P 3306

mysql> show databases;

Database

information_schema

securich

mysql>usesecurich;

Database changed

mysql> call my_privileges('test');

PRIVILEGE

DELETE

INSERT

SELECT

UPDATE

mysql> call create_update_role('add','role1','insert');

mysql> call revoke_privileges('peter' , 'localhost' , 'test' , '' , '' , 'role1' , 'Y');

mysql> call sеt_pаssword('paul' , '10.0.0.2' , '2f791928c4ef44ddd7c', 'password123');

Для получения оперативной справки по Securich, прямо в консоли mysql нужно вызвать команду help, в качестве аргумента передав ей имя процедуры из арсенала Securich. Их общий список и примеры использования доступны в подробнойonline-документации, как дополнениев файле securich.pdf

(включается в комплект поставки Securich) предоставляется схема отношений всех служебных таблиц комплекса.

В заключении – ещё одна приятная особенность Securich – наличие собственного графического фронтенда – SAM-My. Его web-интерфейс позволяет управлять политиками MySQL, даже не вникая в особенности богатого командного арсенала Securich, что также придется по душе определенной категории администраторов БД.

Задание:

1. Ознакомиться с теоретическим материалом.

2. Установить Securich.

3. Ознакомиться с функциями управления ролями

4. Создать три роли с разными привилегиями

4.1 Отобразить созданные роли

5. Удалить/изменить роли

5.1. Отобразить измененные роли

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

Триггеры и работа с ними

Триггеры – это хранимые процедуры специального вида, которые автоматически выполняются при изменении данных с помощью операторов INSERT, UPDATE и DELETE. Триггер создается для определенной таблицы, но может использовать данные других таблиц и объекты других баз данных.

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

CREATE TRIGGER имя_триггеравремя_триггерасобытие_триггера

ON имя_таблицы FOR ЕАСН ROW

тело_триггера

Конструкция время_триггера указывает момент выполнения триггера и может принимать два значения:

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

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

Конструкция событие_триггера может принимать значения

INSERT, UPDATE и DELETE.

Идентификаторы OLD и NEW означают старое и новое значение изменяемых данных.

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

DELIMITER //

-- триггер запускается перед добавлением строки в протокол

--счетов

CREATE TRIGGER ins_prot BEFORE INSERT ON k_protokol

FOR EACH ROW

BEGIN

DECLARE v_kolvoNUMERIC(6); --количество

DECLARE v_bill_numNUMERIC(6); --номерсчета

DECLARE v_price_numNUMERIC(6); --номертовара

DECLARE v_price_sumNUMERIC(9,2); --ценатовара

SET v_kolvo=New.kolvo;

SET v_bill_num=New.k_bill_bill_num;

SET v_price_num=New.k_price_price_num;

IF v_kolvo>0 THEN -- только если количество >0

--из прайс-листа получаем цену товара

SELECT p.price_sum INTO v_price_sum FROM k_price p

WHERE p.price_num=v_price_num;

-- обновляем общую сумму счета

UPDATE k_bill

SET bill_sum=bill_sum+v_kolvo*v_price_sum

WHERE k_bill.bill_num=v_bill_num;

-- цену товара продублируем в протоколе счета

SET New.price_sum=v_price_sum;

END IF;

END//

Протестируем триггер. Предварительно посмотрим информацию о счете №9:

SELECT * FROM k_bill WHERE bill_num=9;

Теперь добавим новую строку в протокол этого счета: добавляем 1 штуку товара с номером 1 и ценой 1000 руб. (цену берем из таблицы k_price).

INSERT INTO k_protokol

(kolvo, price_sum, k_price_price_num, k_bill_bill_num)

VALUES(1, 0, 1, 9);

Посмотрим, как изменился счет №9:

SELECT * FROM k_bill WHERE bill_num=9;

Общая сумма счета увеличилась на 1000 р.

Посмотрим таблицу протокола счетов:

SELECT * FROM k_protokol WHERE k_bill_bill_num=9;

В добавленной строке с номером товара 1 цена заполнилась автоматически, из прайс-листа.

Теперь создадим триггер для операции удаления из той же таблицы. При удалении строки из протокола счета должна уменьшаться общая сумма счета.

Триггер запускается перед удалением строки из протокола счетов

DELIMITER //

CREATE TRIGGER del_prot BEFORE DELETE ON k_protokol

FOR EACH ROW

BEGIN

DECLARE v_kolvoNUMERIC(6); -- количество

DECLARE v_bill_numNUMERIC(6); -- номерсчета

DECLARE v_price_sumNUMERIC(9,2); -- ценатовара

SET v_kolvo=Old.kolvo;

SET v_bill_num=Old.k_bill_bill_num;

SET v_price_sum=Old.price_sum;

IF v_kolvo>0 THEN -- только если количество >0

-- обновляем общую сумму счета

UPDATE k_bill

SET bill_sum=bill_sum-v_kolvo*v_price_sum

WHERE k_bill.bill_num=v_bill_num;

END IF;

END//

Протестируем триггер. Сначала посмотрим содержание таблиц до выполнения операции удаления:

SELECT * FROM k_bill WHERE bill_num=9;

SELECT * FROM k_protokol WHERE k_bill_bill_num=9;

Теперь удалим из протокола счетов информацию о товаре с номером 5:

DELETE FROM k_protokol

WHERE k_bill_bill_num=9 AND k_price_price_num=5;

Снова посмотрим содержимое таблицы k_bill:

SELECT * FROM k_bill WHERE bill_num=9;

Общая сумма счета уменьшилась до 1000 р.

SELECT * FROM k_protokol WHERE k_bill_bill_num=9;

Задание:

1. Ознакомиться с теоретическим материалом.

2. Создать и протестировать, по крайней мере, 1 триггер для разработанной базы данных.

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

Контроль ссылочной целостностис помощьюограничения

FOREIGN KEY в MySQL

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

FOREIGN KEY – особый вид ограничения(constraint) MySQL, которое позволяет предотвратить нарушение ссылочной целостности при удалении/изменении информации в таблицах предках. FOREIGN KEY поддерживается только для таблиц типа InnoDB.

Пример нарушения ссылочной целостности

Пусть существуют две таблицы. Таблица Catalogs является таблицей-предком и содержитсведения о категориях товаров в интернет магазине.Таблица products является таблицей-потомком, содержащая сведения овсех товарах этого магазина.

mysql> SELECT * FROM catalogs;

id_catalog

name

1

Процессоры

2

Материнские платы

3

Видеоадаптеры

4

Жёсткие диски

5

Оперативнаяпамять

mysql> SELECT * FROM products;

id_product

name

id_catalog

Celeron 1.8

1

Celeron 2.0GHz

1

Celeron 2.4GHz

1

Celeron D 320 2.4GHz

1

Celeron D 325 2.53GHz

1

Celeron D 315 2.26GHz

1

Intel Pentium 4 3.2GHz

1

Intel Pentium 4 3.0GHz

1

Intel Pentium 4 3.0GHz

1

Gigabyte GA-8I848P-RS

2

Gigabyte GA-8IG1000

2

| Gigabyte GA-8IPE1000G

2

Asustek P4C800-E Delux

2

Asustek P4P800-VML i865G

2

Epox EP-4PDA3I

2

ASUSTEK A9600XT/TD

3

ASUSTEK V9520X

3

SAPPHIRE 256MB RADEON 9550

3

GIGABYTE AGP GV-N59X128D

3

Maxtor 6Y120P0

4

Maxtor 6B200P0

4

Samsung SP0812C

4

Seagate Barracuda ST3160023A

4

Seagate ST3120026A

4

DDR-400 256MB Kingston

5

DDR-400 256MB Hynix Original

5

DDR-400 256MB PQI

5

DDR-400 512MB Kingston

5

DDR-400 512MB PQI

5

DDR-400 512MB Hynix

5

При удалении категории из таблицы catalogs, в таблице products останутся товары, которые не привязаны ни к одной из категорий, что может повлечь массу проблем для магазина.

mysql> DELETE FROM catalogs WHERE name = 'Процессоры';

mysql> SELECT * FROM catalogs;

id_catalog

name

2

Материнские платы

3

Видеоадаптеры

4

Жёсткие диски

5

Оперативнаяпамять

mysql> SELECT * FROM products WHERE id_catalog = 1;

id_product

name

id_catalog

Celeron 1.8

1

Celeron 2.0GHz

1

Celeron 2.4GHz

1

Celeron D 320 2.4GHz

1

Celeron D 325 2.53GHz

1

Celeron D 315 2.26GHz

1

Intel Pentium 4 3.2GHz

1

Intel Pentium 4 3.0GHz

1

Intel Pentium 4 3.0GHz

1

Это явление называется нарушением ссылочной целостности.

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

  • Добавление новой записи в таблице-потомке. Например, добавление новой товарной позиции в таблицу products. Следует заметить, что важную роль играет изменение именно таблицы-потомка, т.к. изменение таблицы-предка (catalogs) не приведет к нарушению ссылочной целостности, а наличие пустой категории товаров допустимо.

  • Обновление внешнего ключа в таблице-потомке. Эта ситуация похожа на первую и может произойти при изменении у товара ссылки на несуществующий раздел каталога, например товар с id_catalogравным 50

  • Удаление записи из таблицы-предка. Эта ситуация рассмотрена выше.

  • Изменение записи в таблице-предке. Эта ситуация отличается от рассмотренной выше тем, что категория каталога не удаляется а принимает новый id.

Обработка изменений при помощи FOREIGN KEY

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

FOREIGNKEY [index_name] (index_col_name, …)REFERENCES tbl_name (index_col_name,…)[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}][ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

FOREIGN KEY используется при создании/изменении таблиц-потомков таблицах. В рамках данногопримера FOREIGN KEY следует использовать в таблице products. Данная конструкция позволяет задать в таблице-потомке внешний ключ с именем index_name на столбцах таблицы, которые перечисляется в круглых скобках. Можно использовать один или несколько столбцов.

Ключевое слово REFERENCES задаёт таблицу-предка tbl_name на которую будет ссылаться внешний ключ. Поля таблицы-предка задаются в круглых скобках, один или несколько. Необязательные конструкции ON DELETE и ON UPDATE определяют поведение MySQL при удалении/обновлении записей из таблицы-предка. Допустимые параметры для ключевых слов ON DELETE и ON UPDATE:

  • RESTRICT – Если в таблице-потомке существуют записи, ссылающиеся на первичный ключ таблицы-предка, то при удалении или обновлении записей с этим первичным ключом в таблице предке будет возвращена ошибка. Ошибка будет возвращаться до тех пор, пока не останется ни одной ссылки в таблице потомке. В MySQL данный параметр означает то же самое, что и NO ACTION;

  • CASCADE – При удалении/обновлении записей в таблице-предке, будут также обновлены/удалены записи из таблицы-потомка с существующим первичным ключом;

  • SET NULL – При удалении/обновлении записей в таблице-предке, записи из таблицы-потомка с существующим первичным ключом будут обновлены на NULL;

  • NO ACTION – При удалении/обновлении записей в таблице-предке, записи из таблицы-потомка с существующим первичным ключом изменены не будут. В MySQL данный параметр означает то же самое что и RESTRICT;

  • SET DEFAULT – Это действие зарезервировано, но не обрабатывается в InnoDB.

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

ALTER TABLE products ADD CONSTRAINT fk_catalog

FOREIGN KEY (id_catalog) REFERENCES catalogs (id_catalog)

ON DELETE CASCADE

ON UPDATE CASCADE

приведет к тому, что изменения таблицы catalogs приведет к автоматическому изменению таблицы products.

Проверку ограничения внешнего ключа можно отключить, присвоив системной переменной FOREIGN_KEY_CHECKS значение 0

mysql> FOREIGN_KEY_CHECKS = 0;

Задание:

1. Ознакомиться с теоретическим материалом

2. Проверить не нарушается ли ссылочная целостность в созданной БД.

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

Представления в MySQL

Представление (VIEW) – объект базы данных, являющийся результатом выполнения запроса к базе данных, определенного с помощью оператора SELECT, в момент обращения к представлению.

Представления иногда называют «виртуальными таблицами». Такое название связано с тем, что представление доступно для пользователя как таблица, но само оно не содержит данных, а извлекает их из таблиц в момент обращения к нему. Если данные изменены в базовой таблице, то пользователь получит актуальные данные при обращении к представлению, использующему данную таблицу; кэширования результатов выборки из таблицы при работе представлений не производится. При этом механизм кэширования запросов (querycache) работает на уровне запросов пользователя безотносительно к тому, обращается ли пользователь к таблицам или представлениям.

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

Преимущества использования представлений:

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

  2. Позволяет разделить логику хранения данных и программного обеспечения. Можно менять структуру данных, не затрагивая программный код, нужно лишь создать представления, аналогичные таблицам, к которым раньше обращались приложения. Это очень удобно, когда нет возможности изменить программный код или к одной базе данных обращаются несколько приложений с различными требованиями к структуре данных.

  3. Удобство в использовании за счет автоматического выполнения таких действий как доступ к определенной части строк и/или столбцов, получение данных из нескольких таблиц и их преобразование с помощью различных функций.

Ограничения по использованию представлений в MySQL:

  • нельзя повесить триггер на представление;

  • нельзя сделать представление на основе временных таблиц; нельзя сделать временное представление;

  • в определении представления нельзя использовать подзапрос в части FROM;

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

  • в определении представления нельзя использовать параметры подготовленных выражений (PREPARE);

  • таблицы и представления, присутствующие в определении представления, должны существовать.

  • только представления, удовлетворяющие ряду требований, допускают запросы типа UPDATE, DELETE и INSERT.

Создание представлений

Для создания представления используется оператор CREATE VIEW, имеющий следующий синтаксис:

CREATE [OR REPLACE][ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]VIEW view_name [(column_list)]AS select_statement[WITH [CASCADED | LOCAL] CHECK OPTION]view_name–имясоздаваемогопредставления.

select_statement– оператор SELECT, выбирающий данные из таблиц и/или других представлений, которые будут содержаться в представлении

Оператор CREATE VIEW содержит 4 необязательные конструкции:

  1. OR REPLACE – при использовании данной конструкции, в случае существования представления с таким именем старое будет удалено, а новое создано. В противном случае возникнет ошибка, информирующая о существовании представления с таким именем и новое представление создано не будет. Следует отметить одну особенность – имена таблиц и представлений в рамках одной базы данных должны быть уникальны, т.е. нельзя создать представление с именем уже существующей таблицы. Однако конструкция OR REPLACE действует только на представления и замещать таблицу не будет.

  2. ALGORITM – определяет алгоритм, используемый при обращении к представлению (подробнее речь об этом пойдет ниже).

  3. column_list– задает имена полей представления.

  4. WITH CHECK OPTION – при использовании данной конструкции все добавляемые или изменяемые строки будут проверяться на соответствие определению представления. В случае несоответствия, данное изменение не будет выполнено. Следует обратить внимание, что при указании данной конструкции для необновляемого представления возникнет ошибка, и представление не будет создано (подробнее речь об этом пойдет ниже).

По умолчанию колонки представления имеют те же имена, что и поля, возвращаемые оператором SELECT в определении представления. При явном указании имен полей представления column_list должен включать по одному имени для каждого поля разделенных запятой. Существует две причины, по которым желательно использовать явное указание имен полей представления:

  1. Имена полей представления должны быть уникальны в пределах данного представления. При создании представления основанного на нескольких таблицах возможна ситуация повторения имен полей представления. Например:

CREATE VIEW v AS SELECT a.id, b.id FROM a,b;

Во избежание такой ситуации нужно явно указывать имена полей представления

CREATE VIEW v (a_id, b_id) AS SELECT a.id, b.id FROM a,b;

Того же результата можно добиться, используя синонимы (алиасы) для названий колонок:

CREATE VIEW v AS SELECT a.id a_id, b.id b_id FROM a,b;

  1. В случае если в определении представления получаемые данные преобразуются с помощью каких-то функций, то именем поля будет данное выражение, что не очень удобно для дальнейших ссылок на это поле. Например:

CREATE VIEW v AS SELECT group_concat(DISTINCT column_nameoreder BY column_name separator '+') FROM table_name;

Врядлиудобноиспользоватьвдальнейшемвкачествеимениполяgroup_concat(DISTINCT username ORDER BY username separator '+')

Для просмотра содержимого представления можно использовать оператор SELECT (аналогично, как и для простой таблицы).С другой стороны, оператор SELECT есть в самом определении представления, т.е. получается вложенная конструкция – запрос в запросе. При этом, некоторые конструкции оператора SELECT могут присутствовать в обоих операторах. Возможны три варианта развития событий: они обе будут выполнены, одна из них будет проигнорирована и результат будет неопределенным. Рассмотрим подробнее эти случаи:

  1. Если в обоих операторах встречается условие WHERE, то оба этих условия будут выполнены, как если бы они были объединены оператором AND.

  2. Если в определении представления есть конструкция ORDER BY, то она будет работать только в случае отсутствия во внешнем операторе SELECT, обращающемся к представлению, собственного условия сортировки. При наличии конструкции ORDER BY во внешнем операторе сортировка, имеющаяся в определении представления, будет проигнорирована.

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

Алгоритмы представлений

Существует два алгоритма, используемых MySQL при обращении к представлению: MERGE и TEMPTABLE.

В случае алгоритма MERGE, MySQL при обращении к представлению добавляет виспользуемый оператор соответствующие части из определения представления и выполняет получившийся оператор.

В случае алгоритма TEMPTABLE, MySQL заносит содержимое представления во временную таблицу, над которой затем выполняется оператор обращенный к представлению.

Внимание: в случае использования этого алгоритма представление не может быть обновляемым (см. далее).

При создании представления есть возможность явно указать используемый алгоритм с помощью необязательной конструкции

[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

UNDEFINED означает, что MySQL сам выбирает, какой алгоритм использовать при обращении к представлению. Это значение будет по умолчанию, если данная конструкция отсутствует.

Использование алгоритма MERGE требует соответствия 1: 1 между строками таблицы и основанного на ней представления.

Пример.Пусть представление выбирает отношение числа просмотров к числу ответов для тем форума:

CREATE VIEW v AS SELECT subject, num_views/num_replies AS param FROM topics WHERE num_replies>0;

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

SELECT subject, param FROM v WHERE param>1000;

В случае MERGE-алгоритма MySQL включает определение представления в использующийся оператор SELECT: заменяет имя представления на имя таблицы, заменяет список полей на определения полей представления и добавляет условие в части WHERE с помощью оператора AND. Итоговыйоператор, выполняемыйзатем MySQL, выглядитследующимобразом:

SELECT subject, num_views/num_replies AS param FROM topics WHERE num_replies>0 AND num_views/num_replies>1000;

Если в определении представления используются групповые функции (count, max, avg, group_concat и т.д.), подзапросы в части перечисления полей или конструкции DISTINCT, GROUP BY, то не выполняется требуемое алгоритмом MERGE соответствие 1:1 между строками таблицы и основанного на ней представления.

Пусть наше представление выбирает количество тем для каждого форума:

CREATE VIEW v AS SELECT forum_id, count(*) AS num FROM topics GROUP BYforum_id;

Найдем максимальное количество тем в форуме:

SELECT MAX(num) FROM v;

Если бы использовался алгоритм MERGE, то этот запрос был бы преобразован следующим образом:

SELECT MAX(count(*)) FROM topics GROUP BY forum_id;

Выполнение этого запроса приводит к ошибке «ERROR 1111 (HY000): Invalid USE of GROUP function», так как используется вложенность групповых функций.

В этом случае MySQL использует алгоритм TEMPTABLE, т.е. заносит содержимое представления во временную таблицу (данный процесс иногда называют «материализацией представления»), а затем вычисляет MAX() используя данные временной таблицы:

CREATE TEMPORARY TABLE tmp_table SELECT forum_id, count(*) AS num FROM topics GROUP BY forum_id;SELECT MAX(num) FROM tmp_table;DROP TABLE tpm_table;

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

  1. В случае UNDEFINED MySQL пытается использовать MERGE везде, где это возможно, так как он более эффективен, чем TEMPTABLE и, в отличие от него, не делает представление не обновляемым.

  2. Если вы явно указываете MERGE, а определение представления содержит конструкции, запрещающие его использование, то MySQL выдаст предупреждение и установит значение UNDEFIND.

Обновляемые представления

Представление называется обновляемым, если к нему могут быть применимы операторы UPDATE и DELETE для изменения данных в таблицах, на которых основано представление. Для того чтобы представление было обновляемым должно быть выполнено два условия:

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

  2. Поля представления должны быть простым перечислением полей таблиц, а не выражениями col1/col2 или col1+2.

Представление, основанное на нескольких таблицах, может обновлять только одну таблицу за запрос, т.е. конструкция SET оператора UPDATE должна перечислять колонки только одной таблицы из определения представления. Кроме того, чтобы представление, основанное на нескольких таблицах, было обновляемым, таблицы в его определении должны быть объединены только с помощью INNER JOIN, а не OUTER JOIN или UNION.

Обновляемое представление может допускать добавление данных (INSERT), если все поля таблицы-источника, не присутствующие в представлении, имеют значения по умолчанию.

Для представлений, основанных на нескольких таблицах, операция добавления данных (INSERT) работает только в случае если происходит добавление в единственную реальную таблицу. Удаление данных (DELETE) для таких представлений не поддерживается.

При использовании в определении представления конструкции WITH [CASCADED | LOCAL] CHECK OPTION все добавляемые или изменяемые строки будут проверяться на соответствие определению представления.

  • Изменение данных (UPDATE) будет происходить только в том случае, если строка с новыми значениями удовлетворяет условию WHERE в определении представления.

  • Добавление данных (INSERT) будет происходить только в том случае, если новая строка удовлетворяет условию WHERE в определении представления.

Иными словами, нельзя добавить или изменить данные в представлении таким образом, чтобы они не были доступны через представление.

Ключевые слова CASCADED и LOCAL определяют глубину проверки для представлений основанных на других представлениях:

  • Для LOCAL происходит проверка условия WHERE только в собственном определении представления.

  • Для CASCADED происходит проверка для всех представлений на которых основанно данное представление. Значением по умолчанию является CASCADED.

Рассмотрим пример обновляемого представления, основанного на двух таблицах. Пусть наше представление выбирает темы форума с числом просмотров более 2000.

punbb>CREATE OR REPLACE VIEW v AS-> SELECT forum_name, `subject`, num_views FROM topics,forums f-> WHERE forum_id=f.id AND num_views>2000 WITH CHECK OPTION;Query OK, 0 rows affected (0.03 sec)punbb>SELECT * FROM v WHERE subject='test';

forum_name

subject

num_views

Новости

test

3000

1 row IN SET (0.03 sec)punbb>UPDATE v SET num_views=2003 WHERE subject='test';Query OK, 0 rows affected (0.03 sec)Rows matched: 1 Changed: 0 WARNINGS: 0punbb>SELECT * FROM v WHERE subject='test';

forum_name

subject

num_views

Новости

test

2003

1 row IN SET (0.01 sec)punbb>SELECT subject, num_views FROM topics WHERE subject='test';

subject

num_views

test

2003

1 rows IN SET (0.01 sec)

Однако,еслипопробоватьустановитьзначениеnum_viewsменьше 2000, тоновоезначениенебудетудовлетворятьусловиюWHERE num_views>2000 в определении представления и обновления не произойдет.

punbb>UPDATE v SET num_views=1999 WHERE subject='test';ERROR 1369 (HY000): CHECK OPTION failed 'punbb.v'Не все обновляемые представления позволяют добавление данных:

punbb>INSERT INTO v (subject,num_views) VALUES('test1',4000);ERROR 1369 (HY000): CHECK OPTION failed 'punbb.v' Причина в том, что значением по умолчанию колонки forum_id является 0, поэтому добавляемая строка не удовлетворяет условию WHERE forum_id=f.id в определении представления. Указать же явно значение forum_idнельзя, так как такого поля нет в определении представления:

punbb>INSERT INTO v (forum_id,subject,num_views) VALUES(1,'test1',4000);ERROR 1054 (42S22): Unknown COLUMN 'forum_id' IN 'field list'

Сдругойстороны:

punbb>INSERT INTO v (forum_name) VALUES('TEST');QueryOK, 1 row affected (0.00 sec)

Таким образом, наше представление, основанное на двух таблицах, позволяет обновлять обе таблицы и добавлять данные только в одну из них.

Задание:

1. Ознакомиться с теоретическим материалом.

2. Создать два представления, одно из которых должно быть обновляемым.

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

Резервное копирование в MySQL

5.1 Копирование файлов базы данных

Базу данных MySQL можно скопировать, если временно выключить MySQL-сервер и просто скопировать файлы из папки/var/lib/mysql/db/. Если сервер не выключить, по очевидным причинам вероятна потеря и порча данных. Для больших нагруженных баз данных эта вероятность близка к 100%. Кроме того, при первом запуске с «грязной» копией базы данных MySQL-сервер начнет процесс проверки всей базы данных, который может затянуться на часы.

В большинстве приложений регулярное выключение сервера БД на длительное время неприемлемо. Для решения этой проблемы применяется прием, основанный на снэпшотах файловой системы. Снэпшот– это что-то вроде «фотографии» файловой системы на определенный момент времени, сделанный без реального копирования данных (и потому быстро). Аналогичным образом работает «ленивое копирование» объектов во многих современных языках программирования.

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

5.2 Копирование через текстовые файлы

Бэкап (англ. "backup") – это резервное копирование файлов с целью их последующего восстановления. Для того чтобы сделать бэкапбазы данных, необязательно извлекать файлы. Можно выбрать необходимые данные запросом и сохранить их в текстовый файл. Для этого используется SQL-команда SELECT INTO OUTFILE и парная ей LOAD DATA INFILE. Выгрузка производится построчно (можно отобрать для сохранения только нужные строки, как в обычном SELECT). Структура таблиц не указывается, об этом должен заботиться программист. Он также должен позаботиться о включении команд SELECT INTO OUTFILE в транзакцию, если это необходимо для обеспечения целостности данных. На практике SELECT INTO OUTFILE используется для частичного копирования очень больших таблиц, которые нельзя скопировать никаким другим образом.

В большинстве случаев намного более удобна утилита mysqldump, которая формирует файл, содержащий все SQL-команды, необходимые для полного восстановления базы данных на другом сервере. Отдельными опциями можно добиться совместимости этого файла с практически любой СУБД (не только MySQL), кроме того, существует возможность выгрузки данных в форматах CSV и XML. Для восстановления данных из таких форматов существует утилита mysqlimport.Утилита mysqldump консольная. Существуют её надстройки и аналоги, позволяющие управлять бэкапом через веб-интерфейс.

Недостаток универсальных утилит бэкапа текстовых файлов– это относительно невысокая скорость работы и отсутствие возможности делать инкрементные бэкапы.

5.3 Инкрементные бэкапы

Традиционно рекомендуют делать 10 бэкапов: по одному на каждый день недели, а также бэкапы двухнедельной, месячной и квартальной давности.Это позволит достаточно глубоко «откатить» базу данных в случае порчи каких-либо данных. Резервные копии базы данных должны храниться на отдельных дискахи на другом сервере. Эти требования могут стать проблемой для больших баз данных. «Прокачка» копии 100-гигабайтной базы данных по 100-мбитной сети занимает примерно тричаса, на которые полностью займет канал. Частично решить эту проблему позволяют инкрементные бэкапы,которые содержат только добавленные или измененные за определенный период данные. В этом случае сочетание полныхбэкапов, выполняемых через большие промежутки времени, и инкрементныхбэкапов позволяет существенно сэкономить время копирования и восстановления базы данных. Однако при этом возникает другая проблема –выявления изменившихся и добавленных данных.

Наибольшим успехом пользуется система PerconaXtraBackup, которая содержит модифицированный движок InnoDB, анализирует двоичные логиMySQL и «вытаскивает» из них необходимую информацию. Близкими возможностями обладает системаInnoDBHotBackup.

5.4 Репликация

Избежать откатов призвана система репликации MySQL. Идея репликации основана на том, что кроме «главного» сервера («Мастера») постоянно работают ведомые сервера MySQL («слейвы»), которые получают инкрементные бэкапы с мастера в режиме реального времени. В этом случае время восстановления базы данных уменьшается. В случае отказа Мастера, его можно оперативно заменить, назначив «новым Мастером» один из слейвов и перенаправить клиентов на него. Кроме того, слейвы могут параллельно выполнять запросы на чтение данных, что снижает нагрузки на мастера и повышает производительность всей системы. Существует возможность запуска конфигураций Master-Master, а с помощью внешних аппаратно-программных систем – и балансировки нагрузки между мастерами.

Следует, однако, помнить, что реплика – это полная копия базы, но это не резервная копия в смысле долговременного сохранения базы данных. Любые операции по изменению данных, произведенные на мастере выполнить будут тут же скопированы на слейв, и «откатить» их назад станет невозможно. Репликацию можно совместить с бэкапом на уровне файлов базы данных, останавливая слейв, а не мастера.

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

vds-admin/# mysqldump -u [ имя пользователя ] -p [ пароль ] --databases [ название базы данных ] > [ файл резервной копии ]

-u [ имя пользователя ] - имя пользователя с правами, достаточными для создания резервной копии.

-p [ пароль ] - пароль пользователя, указывается без пробела перед ним--databases [ название базы данных ] - Короткий вариант -B, название одной или нескольких баз данных для дампа. При создании резервной копии одной базы данных, опцию --databases, писать не обязательно, здесь она использована для большей наглядности и однозначности. То есть название базы данных можно прописать сразу за паролем пользователя.

>- перенаправление вывода команды mysqldump, в файл дампа.

Создадим резервную копию одной базы данных:

vds-admin/# mysqldump -u dbadmin -pdbadminpass -B workdb> /backup/workdb.sql

Пробел после опции –pотсутствует, сразу указывается пароль. В данном случае будет создан файл резервной копии, содержащий структуру и данные,/backup/workdb.sql. Если данные из этой базыне нужны, а достаточно просто сохранить структуру таблиц, можно воспользоваться следующим вариантом:

vds-admin/# mysqldump -u dbadmin -pdbadminpass --no-data -B workdb> /backup/structure_workdb.sql

Ключ --no-data (сокращенный вариант -d ), указывает mysqldump, не сбрасывать данные в дамп.

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

vds-admin/# mysqldump -u dbadmin -pdbadminpass -B drupal --tables access users > /backup/drupal2tables.sql

Вышеприведеннаякоманда, создаетрезервнуюкопиютаблиц access и users, указанныхвследзаопцией --tables, избазыданных drupal исохраняетвфайл/backup/drupal2tables.sql.

Для создания дампа только структуры таблиц, без данных, используется таже опция --no-data.

vds-admin/# mysqldump -u dbadmin -pdbadminpass --no-data -B drupal --tables access users > ./drupal2tables_nodata.sql

Для создания резервной копии нескольких баз данных, достаточно перечислить их названия после опции --databases(-B ):

vds-admin/# mysqldump -u dbadmin -pdbadminpass -B drupal cacti > /backup/drupal_cacti.sql

Чтобы создать резервную копию всех имеющихся баз данных, нужно использовать опцию --all-databases или ее сокращенный вариант: -A.

vds-admin/# mysqldump -u dbadmin -pdbadminpass -A > /backup/all_databases.sql

После выполнения данной команды, будет создана резервная копия всех имеющихся баз данных.

5.5. Восстановление данных из резервной копии

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

vds-admin/# mysql -u dbadmin -pdbadminpass< /backup/all_databases.sql

После выполнения команды, будут восстановлены все базы данных, из резервной копии, которая была создана выше. Такимже способом происходит и восстановление отдельных баз данных или таблиц. Обратите внимание, при восстановлении таблиц, нужно указать в какую именно базу данных вы ее хотите поместить.

vds-admin/# mysql -u dbadmin -pdbadminpass test /struct_test_table.sql

Сбрасываем в дамп структуру таблицы, test_table из базы данных test.

vds-admin/# mysql -u dbadmin -pdbadminpassother_testdb

Просмотров работы: 2375