Избирательный доступ к данным

 

Цель и задачи работы

Целью лабораторной работы является изучение избирательного доступа к данным.

Порядок выполнения работы

- ознакомится с теоретическими сведениями;

- выполнить задание;

- оформить отчет;

- ответить на контрольные вопросы, заданные преподавателем.

Оформление отчета

Отчет должен содержать: титульный лист, цель работы, описание пунктов выполнения лабораторной работы в соответствии с заданием, ответы на контрольные вопросы и выводы по работе.

Теоретические сведения

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

Когда безопасность строк включена в таблице (ALTER TABLE... ENABLE ROW LEVEL SECURITY), все обычные права доступа к таблице для выбора строк или изменения строк должны быть разрешены политикой безопасности строк (однако владелец таблицы обычно не подпадает под действие политики безопасности строк). Если для таблицы не существует политики, используется политика по умолчанию, то есть строки не отображаются и не могут быть изменены. Операции, применяемые ко всей таблице, такие как усечение и ссылки, не подлежат защите строк.

Политики безопасности строк могут быть специфичными для команд, ролей или для обоих типов. Можно указать политику для применения ко всем командам, а также для SELECT, INSERT, UPDATE, или DELETE. Данной политике может быть назначено несколько ролей, и применяются обычные правила членства и наследования ролей.

Чтобы указать, какие строки являются видимыми или изменяемыми в соответствии с политикой, необходимо выражение, возвращающее логический результат. Это выражение будет вычисляться для каждой строки до любых условий или функций, поступающих из запроса пользователя (единственным исключением из этого правила являются leakproof функции, которые гарантированно не пропускают информацию; оптимизатор может выбрать применение таких функций перед проверкой безопасности строк). Строки, для которых выражение не возвращает true не будет обработано. Отдельные выражения могут быть заданы для обеспечения независимого контроля над строками, которые являются видимыми, и строками, которые могут быть изменены. Выражения политики выполняются как часть запроса и с привилегиями пользователя, выполняющего запрос, хотя функции определения безопасности могут использоваться для доступа к данным, недоступным вызывающему пользователю.

Суперпользователи и роли с атрибутом BYPASSRLS всегда обходят систему безопасности строк при обращении к таблице. Владельцы таблиц обычно также обходят безопасность строк, хотя владелец таблицы может выбрать защиту строк с помощью ALTER TABLE... ENABLE ROW LEVEL SECURITY.

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

Политики создаются с помощью команды CREATE POLICY, изменяются с помощью команды ALTER POLICY и удаляются с помощью команды DROP POLICY. Чтобы включить или отключить защиту строк для данной таблицы, используйте команду ALTER TABLE.

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

Когда несколько политик применяются к данному запросу, они объединяются с помощью OR, чтобы строка была доступна, если это позволяет какая-либо политика. Это аналогично правилу, согласно которому данная роль имеет привилегии всех ролей, членом которых она является.

В качестве простого примера рассмотрим, как создать политику для отношения «учетная запись», чтобы разрешить доступ к строкам только членам роли «менеджеры» и только строкам их учетных записей:

CREATE TABLE accounts (manager text, company text, contact_email text); ALTER TABLE accounts ENABLE ROW LEVEL SECURITY; CREATE POLICY account_managers ON accounts TO managers USING (manager = current_user);CREATE POLICY user_policy ON users USING (user_name = current_user);

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

Если роль не указана или используется специальное имя пользователя PUBLIC, политика применяется ко всем пользователям системы. Чтобы разрешить всем пользователям доступ только к их собственной строке в таблице users, можно использовать простую политику:

CREATE POLICY user_sel_policy ON users FOR SELECT USING (true);CREATE POLICY user_mod_policy ON users USING (user_name = current_user);

Чтобы использовать другую политику для строк, добавляемых в таблицу по сравнению с видимыми строками, можно объединить несколько политик. Эта пара политик позволит всем пользователям просматривать все строки в таблице users, но изменять только свои собственные:

CREATE POLICY user_sel_policy ON users

FOR SELECT

USING (true);

CREATE POLICY user_mod_policy ON users

USING (user_name = current_user);

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

Безопасность строк также можно отключить с помощью команды ALTER TABLE. Отключение защиты строк не удаляет политики, определенные в таблице; они просто игнорируются. Затем все строки таблицы становятся видимыми и изменяемыми в соответствии со стандартной системой привилегий SQL.

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

В некоторых контекстах важно убедиться, что безопасность строк не применяется. Например, при резервном копировании это может привести к катастрофическим последствиям, если безопасность строк приведет к тому, что некоторые строки будут опущены из резервной копии. В такой ситуации параметр конфигурации row_security можно отключить. Это само по себе не обходит безопасность строк; то, что он делает, вызывает ошибку, если результаты любого запроса будут отфильтрованы политикой. Затем можно исследовать и устранить причину ошибки.

В приведенных выше примерах выражения политики учитывают только текущие значения в строке для доступа или обновления. Это самый простой и наиболее эффективный случай; когда это возможно, лучше всего разрабатывать приложения безопасности строк для работы таким образом. Если необходимо проконсультироваться с другими строками или другими таблицами для принятия решения о политике, это можно сделать с помощью sub-SELECTs или функций, содержащих SELECTs, в выражениях политики. Однако имейте в виду, что такой доступ может создать условия гонки, которые могут позволить утечку информации, если не принять меры предосторожности. В качестве примера рассмотрим следующую структуру таблицы:

-- definition of privilege groupsCREATE TABLE groups (group_id int PRIMARY KEY,                group_name text NOT NULL); INSERT INTO groups VALUES (1, 'low'), (2, 'medium'), (5, 'high'); GRANT ALL ON groups TO alice; -- alice is the administratorGRANT SELECT ON groups TO public; -- definition of users' privilege levelsCREATE TABLE users (user_name text PRIMARY KEY,               group_id int NOT NULL REFERENCES groups); INSERT INTO users VALUES ('alice', 5), ('bob', 2), ('mallory', 2); GRANT ALL ON users TO alice;GRANT SELECT ON users TO public; -- table holding the information to be protectedCREATE TABLE information (info text,                     group_id int NOT NULL REFERENCES groups); INSERT INTO information VALUES ('barely secret', 1), ('slightly secret', 2), ('very secret', 5); ALTER TABLE information ENABLE ROW LEVEL SECURITY; -- a row should be visible to/updatable by users whose security group_id is-- greater than or equal to the row's group_idCREATE POLICY fp_s ON information FOR SELECT USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));CREATE POLICY fp_u ON information FOR UPDATE USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user)); -- we rely only on RLS to protect the information tableGRANT ALL ON information TO public;

Теперь предположим, что alice хочет изменить «слегка секретную» информацию, но решает, что mallory не следует доверять новому содержанию этой строки, поэтому она делает:

BEGIN;UPDATE users SET group_id = 1 WHERE user_name = 'mallory';UPDATE information SET info = 'secret from mallory' WHERE group_id = 2;COMMIT;

Это выглядит безопасным; нет окна, в котором mallory должна видеть строку «секрет от mallory». Однако здесь есть расовые условия. Если mallory одновременно делает, скажем,

SELECT * FROM information WHERE group_id = 2 FOR UPDATE;

и ее транзакция в режиме Read COMMITTED, она может видеть «секрет от mallory». Это происходит, если ее транзакция достигает информационной строки сразу после alice. Он блокирует ожидание транзакции Алисы для фиксации, а затем извлекает обновленное содержимое строки благодаря предложению FOR UPDATE. Однако он не извлекает обновленную строку для неявного выбора от пользователей, поскольку этот подвыборка не была для обновления; вместо этого строка пользователей считывается с моментальным снимком, сделанным в начале запроса. Поэтому выражение политики проверяет старое значение mallory's уровень привилегий и позволяет ей видеть обновленную строку.

Существует несколько способов обойти эту проблему. Один простой ответ – использовать SELECT... FOR SHARE in sub-SELECTs Однако это требует предоставления привилегий обновления в таблице ссылок (здесь пользователи) для затронутых пользователей, что может быть нежелательным. Кроме того, одновременное использование блокировок общего доступа к строкам в ссылочной таблице может создать проблемы с производительностью, особенно если ее обновления происходят часто. Другим решением, практичным, если обновления ссылочной таблицы происходят нечасто, является монопольная блокировка ссылочной таблицы при ее обновлении, чтобы никакие параллельные транзакции не могли проверять старые значения строк. Или можно просто дождаться завершения всех параллельных транзакций после фиксации обновления ссылочной таблицы и внесения изменений, зависящих от новой ситуации безопасности.

Более подробная информация: PostgreSQL: Documentation: 9.5: Row Security Policies

 

Оборудование

Оборудование: персональный компьютер с установленной операционной системой Windows XP/7/8, браузер (Например, InternetExplorer, GoogleChrome, Opera), СУБД PostgreSQL.

Задание на работу

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

Создайте:

1. Таблицу меток.

2. Таблицу соответствия имени пользователя и метки.

3. Дополнительной столбец в защищаемой таблице, содержащий метку строки.

4. Представление, выбирающее для пользователя только принадлежащих ему данных. Для этого необходимо использовать ключевое слово USER (оно содержит имя ткущего подключенного пользователя).

5. Триггер INSERT для представления, заполняющий дополнительный столбец в защищаемой таблице в зависимости от метки текущего пользователя.

6. А потом сделайте то же самое с использованием встроенной в PostgreSQL+ защиту на уровне строк.

В работе приведите:

1. Запросы на создание необходимых объектов.

2. Проверочные запросы от имени двух разных пользователей.

 

7. Контрольные вопросы

1. Какие подходы к вопросу обеспечения безопасности данных поддерживаются в современных СУБД.

2. К какой группе по умолчанию относится вновь созданный пользователь?

3. Какой оператор используется для предоставления привилегий? Приведите примеры.

4. Какой оператор используется для отмены ранее назначенных привилегий? Приведите примеры.

5. Что такое ограничения полей, ограничение таблиц? Как они используются и для чего?

 

Лабораторная работа № 17

Транзакции и блокировки

Цель и задачи работы

Целью лабораторной работы является изучение понятия целостности базы данных, выполнение транзакций.

Порядок выполнения работы

- ознакомится с теоретическими сведениями;

- выполнить задание;

- оформить отчет;

- ответить на контрольные вопросы, заданные преподавателем.

Оформление отчета

Отчет должен содержать: титульный лист, цель работы, описание пунктов выполнения лабораторной работы в соответствии с заданием, ответы на контрольные вопросы и выводы по работе.

Теоретические сведения

PostgreSQL предоставляет разработчикам богатый набор средств для управления конкурентным доступом к данным. Внутри он поддерживает целостность данных, реализуя модель MVCC (Multiversion Concurrency Control, Многоверсионное управление конкурентным доступом). Это означает, что каждый SQL-оператор видит снимок данных (версию базы данных) на определённый момент времени, вне зависимости от текущего состояния данных. Это защищает операторы от несогласованности данных, возможной, если другие конкурирующие транзакции внесут изменения в те же строки данных, и обеспечивает тем самым изоляцию транзакций для каждого сеанса баз данных. MVCC, отходя от методик блокирования, принятых в традиционных СУБД, снижает уровень конфликтов блокировок и таким образом обеспечивает более высокую производительность в многопользовательской среде.

Основное преимущество использования модели MVCC по сравнению с блокированием заключается в том, что блокировки MVCC, полученные для чтения данных, не конфликтуют с блокировками, полученными для записи, и поэтому чтение никогда не мешает записи, а запись чтению. PostgreSQL гарантирует это даже для самого строгого уровня изоляции транзакций, используя инновационный уровень изоляции SSI (Serializable Snapshot Isolation, Сериализуемая изоляция снимков).

Для приложений, которым в принципе не нужна полная изоляция транзакций и которые предпочитают явно определять точки конфликтов, в PostgreSQLтакже есть средства блокировки на уровне таблиц и строк. Однако при правильном использовании MVCC обычно обеспечивает лучшую производительность, чем блокировки. Кроме этого, приложения могут использовать рекомендательные блокировки, не привязанные к какой-либо одной транзакции.

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

Стандарт описывает следующие особые условия, недопустимые для различных уровней изоляции:

«грязное» чтение: транзакция читает данные, записанные параллельной незавершённой транзакцией.

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

фантомное чтение: транзакция повторно выполняет запрос, возвращающий набор строк для некоторого условия, и обнаруживает, что набор строк, удовлетворяющих условию, изменился из-за транзакции, завершившейся за это время.

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

Уровни изоляции транзакций, описанные в стандарте SQL и реализованные в PostgreSQL, описываются в Таблице 1.

Таблица 1. Уровни изоляции транзакций

Уровень изоляции	«Грязное» чтение	Неповторяемое чтение	Фантомное чтение	Аномалия сериализации
Read uncommited (Чтение незафиксированных данных)	Допускается, но не в PG	Возможно	Возможно	Возможно
Read committed (Чтение зафиксированных данных)	Невозможно	Возможно	Возможно	Возможно
Repeatable read (Повторяемое чтение)	Невозможно	Невозможно	Допускается, но не в PG	Возможно
Serializable (Сериализуемость)	Невозможно	Невозможно	Невозможно	Невозможно

 

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

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

Для выбора нужного уровня изоляции транзакций используется команда SET TRANSACTION.

Для управления параллельным доступом к данным в таблицах PostgreSQL предоставляет несколько режимов явных блокировок. Эти режимы могут применяться для блокировки данных со стороны приложения в ситуациях, когда MVCC не даёт желаемый результат. Кроме того, большинство команд PostgreSQL автоматически получают блокировки соответствующих режимов, защищающие от удаления или изменения задействованных таблиц, несовместимого с характером выполняемой команды. (Например, TRUNCATE не может безопасно выполняться одновременно с другими операциями с этой таблицей, так что во избежание конфликта эта команда получает исключительную блокировку для данной таблицы.)

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

Более подробная информация: PostgreSQL: Документация: 9.5: Управление конкурентным доступом

Оборудование

персональный компьютер с установленной операционной системой Windows XP/7/8, браузер (Например, InternetExplorer, GoogleChrome, Opera), СУБД PostgreSQL.

Задание на работу

1. Продемонстрируйте работу команд COMMIT, ROLLBACK, SAVEPOINT, ROLLBACK TO.

2. Подключитесь к СУБД двумя клиентами. Продемонстрируйте работу всех уровней изоляции. В работе должны быть показаны явные отличия этих уровней изоляции.

3. Покажите, как работают блокировки уровня таблиц и уровня строк.

4. Вызовите deadlock (взаимную блокировку)

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

 

7. Контрольные вопросы

1. Дайте понятие транзакции.

2. Для чего используется ограничения целостности БД?

3. Назовите условия для обеспечения контроля целостности?

4. Какие инструкции языка SQL используются для выполнения транзакций?

5. Опишите модель автоматического выполнения транзакций.

6. Опишите модель управляемого выполнения транзакций.

Лабораторная работа № 18

Аудит

Цель и задачи работы

Целью работы является практическое знакомство с аудитом и получение навыков по детальной настройке аудита и мониторингу событий.

 

Порядок выполнения работы

- ознакомится с теоретическими сведениями;

- выполнить задание;

- оформить отчет;

- ответить на контрольные вопросы, заданные преподавателем.

Оформление отчета

Отчет должен содержать: титульный лист, цель работы, описание пунктов выполнения лабораторной работы в соответствии с заданием, ответы на контрольные вопросы и выводы по работе.

Теоретические сведения

Ниже приведен пример универсальной триггерной функции, используемой для записи изменений в таблицы журнала аудита. Он будет записывать старые и новые записи, затронутую таблицу, пользователя, внесшего изменения, и временную метку для каждого изменения.

-- create a schema named "audit"

CREATE schema audit;

REVOKE CREATE ON schema audit FROM public;

CREATE TABLE audit.logged_actions (

schema_name text NOT NULL,

TABLE_NAME text NOT NULL,

user_name text,

action_tstamp TIMESTAMP WITH TIME zone NOT NULL DEFAULT CURRENT_TIMESTAMP,

action TEXT NOT NULL CHECK (action IN ('I','D','U')),

original_data text,

new_data text,

query text

) WITH (fillfactor=100);

 

REVOKE ALL ON audit.logged_actions FROM public;

 

-- You may wish to use different permissions; this lets anybody

-- see the full audit data. In Pg 9.0 and above you can use column

-- permissions for fine-grained control.

GRANT SELECT ON audit.logged_actions TO public;

 

CREATE INDEX logged_actions_schema_table_idx

ON audit.logged_actions(((schema_name||'.'|| TABLE_NAME)::TEXT));

 

CREATE INDEX logged_actions_action_tstamp_idx

ON audit.logged_actions(action_tstamp);

 

CREATE INDEX logged_actions_action_idx

ON audit.logged_actions(action);

 

--

-- Now, define the actual trigger function:

--

CREATE OR REPLACE FUNCTION audit.if_modified_func() RETURNS TRIGGER AS $body$

DECLARE

v_old_data TEXT;

v_new_data TEXT;

BEGIN

/* If this actually for real auditing (where you need to log EVERY action),

   then you would need to use something like dblink or plperl that could log outside the transaction,

   regardless of whether the transaction committed or rolled back.

*/

 

/* This dance with casting the NEW and OLD values to a ROW is not necessary in pg 9.0+ */

 

IF (TG_OP = 'UPDATE') THEN

   v_old_data:= ROW(OLD.*);

   v_new_data:= ROW(NEW.*);

   INSERT INTO audit.logged_actions (schema_name,table_name,user_name,action,original_data,new_data,query)

   VALUES (TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),v_old_data,v_new_data, current_query());

   RETURN NEW;

ELSIF (TG_OP = 'DELETE') THEN

   v_old_data:= ROW(OLD.*);

   INSERT INTO audit.logged_actions (schema_name,table_name,user_name,action,original_data,query)

   VALUES (TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),v_old_data, current_query());

   RETURN OLD;

ELSIF (TG_OP = 'INSERT') THEN

   v_new_data:= ROW(NEW.*);

   INSERT INTO audit.logged_actions (schema_name,table_name,user_name,action,new_data,query)

   VALUES (TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),v_new_data, current_query());

   RETURN NEW;

ELSE

   RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - Other action occurred: %, at %',TG_OP,now();

   RETURN NULL;

END IF;

EXCEPTION

WHEN data_exception THEN

   RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [DATA EXCEPTION] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;

   RETURN NULL;

WHEN unique_violation THEN

   RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [UNIQUE] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;

   RETURN NULL;

WHEN OTHERS THEN

   RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [OTHER] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;

   RETURN NULL;

END;

$body$

LANGUAGE plpgsql

SECURITY DEFINER

SET search_path = pg_catalog, audit;

--

-- To add this trigger to a table, use:

-- CREATE TRIGGER tablename_audit

-- AFTER INSERT OR UPDATE OR DELETE ON tablename

-- FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func();

--

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

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

Этот триггер аудита не фиксирует действие выбора. Единственный способ аудита SELECT в PostgreSQL ­– через системные журналы, так как триггеры SELECT не поддерживаются, а аудит SELECT не будет работать без автономных транзакций в триггерах, чтобы предотвратить потерю данных аудита при откате.

Более подробная информация: Audit trigger - PostgreSQL wiki, Audit trigger 91plus - PostgreSQL wiki.

 

Оборудование

персональный компьютер с установленной операционной системой Windows XP/7/8, браузер (Например, InternetExplorer, GoogleChrome, Opera), СУБД PostgreSQL.

Задание на работу

1. Реализуйте два варианта аудита событий в базе данных на основе двух представленных ссылок.

2. Разберитесь с описанием. Приведите последовательность выполняемых операций по созданию системы. Покажите на примерах все реализованные вами возможности аудита.

3. Дайте каждому примеру краткий, но емкий комментарий.

Эта работа, как и все предыдущие, должна выполняться в вашей предметной области!

7. Контрольные вопросы

1. Зачем используется аудит?

2. Когда пользователям следует подвергаться аудиту?

3. Как могут быть проконтролированы пользователи?

4. Какие проблемы могут возникнуть с производительностью и сложностью?

5. Для чего используется временная метка?

 

Лабораторная работа № 19

Древовидные структуры

Цель и задачи работы

Целью работы является практическое знакомство с древовидными структурами.

 

Порядок выполнения работы

- ознакомится с теоретическими сведениями;

- выполнить задание;

- оформить отчет;

- ответить на контрольные вопросы, заданные преподавателем.

Оформление отчета

Отчет должен содержать: титульный лист, цель работы, описание пунктов выполнения лабораторной работы в соответствии с заданием, ответы на контрольные вопросы и выводы по работе.

Теоретические сведения

Иерархические запросы (ИЗ) в PostgreSQL реализовано на базе стандратной SQL clause WITH. Не рекурсивный WITH позволяет удешевить повторяющиеся подзапросы, разделить сложный запрос на несколько меньших, является удобным так сказать ярлыком для обращения к подзапросу и само по себе удобно в плане экономии времени при написании кода. В примере ниже удалось избежать использования подзапроса в WHERE за счет применения временой таблицы top_regions сформированой специально для этого запроса.

1. WITH regional_sales AS (

2. SELECT region, SUM(amount) AS total_sales

3. FROM orders

4. GROUP BY region

5.), top_regions AS (

6. SELECT region

7. FROM regional_sales

8. WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)

9.)

10. SELECT region,

11. product,

12. SUM(quantity) AS product_units,

13. SUM(amount) AS product_sales

14. FROM orders

15. WHERE region IN (SELECT region FROM top_regions)

16. GROUP BY region, product;

Добавление необязательного оператора RECURSEVE позволяет запросу в Postgre обращаться к своим же выходным данным. Алгоритм запроса должен состоять из двух частей, первая часть это основа, обычно возвращающий одну строку с исходной точкой иерархии или части иерархии. То есть место в иерархии откуда будет начат отсчет (например, корень), и вторя рекурсивная часть которая будет связываться с временной таблицей которую мы объявили после WITH. Объединяются первая и вторая части оператором UNION или UNION ALL. Создадим таблицу в которой будет описана структура одной компании:

 

CREATE TABLE KPO

(

"ID" character varying(55),

"DESCRIPTION" character varying(255),

"PARENT" character varying(55

);

после внесения туда данных:

Select * from kpo

ID	DESCRIPTION	PARENT
== =====	================================	=======
KPO	KARACHAGANAK PETROLEUM OPERATING	{null}
AKSAY	AKSAY	KPO
URALSK	KPO	KPO
LONDON	LONDON	KPO
KPC	KPC	AKSAY
U2	UNIT-2	AKSAY
U3	UNIT-3	AKSAY
PROD	PRODACTION	KPC
MAINT	MAINTENANCE	AKSAY
CMMS	CMMS TEAM	MAINT

 

Теперь сам рекурсивный запрос:

1. WITH RECURSIVE temp1 ("ID","PARENT","DESCRIPTION",PATH, LEVEL) AS (

2. SELECT T1."ID",T1."PARENT", T1."DESCRIPTION", CAST (T1."ID" AS VARCHAR (50)) asPATH, 1

3. FROM KPO T1 WHERE T1."PARENT" IS NULL

4. union

5. select T2."ID", T2."PARENT", T2."DESCRIPTION", CAST (temp1.PATH ||'->'|| T2."ID" ASVARCHAR(50)),LEVEL + 1

6. FROM KPO T2 INNER JOIN temp1 ON(temp1."ID"= T2."PARENT"))

7. select * from temp1 ORDER BY PATH LIMIT 100

Первая часть (строки 2-3) возвращает во временную таблицу первую строку в данном случае корневую запись нашей структуры, от которой будет начинаться отсчет в нашей иерархии. Вторая часть (строки 4-5) добавляет в эту же временную таблицу записи связанные с уже содержащейся в temp1 строкой через JOIN (ID = PARENT) и так до конца пока все листья нашего ROOTa не окажутся в temp1.

Так же в данном примере была сымитирована Ораколавская функция sys_connect_by_path.

 

«ID»	«PARENT»	«DESCRIPTION»	«path»	«level»
KPO		KARACHAGANAK PETROLEUM OPERATING	KPO	1
AKSAY	KPO	AKSAY	KPO->AKSAY	2
KPC	AKSAY	KPC	KPO->AKSAY->KPC	3
PROD	KPC	PRODAUCTION	KPO->AKSAY->KPC->PROD	4
MAINT	AKSAY	MAINTENANCE	KPO->AKSAY->MAINT	3
CMMS	MAINT	CMMS TEAM	KPO->AKSAY->MAINT->CMMS	4
U2	AKSAY	UNIT-2	KPO->AKSAY->U2	3
U3	AKSAY	UNIT-3	KPO->AKSAY->U3	3
LONDON	KPO	LONDON	KPO->LONDON	2
URALSK	KPO	URALSK	KPO->URALSK	2

 

В Postgre нет встроенной проверки на зацикливание, поэтому если данные получили от тех, кто занимался непосредственно созданием структуры в Excel, то необходимо проверить эту структуру на целостность. Иногда достаточно использовать UNION вместо UNION ALL, но это только иногда. Если в первой части задали отправную точку в иерархии, и если даже где-то в иерархии есть обрывы в принципе запустив вышеупомянутый квери ошибки не будет, просто строки «отщипенцы» будут проигнорированы. Но нам же надо знать где ошибка, и реализовать это можно внедрив дополнительную проверку перед выполнением UNION.

1. WITH RECURSIVE temp1 ("ID","PARENT","DESCRIPTION",PATH, LEVEL, cycle) AS (

2. SELECT T1."ID",T1."PARENT", T1."DESCRIPTION", cast (array[T1."ID"] as varchar(100)[]), 1, FALSE

3. FROM KPO T1

4. union all

5. select T2."ID", T2."PARENT", T2."DESCRIPTION", cast(temp1.PATH || T2."ID" asvarchar(100) []),LEVEL + 1,

6. T2."ID" = any (temp1.PATH)

7. FROM KPO T2 INNER JOIN temp1 ON(temp1."ID"= T2."PARENT") AND NOT CYCLE)

8.

9. select * from temp1 WHERE CYCLE IS TRUE LIMIT 100;

Здесь создается такое же поле Path но уже все «предшествующие родители» организованны в массиве, что дает сравнивать каждый новый “ID” на дубликат, и если в массиве уже есть такая запись тогда во временную таблицу строка заносится с флагом и в следующий проход уже не используют эту строку для поиска потомков, благодаря этому избегается зацикливание (union all… WHERE … AND NOT CYCLE).

 

Более подробная информация: Рекурсивные запросы в PostgreSQL (WITH RECURSIVE) / Хабрахабр, Запросы WITH (Общие табличные выражения) | PostgreSQL, Рекурсивные (Иерархические) запросы в PostgreSQL / Хабрахабр, Nested Sets + PostgreSQL TRIGGER / Хабрахабр, http://www.ibase.ru/treedb/

Оборудование

персональный компьютер с установленной операционной системой Windows XP/7/8, браузер (Например, InternetExplorer, GoogleChrome, Opera), СУБД PostgreSQL.

Задание на работу

1. Опишите, для чего в вашей предметной области могла бы понадобиться древовидная структура.

2. Создайте древовидную структуру (таблицу, содержащую внешний ключ, ссылающийся на эту же таблицу). Наполните ее данными. Создайте рекурсивный вопрос, выводящий данные в соответствии с их иерархией. Проанализируйте зависимость времени выполнения запроса от количества записей в таблице (определите для вашего компьютера, при каком количестве записей запрос будет выполняться 0,1 секунду, 1 секунду и 10 секунд).

3. Создайте структуру для хранения древовидных данных типа Nested Sets и все необходимые триггеры. Покажите на примерах, как работает эта структура.

4. Проанализируйте зависимость времени добавления новых данных от числа строк в таблице (определите для вашего компьютера, при каком количестве записей добавление новых данных будет выполняться 0.1 секунду, 1 секунду и 10 секунд).

 

7. Контрольные вопросы

1. Как представить дерево данных в Postgres?

2. Как эффективно получить произвольный узел и всех его потомков (и потомков потомков)?

3. Как выглядит общая схема рекурсивного запроса?