来自审计跟踪数据库的 Mysql 用户事件报告

Question

我需要一个 mysql 查询来创建关于用户修改表的月度报告。我们有一个审计跟踪数据库，其中每次插入和更新都将登录到相应的表中。
tblappuser (如果用户在数据库 mydb 中列出)

id  name   crdate      mddate             createdby modifiedby
0   admin  crdatetime  modifieddatetime   0         0
1   abc                                   0         0
2   xyz                                   0         0
3   abc1                                  0         0
4   xyz1                                  0         0

表1 (mydb 数据库中的示例表结构 - 初始数据)

id    name   crdate      mddate             createdby modifiedby
100   data1  crdatetime  modifieddatetime   0         2
101   data2                                 3         3
102   data3                                 4         0
103   data4                                 2         1
104   data5                                 1         0

table1_audit (mydb_audit 数据库中的示例表结构)(如果 102 记录的初始 createdby&modofiedby 4,0 更改为 4,2 然后是 4,1，则审计将有一个条目)

newid oldid    name   crdate      mddate             createdby modifiedby
tblpk 102      data3  crdatetime  modifieddatetime   4         0
      102      data3                                 4         2

我必须创建一个由用户修改的表列表的报告(用户应该在 tblappuser 表中)像这样一个月。非常感谢快速解决方案。

User Activity Report
--------------------
User    Tables               Date
xyz1    Table1,Table2..      modifieddate
abc     No Tables
abc1    Table3

编辑
我为单个用户尝试了它，但不确定每天循环 tblappuser 表的所有用户和数据库中所有表的相应条目。因此寻找一种解决方案来为用户的所有表修改获取动态游标。

DROP PROCEDURE IF EXISTS useractivity;
delimiter //
CREATE PROCEDURE useractivity()
 BEGIN
 DECLARE userlist INT DEFAULT 0;
 DECLARE i INT DEFAULT 0;
 SELECT COUNT(*) FROM tblappuser INTO userlist;
 SET i=0;
 WHILE i<userlist  DO 
   INSERT INTO `mydb`.`useractivity` (username, tblname) 
      SELECT tu.name,'table1'
         FROM `mydb`.`tblappuser` tu
         INNER JOIN `mydb`.`table1` t ON t.modifiedby = tu.id
         INNER JOIN `mydb_audit`.`table1_audit` t_a ON t.id = t_a.id 
        WHERE t.mddate <> t_a.mddate AND t.modifiedby=i ; 
   SET i = i + 1;
 END WHILE;
 End;
 //

下面的查询列出了一天内修改的表，任何人都可以帮助我迭代每个表的修改数据与 tblappuser 表中的用户或上述过程的结果相关联，因为上述过程仅检查一个表。

SELECT table_name 
FROM information_schema.tables
WHERE UPDATE_TIME >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
and table_schema='mydb' and table_name in (SELECT table_name 
FROM information_schema.tables
WHERE UPDATE_TIME >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
and table_schema='mydb_audit') ;

Answer 1

终于按预期得到了解决方案，但准备语句太长因此 选择@SQL 只打印几行，这里有任何帮助。

use mydb;
DROP PROCEDURE IF EXISTS UserActivity;
DELIMITER $$
CREATE PROCEDURE UserActivity()
BEGIN
DECLARE var1 CHAR(50);
DECLARE done TINYINT DEFAULT FALSE;
DECLARE cursor1
 CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE  UPDATE_TIME >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
WHERE table_schema='mydb' and table_name in (SELECT table_name 
FROM information_schema.tables WHERE table_schema='mydb_audit') ;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 OPEN cursor1;
 my_loop: 
 LOOP
 FETCH cursor1 INTO var1;
  IF done THEN
   LEAVE my_loop;
  ELSE
SET @SQL = CONCATE('INSERT INTO mydb_audit.UserActivity (userName,tableName,date) ',
 'SELECT Concat(ta.firstname, ''' ''', ta.lastname) AS ''UserName'',"', var1 ,'",
 DATE_FORMAT(lma.newDate, ''%y-%m-%d'') ''Date''
 from mydb.tblappuser ta '
'inner join mydb_audit.', var1, ' lma on lma.modifiedBy = ta.id 
and lma.newDate > current_date() 
Inner Join abc c on ta.xxId = c.id 
inner join abc1 c1 on c.id=c.xxid
DATE_FORMAT(lma.newDate, ''%y-%m-%d'') ' );
SELECT @SQL;
PREPARE stmt FROM @SQL;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
  END IF;
 END LOOP;
END$$
DELIMITER ;