Ведение журнала изменений

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

Определим в БД таблицу ZAKAZY_JOURNAL:

CREATE TABLE store.zakazy_journal

(

journal_id serial NOT NULL,

dat_izm timestamp without time zone,

deistvie character(3),

old_values character varying(300),

new_values character varying(300),

CONSTRAINT pkjournal PRIMARY KEY (journal_id)

)

в которую будем автоматически записывать любые изменения, добавления, удаления в таблице zakazy. При этом будем фиксировать дату, операцию (INS, UPD, DEL) над таблицей zakazy, а также новое и старое значения столбцов (old_values, new_values). Для операции удаления новое значение столбцов new_values будет пустым. Для операции добавления пустым будет старое значение столбцов old_values.

CREATE OR REPLACE FUNCTION store.zakazy_action()

RETURNS trigger AS

$BODY$

DECLARE

op TEXT;

new_v TEXT;

old_v TEXT;

BEGIN

IF(TG_OP = 'INSERT') THEN

OP = 'INS';

new_v = NEW.id_order ||', ' || NEW.date_order ||', ' ||NEW.id_cln ||

', ' || NEW.id_slzh ||', ' || NEW.id_mvr ||', ' || NEW.id_prd ||

', ' || NEW.count_ord ||', ' || NEW.price_all;

old_v = '';

INSERT INTO store.zakazy_journal (dat_izm, deistvie, old_values, new_values)

VALUES (NOW(), op, old_v, new_v);

RETURN NEW;

ELSEIF (TG_OP = 'UPDATE') THEN

OP = 'UPD';

new_v = NEW.id_order ||', ' || NEW.date_order ||', ' ||NEW.id_cln ||

', ' || NEW.id_slzh ||', ' || NEW.id_mvr ||', ' || NEW.id_prd ||', '

|| NEW.count_ord ||', ' || NEW.price_all;

old_v = OLD.id_order ||', ' || OLD.date_order ||', ' ||OLD.id_cln ||

', ' || OLD.id_slzh ||', ' || OLD.id_mvr ||', ' || OLD.id_prd ||', '

|| OLD.count_ord ||', ' || OLD.price_all;

INSERT INTO store.zakazy_journal (dat_izm, deistvie, old_values, new_values)

VALUES (NOW(), op, old_v, new_v);

RETURN NEW;

ELSE

OP = 'DEL';

new_v = '';

old_v = OLD.id_order ||', ' || OLD.date_order ||', ' ||OLD.id_cln ||

', ' || OLD.id_slzh ||', ' || OLD.id_mvr ||', ' || OLD.id_prd ||', '

|| OLD.count_ord ||', ' || OLD.price_all;

INSERT INTO store.zakazy_journal (dat_izm, deistvie, old_values, new_values)

VALUES (NOW(),op,old_v,new_v);

RETURN NEW;

END IF;

RETURN NULL;

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE

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

SELECT *

FROM STORE.zakazy_JOURNAL;

получим историю изменений таблицы zakazy:

Управление пользователями и группами

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

Учетные записи пользователей определяют, кому разрешено выполнение определенных действий в системе (а также каких именно действий). Группы су­ществуют как вспомогательное средство, упрощающее предоставление этих прав. Пользователи и группы существуют как глобальные объекты баз данных, а это оз­начает, что они не связаны ни с какой конкретной базой данных.

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

Управление пользователями

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

Каждому пользователю PostgreSQL назначается внутренний системный иден­тификатор (sysid) и пароль, хотя пароль не всегда обязателен для подключения (это зависит от файла конфигурации pg_hba.conf). Системный идентификатор связывает объекты базы данных с владельцем (так называется пользователь, которому разрешено предоставлять и отзывать права до­ступа к объекту).

Помимо прав, предоставляемых владельцами объектов, при создании учетной записи пользователю также могут быть предоставлены глобальные права. Они определяют, разрешено ли пользователю создавать и уничтожать базы данных и яв­ляется ли он суперпользователем (пользователем, обладающим всеми правами во всех базах данных, включая право создания других пользователей). Глобальные права могут быть в любой момент изменены текущим суперпользователем. В PostgreSQL по умолчанию создается один суперпользователь с именем postgres. Все остальные пользователи создаются этим суперпользователем или другим, созданным позднее.

Просмотр списка пользователей

Вся информация о пользователях PostgreSQL хранится в системной таблице pg_shadow. Выборка из этой таблицы разрешена только суперпользователям, хотя ограниченное представление этой таб­лицы с именем pg_user доступно и для обычных пользователей.

Создание пользователей

Создание пользователей требует прав суперпользователя или пользователей с соответствующими правами.

Команда CREATE USER имеет всего один обязательный параметр — имя нового пользо­вателя. Ей также можно передать множество других параметров, в том числе па­роль, системный идентификатор, группу и права, назначаемые новому пользова­телю. Полный синтаксис команды CREATE USER выглядит так:

CREATE USER пользователь

[WITH [SYSID uid]

[PASSWORD 'пароль']]

[CREATEDB | NOCREATEDB]

[CREATEUSER | NOCREATEUSER]

[IN GRDUP группа [. ...]]

[VALID UNTIL 'срок']

Имя создаваемого пользователя определяется параметром пользователь. В си­стеме не допускается присутствие двух пользователей с одинаковыми именами. За ключевым словом WITH следуют секции SYSID и/или PASSWORD.

Все остальные необязательные секции могут следовать в произвольном поряд­ке (ключевое слово WITH для них не обязательно). Ниже перечислены все необяза­тельные ключевые слова.

– SYSID uid. Пользователю назначается системный идентификатор uid. Если зна­чение не указано, выбирается некоторое уникальное число.

– PASSWORD ' пароль'. Новому пользователю назначается заданный пароль. Если значение не указано, по умолчанию используется пароль NULL.

– CREATEDB | NOCREATEDB. Ключевое слово CREATEDB предоставляет новому пользо­вателю право создания баз данных, а также право уничтожения принадлежа­щих ему баз данных. Ключевое слово NOCREATEDB явно указывает на отсутствие такого права (используется по умолчанию).

– CREATEUSER | NOCREATEUSER. Ключевое слово CREATEUSER предоставляет пользовате­лю право создания пользователей, наделяя его тем самым правами суперполь­зователя. Пользователь с правом создания других пользователей обладает все­ми правами во всех базах данных (включая право создания баз данных, даже если было указано ключевое слово NOCREATEDB). Ключевое слово NOCREATEUSER явно указывает на отсутствие права создания новых пользователей.

– IN GROUP группа [. ... ]. Новый пользователь включается в группу с заданным именем. Допускается перечисление нескольких групп через запятую. Чтобы создание пользователя прошло успешно, перечисленные группы должны су­ществовать.

– VALID UNTIL 'срок'. Пароль пользователя становится недействительным в ука­занный момент, который задается в одном из поддерживаемых форматов вре­мени. По истечении срока действия пароль необходимо сменить.

– VALID UNTIL 'infinity'. Срок действия пароля не ограничивается.

При отсутствии ключевых слов CREATEDB и CREATEUSER создается «обычный» пользователь, не обладающий особыми привилегиями. Он не может ни создавать, ни уничтожать базы данных и других пользователей. Обычные пользователи могут подключаться к базам данных PostgreSQL, но при этом они могут выполнять лишь те команды, выполнение которых им было разрешено (см. раздел «Предо­ставление привилегий» этой главы).

Для примера создадим нового суперпользователя:

CREATE USER admin

WITH PASSWORD 'adminadmin'

CREATEDB

CREATEUSER

А также просто пользователя БД:

CREATE USER user1

WITH PASSWORD 'useruser'

Не нашли, что искали? Воспользуйтесь поиском по сайту: