Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате
Форум программистов > Firebird, Interbase > Журналирование операций (FB 2.0)


Автор: Akella 5.2.2007, 16:29
1. Вначале создаем функцию для чтения контекстных переменных
Код

DECLARE EXTERNAL FUNCTION RDB$GET_CONTEXT
    VARCHAR(80) CHARACTER SET NONE NULL,
    VARCHAR(80) CHARACTER SET NONE NULL
RETURNS VARCHAR(255) FREE_IT
ENTRY_POINT 'get_context' MODULE_NAME 'system_module'


теперь для записи контекстных переменных
Код

DECLARE EXTERNAL FUNCTION RDB$SET_CONTEXT
    VARCHAR(80) CHARACTER SET NONE NULL,
    VARCHAR(80) CHARACTER SET NONE NULL,
    VARCHAR(255) CHARACTER SET NONE NULL
RETURNS INTEGER BY VALUE
ENTRY_POINT 'set_context' MODULE_NAME 'system_module'



2. создаем саму таблицу журнала
Код

CREATE GENERATOR GEN_JOURNAL_ID;

CREATE TABLE JOURNAL (
    ID          BIGINT NOT NULL,
    UID         INTEGER NOT NULL,
    DATE_TIME   DATETIME NOT NULL /* DATETIME = TIMESTAMP */,
    TABLE_NAME  STRING110 NOT NULL /* STRING110 = VARCHAR(110) */,
    ACT         STRING110 NOT NULL /* STRING110 = VARCHAR(110) */,
    CLIENT_IP   STRING110 NOT NULL /* STRING110 = VARCHAR(110) */,
    ID_NEW      BIGINNT /* BIGINNT = BIGINT */,
    ID_OLD      BIGINNT /* BIGINNT = BIGINT */
);

ALTER TABLE JOURNAL ADD CONSTRAINT PK_JOURNAL PRIMARY KEY (ID);

CREATE TRIGGER JOURNAL_BI FOR JOURNAL
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GEN_JOURNAL_ID,1);
END



3. Создаем процедуру для чтения глобальной переменной
Код

CREATE PROCEDURE SP_GET_GLOBAL_VAR (
    var_name varchar(110))
returns (
    var_value varchar(110))
as
begin
  VAR_VALUE = RDB$GET_CONTEXT('USER_SESSION', :VAR_NAME);
  suspend;
end


для записи глобальной переменной
Код

CREATE PROCEDURE SP_GET_GLOBAL_VAR (
    var_name varchar(110))
returns (
    var_value varchar(110))
as
begin
  VAR_VALUE = RDB$GET_CONTEXT('USER_SESSION', :VAR_NAME);
end


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

CREATE PROCEDURE SP_JOURNALING (
    table_name varchar(100),
    act varchar(100),
    id_new bigint,
    id_old bigint)
as
declare variable user_id integer;
begin
  select MAX(coalesce(VAR_VALUE, -1)) from sp_get_global_var('USER_ID') into :USER_ID;
--  USER_ID = (select MAX(coalesce(VAR_VALUE, -1)) from sp_get_global_var('USER_ID'));
  if (:act = 'ДОБАВЛЕНИЕ') then begin
    insert into journal
      (UID,     date_time,         TABLE_NAME, ACT,   client_ip, id_new, id_old)
    values
      (:USER_ID, current_timestamp, :Table_name, :act, rdb$get_context('SYSTEM', 'CLIENT_ADDRESS'),:id_new, :id_old);
  end

  if (:act = 'ОБНОВЛЕНИЕ') then begin
    insert into journal
      (UID,     date_time,         TABLE_NAME, ACT,   client_ip, id_new, id_old)
    values
      (:USER_ID, current_timestamp, :Table_name, :act, rdb$get_context('SYSTEM', 'CLIENT_ADDRESS'),:id_new, :id_old);
  end

  if (:act = 'УДАЛЕНИЕ') then begin
    insert into journal
      (UID,     date_time,         TABLE_NAME, ACT,   client_ip, id_new, id_old)
    values
      (:USER_ID, current_timestamp, :Table_name, :act, rdb$get_context('SYSTEM', 'CLIENT_ADDRESS'),:id_new, :id_old);
  end

end



5. Создаем для КАЖДОЙ таблицы нашей базы данных триггеры такого вида
Код

CREATE TRIGGER APART_BI0 FOR APART
ACTIVE BEFORE INSERT OR UPDATE OR DELETE POSITION 1
AS
declare variable table_name varchar(100);
begin
  table_name = 'APART';-- НЕ ЗАБЫВАЕМ МЕНЯТЬ В ТРИГГЕРАХ ИМЯ ТАБЛИЦЫ
  if (inserting) then execute procedure sp_journaling(table_name,'ДОБАВЛЕНИЕ',new.id,null);
  if (updating) then execute procedure sp_journaling(table_name,'ОБНОВЛЕНИЕ',new.id,old.id);
  if (deleting) then execute procedure sp_journaling(table_name,'УДАЛЕНИЕ',null,old.id);
end


6. В приложении на форме ввода логина и пароля узнаем id пользователя
и записываем USER_ID в контекстную переменную
см. строку dm.fibDB.Execute('execute procedure sp_set_global_var(''USER_ID'','+IntToStr(UserData.UserID)+')');
Код

...
type
  TUSerData = record
    UserName, Password:String;
    UserID:integer;
  end;
...

//использую FIBPlus
function TfmLogon.CheckPass(user,pass:string):boolean;
Var
 SQL : string;
 q   : TpFIBQuery;
begin
  q := TpFIBQuery.Create(nil);
  try
    q.Database := dm.fibDB;
    q.GoToFirstRecordOnExecute := true;
    SQL := 'SELECT ID FROM USERS WHERE (NAME = '+QuotedStr(user)+') AND (PASS = '+QuotedStr(pass)+') AND (UPPER(DELETED) <> '+QuotedStr('T')+')';
    q.SQL.Add(SQL);
    q.GoToFirstRecordOnExecute := true;
    q.ExecQuery;
    result := q.RecordCount > 0;
    if Result then begin
      UserData.UserID := q.FieldByName('ID').AsInteger;

//устанавливаем в базе контекстную переменную - UserID
      dm.fibDB.Execute('execute procedure sp_set_global_var(''USER_ID'','+IntToStr(UserData.UserID)+')');

    end else
      UserData.UserID := -1;
  finally
    FreeAndNil(q);
  end;
end;


Вот теперь даже если с помощью др. средства манипулировать данными в базе (как то IBExpert), и если UID не запишется, то client_ip точно запишеться. Если в  поле client_ip будет запись в виде целого числа (например, 2864), то данными манипулировали по протоколу не IPv4, а по протоколу XNET.

Автор: Akella 5.2.2007, 16:49
С одной стороны так делать не очень хорошо. В идеале нужно работать с системными учётными записями. Но в моем случае у меня в базе своя таблица пользователей, своя таблица групп пользователей и своя таблица прав доступа.

Автор: Akella 7.2.2007, 12:47
Потом добавляем вьюху

Код

CREATE VIEW VW_JOURNAL(
    ID,
    USER_NAME,
    DATE_TIME,
    TABLE_NAME,
    ACT,
    CLIENT_IP,
    ID_OBJ,
    NEW_DATA,
    OLD_DATA,
    ADV_DATA)
AS
select
j.id,
u.name,
j.date_time,
j.table_name,
j.act,
j.client_ip,
j.id_obj,
j.new_data,
j.old_data,
j.adv_data

 from journal J
left join
 users U on U.ID = J.uid
;


Добавлено @ 12:50 
В приложении бросам на форму TQuery, там пишем запрос

Код

select * from vw_journal

В итоге такая картина
user posted image

Автор: LostAlly 9.2.2007, 09:00
Добрый день.
Хорошая тема, у меня сейчас стоит примерно такая же задача. Точнее такая же потребность создавать свои таблицы пользователей групп и прав.
Тупиком для меня стала задача - где хранить пользователя и пароль для подключения к БД?

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

Ваш вариант мне полностью подходит, как вы подключаетесь к БД?

Автор: Akella 9.2.2007, 10:04
Цитата(LostAlly @  9.2.2007,  09:00 Найти цитируемый пост)
Тупиком для меня стала задача - где хранить пользователя и пароль для подключения к БД?

здесь 2 варианта
1. По идее правильный, но не всегда возможный - это системные пользователи FireBird, т.е. использовать встроенную безопасность FireBird. Использовать Права и Роли FireBird.
2. Создать в своей БД таблицу USERS, с нужными тебе полями.
Например:
Код

CREATE TABLE USERS (
    ID        "INT" NOT NULL /* "INT" = INTEGER CHECK ((VALUE >= 0) or (VALUE is NULL) or (VALUE = -1)) */,
    NAME      STRING20 NOT NULL COLLATE PXW_CYRL /* STRING20 = VARCHAR(20) */,
    PASS      STRING10 DEFAULT '' COLLATE PXW_CYRL /* STRING10 = VARCHAR(10) */,
    ID_GROUP  "INT" /* "INT" = INTEGER CHECK ((VALUE >= 0) or (VALUE is NULL) or (VALUE = -1)) */,
    EXP       BOOL COLLATE PXW_CYRL /* BOOL = VARCHAR(1) DEFAULT 'F' */,
    DELETED   BOOL DEFAULT 'F' COLLATE PXW_CYRL /* BOOL = VARCHAR(1) DEFAULT 'F' */
);


Все пользователи работают физически под одним пользователем. Создаешь, кроме SYSDBA ещё одного. Чтобы никто не работал под SYSDBA.
Теперь выполняешь подключение к БД. На форме ввода логина и пароля есть 2 основных элемента. Из выпадающего списка выбирается пользователь. Далее вводится пароль. Ты делаешь запрос к своей таблице USERS типа

Код

Select Count(*) from users where (ID = 100) and (pass = 'бе-бе-бе')


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

Автор: Romkin 9.2.2007, 10:04
Здесь тоже пользователь может подключиться напрямую к БД, все через SYSDBA ходят. 
Или уже нет?  smile 

Автор: Akella 9.2.2007, 10:09
Romkin, я не пойму, чё ты пристал с этим одним пользователем...?? Моя база - что хочу, то и делаю, за то  - больше возможностей.

Автор: Romkin 9.2.2007, 10:15
Не с одним, а именно с SYSDBA. С одним, у которого минимальные права, я считаю что нормально. Сам, каюсь, грешен иногда, через SYSDBA доступ идет. Стараюсь закрывать, но за всем не уследишь smile

Добавлено @ 10:19 
Кстати, я вижу, что здесь у тебя при обновлении записывается старое значение ключа и новое. У тебя во всех таблицах при обновлении записи она добавляется что-ли?

Автор: LostAlly 9.2.2007, 10:59
Это все понятно, не понятно одно, где в программе хранить пользователя БД которому разрешено цеплятся к базе. Ведь чтобы сделать селект пользователей из своей таблицы к базе нужно перед этим подключиться.

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

2Romkin
Не обязательно это должен быть пользователь SYSDBA, можно создать другого с нужным набором прав.

Автор: Akella 9.2.2007, 12:51
Цитата(Romkin @  9.2.2007,  10:15 Найти цитируемый пост)
Кстати, я вижу, что здесь у тебя при обновлении записывается старое значение ключа и новое. 

Нет, не ключа! Зачем?
Допустим есть таблица-справочник:
ID, NAME, EXP
Пользователь сделал изменение записи.
Вот при обновлении и видно какое значение поля NAME было, а какое стало. (В данном случае поле NAME - основное) Очень удобно. Конечно, если в таблице 50 полей, то все сохранять - это большая рутина и огромная база. Но если нужно хранить все изменения... то можно сделать дочернюю таблицу, где хранить изменения каждого поля.

ID, ID_MASTER, FIELD_NAME, NEW_DATA, OLD_DATA.

Добавлено @ 12:53 
Цитата(LostAlly @  9.2.2007,  10:59 Найти цитируемый пост)
И еще при подключении к базе в каком виде передается пароль базы данных.

Это сам решай, можешь шифровать.

Автор: Akella 9.2.2007, 13:07
Цитата(LostAlly @  9.2.2007,  10:59 Найти цитируемый пост)
Это все понятно, не понятно одно, где в программе хранить пользователя БД которому разрешено цеплятся к базе. Ведь чтобы сделать селект пользователей из своей таблицы к базе нужно перед этим подключиться.


С помощью утилиты gsec создай в БД пользователя и работай под ним и подключайся к базе в клиентском приложении под ним.

Смотри, коннектишься к базе под системным пользователем, загружаешь в выпадающий список формы-логина из СВОЕЙ таблицы пользователей все имена. Кроме имён пока тебе ничего не нужно. Загрузил и жди пока юзер введет пароль и нажмет ОК. Нажал ОК? проверяй пароль выбранного пользователя.
делай запрос вида 
Код
Select Count(*) from users where (ID = 100) and (pass = 'бе-бе-бе')

если в поле значение "0", то знач пароль неверный.

Добавлено @ 13:08 
В твоей таблице USERS поле USER_NAME должно иметь только уникальные записи.

Автор: LostAlly 13.2.2007, 14:01
Да это все и так понятно, просто боюсь хранить пароль к БД в программе.

Автор: Akella 13.2.2007, 14:07
храни в базе в зашифрованном виде, шифруй без обратимого шифрования, или какой пароль?

Автор: LostAlly 14.2.2007, 10:37
Пароль для обращения к БД. Допустим путь это будет тот же самый SYSDBA.
Ведь чтобы добраться до самих пользователей(которые в моей таблице хранятся) мне нужно сначала подрубиться к БД.

Автор: Akella 14.2.2007, 13:41
храни в программе, а что такого? Если тебе нужна безопасность, то тебе лучше использовать системных пользователей

Автор: Akella 18.4.2007, 08:13
Хочу предупредить про подводный камень, на который наткнулся при использовании программы со встраиваемым сервером. В LOG-таблице есть поле ClientIP, которое не может быть пустым(not null). Так вот, при использовании встраиваемого сервера.

Код должен быть таким:
Код

CREATE GENERATOR GEN_JOURNAL_ID;
CREATE TABLE JOURNAL (
    ID          BIGINT NOT NULL,
    UID         INTEGER NOT NULL,
    DATE_TIME   DATETIME NOT NULL /* DATETIME = TIMESTAMP */,
    TABLE_NAME  STRING110 NOT NULL /* STRING110 = VARCHAR(110) */,
    ACT         STRING110 NOT NULL /* STRING110 = VARCHAR(110) */,
    CLIENT_IP   STRING110 /* STRING110 = VARCHAR(110) */,
    ID_NEW      BIGINNT /* BIGINNT = BIGINT */,
    ID_OLD      BIGINNT /* BIGINNT = BIGINT */
);
ALTER TABLE JOURNAL ADD CONSTRAINT PK_JOURNAL PRIMARY KEY (ID);
CREATE TRIGGER JOURNAL_BI FOR JOURNAL
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GEN_JOURNAL_ID,1);
END


т.к. в хранимке функция возвращает null в этом случае (rdb$get_context('SYSTEM', 'CLIENT_ADDRESS')
Код

values
      (:USER_ID, current_timestamp, :Table_name, :act, rdb$get_context('SYSTEM', 'CLIENT_ADDRESS'),:id_new, :id_old);


Добавлено через 1 минуту и 4 секунды
т.е. поле ClientIP должно допускать "пустое" значение.

Автор: Akella 11.5.2007, 11:02
Цитата(Akella @  5.2.2007,  16:49 Найти цитируемый пост)
С одной стороны так делать не очень хорошо. В идеале нужно работать с системными учётными записями. Но в моем случае у меня в базе своя таблица пользователей, своя таблица групп пользователей и своя таблица прав доступа. 

но можно ассоциировать свою и системную таблицы пользователей и групп

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)