mysql(几乎)完成审核 [英] mysql (almost) complete auditing

问题描述

我正在寻找一种为表创建简单事件日志的方法.我有几个表可以由不同的用户更改，并且我想跟踪一下:

I'm looking for a way of making simple event log for my tables. I have few tables that can be changed by various users, and I want to keep track on:

- who made the change
- when 
- what was before update
- what is the new value
- which table and which record & column

类似的东西会很棒:

20:00:00 | john | update | products | 113 | product_name | "xbox" | "xbox 360"
20:00:10 | jim  | update | products | 113 | product_name | "xbox 360" | ""
20:01:00 | jim  | delete | products | 113

所以我读到触发器可能是答案，但是据我看来，我需要为要跟踪的每一列创建一个完整的新表.触发器对于这项工作来说并不完美，这还因为我想记录谁进行了更改，而根据我的阅读，这是不可能的.

So i read that triggers could be the answer but as far as I read it seems that I need to have a complete new table for each column I want to keep track on. Triggers are not perfect for this job also because I want to log who made the change, and from what I read this is not possible.

我想到了为CRUD提供3种不同的功能(插入，更新，删除)，并且在进行查询之前，先检查更改内容并制作日志，然后运行查询.但是从这里开始，它似乎非常缓慢且复杂.

I thought of making 3 different functions for CRUD (insert, update, delete), and just before making the query, to check what is changed and to make the log and then run the query. But from here it seems to be very slow and complicated.

还有另一种更好的方法吗?

Is there another better way ?

谢谢

好吧，我再次检查了触发器，而不是我要找的触发器，所以我编写了简单的函数，如果新值不同，它将检查要记录的每个查询，如果是，则将其记录下来.

Ok, I checked again the triggers and its not what I was looking for, so I wrote simple functions that will check for each query you want to log if the new values are different, and if so - it logs it.

主要的问题是，我没有测量它，但是它显然要慢一些.

The main problem is, and I didn't measure it, but it obviously slower.

首先，您需要创建一个新的mysql表，如下所示:

first you need to make a new mysql table as follow:

• id(a_i，主要)
• creation_date(日期时间)
• user_id(int)
• 表名(小文本)
• record_id(int)
• cell_name(小文本)
• action_type(小文本)
• 旧值(文本)
• 新值(文本)

然后，编写函数.我还没有写'INSERT'和'DELETE'部分，但我认为我应该容易得多.

after that, write the functions. I didn't write yet the 'INSERT' and 'DELETE' sections but I think i should be much easier.

function log_query($action_type, $table, $values, $parameters){

if ($action_type == 'UPDATE'){

    log_updates($action_type, $table, $values, $parameters);

    $query = "UPDATE $table SET ";
    foreach ($values as $key => $value){
        $query .= $key."='";
        $query .= $value."', ";
    }
    unset($value);

    $query = substr($query, 0, -2);

    $query .= ' WHERE ';

    foreach ($parameters as $key => $value){
        $query .= $key."='";
        $query .= $value."' AND ";
    }
    unset($value);

    $query = substr($query, 0, -4);

    $result = mysql_query($query);

    }
} 

和:

function log_updates($action_type, $table, $values, $parameters){
$where = " WHERE ";
$user_id = '1234'; //example
foreach ($parameters as $key => $value){
        $where .= $key."='";
        $where .= $value."' AND ";
}
unset($value);

$where = substr($where, 0, -4);

foreach ($values as $key => $value){
    $result = mysql_query("SELECT $key, id FROM $table $where");

    $row = mysql_fetch_row($result);
    $old_value = $row[0];
    $record_id = $row[1];

    if ($action_type == 'UPDATE'){
        if ($old_value != $value){
            $logger = mysql_query("INSERT INTO auditing (event_date, action_type, user_id, table_name, record_id, cell_name, old_value, new_value)
                                    VALUES (NOW(), '$action_type', $user_id, '$table', '$record_id', '$key', '$old_value', '$value')");
            if (!$logger) echo mysql_error();
        }
    } 


    }
    unset($value);

}

要调用函数，您需要先对参数进行排序以找到特定的行，并将新值存储到数组中，然后再调用log_query函数:

to call the functions you need to first order the parameters to find the specific row, and the new values in to arrays, and after that call the log_query function:

$update = Array('name' => 'barbara', 'description' => 'new name');
$parameters = Array('id' => '1', 'name' => 'barbi');
log_query('UPDATE', 'checktable', $update, $parameters);

这实际上将检查名称"是否已更改以及描述是否已更改.对于每一项，如果发生更改，它将在审核"表中插入新记录，指定确切的更改. 记录更改后，它将运行更新查询.在我们的示例中:

This will actualy will check if the 'name' has changed and if the description is changed. for each one, if it change, it will insert new record into 'auditing' table specifying the exact change. after loging the change, it will run the update query. in our example:

UPDATE checktable SET name='barbara', description='new name' WHERE id='1' AND name='barbi'

希望这会有所帮助.它已经过测试，并且可以正常工作.如果有更新-我会在这里发布.

hope this is helping. It tested for now and works. If there will be updates - I'll post it here.

推荐答案

嗯，我也在想这个.

• 每个表到每个修订版都有一个表对于我个人来说不是什么大问题，但是嘿.
我相信
• 用户名可以保留用户定义的变量(在会话开始后发出SET @user='someone'之类的东西，然后使用它.
• 只要在INSERT，UPDATE和DELETE之后有触发器，获取上一个/下一个值就是一个简单的查询，那么我只会存储OLD值.

• Having a table per table-to-keep revisions for would not be that much of a problem for me personally, but hey.
• Username can be kept with user-defined variables I believe, (after a session start issue something like SET @user='someone', and use that.
• As longs as there are triggers after INSERT, UPDATE and DELETE, getting the previous / next values is a simple query, I'll only store the OLD values.

简而言之，对于具有coluns(a，b，c)的表，我将创建具有列(user_id，modtime，a，b，c)的表.

In short, for a table with coluns (a,b,c) I'd create a table with columns (user_id,modtime,a,b,c).

主要缺点:

• 批处理更新慢(因此，请选择表以保留修订以进行仔细检查)
• 豪华数据复制，您将/我将必须有足够的存储空间
• 相关"数据不会触发修订(即:更改group_members表并不会真正更改groups表，尽管您可能希望将其保留为groups的时间点，而不是然后仔细研究group_members更改.

• batch updates are slow (so choose your tables to keep revisions for carefully)
• data duplication deluxe, you'll / I'll have to have enough storage space
• 'related' data does not trigger a revision (i.e: altering a group_members table doesn't really alter a groups table, while you may want to keep that as a point in time for groups rather then delve through group_members alterations.

总的来说，这对我来说似乎很有意义，但正如我在实践中很少看到的那样，必须有令人信服的理由说明其不良之处，所以我将等待这些答案.

All in all it seems a good deal to me, but as I've seldomly seen it in practise there must be compelling reasons why its bad, so I'll await those answers.

这篇关于mysql(几乎)完成审核的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！