使用 Oracle 触发器自动记录 DROP 和 TRUNCATE 操作

在 Oracle 数据库中，记录 DDL 操作（如 DROP 和 TRUNCATE）对于审计和数据恢复至关重要。通过创建触发器，我们可以自动记录这些操作，并将相关信息保存到一个专用表中。本文将介绍如何实现这一功能。

背景

在实际生产环境中，数据库表的 DROP 和 TRUNCATE 操作会导致数据丢失。这类操作需要仔细审计，以便在出现问题时进行追溯和恢复。使用触发器，我们可以在执行 DROP 和 TRUNCATE 操作时自动记录相关信息，如操作时间、表名、操作类型等。

创建记录表

首先，我们需要创建一个表来存储记录。该表将包含以下字段：序列 ID、表名、表所有者、操作时间、操作类型、操作系统用户和主机名。

CREATE TABLE DDLOPERATION
(
  seqid      NUMBER PRIMARY KEY,
  tablename  VARCHAR2(30),
  tableowner VARCHAR2(30),
  opertime   DATE,
  opertype   VARCHAR2(20),
  os_user    VARCHAR2(60),
  host_name  VARCHAR2(60)
);

创建触发器

接下来，我们创建一个触发器，用于在 SCOTT 模式下执行 DROP 和 TRUNCATE 操作时记录相关信息。这个触发器在这些操作之后触发，并插入记录到 DDLOPERATION
表中。

CREATE OR REPLACE TRIGGER ddl_operations_trigger
AFTER TRUNCATE OR DROP ON scott.schema
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  v_os_user VARCHAR2(100);
  v_host_name VARCHAR2(100);
BEGIN
  v_os_user := SYS_CONTEXT('USERENV', 'OS_USER');
  v_host_name := SYS_CONTEXT('USERENV', 'HOST');

  IF ora_sysevent = 'TRUNCATE' THEN
    INSERT INTO ddloperation 
      VALUES (seq_id.NEXTVAL, ora_dict_obj_name, ora_dict_obj_owner, SYSDATE, 'TRUNCATE', v_os_user, v_host_name);
  ELSIF ora_sysevent = 'DROP' THEN
    INSERT INTO ddloperation 
      VALUES (seq_id.NEXTVAL, ora_dict_obj_name, ora_dict_obj_owner, SYSDATE, 'DROP', v_os_user, v_host_name);
  END IF;
  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE || '---' || SQLERRM);
END;
/

在触发器中，我们使用 SYS_CONTEXT
函数获取当前操作系统用户和主机名。ora_dict_obj_name
和 ora_dict_obj_owner
分别表示操作对象的名称和所有者。触发器使用 PRAGMA AUTONOMOUS_TRANSACTION
来确保插入操作独立于原始事务，并在操作结束后立即提交。

示例操作

我们可以通过以下示例来测试触发器的功能。

-- 删除表
DROP TABLE EMP_BAK2;

-- 截断表
TRUNCATE TABLE tab2;

执行以上操作后，查询 DDLOPERATION
表：

SELECT * FROM DDLOPERATION;

-- 查询结果示例：
-- SEQID TABLENAME        TABLEOWNER       OPERTIME            OPERTYPE   OS_USER    HOST_NAME
-- ----- ---------------  ---------------  ------------------- ---------- ---------  ----------------
-- 3     EMP_BAK2         SCOTT            2024-06-03 20:10:43 DROP       oracle     orcl1
-- 4     TAB2             SCOTT            2024-06-03 20:11:06 TRUNCATE   oracle     orcl1

总结

通过本文的介绍，我们展示了如何在 Oracle 数据库中使用触发器自动记录 DROP 和 TRUNCATE 操作。这种方法可以有效地审计敏感操作，帮助数据库管理员更好地管理和保护数据。希望本文能够为您的数据库管理工作提供有益的参考。

「欢迎关注我们的公众号，获取更多技术分享与经验交流。」