лабораторный практикум. Русакова М.С. Лабораторный практикум. Практикум по базам данных самара 2015 министерство образования и науки российской федерации
 Скачать 0.72 Mb.
|
|
Создание представления Представление создается путем включения подзапроса в команду CREATE VIEW. Сокращенный синтаксис: CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW представление [(псевдоним [, псевдоним]…)] AS подзапрос [WITH CHECK OPTION [CONSTRAINT ограничение]] [WITH READ ONLY] Здесь: FORCE создание представления независимо от того, существуют ли базовые таблицы; NOFORCE создание представления только при условии существования базовых таблиц. Значение принято по умолчанию; представление имя представления; псевдоним имена выражений, выбранных в запросе для представления. Количество псевдонимов должно быть равным количеству выражений, выбранных представлением; подзапрос полная команда SELECT. Для столбцов в списке SELECT можно использовать псевдонимы; 40 WITH CHECK OPTION режим, при котором добавлять или обновлять можно только строки, доступные в представлении; ограничение имя, присвоенное ограничению CHECK OPTION; WITH READ ONLY запрет применения к данному представлению операций DML. Указания Запрос, который определяет представление, может содержать команду SELECT со сложным синтаксисом, включая соединения, группы и подзапросы. Запрос, который определяет представление, не может содержать предложение ORDER BY. Если вы не указываете имя ограничения сами, система присвоит его по умолчанию в формате SYS_Сn. Для изменения определения представления без его удаления и создания заново, а также для изменения предоставленных привилегий можно использовать режим OR REPLACE. Существуют два вида представлений: простые и сложные. Основное отличие связано с операциями DML. Таблица 21. Сравнительные характеристики простых и сложных представлений Характеристика Простые представления Сложные представления Количество таблиц Только одна Одна или более Содержит функции Нет Да Содержит группы данных (DISTINCT или групповые функции) Нет Да Операции DML над представлением Да Нет Пример CREATE VIEW lease_vw AS SELECT NLease, AdR, LDate FROM lease / SELECT 2 FROM lease_vw / Указать имена столбцов можно путем включения псевдонимов столбцов в подзапрос. Еще один способ указания имен столбцов – это включить псевдонимы столбцов в предложение CREATE VIEW. Для изменения определения представления используйте предложение CREATE OR REPLACE. Пример CREATE VIEW lease_vw AS SELECT NLease Number, AdR Address, LDate Date_Dogovor FROM lease; 41 Пример CREATE OR REPLACE VIEW lease_vw (NLease, AdR, LDate) AS SELECT NLease, Non, NTn FROM lease / Для вывода данных из двух и более таблиц используется сложное представление. Пример CREATE VIEW renta_vw (Rent_Type, Rent_Address, Tn_Name, Min_Rn) AS SELECT realty.Type, lease.AdR, tenant.Tn, rent.Rn FROM realty, lease, tenant, rent WHERE lease.NTn = tenant.NTn AND lease.AdR = realty.AdR AND realty.Type = rent.Type / Выполнение операции DML над представлением Операции DML могут выполняться над представлениями в соответствии со следующими правилами. Удаление строки из представления возможно, если представление не содержит ничего из следующего: o условие соединения; o групповые функции; o предложение GROUP BY; o команда DISTINCT. Данные в представлении могут быть изменены, если представление не содержит ничего из вышеперечисленного и ничего из следующего списка: o столбцы, описанные как выражения, например, Rn*1.5; o псевдостолбец ROWNUM. Добавление данных через представление возможно, если оно не содержит ничего из вышеперечисленного и если в базовой таблице нет столбцов типа NOT NULL, не включенных в представление. Все необходимые значения должны присутствовать в представлении. Помните, что через представление вы добавляете значения прямо в основную таблицу. Можно сделать так, чтобы при добавлении или обновлении данных через простое представление можно было запрашивать добавленные или обновленные данные через представление. Можно сделать так, чтобы выполнение операции DML через представление было невозможно. Для этого представление создается с режимом WITH READ ONLY. 42 Пример CREATE OR REPLACE VIEW lease_vw AS SELECT *FROM Lease WHERE Non = 2 WITH CHECK OPTION CONSTRAINT lease_vw_ck / UPDATE lease_vw SET NOn = 10000 WHERE NLease = 3 / Пример CREATE OR REPLACE VIEW lease_vw (NLease, AdR, LDate) AS SELECT NLease, Non, NTn FROM Lease WHERE Non = 2 WITH READ ONLY / DELETE FROM lease_vw WHERE NLease = 3 / Вывод имен и структур представления Увидеть имена представлений и предложения SELECT, определяющие представления, можно в таблице словаря данных USER_VIEWS. Пример DESC user_views; SELECT * FROM user_views / Для удаления представления используйте команду DROP VIEW. Эта команда удаляет определение представления из базы данных. Удаление представления не изменяет таблиц, на основе которых оно было создано. Другие представления или приложения, созданные на базе удаленных представлений, становятся недействительными и не могут далее использоваться. Удалить представление может только тот, кто его создал, или пользователь с привилегией DROP ANY VIEW. Синтаксис: DROP VIEW представление; Здесь: представление имя представления. 43 Задания На основе таблицы owner создайте простое представление, в котором хранятся фамилии и адреса владельцев. Присвойте столбцам представления осмысленные названия. Выведите на экран содержимое представления. Напишите скрипт-файл для вывода на экран определения представления. Передайте скрипт-файлу имя представления. Сохраните этот скрипт-файл и выполните его. Измените при помощи представления адрес одного из владельцев недвижимости. На основе таблиц tenant, owner, lease и rent создайте представление для вывода полных данных о каждом объекте недвижимости: тип, адрес, стоимость, дата договора, фамилии владельцев и арендаторов. Выведите структуру и содержимое представления, а также определение представления. Получите при помощи представления фамилии всех владельцев определенного типа недвижимости. Тип недвижимости задается с клавиатуры. Получите при помощи представления все адреса определенного типа недвижимости, потом фамилии всех арендаторов определенного типа недвижимости. Измените предыдущее представление так, чтобы оно содержало только данные по одному самому распространенному типу недвижимости. Добавьте ограничение, запрещающее изменять тип недвижимости. Выведите содержимое нового представления. Попробуйте изменить тип недвижимости. Измените представление в задании 1 так, чтобы строки в нем можно было просматривать только между 11:00 и 17:00. Требования к сдаче лабораторной работы Каждое задание должно быть сохранено в отдельный командный файл. После проверки преподавателем ваших заданий удалите все представления из словаря данных. 44 Лабораторная работа №6 Управление доступом пользователей В многопользовательской среде необходимо заботиться о безопасности базы данных. Система безопасности сервера Oracle позволяет: управлять доступом к базе данных; предоставлять доступ к определенным объектам базы данных; просматривать выданные и полученные привилегии в словаре данных Oracle; создавать синонимы для объектов базы данных. Безопасность базы данных можно разделить на 2 части: безопасность системы и безопасность данных. Безопасность системы охватывает доступ к базе данных и пользование базой данных на системном уровне, т.е. имя и пароль пользователя, дисковое пространство, выделяемое пользователю, и системные операции, разрешенные пользователю. Безопасность данных включает доступ к объектам базы данных, их использование и действия, которые может выполнять пользователь с этими объектами. Привилегии Привилегии – это права на выполнение определенных команд SQL. Самый высокий уровень пользователя – это администратор базы данных. Он имеет право разрешать другим пользователям доступ к базе данных и ее объектам. Пользователям необходимы системные привилегии для получения доступа к базе данных вообще и привилегии на объекты для манипулирования содержимым объектов в базе данных. Пользователям может быть также предоставлена привилегия на выдачу дополнительных привилегий другим пользователям или ролям, которые являются именованными группами связанных привилегий. Схема Схема – это совокупность таких объектов, как таблицы, представления и последовательности. Владельцем схемы является пользователь базы данных. Схема имеет такое же имя, как пользователь. Создание пользователя Администратор базы данных регистрирует нового пользователя сервера и предоставляет ему ряд системных привилегий. От этих привилегий зависит, что может делать данный пользователь на уровне базы данных. Для регистрации пользователя администратор базы данных использует команду CREATE USER. После выполнения просто этой команды пользователь системных привилегий не имеет. Сокращенный синтаксис: CREATE USER пользователь IDENTIFIED BY пароль; Здесь: пользователь имя регистрируемого пользователя; пароль пароль, с которым пользователь должен входить в систему. 45 Системные привилегии Существует более 80 видов привилегий, предоставляемых пользователям и ролям. Системные привилегии обычно предоставляются администратором базы данных. Зарегистрировав пользователя, администратор базы данных может предоставить ему привилегии. Администратор базы данных предоставляет системные привилегии пользователям с помощью команды GRANT. Пользователь может пользоваться предоставленными привилегиями сразу после их получения. Таблица 22. Обычные привилегии администратора базы данных Системная привилегия Разрешенные операции CREATE USER Позволяет регистрировать других пользователей Oracle (необходимая для роли администратора базы данных) DROP USER Позволяет удалять других пользователей DROP ANY TABLE Позволяет удалять таблицы из любой схемы BACKUP ANY TABLE Позволяет копировать любую таблицу в любой схеме с помощью утилиты экспорта Таблица 23. Основные привилегии пользователя Системная привилегия Авторизованные операции CREATE SESSION Позволяет начать сеанс работы с базой данных CREATE TABLE Позволяет создавать таблицы в пользовательской схеме CREATE SEQUENCE Позволяет создавать последовательности в пользовательской схеме CREATE VIEW Позволяет создавать представления в пользовательской схеме CREATE PROCEDURE Позволяет создавать хранимые процедуры, функции или пакеты в пользовательской схеме Сокращенный синтаксис: GRANT привилегия [, привилегия…] TO пользователь [, пользователь…]; Здесь: привилегия предоставляемая системная привилегия; пользователь имя пользователя. Роли Роль – это именованная группа взаимосвязанных привилегий, которая может быть предоставлена пользователю. Этот метод упрощает процесс предоставления и отмены привилегий. Пользователь может иметь доступ к нескольким ролям, а одна и та же роль может быть предоставлена многим пользователям. Обычно роли создаются для приложений базы данных. 46 Создание и присвоение роли: CREATE ROLE роль; Здесь: роль имя создаваемой роли. Теперь администратор может назначить данной роли системные привилегии, а также назначить пользователям данную роль. Пример CREATE ROLE manager / GRANT create table, create view TO manager / GRANT manager TO las, sirvp, volena / Изменение своего пароля Каждый пользователь имеет пароль, присвоенный ему администратором баз данных при регистрации. Изменить свой пароль можно при помощи команды ALTER USER. Синтаксис: ALTER USER пользователь IDENTIFIED BY пароль; Здесь: пользователь имя пользователя; пароль новый пароль. Примечание. Кроме изменения своего пароля эта команда предоставляет и другие возможности, но для их использования необходима привилегия ALTER USER. Привилегии на объекты Таблица 24. Привилегии на объекты Привилегии на объект Таблица Представ- ление Последова- тельность Процедура Снимок ALTER Х Х DELETE Х Х EXECUTE Х INDEX Х INSERT Х Х REFERENCES Х SELECT Х Х Х Х UPDATE Х Х 47 Администратор баз данных может разрешать пользователям выполнять конкретные действия над определенными таблицами, представлениями, последовательностями, процедурами. Владелец объекта имеет на него все привилегии. Чтобы предоставить доступ к своим объектам другим пользователям, необходимо использовать команду GRANT. Синтаксис: GRANT {привилегия (, привилегия…) | ALL}[(столбцы)] ON объект TO {пользователь (, пользователь…) | роль | PUBLIC} [WITH GRANT OPTION] Здесь: привилегия предоставляемая привилегия на объект; ALL все привилегии на объект; столбцы столбец таблицы или представления, на который даются привилегии; ON объект объект, на который предоставляются привилегии; TO кому предоставляются привилегии; PUBLIC привилегии на объект предоставляются всем пользователям; WITH GRANT OPTION позволяет пользователю, получившему привилегии на объект, передавать их другим пользователям и ролям. Примечание. Что касается процедур, то речь идет об одиночных процедурах, функциях и конструкциях в общедоступных пакетах. Привилегии INDEX и REFERENCES ролям не предоставляются. Указания Чтобы предоставлять привилегии на объект, объект должен находиться в вашей схеме или вы сами должны были получить эти привилегии с правом передачи (WITH GRANT OPTION). Владелец объекта может предоставить любую привилегию на свой объект любому пользователю или роли. Владелец объекта автоматически получает все привилегии на свой объект. Пример GRANT select ON lease TO las, volena / GRANT UPDATE (NTn, Tn, AdT) ON tenant TO las, manager / 48 Предложение WITH GRANT OPTION Пользователь, получивший привилегию с опцией WITH GRANT OPTION, может предоставлять эту привилегию другим пользователям. Привилегия на объект, предоставленная другому пользователю в режиме WITH GRANT OPTION, отменяется тогда, когда она отменяется для того, кто ее выдал. Пример GRANT select, insert ON lease TO las, volena WITH GRANT OPTION / Ключевое слово PUBLIC С помощью ключевого слова PUBLIC владелец таблицы может предоставить доступ к ней всем другим пользователям. Пример GRANT select ON lease TO public / Просмотр предоставленных привилегий, отмена привилегий Если вы попытаетесь выполнить неразрешенную операцию, например, удалить строку из таблицы, на которую у вас нет привилегии DELETE, то сервер Oracle не позволит вам это сделать. Сообщение от сервера «таблица или представление не существует» может означать следующее: таблицы или представления с таким именем не существует; вы пытались обратиться к таблице или представлению, на которые у вас нет соответствующей привилегии. Свои привилегии можно посмотреть в словаре данных. Таблица 25. Таблицы словаря данных, хранящие сведения о привилегиях. Таблица словаря данных Описание ROLE_SYS_PRIVS Системные привилегии, предоставленные ролям ROLE_TAB_PRIVS Привилегии на таблицы, предоставленные ролям USER_ROLE_PRIVS Роли, доступные пользователю USER_TAB_PRIVS_MADE Привилегии, предоставленные пользователю на его объекты USER_TAB_PRIVS_RECD Привилегии на чужие объекты, предоставленные пользователю USER_COL_PRIVS_MADE Привилегии, предоставленные пользователем на определенные столбцы его объектов USER_COL_PRIVS_RECD Привилегии, предоставленные пользователю на определенные столбцы чужих объектов Отмена привилегий, предоставленных другим пользователям, производится при помощи команды REVOKE, отменяющей указанные вами привилегии для всех указанных пользователей, которым они были предоставлены. 49 Синтаксис: REVOKE {привилегия [, привилегия…] | ALL} ON объект FROM {пользователь [, пользователь…] | роль | PUBLIC} [CASCADE CONSTRAINTS] Здесь: CASCADE CONSTRAINTS используется для отмены ограничений, наложенных на объект с помощью привилегии REFERENCES для сохранения ссылочной целостности. Пример REVOKE select ON lease FROM las, volena / Создание синонима объекта Для ссылки на таблицу, принадлежащую другому пользователю, необходимо добавить к имени таблицы префикс в виде имени пользователя, создавшего таблицу, и точки. Создание синонима устраняет необходимость указания схемы в имени объекта и обеспечивает альтернативное имя для таблицы, представления, последовательности, процедуры или других объектов. Метод особенно полезен для длинных имен объектов – например, имен представлений. Синтаксис: CREATE [PUBLIC] SYNONYM синоним FOR объект; Здесь: PUBLIC создает синоним, доступный всем пользователям; синоним имя создаваемого синонима; объект объект, для которого создается синоним. Указания Объект не может быть частью пакета. Имя личного синонима должно отличаться от имен всех других объектов данного пользователя. Пример CREATE SYNONYM my_lease FOR volena.lease / CREATE PUBLIC SYNONYM my_lease FOR volena.lease / Для удаления синонима используется команда DROP SYNONYM. Синоним PUBLIC может удалить только администратор базы данных. |