当前位置:首页 > Oracle > 正文内容

Oracle中使用系统触发器记录表结构的修改操作

管理员2年前 (2018-09-26)Oracle825
在运行的某个业务系统中,发现某张表多出来了一些字段,所有权限的用户都没有对其进行操作,怀疑是有一些后台服务做了这个操作,遂查询一下根源,来确定这些修改是实现哪些功能的。

在实际工作中,我们记录对某个表数据的操作,一般会使用触发器和日志表相配合来完成,那么DML的操作可以使用触发器,DDL是否也可以呢,答案是肯定的。

1、问题引入


我们先看一个针对 DML操作的简单操作片断,BEFORE INSERT是表明在INSERT操作时这个触发器的文本才会执行,ON TEST 表明是应用于TEST表。

create or replace trigger tri_test_insert
  before insert on test
  for each row
declare

2、实现过程


ORACLE除了针对于DML的触发器以外,还有专门针对DDL操作的触发器,比如对于CREATE、DROP、ALTER 的操作,下面的示例将是一个记录对所有对象(表、视图、过程、函数等等)的增加、删除、修改的过程。
首先创建一个日志表,和填充日志表的一个序列,表结构的说明如下:username用于记录是当前的用户名,event用于记录操作的类型,是CREATE还是DROP,object_name记录一下被操作的对象名称,ddl_sql用于记录当时执行的实际SQL,created 用于记录操作日期和时间。

SQL> create sequence seq_ddl_logger;
Sequence created
SQL> create table ddl_logger(
  2      id int,
  3      username varchar(30),
  4      event varchar(30),
  5      object_name varchar(30),
  6      ddl_sql varchar(4000),
  7      crdate date default sysdate
  8  );
Table created

下面将是触发器的文本,before ddl,表明记录所有的DDL操作,on schema表明只在当前用户下的操作才能生效,ora_login_user是登录的用户名,ora_sysevent是具体的操作,ora_dict_obj_name是对象名称,ora_name_list_t中存储的是具体的SQL,这4个属性,都是系统提供的,我们可以直接使用。

create or replace trigger tri_schema_ddl
  before ddl on schema
declare
  l_text   varchar(300);
  sql_text ora_name_list_t;
  i        number;
begin
  i      := sql_txt(sql_text);
  l_text := sql_text(1);

  insert into ddl_logger
    (id, username, event, object_name, ddl_sql)
  values
    (seq_ddl_logger.nextval,
     ora_login_user,
     ora_sysevent,
     ora_dict_obj_name,
     l_text);
end;

到此为止,所有的准备工作都已完成,下在可以做一个简单的测试,新建表、增加字段、增加索引、增加主键约束、删除表等几个操作。

SQL>
SQL> create table test(id int, name varchar(10));
Table created
SQL> alter table test add remark varchar(10);
Table altered
SQL> create index ix_test_name on test(name);
Index created
SQL> alter table test add primary key (id);
Table altered
SQL> drop table test purge;
Table dropped
SQL>

3、查看结果


此时,我们可以查看一下日志的记录情况,基本上可以从这个结果中,提取出最开始需求的内容。

SQL> select id, event, object_name, ddl_sql from ddl_logger;
I EVENT      OBJECT_NAME    DDL_SQL
- ---------- -------------- ---------------------------------------------------------
1 CREATE     TEST           create table test(id int, name varchar(10))
2 ALTER      TEST           alter table test add remark varchar(10)
3 CREATE     IX_TEST_NAME   create index ix_test_name on test(name)
4 ALTER      TEST           alter table test add primary key (id)
5 CREATE     SYS_C0015235   CREATE UNIQUE INDEX "RDM"."SYS_C0015235" on "RDM"."TEST"("ID")
6 DROP       TEST           drop table test purge
6 rows selected

SQL>

4、注意事项

不能记录其他的用户登录以后(比如 sys或system),对本账户表结构的调整,如想要这些内容,可以把 on schema 修改为 on database或 on instance,当然这需要更大的权限。
在本例中,记录的事件范围是 ON DDL,如果不想记录太多的记录,可以调整为 ON ALTER,则不会记录 CREATE 和 DROP 这些操作。
当该触发器牌无效状态时,所有的 DDL 操作都会报错,所在结束记录这些操作之前,应该先删除该触发器,再删除 logger 表,以防止对其他的会话产生影响。
如果在本例中的记录的 SQL 不完整,可以把sql_text这个数据组里的其他数据也取出来,拼接在一起。

打赏 支付宝打赏 微信打赏
    扫描二维码至手机访问

    扫描二维码推送至手机访问。

    版权声明:本文由卖水果的net发布,如需转载请注明出处。

    转载请注明出处:http://www.msgde.net/oracle/oracle_sys_trigger_on_ddl.html

    标签: 触发器
    分享给朋友:

    评论列表

    访客
    访客
    2年前 (2018-11-14)

    对用户的访问限制,可以设置吗?

    发表评论

    访客

    ◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。