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

问题描述

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

- 谁进行了更改- 什么时候- 更新前是什么- 什么是新值- 哪个表和哪个记录&柱子

类似的东西会很棒:

20:00:00 |约翰 |更新 |产品 |113 |产品名称 |"xbox" |Xbox 360"20:00:10 |吉姆 |更新 |产品 |113 |产品名称 |"Xbox 360" |"20:01:00 |吉姆 |删除 |产品 |113

所以我读到触发器可能是答案，但据我所知，似乎我需要为要跟踪的每一列创建一个完整的新表.触发器并不适合这项工作，还因为我想记录谁进行了更改，而根据我的理解，这是不可能的.

我想为 CRUD 制作 3 个不同的函数(插入、更新、删除)，并在进行查询之前，检查更改的内容并制作日志，然后运行查询.但从这里开始，它似乎非常缓慢和复杂.

还有更好的方法吗?

谢谢

<小时>

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

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

首先你需要创建一个新的mysql表如下:

• id (a_i, 主要)
• creation_date(日期时间)
• user_id (int)
• table_name (tinytext)
• record_id (int)
• cell_name(小文本)
• action_type(小文本)
• old_value(文本)
• new_value(文本)

之后，编写函数.我还没有写插入"和删除"部分，但我认为我应该更容易.

function log_query($action_type, $table, $values, $parameters){如果($action_type == '更新'){log_updates($action_type, $table, $values, $parameters);$query = "更新 $table SET ";foreach ($values as $key => $value){$query .= $key."='";$query .= $value."', ";}未设置($值)；$query = substr($query, 0, -2);$query .= 'WHERE';foreach ($parameters as $key => $value){$query .= $key."='";$query .= $value."' AND ";}未设置($值)；$query = substr($query, 0, -4);$result = mysql_query($query);}}

和:

function log_updates($action_type, $table, $values, $parameters){$where = " WHERE ";$user_id = '1234';//例子foreach ($parameters as $key => $value){$where .= $key."='";$where .= $value."' AND ";}未设置($值)；$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];如果($action_type == '更新'){如果($old_value ！= $value){$logger = mysql_query("插入审计(event_date, action_type, user_id, table_name, record_id, cell_name, old_value, new_value)值 (NOW(), '$action_type', $user_id, '$table', '$record_id', '$key', '$old_value', '$value')");if (!$logger) echo mysql_error();}}}未设置($值)；}

要调用函数，您需要先对参数进行排序以查找特定行，并将新值放入数组，然后调用 log_query 函数:

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

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

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

希望这有帮助.它现在经过测试并且有效.如果有更新 - 我会在这里发布.

解决方案

嗯，我也在考虑这个.

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

简而言之，对于带有 (a,b,c) 列的表，我会创建一个带有列 (user_id,modtime,a,b,c) 的表.

主要缺点:

• 批量更新缓慢(因此请谨慎选择您的表格以进行修订)
• 数据复制豪华版，你/我必须有足够的存储空间
• 'related' 数据不会触发修订(即:更改 group_members 表并不会真正更改 groups 表，而您可能希望将其保留为groups 的时间点，而不是钻研 group_members 更改.

总而言之，这对我来说似乎很划算，但正如我在实践中很少看到的那样，必须有令人信服的理由为什么它很糟糕，所以我会等待这些答案.>

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

somthing like will be great:

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.

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 ?

Thanks

---

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.

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

• id (a_i, primary)
• creation_date (datetime)
• user_id (int)
• table_name (tinytext)
• record_id (int)
• cell_name (tinytext)
• action_type (tinytext)
• old_value (text)
• new_value (text)

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);

    }
} 

and :

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);

}

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.

解决方案

Hmm, I was also thinking about this.

• 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.

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

Major drawbacks:

• 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屋！