Вьюверы: ограниченый доступ к даннымЧто такое вьверы?
Вьювер - это альтернативный способ представления данных из одной или нескольких таблиц. Вьювер можно представлять себе как фильтр, через который пропускаются табличные данные, прежде чем их увидит пользователь. В этом смысле можно говорить о представлении или изменении данных “через” вьювер.
Вьювер создается на базе одной или нескольких таблиц, расположенных в базе данных. Таблицы, из которых строится вьювер называются базовыми или основными. Вьювер может быть также построен на основе другого вьювера.
Определение вьювера и названия его базовых таблиц сохраняется в базе данных. При определении вьювера не создается никаких копий табличных данных. Все данные, которые появляются во вьювере, на самом деле распологаются в базовых таблицах.
По внешнему виду вьювер ничем не отличается от таблицы базы данных. С расположенными в нем данными можно работать почти также как с табличными данными. Язык Transact-SQL был расширен для того, чтобы снять все ограничения на выборку данных через вьювер и ослабить обычные ограничения на модификацию данных. Оставшиеся исключения и ограничения будут описаны в этой главе.
Когда через вьювер происходит изменение видимых в нем данных, то на самом деле изменяются данные, находящиеся в базовых таблицах. Обратно, если изменяются данные в базовых таблицах, то автоматически происходят изменения во вьюверах, построенных на основе этих таблиц.
Преимущества вьюверов
Примеры, которые приведены в этой главе, показывают, как вьюверы могут использоваться для выделения нужных данных и упрощения их восприятия. Вьюверы также обеспечивают простой в использовании механизм защиты данных. Кроме того, вьюверы могут быть полезны, когда изменяется структура базы данных, а пользователь предпочитает работать в привычном для себя стиле.
Фокусировка
Вьюверы позволяют пользователю сосредоточиться на интересующих его данных и на задаче, которую ему надо решить. Данные, которые не имеют отношения к конкретному пользователю, могут быть скрыты из его поля зрения.
Более простая работа с данными
Вьюверы облегчают не только восприятие данных, но и работу с ними, поскольку часто используемые объединения, проекции и выборки могут быть определены для вьюверов, что позволяет пользователю не указывать всех условий и уточнений каждый раз, когда выполняется очередная операция.
Гибкость
Вьюверы позволяют различным пользователям иметь различные точки зрения на одни и те же данные. Это особенно важно, когда много различных пользователей с различной квалификацией работают с одной и той же базой данных.
Безопасность
Через вьювер пользователи могут запрашивать и модифицировать только те данные, которые они видят. Остальная часть базы данных остается для них скрытой и недоступной.
С помощью команд grant (предоставлять) и revoke (отнимать) можно разрешить доступ каждому пользователю только к определенным объектам базы данных, включая вьюверы. Если вьювер, а также все базовые таблицы и вьюверы, на которых он основан, принадлежат одному владельцу, то этот владелец может давать разрешение другим пользователям на использование этого вьювера и в то же время запретить непосредственное использование базовых таблиц и вьюверов. Это простой и вместе с тем эффективный механизм обеспечения защиты данных. Детальное описание команд grant и revoke дается в Руководстве по администрированию доступа к SQL Серверу.
Определив различные вьюверы и задав различные права доступа к ним, можно разрешить доступ пользователей только к опеределенным подмножествам данных. Далее приводятся примеры использования вьюверов исходя из соображений безопасности:
·Доступ может быть разрешен только к определенным строкам базовой таблицы, содержащих определенную информацию. Например, можно создать вьювер, в котором будут показаны только книги по бизнесу и психологии, а информация о всех других книгах будет скрыта;·Доступ может быть разрешен только к определенным столбцам базовой таблицы. Например, можно определить вьювер, в котором будут видны все строки из таблицы titles, но данные в столбцах royalty (гонорар) и advance (аванс) будут скрыты, поскольку они носят коммерческий характер;·Доступ может быть разрешен только к определенным строкам и столбцам базовой таблицы;·Доступ может быть разрешен только к тем строкам, которые удовлетворяют условиям соединения с несколькими базовыми таблицами. Например, можно определить вьювер, в котором соединяются данные из таблиц titles, authors и titleauthor, чтобы были видны фамилии авторов вместе с названиями книг, которые они написали. В этом вьювере можно не показывать персональную информацию об авторах и информацию коммерческого характера;·Доступ может быть разрешен только к итоговым данным в основной таблице. Например, через вьювер category_price (категория_цена) пользователь может узнать только средние цены по каждому виду книг;·Доступ может быть разрешен только к подмножеству строк в другом вьювере или к комбинации вьюверов и таблиц. Например, через вьювер hiprice_computer пользователь может узнать названия и цены книг по компьютерной тематике, которые удовлетворяют условиям, указанным в определении вьювера.
Чтобы определить вьювер, пользователь должен получить разрешение на выполнение команды creat view (создать вьювер) от владельца базы данных и иметь соответствующие права доступа ко всем таблицам или вьюверам, которые используются в определении этого вьювера.
Если в определении вьювера участвуют объекты из различных баз данных, то пользователь, создающий вьювер, должен иметь право доступа к этим базам или иметь гостевые права в этих базах.
Пользователь, владеющий объектом в базе, должен сам следить за тем, как другие пользователи используют его информацию через вьюверы. Рассмотрим ситуацию, когда владелец базы данных разрешил “Гарольду” создавать вьюверы, а пользователь по имени “Моди” разрешила “Гарольду” выбирать данные из своей таблицы. С этими правами “Гарольд” может определить вьювер, через который будет открыта вся информация из таблицы, принадлежащей “Моди”. Если после этого “Моди” отберет у “Гарольда” права на чтение данных из своей таблицы, он тем не менее может увидеть их через свой вьювер.
Логическая независимость данных
Вьюверы помогают защитить пользователей от изменений в структурах таблиц, если внесение таких изменений становится совершенно необходимым.
Например, предположим, что база данных была реструктурирована с помощью оператора select into (выбрать в) и таблица titles была разделена на две новых таблицы titletext и titlenumbers с удалением таблицы titles:
titletext (title_id, title, type, notes)
titlenumbers (title_id, pub_id, price, advance, royalty, total_sales, pub_date)
Заметим, что прежняя таблица titles может быть “восстановлена” с помощью соединения по столбцу title_id двух новых таблиц. Чтобы защитить от этого изменения пользователей, достаточно создать вьювер на основе двух новых таблиц. Можно даже назвать его прежним именем titles.
Любой запрос или любая сохраняемая процедура, которая раньше обращалась к таблице titles теперь будет обращаться к вьюверу titles. Все операторы select (выбор) у пользователей будут работать как и раньше. Пользователям, которые только выбирают данные из этой таблицы, можно даже не сообщать о проишедшем изменении.
К сожалению, вьюверы обеспечивают лишь частичную логическую независимость. Некоторые операторы модификации данных не будут выполняться с новым объектом titles, поскольку они не разрешены для вьюверов.
Примеры вьюверов
В нашем первом примере вьювер будет получен из таблицы titles. Предположим, что пользователя интересуют только книги, цена которых выше 15 долларов и по которым был выплачен больший чем 5000 долларов. В соответствующем оператор выбора нужно указать следующие условия отбора строк:

select *
from titles
where price > $15
and advance > $5000
Предположим теперь, что необходимо выполнить много операций выборки и обновления на этом массиве данных. Можно, конечно, каждый раз указывать вышеприведенные условия в каждой выполняемой команде. Однако, удобней создать вьювер, в котором явно указать какие записи должны быть видны:
create view hiprice
as select *
from titles
where price > $15
and advance > $5000
Когда SQL Сервер получит эту команду, то вместо выполнения оператора выбора select, который указан здесь после ключевого слова as, сервер сохранит этот оператор, который фактически является определением вьювера hiprice, в системной таблице syscomments. Информация о каждом столбце вьювера будет также записана в системные таблицы sysobjects и syscolumns.
Теперь, при обращении к вьюверу hiprice, SQL Сервер объединит выполняемый оператор с определением вьювера hiprice. Например, пользователь может изменить все цены в hiprice также, как и в любой другой таблице, с помощью следующего оператора:
update hiprice
set price = price * 2
SQL Сервер сам находит определение вьювера в системных таблицах и конвертирует этот оператор обновления следующим образом:
update titles
set price = price * 2
where price > $15
and advance > $5000
Другими словами, SQL Сервер, основываясь на определении вьювера, обновит таблицу titles. При этом, конечно, будут обновляться только те строки, которые удовлетворяют условиям по цене и авансу, которые также были указаны в определении вьювера и переписаны в оператор обновления.
Последствия выполнения этого оператора обновления можно увидеть как через вьювер, так и непосредственно в таблице titles. И наоборот, если бы пользователь выполнил второй из указанных операторов обновления непосредственно с таблицей titles, то изменения можно было бы увидеть и через вьювер.
Обновление базовой таблицы может привести к изменениям в данных, видимых через вьювер. Например, если увеличить цену книги “You can combat computer stress” до 25.95 долларов, то ее можно будет увидеть через вьювер, поскольку она будет удовлетворять условиям отбора данных в этом вьювере.
Однако, если происходят изменения в структуре базовой таблицы, например к ней добавляются новые столбцы, то эти столбцы не будут отображены во вьювере, поскольку их нет в соответствующем операторе выбора select *. Для их появления необходимо переопределить вьювер, поскольку звездочка (*) является сокращением для списка столбцов, присутствующих в таблице в момент определения вьювера.
Создание вьюверов
Название вьювера должно быть уникальным среди уже сущестующих названий таблиц и вьюверов. Если установлена опция командой set quoted_identifier on, то можно заключать название вьювера в кавычки. В противном случае, название должно удовлетворять правилам написания идентификаторов, изложенным в главе 1.
Вьюверы можно строить из других вьюверов, а также на базе процедур, которые обращаются к вьюверам. На вьюверах можно определять главные, внешние (foreign) и общие ключи. С вьюверами нельзя связывать правил, значений по умолчанию, триггеров или индексов. Нельзя создавать временные вьюверы и нельзя строить вьювер на временной таблице.
Синтаксис команды создания вьюверов
Оператор создания вьюверов имеет следующий общий вид:
create view [[название_базы.]владелец.]название_вьювера
[(название_столбца [, название_столбца]...)]
as select [distinct] оператор_выбора
[with check option]
Как было показано в примере, приведенном в предыдущем разделе, пользователь может не указывать названий столбцов в предложении create оператора создания вьювера. В этом случае SQL Сервер присваивает столбцам вьювера те же названия и те же типы данных, что приведены в списке выбора оператора select. Этот список может быть заменен звездочкой (*), как в предыдущем примере, или быть частью списка названий столбцов базовой таблицы.
Пользователь может определить вьювер, в котором не будет повторяющихся строк. Для этого нужно указать ключевое слово distinct в операторе выбора select.
Вьюверы, созданные с ключевым словом distinct нельзя обновлять.
Всегда возможно явно указать названия столбцов. Однако, названия столбцов нужно указывать в предложении create для каждого столбца, если имеет место любое из следующих условий:
·Если столбец получается в результате вычисления арифметической, агрегирующей или встроенной функции или содержит константу;·Если два или более столбца вьювера, в противном случае, получат одинаковое название. Это обычно происходит при построении вьювера на основе соединения, когда соединяемые таблицы имеют столбцы с одинаковым названием;·Когда желательно изменить название столбца вьювера по сравнению с его табличным названием. Пользователь может переименовать столбцы и в операторе выбора select, который определяет вьювер. Независимо от изменения названия столбец вьювера наследует тип данных из столбца таблицы, на котором он основан.
Далее приводится пример определение вьювера, в котором изменено название столбца по сравнению с табличным названием:
create view pub_view (Publisher, city, state)
as select pub_name, city, state
from publishers
Далее приводится другой способ создание того же вьювера, в котором столбец переименовывается в операторе выбора:
create view pub_view2
as select Publisher = pub_name, city, state
from publishers
В примерах, которые приводятся в следующих разделах, будут показаны остальные способы включения названий столбцов в предложение create view.
В следующем разделе рассматривается оператор выбора, определяющий вьювер, ключевое слово distinct и опция with check option (опция проверки). После этого описывается команда удаления вьюверов drop.
Использование оператора выбора при создании вьюверов
Команда выбора select в операторе создания вьювера по существу определяет вьювер. Пользователь должен иметь право доступа ко всем объектам, указанным в этой команде.
Вьювер не обязан быть простым подмножеством строк или столбцов одной таблицы, как это было показано в нашем предыдущем примере. Вьювер можно строить из нескольких таблиц и вьюверов с помощью оператора выбора любой сложности.
Имеется несколько следующих ограничений для оператора выбора в определении вьювера:·В нем нельзя использовать предложения order by и compute;·В нем нельзя использовать ключевое слово into;·В нем нельзя использовать временные таблицы.
Определение вьювера в виде проекции
Чтобы создать вьювер, в котором отображаются все строки таблицы titles, содержащие информацию только из части столбцов (проекция), можно воспользоваться следующим оператором:
create view titles_view
as select title, type, price, pubdate
from titles
Заметим, что в предложении create view этого оператора нет названий столбцов, поскольку все они указаны в списке выбора.
Определение вьювера с вычисляемым столбцом
Далее приводится определение вьювера, значение в одном из столбцов которого вычисляется из величин, содержащихся в столбцах price, royalty и total_sales:
create view accounts (title, advance, amt_due)
as select titles.title_id, advance, (price * royalty /100 ) * total_sales
from titles, roysched
where price > $15
and advance > $5000
and titles.title_id = roysched.title_id
and total_sales between lorange and hirange
В этом примере список столбцов должен быть указан в предложении creat, поскольку в данном случае нет названия для столбца, в котором перемножаются величины из столбцов price, royalty и total_sales. Вычисляемый столбец получил название amt_due, которое должно быть указано на том же по порядку месте в предложении creat, что и соответствующее ему вычисляемое выражение в предложении select.
Определение вьювера с агрегирующими и встроенными функциями
Если в определении вьювера используются агрегирующие или встроенные функции, то в предложении creat нужно указывать названия столбцов. Например:
create view categories (category, average_price)
as select type, avg(price)
from titles
group by type
Если вьювер создается из соображений безопасности данных, то нужно проявлять осторожность при использовании агрегирующих функций и группировки (group by). Поскольку в языке Transact SQL нет ограничений на использование столбцов при группировке в операторе выбора, то может возникнуть ситуация, когда вьювер возвратит больше информации чем нужно. Например:
create view categories (category, average_price)
as select type, avg(price)
from titles
where type = "business"
В предыдущем примере возможно было бы целесообразно ограничиться только книгами по бизнесу, поскольку результаты будут выданы по всем категориям книг. Дополнительная информация о группировке была приведена в главе 3.
Определение вьювера через соединение
Пользователь может создать вьювер из нескольких базовых таблиц. Далее приводится пример вьювера, полученного из таблиц authors и publishers. В нем отображаются фамилии авторов книг, которые живут в одном городе с издателем, вместе с названием издателя и названием города.
create view cities (authorname, acity, publishername, pcity)
as select au_lname, authors.city, pub_name, publishers.city
from authors, publishers
where authors.city = publishers.city
Построение вьювера из других вьюверов
Вьювер можно определить на основе других вьюверов, например:
create view hiprice_computer
as select title, price
from hiprice
where type = 'popular_comp'
Построение вьювера с ключевым словом distinct
Пользователь может определить вьювер, в котором не будут повторяться строки, например:
create view author_codes
as select distinct au_id
from titleauthor
Строка является дубликатом другой строки, если значения во всех столбцах этой строки в точности совпадают со значениями в другой строке. При этом неопределенные значения рассматриваются как одинаковые.
SQL Сервер обеспечивает отсутствие дубликатов, когда вьювер открывается в первый раз и перед каждой проекцией или селекцией. Вьюверы выглядят и ведут себя как обычные таблицы. Если выполняется проекция во вьювере без повторений, т.е. выбираются все строки по некоторым столбцам, то могут появиться результаты, которые кажутся повторяющимися. Однако, каждая строка вьювера по прежнему будет уникальной. Например, предположим, что создается вьювер без повторений myview с тремя столбцами a,b, и c, которые содержат следующие величины:
Таблица 9-1: Пример вьювера myview без повторений
a
b
c
1
1
2
1
2
3
1
1
0
Если появится запрос:
select a,b from myview,
то результаты будут выглядеть следующим образом:
a b
--- -----
1 1
1 2
1 1
Первая и третья строки выглядят здесь одинаково. Однако, исходные строки вьювера по прежнему уникальны.
Вьюверы со счетчиками
Пользователь может определить вьювер со столбцом счетчика, например:
create view sales_view
as select syb_identity, stor_id
from sales_daily
В операторе выбора можно указать столбец счетчика с помощью ключевого слова syb_identity, если вьювер удовлетворяет следующим условиям:
·В операторе выбора указан только один столбец счетчик;·Вьювер строится на основе только одной таблицы;·Значение счетчика не используется в вычисляемых столбцах;·Вьювер не содержит агрегирующих функций.
Если хотя бы одно из этих условий не имеет места, то SQL Сервер не будет определять этот столбец как счетчик вьювера. Если пользователь в этом случае посмотрит атрибуты вьювера с помощью системной процедуры sp_help, то атрибут IDENTITY для этого столбца будет равен 0.
Использование опции with check в определении вьювера
Обычно, SQL Сервер не проверяет попадают ли модифицируемые операторами insert (вставка) и update (обновление) строки в область видимости вьювера или нет. Оператор может через вьювер вставить строку в базовую таблицу, которую не видно во вьювере, или изменить в строке данные таким образом, что строка не будет удовлетворять условиям отбора во вьювер.
Если вьювер определяется с опцией with check (с проверкой), то при выполнении операторов модификации данных insert и update, будет проводиться проверка видимости данных. Другими словами, при вставке или модификации данных через этот вьювер, они должны оставаться видимыми, в противном случае оператор будет трактоваться как ошибочный.
Далее приводится пример вьювера stores_cal, созданного с опцией with check. В этом вьювере содержатся данные о книжных магазинах, расположенных в Калифорнии, но нет информации о магазинах, расположенных в других штатах. В этот вьювер отбираются все строки из таблицы stores, в которых в поле штат указана величина “CA”:
create view stores_cal
as select * from stores
where state = "CA"
with check option
Когда пользователь вставляет новую строку через вьювер stores_cal, SQL Сервер проверяет удовлетворяет ли новая строка условиям видимости в этом вьювере. Следующий оператор вставки является ошибочным, поскольку втавляется строка, в которой указан штат Нью-Йорк (NY), а не Калифорния:
insert stores_cal
values ("7100", "Castle Books", "351 West 24 St.", "New York", "NY", "USA", "10011", "Net 30")
Когда пользователь модифицирует строку через вьювер stores_cal, SQL Сервер проверяет удовлетворяет ли эта строка условиям видимости в этом вьювере. Следующий оператор обновления является ошибочным, поскольку в нем код штата изменяется с “CA” на “MA”. Если допустить такое обновление, то новая строка не будет видна через этот вьювер.
update stores_cal
set state = "MA"
where stor_id = "7066"
Построение вьювера на основе другого вьювера с опцией with check
Если вьювер был опеределен с опцией with check, то все вьюверы определенные на его базе (вторичные вьюверы) также будут удовлетворять этому условию. Каждая строка, вставляемая или обновляемая во вторичном (derived) вьювере должна быть видна в базовом вьювере.
Рассмотрим вьювер stores_cal30, который определяется на основе вьювера stores_cal. Во вторичный вьювер включается информация о книжых магазинах в Калифорнии с атрибутом payterms, равным “Net 30”:
create view stores_cal30
as select * from stores_cal
where payterms = "Net 30"
Поскольку вьювер stores_cal создавался в опцией with check, то все строки вставляемые или обновляемые во вторичном вьювере stores_cal30 должны быть видны через базовый вьювер stores_cal, т.е. любая строка, в которой указан штат отличный от Калифорнии будет отвергнута.
Заметим, что сам вторичный вьювер stores_cal30 был определен без опции with check. Отсюда следует, что через этот вьювер возможна вставка или обновление строк, у которых поле payterms не равно "Net 30". Следующий оператор обновления будет успешно выполнен, несмотря на то, что обновляемая строка не будет больше видна во вьювере stores_cal30:
update stores_cal30
set payterms = "Net 60"
where stor_id = "7067"
Ограничения вьюверов основанных на внешнем соединении
Вьюверы, определенные через внешнее соединение, имеют некоторые ограничения, которые могут приводить к неожиданным результатам при выборке данных. При использовании таких вьюверов нужно быть особенно внимательным.
Если вьювер определен через внешнее соединение и через него запрашиваются данные по значению в столбце внутренней (inner) таблицы внешнего соединения, то результаты могут отличаться от ожидаемых. Значение, указанное в запросе не будет ограничивать число возвращаемых строк, а будет влиять лишь на возвращение неопределенных значений. В тех строках, в которых значение отличается от указанного, появятся неопределенные значения в соответствующих столбцах внутренней таблицы. Это является следствием того факта, что Transact-SQL формирует внутреннее представление запроса к вьюверу в виде комбинации из определения вьювера и ограничений в запросе.
Например, предположим, что имеются следующие таблицы:
Таблица А
a
1
2
3
Таблица В
b
c
1
10
2
11
6
12
Определим вьювер на основе этих двух таблиц с помощью внешнего соединения:
create view A_B as
select a,b,c from A,B
where A.a*=B.b
Если теперь обратиться к этому вьюверу с приведенным ниже запросом, то получим следующие результаты:
select a,c from A_B where c = 10
a с
---- ----
1 10
2 NULL
3 NULL
(Выбрано 3 строки)
Ограничение (с=10) не повлияло на число выводимых строк. Здесь появились неопределенные значения в столбце внутренней таблицы для каждой строки, в которой либо находиться другое значение (2 строка), либо во второй таблице отсутствует соответствующее внешнему соединению значение (3 строка).
Выборка данных через вьювер
Когда через вьювер пользователь выбирает данные, SQL Сервер проверяет существование всех запрашиваемых в операторе объектов и возможности доступа к ним из данного оператора. Если все проверки закончились успешно, то SQL Сервер объединяет этот оператор с определением вьювера и транслирует его в запрос к базовым таблицам, как уже отмечалось в предыдущем разделе. Этот процесс называется раскрытием вьювера (view resolution).
Рассмотрим следующий оператор с определением вьювера, который уже приводился в этой главе, вместе с запросом к этому вьюверу:
create view hiprice
as select *
from titles
where price > $15
and advance > $5000
select title, type
from hiprice
where type = 'popular_comp'
SQL Сервер объединяет запрос к вьюверу hiprice с его определением и в результате получается следующий запрос:
select title, type
from titles
where price > $15
and advance > $5000
and type = 'popular_comp'
В общем случае через вьювер можно запрашивать данные также как и через обычную таблицу. Здесь также можно использовать соединения, проводить группировку (в предложении group by), включать подзапросы в любых комбинациях. Однако, если вьювер определяется внешним соединением или с агрегирующими функциями, то запрос к этому вьюверу может привести к неожиданным результатам, как это было отмечено в предыдущем разделе “Ограничения вьюверов ...”.
Замечание. Можно выбирать текстовую и графическую информацию из соответствующих столбцов вьювера, но не разрешается использовать команды readtext (считать текст) и writetext (записать текст). Кроме того, через вьювер нельзя выбирать текстовую информацию из столбца, в котором различается регистр символов.
Раскрытие вьювера
Когда пользователь определяет вьювер, SQL Сервер проверяет существование базовых таблиц и вьюверов, преречисленных в предложении from, и выдает сообщение об ошибке при возникновении проблем. Аналогичная проверка проводится при появлении запроса через вьювер.
Между определением вьювера и запросом к нему ситуация может измениться. Например, некоторые из базовых таблиц или вьюверов за это время могут быть уничтожены, или некоторые столбцы вьювера могут быть переименованы.
Для полного раскрытия вьювера SQL Сервер проверяет следующие условия:
·Все базовые таблицы и вьюверы, а также соответствующие столбцы, на которых основан данный вьювер, должны существовать;·Типы данных всех столбцов, от которых зависит данный вьювер, должны быть совместимы с типами данных соответствующих столбцов вьювера;·Если оператор изменяет данные (оператор обновления, вставки или удаления), то он не должен нарушать ограничений на изменение данных, установленных для данного вьювера. Этот вопрос также будет обсуждаться в следующем разделе этой главы.
Если хотя бы одно из этих условий не выполнено, то SQL Сервер выдает сообщение об ошибке.
Переопределение вьюверов
В отличие от многих других систем управления базами данных, SQL Сервер позволяет переопределять вьювер. При этом зависящие от него вьюверы нужно переопределять лишь в том случае, если SQL Сервер не сможет автоматически получить для них новых описаний.
В качестве примера рассмотрим таблицу authors и три вьювера, определенные далее. Каждый последующий вьювер базируется на предыдущем, т.е. view2 базируется на view1, а view3 базируется на view2. В этом смысле view2 зависит от view1, а view3 от обеих предшествующих вьюверов.
Далее после названия каждого вьювера приведено его определение:
view1:
create view view1 as
select au_lname, phone from authors
where postalcode like "94%"
view2:
create view view2 as
select au_lname, phone from view1
where au_lname like "[M-Z]%"
view3:
create view view3 as
select au_lname, phone from view2
where au_lname = "MacFeather"
Таблица authors (авторы), на которой базируются эти вьюверы, состоит из столбцов au_id (код автора), au_lname (фамилия), au_ftname (имя), phone (телефон), address (адрес), city (город), state (штат), postalcode (почтовый код).
Пользователь может удалить вьювер view2 или заменить его другим вьювером с тем же названием, но с другим критерием видимости данных, например, фамилии авторов должны начинаться с букв М-Р. При этом зависимый от него вьювер view3 останется правильным и его не нужно будет переопределять. Когда появляется запрос к view2 или к view3, то раскрытие вьювера происходит обычным образом.
Если переопределить вьювер view2 таким образом, что вьювер view3 не может быть из него получен, то вьювер view3 становиться неправильным и его необходимо переопределить. Например, если в новой версии вьювера view2 содержится только один столбец au_lname вместо двух, то вьювер view3 нельзя будет использовать, поскольку он не имеет доступа к столбцу phone объекта, от которого он зависит.
Тем не менее вьювер view3 будет существовать и его можно будет вновь использовать, если удалить новую версию вьювера view2 и восстановить верию view2 с двумя столбцами au_lname и phone.
Другими словами, можно переопределить промежуточный вьювер без необходимости переопределения зависящих от него вьюверов, если только оператор выбора select в определении зависимого вьювера остается правильным. Если это условие нарушено, то на запрос, который обращается к ошибочному вьюверу, будет выдано сообщение об ошибке.
Переименование вьюверов
Пользователь может переименовать вьювер с помощью системной процедуры sp_rename. Синтаксис вызова этой процедуры имеет следующий вид:
sp_rename objname, newname
Например, для переименования вьювера titleview в bookview можно использовать следующую команду:
sp_rename titles_view, bookview
Конечно, новое название вьювера должно удовлетворять правилам написания идентификаторов (нельзя использовать процедуру sp_rename для присвоения нового названия, заключенного в кавычки). Пользователь может переименовывать лишь те вьюверы, которыми он владеет. Владелец базы данных может переименовать любой вьювер пользователя. Вьювер, название которого изменяется, должен находиться в текущей базе данных.
Изменение и удаление базовых объектов
Если изменяется название базовой таблицы, из которой получен вьювер, то у пользователя могут возникнуть проблемы. Вьюверы, зависящие от этой таблицы, могут продолжать успешно работать и дальше. На самом деле они будут работать до тех пор, пока SQL Сервер может перекомпилировать их. Перекомпиляция проводится по многим причинам, причем без уведомления пользователя, например, после загрузки базы данных, или если пользователь сначала удалил, а затем восстановил таблицу или индекс. Если по каким-либо причинам перекомпиляция не проводилась или оказалась невозможной, то обращение к вьюверу или попытка его модификации могут вызвать неожиданное сообщение об ошибке.
В этом случае пользователь должен удалить этот вьювер, а затем снова определить его так, чтобы отразить в новом определении произошедшие изменения названий базовых объектов. Чтобы избежать этих проблем, лучше всего вообще не переименовывать таблицы и вьюверы, от которых зависят другие вьюверы, или сразу после переименования проводить переопределение всех зависимых объектов.
Аналогичная ситуация возникает, если вьювер зависит от таблицы, которая была удалена. Если обратиться к такому вьюверу, то SQL Сервер выдаст сообщение об ошибке. Однако, если будет создана новая таблица, которая заменяет старую, то вьювер вновь можно будет использовать.
Если вьювер определяется предложением select * и затем изменяется структура базовой таблицы путем добавления некоторых столбцов, то новые столбцы не будут видны в этом вьювере, поскольку звездочка интерпретируется как полный список столбцов существующих на момент создания вьювера. Чтобы увидеть новые столбцы, нужно удалить этот вьювер, а затем переопределить его.
Изменение данных через вьювер
Хотя SQL Сервер не накладывает никаких ограничений на выборку данных через вьювер, и хотя язык Transact-SQL накладывает минимальные ограничения на изменение данных через вьювер по сравнению с другими версиями языка SQL, тем не менее существуют следующие виды модификации данных, которые нельзя проводить через вьювер:
·Нельзя проводить обновление, вставку и удаление данных из столбцов, значения в которых вычисляются по формулам или с помощью встроенных функций;·Нельзя проводить обновление, вставку и удаление данных через вьювер, в котором вычисляются итоговые значения или групповые итоговые значения;·Нельзя проводить обновление, вставку и удаление данных через вьювер, который определяется с опцией distinct (различные);·Оператор вставки insert можно использовать лишь в том случае, если все столбцы базовой таблицы, в которых не допускаются неопределенные значения (NOT NULL), включены во вьювер, через который происходит вставка, поскольку в противном случае SQL Сервер не может присвоить соответствующим полям базовой таблицы исходные значения;·Если вьювер определен с опцией with check option, то все вставляемые или обновляемые через него (или через любой зависимый от него вьювер) строки должны удовлетворять условиям видимости в этом вьювере;·Нельзя удалять (оператором delete) данные через мультитабличный (т.е. определенный на основе нескольких таблиц) вьювер;·Нельзя вставлять (оператором insert) данные через мультитабличный вьювер, определенный с опцией with check option;·Можно обновлять (оператором update) данные через мультитабличный вьювер, определенный с опцией with check option. Это обновление будет ошибочным лишь в том случае, если изменяется значение в столбце, который встречается в одном из выражений предложения where вместе с названиями столбцов из других таблиц;·Нельзя использовать операторы insert и update для мультитабличных вьюверов, определенных с опцией distinct (различные);·Нельзя обновлять (оператором update) значение в поле счетчика (IDENTITY). Владелец таблицы или владелец базы данных или системный администратор могут вставлять точно указанное значение в поле счетчика, если для этой таблицы установлена опция identity_insert;·Если вставляется или обновляется строка через мультитабличный вьювер, то все изменяемые столбцы должны принадлежать к одной таблице;·Команду writetext нельзя применять к тектовым (text) или графическим (image) полям вьювера.
Когда выполняется оператор обновления, вставки или удаления, то SQL Сервер проверяет, что ни одно из вышеупомянутых условий не имеет места и что не нарушаются условия целостности данных.
Почему через некоторые вьюверы можно обновлять данные, а через другие нельзя ? Чтобы лучше понять возникающие здесь ограничения, рассмотрим по примеру на каждый тип вьювера, через который нельзя обновлять данные.
Ограничения на обновление через вьюверы
Вычисляемые столбцы в определении вьювера
Первое ограничение касается вычисляемых столбцов во вьюверах или столбцов, значения в которых вычисляются через встроенные функции. Рассмотрим, например, столбец amt_due во вьювере accounts, который был определен ранее следующим образом.
create view accounts (title_id, advance, amt_due)
as select titles.title_id, advance, (price * royalty/100) * total_sales
from titles, roysched
where price > $15
and advance > $5000
and titles.title_id = roysched.title_id
and total_sales between lorange and hirange
Через этот вьювер видны следующие строки:
select * from accounts
title_id advance amt_due
--------- -------- ---------
PC1035 7,000.00 32,240.16
PC8888 8,000.00 8,190.00
PS1372 7,000.00 809.63
TC3218 7,000.00 785.63
(Выбрано 4 строки))
Нельзя модифицировать и вставлять данные в столбец amt_due, поскольку невозможно определить соответсвующие значения для столбцов price и royalty, исходя из введенного в столбец amt_due значения. Операция удаления здесь вообще не имеет смысла, поскольку отсутствует само значение, которое надо удалить.
Предложения group by и compute by в определении вьювера
Второе ограничение касается вьюверов, содержащих агрегирующие функции (подведение итогов), т.е. вьюверов, определение которых содержит конструкции group by или compute by. Далее приведен пример вьювера с конструкцией группировки (group by) и показаны видимые через него данные:
create view categories (category, average_price)
as select type, avg(price)
from titles
group by type
select * from categories
category average_price
------------- -------------
UNDECIDED NULL
business 13.73
mod_cook 11.49
popular_comp 21.48
psychology 13.50
trad_cook 15.96
(Выбрано 6 строк)
Не имеет смысла вставлять строки через вьювер categories, поскольку непонятно к какой группе в базовой таблице отнести вставляемую строку. Кроме того, обновление значений в столбце average_price (средняя цена) также не имеет смысла, поскольку невозможно подогнать базовые значения цены под введенное значение.
Теоретически можно представить себе обновление и удаление через этот вьювер, но SQL Сервер не поддерживает эти возможности.
Неопределенные значения в базовых объектах
Третье ограничение касается оператора вставки (insert), когда в базовых таблицах или вьюверах имеются столбцы, в которых нельзя использовать неопределенное значение (NOT NULL).
Например, предположим, что в некотором столбце базовой таблицы вьювера нельзя использовать неопределенное значение. Обычно, когда вставляются строки через вьювер, устанавливается неопределенное значение во всех столбцах базовой таблицы, которые не видны во вьювере. Если в столбце нельзя использовать неопределенное значение, то нельзя допускать и вставок через такой вьювер.
Рассмотрим следующий вьювер:
create view titleview
as select title_id, price, total_sales
from titles
where type = 'business'
Неопределенные значения не разрешены в столбце title базовой таблицы titles, поэтому и вставка через вьювер titleview не разрешена. Хотя столбец title даже не упоминается во вьювере, тем не менее запрет на использование в нем неопределенных значений делает любую вставку через этот вьювер ошибочной.
Аналогично, если столбец title_id имеет уникальный индекс, то любое обновление или вставка через вьювер, которые приводят к потере уникальности значений в этом столбце базовой таблицы, будут ошибочными, хотя возможно во вьювере все значения в этом столбце будут различными.
Вьюверы, определенные с опцией with chek option
Четвертое ограничение касается модификации данных через вьювер, определенный с опцией проверки (with check). В таком вьювере любая вставляемая или обновляемая строка должна быть видима через этот вьювер. Это правило действует как для модификации данных осуществляемой непосредственно через этот вьювер, так и для модификации данных, осуществляемой через зависимые от него вьюверы.
Мультитабличные вьюверы
Пятое ограничение касается модификации данных проводящейся, через вьюверы, в которых соединяются данные из нескольких таблиц (мультитабличные вьюверы). SQL Сервер не допускает удаления данных через такие вьюверы, но позволяет их вставлять и обновлять, что обычно не допускается в других системах.
Пользователь может вставлять и обновлять данные через мультитабличный вьювер, если выполняются следующие условия:
·Вьювер определен без опции with check option;·Все столбцы, в которые вставляются или в которых изменяются данные, должны принадлежать одной базовой таблице.
Например, рассмотрим следующий вьювер, в который включены столбцы из таблиц titles и publishers и который не имеет опции проверки модификации with check option:
create view multitable_view
as select title, type, titles.pub_id, state
from titles, publishers
where titles.pub_id = publishers.pub_id
Каждый оператор вставки или обновления должен модифицировать данные или только из таблицы titles или только из таблицы publishers. Следующий оператор обновления является правильным:
update multitable_view
set type = "user_friendly"
where type = "popular_comp"
Но нижеприведенный оператор является неправильным, поскольку в нем обновляются столбцы как из таблицы titles, так и из таблицы publishers:
update multitable_view
set type = "cooking_trad",
state = "WA"
where type = "trad_cook"
Вьюверы со столбцами-счетчиками
Последнее ограничение касается модификации данных через вьюверы, которые содержат столбцы-счетчики (IDENTITY column). По определению значения в таком столбце изменять нельзя. При обновлении через вьювер нельзя обновлять значение в столбце-счетчике.
Вставка данных в столбец-счетчик разрешена только владельцу таблицы, владельцу базы данных или системному администратору. Чтобы вставить значение в столбец-счетчик через вьювер, нужно установить для этого вьювера опцию identity_insert.
Удаление вьюверов
Чтобы удалить вьювер из базы данных, нужно выполнить команду drop view (удалить вьювер). Эта команда имеет следующий вид:
drop view [[база_данных.]владелец.]название_вьювера
[, [[база_данных.]владелец.]название_вьювера] ...
Как видно из этого описания, пользователь может удалить сразу несколько вьюверов. Только владелец вьювера (или владелец базы данных) может удалить его.
Далее приводится команда удаления вьювера hiprice:
drop view hiprice
Когда выполняется команда drop view, название указанного вьювера удаляется из системных таблиц sysprocedures, sysobjects, syscolumns, syscomments, sysprotects sysdepends. Все права на этот вьювер также анулируются.
Если вьювер зависит от таблицы или от другого вьювера, которые были удалены, то SQL Сервер выдает сообщение об ошибке при попытке его использования. Если создается новая таблица или вьювер с тем же названием вместо удаленного, то зависимый вьювер можно вновь использовать, до тех пор пока существуют все столбцы, указанные в определении вьювера.
Использование вьюверов для обеспечения безопасности
Права на доступ к данным через вьювер должны быть явно предоставлены или отняты независимо от прав доступа к базовым таблицам, на которых построен этот вьювер. Данные из базовой таблицы, которые не видны во вьювере, являются скрытыми для тех пользователей, кто имеет права доступа ко вьюверу, но не имеют прав доступа к базовой таблице.
Например, можно ограничить доступ некоторым пользователям к столбцам таблицы titles, содержащим финансовую информацию. Для этого можно создать вьювер на базе этой таблицы, в котором не видны соответствующие столбцы, и предоставить всем пользователям право пользоваться этим вьювером, а к базовой таблице предоставить доступ только сотрудникам отдела продаж. Это можно сделать следующим образом:
revoke all on titles to public
grant all on bookview to public
grant all on titles to sales
Более подробную информацию о том, как предоставлять (grant) и отнимать (revoke) права, можно посмотреть в Руководстве пользователя по средствам ограничения доступа SQL Сервера.
Получение информации о вьюверах
Несколько системных процедур предоставляют информацию из системных таблиц об имеющихся вьюверах.
Пользователь может получить отчет о вьювере с помощью системной процедуры sp_help. Например:
sp_help hiprice

Name Owner type Created_on
--------- ------ ----- -------------------
hiprice dbo view Feb 12 1987 11:57AM

Data_located_on_segment When_created
---------------------------- --------------------

Column_name Type Length Precision Scale
------------ ------- -------- --------- ---------
title_id tid 6 NULL NULL
title varchar 80 NULL NULL
type char 12 NULL NULL
pub_id char 4 NULL NULL
price money 8 NULL NULL
advance money 8 NULL NULL
royalty int 4 NULL NULL
total_sales int 4 NULL NULL
notes varchar 200 NULL NULL
pubdate datetime 8 NULL NULL
Null Default_name Rule_name Identity
------ -------------- --------- --------
0 NULL NULL 0
0 NULL NULL 0
0 NULL NULL 0
1 NULL NULL 0
1 NULL NULL 0
1 NULL NULL 0
1 NULL NULL 0
1 NULL NULL 0
1 NULL NULL 0
0 NULL NULL 0
No defined keys for this object.
(return status = 0)
Чтобы увидеть определение вьювера, которое было помещено в оператор создания вьювера (creat view), нужно выполнить системную процедуру sp_helptext:
sp_helptext hiprice
----------
1
(1 row affected)
text
-----------------------
create view hiprice
as select *
from titles
where price > $15
and advance > $5000
Системная процедура sp_depends перечисляет все объекты, от которых зависит данная таблица или вьювер и которые расположены в текущей базе данных, а также все объекты, которые зависят от данной таблицы или вьювера. Например:
sp_depends titles
Things inside the current database that reference the object (Объекты текущей базы данных, которые зависят от данного объета).
object type
------------- --------------------------
dbo.hiprice view
dbo.titleview view
dbo.reptq1 stored procedure
dbo.reptq2 stored procedure
dbo.reptq3 stored procedure