5.9.7. Псевдотаблицы (view)

С помощью механизма прав на уровне таблицы можно управлять доступом разных пользователей на уровне таблицы целиком, либо на уровне полей в таблице. Но иногда возникает ситуация, когда надо управлять доступом к отдельным записям в таблице. Например, на некоторой фирме надо обеспечить такой доступ к информации о сотрудниках, чтобы каждый пользователь мог видеть записи только о тех сотрудниках, которые работают в одном с ним отделе. Как решить эту задачу? Если пользоваться правами на уровне таблиц, то потребуется создавать столько таблиц с одинаковой структурой, сколько отделов на фирме. Очевидно, это усложнит структуру базы данных, сделает ее негибкой и затруднит разработку прикладных программ.

В SQL данная проблема может быть решена с помощью псевдотаблиц (VIEW). Псевдотаблица строится на основе уже существующих таблиц или пседотаблиц. В основе псевдотаблицы лежит оператор выборки. Собственно говоря, псевдотаблица и задается оператором выборки:

CREATE VIEW <имя псевдотаблицы> AS <оператор выборки>

Например:

CREATE VIEW cheap_items AS SELECT item_id, company, name, price FROM items WHERE price < 100.00

В этом примере была создана псевдотаблица, содержащая информацию только о дешевых (дешевле 100 единиц) товаров.

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

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

Так как же решить ту задачу, с которой мы начали этот параграф? А именно, как сделать так, чтобы разные пользователи, просматривая одну и ту же таблицу, видели разные данные, а, говоря точнее, информацию только о тех сотрудниках, которые работают в одном с ним отделе? Для этого надо вначале создать таблицу, в которой будет поле с номером отдела:

CREATE TABLE employers ( login_name CHAR(8), { системное имя } depat_num INTEGER, { номер отдела } .............. )

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

SELECT login_name, depat_num, ... FROM employers WHERE depat_num = (SELECT depat_num FROM employers WHERE login_name = USER)

Если, например, таблица employers имеет следующие записи

-------------T-----------T-----¬ ¦ login_name ¦ depat_num ¦.....¦ +------------+-----------+-----+ ¦ nick ¦ 0 ¦ ¦ ¦ andy ¦ 4 ¦ ¦ ¦ kate ¦ 2 ¦ ¦ ¦ george ¦ 2 ¦ ¦ ¦ micky ¦ 4 ¦ ¦ ¦ roma ¦ 4 ¦ ¦ L------------+-----------+------

то пользователь "george" при исполнении этого запроса увидит информацию только о себе и о пользователе "kate", пользователь "andy" увидит информацию о себе и пользователях "roma" и "micky", а пользователь "nick" получит данные только о себе.

Теперь осталось только на основе данного оператора SELECT создать псевдотаблицу my_collegues:

CRETATE VIEW my_collegues AS SELECT login_name, depat_num, ... FROM employers WHERE depat_num = (SELECT depat_num FROM employers WHERE login_name = USER)

Теперь запросы вида

SELECT login_name FROM my_collegues

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

GRANT SELECT ON my_collegues TO PUBLIC

REVOKE ALL ON employers FROM PUBLIC

В псевдотаблицу можно вставлять значения, обновлять данные и удалять записи, но только в том случае, если псевдотаблица образована из одной таблицы. Так, операторы UPDATE, INSERT, DELETE применимы для описанных выше псевдотаблиц my_collegues и cheap_items. Но если для определенния псевдотаблицы использовались поля из двух или более таблиц, то такая псевдотаблица может быть использована только в операторе SELECT. Пример определения псевдотаблицы, построенной из нескольких таблиц:

CREATE VIEW pers_comp AS SELECT persons.lname, persons.fname, persons.sname, companies.name, companies.addresss FROM persons, companies WHERE persons.company = companies.company_id

При создании VIEW можно дополнительно указать, что эта псевдотаблица будет с проверкой. Это означает, что при вставке, удалении или модификации записи будет проверяться условие WHERE в операторе SELECT, который образует данную псевдотаблицу. Для описания псевдотаблицы с проверкой надо указать ключевые слова WITH CHECK OPTION в операторе CREATE VIEW:

CREATE VIEW <имя псевдотаблицы> AS <оператор выборки> WITH CHECK OPTION

Например, в определенную выше псевдотаблицу cheap_items можно вставить запись с ценой, равной 500 единиц:

INSERT INTO cheap_items (company, name, price) VALUES (101, "смокинг", 500)

так как эта псевдотаблица создана без проверки. Правда, сразу после этого оператора вставки в данной псевдотаблице запись о смокинге ценой в 500 единиц будет все равно отсутствовать (при выборке эта запись будет отсечена по условию price<100). А если бы мы создали эту псевдотаблицу с проверкой, то SQL-сервер выдал бы сообщение об ошибке:

CREATE VIEW cheap_items AS SELECT item_id, company, name, price FROM items WHERE price < 100.00 WITH CHECK OPTION

Использование псевдотаблиц бывает полезным не только в случаях разграничения доступа, но и в случаях изменения структуры базы данных. Если при проектировании базы данных была допущена ошибка, или изменились условия задачи, то очень часто приходится переделывать структуру базы данных - менять структуру таблиц, вводить новые таблицы и т.д. Обычно это требует переписывать прикладную программу. Но если с помощью псевдотаблиц можно можно симитировать старую структуру, то ничего переписывать не надо. Кроме того, использования псевлотаблиц с проыеркой (WITH CHECK OPTION) является еще одним способом поддержания целостности базы данных.

Удаляется псевдотаблицы оператором

DROP VIEW <имя псевдотаблицы>

Например:

DROP VIEW cheap_items DROP VIEW employers

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