бд. метод указ Проектирование БД. Методическое указания для выполнение лабораторных работ по дисциплине
 Скачать 0.74 Mb.
|
|
3.3 Триггеры Триггер – это откомпилированная процедура, используемая для выполнения действий, инициируемых происходящими в базе данных событиями. Триггер представляет собой сохраненную в базе данных процедуру, которая запускается тогда, когда в отношении таблицы выполняются определенные действия. Триггер может выполняться до или после операторов INSERT, UPDATE или DELETE. Наиболее распространенное применение триггеров – это проверка сложных критериев в базе данных. Триггер - особый инструмент SQL-сервера, поддерживающий целостность данных в БД . Рассмотрим простой пример. Стипендия студента не может превышать 15000. Определим триггер, который не позволял бы вводить сумму стипендии больше 15000. Для создания триггера с помощью утилиты SQL Server Enterprise Manager необходимо выбрать таблицу Progressвсписке объектов базы данных, после чего выполнить команду контекстногоменю Аlltasks (Все задачи) - ManageTriggers. Эти действия приведут к открытию диалогового окна свойств триггера. CREATE TRIGGER tri_stip ON stud FOR INSERT, UPDATE AS /*описываются локальные переменные*/ DECLARE @stip smallmoney /* определяется информация о вставляемых записях*/ SELECT @stip = U.stud_stip FROM Inserted U IF @stip >15000 BEGIN /*Команда ROLLBACK используется для того, чтобы отменить модификацию данных в случае, если база заблокирована*/ ROLLBACK TRAN RAISERROR ('Стипендия студента не может превышать 15000', 16,10) END Определим триггер tri_ins_progress для таблицы Progress, который будет запускаться каждый раз, когда запись вставляется в таблицу Progress или модифицируется. Если экзамен или зачет сданы не в срок (например, после 15-го числа месяца), то запись не принимается: CREATE TRIGGER tri_ins_progress ON Progress FOR INSERT, UPDATE AS /*описываются локальные переменные*/ DECLARE @nDayOfMonth TINYINT /* определяется информация о вставляемых записях*/ SELECT @nDayOfMonth = DATEPART (Day, I.Pr_DATE) FROM Progress P, Inserted I WHERE P.Stud_ID = I.Stud_ID AND P.Ocenka = I.Ocenka /*проверяется условие вставки записи*/ /*и при необходимости сообщается об ошибке*/ IF @ nDayOfMonth >15 BEGIN /*Команда ROLLBACK используется для того, чтобы отменить модификацию данных в случае, если база заблокирована*/ ROLLBACK TRAN RAISERROR ('Вводить оценки, полученные до 15-го числа', 16,10) END Попробуйте ввести в таблицу Progress данные об оценках, полученных студентами позже 15-го числа. Триггер можно удалить следующим образом: DROP TRIGGER имя_триггера или из контекстного меню Аll tasks (Все задачи) - Manage Triggers выбором имени триггера из списка. Триггер на добавление студента: CREATE TRIGGER tr_ins_stud ON Students FOR INSERT AS DECLARE @grup integer SELECT @grup=I.Grup_ID FROM Inserted I UPDATE Gruppa SET Grup_KOLSTUD = Grup_KOLSTUD+1 WHERE Gruppa.Grup_ID=@grup Пусть нам необходимо знать, кто (пользователь) и когда производил изменения оценок студентов. Для этого необходимо создать таблицу журнала и собственно тригер. Создайте таблицу для журналирования изменений CREATE TABLE journ ( mod_oper CHAR(20), /* Тип производимой операции */ mod_datetime DATEtime, /*Дата изменеия */ mod_user VARCHAR(30), /*Пользователь БД */ mod_id INTEGEr, /* id студента чья оценка была изменена*/ mod_ocen integer /* Измененая оценка*/ old_ocen integer ) Тригер, регистрирующий изменения оценки: CREATE TRIGGER treg ON Progress FOR Update AS DECLARE @id int, @ocen int, @old_ocen int SELECT @old_ocen=P.ocenka,@id = P.stud_id , @ocen = U.ocenka FROM Progress P, Inserted U INSERT INTO journ VALUES('Обновлена',Current_timestamp,Current_USER,@id, @ocen,@old_ocen); Данный тригер будет вносить информацию о изменениях оценок студентов. Контрольные вопросы 1. Может ли сохраненная процедура вызывать другую сохраненную процедуру? 2. В чем преимущества использования процедур? 3. В чем преимущества использования функций? 4. Каковы различия между процедурами и функциями? 5. Когда выполняются триггеры – до или после выполнения команд INSERT, UPDATE и DELETE? 6. Можно ли изменить триггер? Лабораторная работа № 8. Права пользователей. Разработка системы пользователей базы данных 1 Цель работы: научить студентов разрабатывать систему безопасности СУБД и базы данных скриптом и в графической среде. 2 Задание на лабораторную работу Выполните упражнения, приведенные в п.3.1-3.7. Выполните следующие задания: 1. Для работы с базой данных будут использованы две роли – studentABD_XX и teacherABD_XX (XX – номер компьютера, за которым сидит исполнитель работы). Назначить роли teacherABD_XX полный доступ ко всем объектам базы данных на просмотр таблиц и выполнение запросов SELECT, но не на создание новых таблиц и не на изменение данных в таблицах. Назначить роли studentABD_XX доступ владельца базы данных, т.е. полный доступ на чтение и на изменение любых объектов базы данных. Составьте план выполнения необходимых хранимых процедур для того, чтобы выполнить такие действия, на бумаге. 2. Пусть к данной базе данных будут иметь доступ два пользователя роли teacherABD_XX (teachABD1_XX, teachABD2_XX) и один пользователь роли studentABD_XX (studABD1_XX). Составьте план выполнения необходимых хранимых процедур для того, чтобы выполнить такие действия, на бумаге. 3. Задайте от одной до трех учетных записей для доступа к серверу. Составьте план выполнения необходимых хранимых процедур для того, чтобы выполнить такие действия, на бумаге. 4. Откройте CIS в приложении Borland SQL Explorer. 5. В окне запросов ввести по очереди все запросы на создание системы пользователей и учетных записей для доступа к БД. Выполнить эти запросы. 3 Методические указания к выполнению лабораторной работы 3.1 Требования к программному обеспечению: СУБД MS SQL Server должна иметь запись о пользователе user (пароль-user) с привилегиями db_owner для всех объектов в рабочем пространстве CIS. 3.2 Система безопасности СУБД Система безопасности СУБД основывается на таких понятиях как учетная запись, роль, группа, пользователь. Каждый пользователь проходит два этапа проверки системы безопасности при попытке доступа к данным: - этап 1 – аутентификация; - этап 2 – получение доступа к данным. Первый этап относится к уровню работы всего сервера СУБД. На первом этапе пользователь идентифицирует себя с помощью логического имени (login) и пароля (password). Логическое имя и пароль хранятся на сервере СУБД в виде учетной записи (account). Если данные были введены правильно, то считается, что процедура аутентификации пройдена, и данный сервер СУБД разрешает попытку доступа к конкретной базе данных. Однако сама по себе аутентификация не дает пользователю права доступа к каким бы то ни было данным. Для получения доступа к данным необходимо, чтобы учетной записи пользователя соответствовал некоторый пользователь базы данных (database user). Пользователь базы данных – совокупность разрешений и запретов на работу с данными в конкретной базе данных. На втором этапе учетная запись пользователя отображается в пользователя базы данных и получает все привилегии, соответствующие этому пользователю базы данных. Второй этап задействует систему безопасности конкретной базы данных, а не всего сервера СУБД. В разных базах данных одной и той же учетной записи могут соответствовать одинаковые или разные имена пользователя базы данных с разными правами доступа. В том случае, когда учетная запись пользователя не отображается в пользователя базы данных, клиент все же может получить доступ к базе данных под гостевым именем guest (если оно не запрещено администратором БД). Гостевой вход позволяет минимальный доступ к данным только в режиме чтения. Пользователи баз данных могут объединяться в роли (иногда – группы) для упрощения управления системой безопасности. Роли базы данных объединяют нескольких пользователей в административную единицу и позволяют назначать права доступа к объектам базы данных для роли, наделяя этими правами всех участников этой роли. Различают пользовательские и встроенные роли. Встроенные роли создаются автоматически при установке сервера СУБД (и не могут меняться). Различают встроенные роли уровня СУБД и уровня конкретной базы данных. Так, например, в SQL Server 2000 есть такие роли сервера:
SQL Server 2000 поддерживает такие встроенные роли уровня базы данных:
Кроме указанных ролей, существует еще одна, неудаляемая роль – public. Участниками этой роли являются все пользователи, имеющие доступ к базе данных. Нельзя явно указать участников этой роли, т.к. все пользователи уже включены в нее. 3.2.1 Манипулирование элементами системы безопасности СУБД 3.2.1.1 Создание учетной записи Для создания учетной записи в MS SQL Server 2000 используется системная хранимая процедура sp_addlogin: Ее формат таков: sp_addlogin [@loginname=] login [, [@passwd=] password] [, [@defdb=] database] [, [@deflanguage=] language] [, [@sid=] SID] [, [@encryptopt=] encryption_option] где: login - логическое имя (login); password - пароль, который будет ассоциироваться с данной учетной записью; database - база данных по умолчанию. Сразу же после установления соединения с сервером пользователь будет работать в этой базе данных; language - язык сообщений, выдаваемых сервером СУБД пользователю; SID - двоичное число, которое будет являться идентификатором безопасности создаваемой учетной записи. Используя эту возможность, можно создавать на разных серверах СУБД учетные записи с одинаковыми идентификаторами безопасности; encryption_option определяет, будет ли шифроваться пароль данной учетной записи. По умолчанию, пароль шифруется. Пример. Добавить учетную запись teacher к базе данных education. EXEC sp_addlogin teacher, 123, education 3.2.1.2 Создание пользователя базы данных Для создания пользователя базы данных в MS SQL Server 2000 используется системная хранимая процедура sp_grantdbaccess (ранее - sp_adduser). Ее формат таков: sp_grantdbaccess [@loginame=] login [,[@name_in_db=] user [OUTPUT]] Использование параметра OUTPUT заставляет хранимую процедуру поместить значение параметра user созданного пользователя в некоторую переменную, для дальнейшей обработки. Формат процедуры sp_adduser: sp_adduser [@loginname=] login [, [@name_in_db=] user] [, [@grpname=] role] где: login - логическое имя (login), которое необходимо связать с создаваемым пользователем; user - имя создаваемого пользователя базы данных, с которым будет ассоциироваться данная учетная запись. В базе данных не должно быть пользователя с таким именем; role - роль, в которую данный пользователь будет включен. Пример.Добавить нового пользователя teacher_adb к базе данных education. EXEC sp_adduser teacher, teacher_adb, db_owner 3.2.1.3 Создание роли базы данных Для создания роли базы данных в MS SQL Server 2000 используется системная хранимая процедура sp_addrole: Ее формат таков: sp_addrole [@rolename=] role [, [@ownername=] owner] где: role - имя создаваемой роли. Должно быть уникальным в пределах БД; owner - имя владельца роли. Владельцем может быть только роль или пользователь из этой базы данных. По умолчанию, владелец – dbo. Эта процедура не может быть запущена как часть транзакции, определенной пользователем. Исполнять эту процедуру могут только участники роли сервера sysadmin, либо участники ролей db_securityadmin, db_owner. Пример.Добавление роли Managers. |