mysql(几乎)完成审计
我正在寻找一种为我的表制作简单事件日志的方法。我有几个表可以由不同的用户更改,我想跟踪:
- 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
所以我读到触发器可能是答案,但据我所知,我似乎需要有一个完整的我想要跟踪的每一列的新表。触发器对于这项工作来说并不完美,因为我想记录谁进行了更改,而从我读到的内容来看,这是不可能的。
我想为 CRUD 创建 3 个不同的函数(插入、更新、删除),并在进行查询之前检查更改的内容并创建日志,然后运行查询。但从这里开始,似乎非常缓慢且复杂。
还有其他更好的方法吗?
谢谢
好的,我再次检查了触发器,这不是我想要的,所以我编写了简单的函数来检查您想要记录的每个查询是否新值不同,如果是不同的话 - 它会记录它。
主要问题是,我没有测量它,但它显然更慢。
首先,您需要创建一个新的mysql表,如下所示:
- 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)
之后,编写函数。我还没有写“插入”和“删除”部分,但我想我应该容易得多。
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 函数:
$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'
希望这有帮助。目前已经测试并且可以工作。如果有更新 - 我会将其发布在这里。
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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
嗯,我也在思考这个问题。
SET @user='someone'
之类的问题,并使用它。简而言之,对于具有列(a,b,c)的表,我将创建一个具有列(user_id,modtime, a、b、c)。
主要缺点:
group_members
表并不会真正更改groups
表,而您可能希望将其保留为一个时间点 总而言之,这对我来说似乎很划算,但由于我很少在实践中看到它,所以必须< /em> 是其糟糕的令人信服的原因,所以我将等待这些答案。
Hmm, I was also thinking about this.
SET @user='someone'
, and use that.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:
group_members
table doesn't really alter agroups
table, while you may want to keep that as a point in time forgroups
rather then delve throughgroup_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.