Токан Диас сиб-44
жүктеу/скачать 28,2 Kb.
|
Токан Диас СИБ-44
|
Токан Диас СИБ-44 Выполнить представления на стр. 89-98 продолжения лабораторной по представлениям, в Тимсе файл Лабораторные по ЗБД ) При подключении к SQL Server все возможные действия пользователей определяются правами ( привилегиями, разрешениями), выданными их учетной записи, группе или роли, в которых они состоят. Права можно разделить на три категории: права на доступ к объектам ; права на выполнение команд ; неявные права.
Работа с данными и выполнение хранимых процедур требуют наличия класса доступа, называемого правами на доступ к объектам баз данных. Под объектами подразумеваются таблицы, столбцы таблиц, представления, хранимые процедуры. Для различных объектов применяются разные наборы прав доступа к ним: SELECT, INSERT, UPDATE, DELETE, REFERENCES – для таблицы или представления; SELECT, UPDATE – для конкретного столбца таблицы или представления; EXECUTE – для хранимых процедур и функций. Право INSERT позволяет вставлять новые строки в таблицу или представление и выдается только на уровне таблицы или представления; оно не может быть выдано на уровне столбца. Право UPDATE выдается либо на уровне таблицы, что позволяет изменять в ней все данные, либо на уровне отдельного столбца, что разрешает изменять данные только в его пределах. Право DELETE позволяет удалять строки из таблицы или представления, выдается только на уровне таблицы или представления, но не может быть выдано на уровне столбца. Право SELECT разрешает выборку данных и может выдаваться как на уровне таблицы, так и на уровне отдельного столбца. Право REFERENCES предоставляет возможность ссылаться на указанный объект. Применительно к таблицам разрешает создавать внешние ключи, ссылающиеся на первичный ключ или уникальный столбец этой таблицы. Предоставление прав Для управления разрешением пользователя на доступ к объектам базы данных используется команда: <предоставление_привилегий>::= GRANT { ALL [ PRIVILEGES] | <привилегия> [,...n]} { [( имя_столбца [,...n])] ON { имя_таблицы | имя_просмотра} | ON {имя_таблицы | имя_просмотра } ([имя_столбца [,...n])] | ON {имя_хранимой_процедуры | имя_внешней_процедуры}} TO { имя_пользователя | имя_группы | имя_роли} [,...n] [WITH GRANT OPTION ] [AS {имя_группы | имя_роли }] Параметр <привилегия> представляет собой следующую конструкцию: <привилегия>::= {SELECT | DELETE | INSERT | UPDATE | EXECUTE | REFERENCES } Параметр WITH GRANT OPTION поможет пользователю, которому вы предоставляете права, назначить права на доступ к объекту другим пользователям. Его использование требует особой осторожности, поскольку при этом владелец теряет контроль над предоставлением прав на доступ другим пользователям. Лучше всего ограничить круг пользователей, обладающих возможностью управлять назначением прав. Необязательный параметр AS {имя_группы | имя_роли } позволяет указать участие пользователя в роли, обеспечивающей предоставление прав другим пользователям. Единственное право доступа, которое может быть предоставлено для хранимой процедуры, – право на ее выполнение ( EXECUTE ). Естественно, кроме этого владелец хранимой процедуры может просматривать и изменять ее код. Для функции можно выдать право на ее выполнение, а кроме того, выдать право REFERENCES, что обеспечит возможность связывания функции с объектами, на которые она ссылается. Такое связывание позволит запретить внесение изменений в структуру объектов, способных привести к нарушению работы функции. Права на выполнение команд SQL Этот класс прав контролирует возможность создания объектов в базе данных, самой базы данных и выполнения процедуры резервного копирования. Можно использовать следующую команду для предоставления права на выполнение команд SQL: <предоставление_права_выполнения>::= GRANT {ALL | <команда>[,...n]} TO {имя_пользователя | имя_группы | имя_роли} [,...n] Параметр <команда> представляет собой следующую конструкцию: <команда>::= {CREATE DATABASE | CREATE TABLE | CREATE VIEW | CREATE DEFAULT | CREATE RULE | CREATE PROCEDURE | BACKUP DATABASE | BACKUP LOG | ALL } Таким образом, можно предоставить право на создание базы данных, таблицы, просмотра, умолчания, правила, хранимой процедуры, резервной копии базы данных и журнала транзакций или предоставить сразу все вышеперечисленные права. При создании представления пользователь должен иметь привилегию SELECT ко всем базовым таблицам, на которые имеются ссылки в создаваемом представлении. Если представление – модифицируемое, то пользователь должен иметь привилегии INSERT, UPDATE, и DELETE к базовым таблицам, причем эти привилегии будут автоматически передаваться и к представлению. Если пользователь не имеет привилегий на модификацию в базовых таблицах, то он не сможет получить их и в представлениях, которые он создал, даже если сами эти представления – модифицируемые. Внешние ключи в представлениях не используются, поэтому и соответствующая привилегия REFERENCES при создании представлений никогда не используется. Реализация техники представлений в СУБД в MySQL. Для версии MySQL 5.1 существуют определенные ограничения по работе с представлениями в MySQL (в последующих версиях эти ограничения, возможно, будут сокращаться): на представление нельзя наложить триггер; 90 нельзя сделать представление на основе временных таблиц; нельзя сделать временное представление; в определении представления нельзя использовать подзапрос в части 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 – при использовании данной конструкции, в случае существования представления с таким именем, старое будет удалено, а новое создано. При ее отсутствии возникнет ошибка, информирующая о существовании представления с 91 таким именем, и новое представление создано не будет. Имена таблиц и представлений в рамках одной БД должны быть уникальны, т.е. нельзя создать представление с именем уже существующей таблицы. Однако конструкция 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; Во избежание такой ситуации нужно явно указывать имена полей представления 92 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; 2. В том случае, если в определении представления получаемые данные преобразуются с помощью каких-то функций, то именем поля будет данное выражение, например: CREATE VIEW v AS SELECT group_concat(DISTINCT c olumn_name oreder BY column_name separator '+') FROM table_name; Использовать в дальнейшем в качестве имени поля `group_concat(DISTINCT username ORDER BY username separator '+')` вряд ли удобно. Алгоритмы представлений. Существует два алгоритма, используемых MySQL при обращении к представлению: MERGE и TEMPTABLE. В случае алгоритма MERGE, при обращении к представлению MySQL добавляет в использующийся оператор соответствующие части из определения представления и выполняет получившийся оператор. В случае алгоритма TEMPTABLE, при обращении к представлению MySQL заносит содержимое представления во временную таблицу, над которой затем выполняется оператор, обращенный к представлению. В случае использования этого алгоритма представление не может быть обновляемым. При создании представления есть возможность явно указать используемый алгоритм с помощью необязательной конструкции [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] 93 UNDEFINED означает, что СУБД сама выбирает, – какой алгоритм использовать при обращении к представлению. Использование алгоритма MERGE требует соответствия 1 к 1 между строками таблицы и основанного на ней представления. Пусть представление выбирает отношение числа просмотров к числу ответов для тем форума: CREATE VIEW v AS SELECT subject, num_views/num_replies AS param FROM topics WHERE n um_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 n um_replies>0 AND num_views/num_replies>1000; Если в определении представления используются групповые функции (count, max, avg, group_concat и т.д.), подзапросы в части перечисления полей или конструкции DISTINCT, GROUP BY, то требуемое алгоритмом MERGE соответствие 1 к 1 между строками таблицы и основанного на ней представления не выполняется. 94 Пусть представление выбирает количество тем для каждого форума: CREATE VIEW v AS SELECT forum_id, count(*) AS n um FROM topics GROUP BY forum_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_i d, count(*) AS num FROM topics GROUP BY forum_id; SELECT MAX(num) FROM tmp_table; DROP TABLE tpm_table; Подводя итог, следует отметить, что в MySQL нет серьезных причин явно указывать алгоритм при создании представления, так как: 1. В случае UNDEFINED, MySQL пытается использовать алгоритм MERGE везде, где это возможно, так как он более эффективен, чем TEMPTABLE и, в отличие от него, не делает представление не обновляемым. 95 2. Если явно указывается алгоритм MERGE, а определение представления содержит конструкции, запрещающие его использование, то MySQL выдаст предупреждение и установит значение UNDEFIND. Обновляемые представления. Представление называется обновляемым (модифицируемым), если к нему могут быть применимы операторы UPDATE и DELETE для изменения данных в таблицах, на которых основано представление. Для того чтобы представление было обновляемым должны быть выполнены 2 условия: 1. Имеется соответствие 1 к 1 между строками представления и таблиц, на которых основано представление, т.е. каждой строке представления должно соответствовать по одной строке в таблицах-источниках. 2. Поля представления должны быть простым перечислением полей таблиц, а не выражениями col1/col2 или col1+2. Через представление, основанное на нескольких таблицах, можно обновлять только одну таблицу за один запрос, т.е. конструкция SET оператора UPDATE должна перечислять колонки только одной таблицы из определения представления. Кроме того, чтобы представление, основанное на нескольких таблицах, было обновляемым, таблицы в его определении должны быть объединены только с помощью INNER JOIN, а не OUTER JOIN или UNION. Обновляемое представление может допускать добавление данных (INSERT), если все поля таблицы-источника, не присутствующие в представлении, имеют значения по умолчанию. Для представлений, основанных на нескольких таблицах, операция добавления данных (INSERT) работает только в том случае, если происходит добавление в единственную реальную таблицу. Удаление данных (DELETE) для таких представлений не поддерживается. При использовании в определении представления конструкции WITH [CASCADED | LOCAL] CHECK OPTION все добавляемые или изменяемые строки будут проверяться на соответствие определению представления. 96 Изменение данных (UPDATE) будет происходить только в том случае, если строка с новыми значениями удовлетворяет условию WHERE в определении представления. Добавление данных (INSERT) будет происходить только в том случае, если новая строка удовлетворяет условию WHERE в определении представления. Иными словами, нельзя добавить или изменить данные в представлении таким образом, чтобы они не были доступны через представление. Ключевые слова CASCADED и LOCAL определяют глубину проверки для представлений, основанных на других представлениях: Для LOCAL происходит проверка условия WHERE только в собственном определении представления. Для CASCADED происходит проверка для всех представлений, на которых основано данное представление. Значением по умолчанию является CASCADED. жүктеу/скачать 28,2 Kb. Достарыңызбен бөлісу:
| ||||||||||||||||||||||||||||||