创建一个监听多个表的全局触发器 [英] Creating a global trigger that listens to multiple tables

问题描述

我想在Oracle 11g中创建一个全局触发器.可用于审核约100个表.多个表可以触发一个触发器.如果是，那我该如何实现呢?

I want to create a global trigger in Oracle 11g. Which can be used for auditing of around 100 tables. Can multiple tables fire a single trigger. If yes then how can I achieve this?

推荐答案

我想在oracle 11g中创建一个全局触发器，该触发器可用于审核约100个表

I want to create a global trigger in oracle 11g ,which can be used for auditing of around 100 tables

您是否有理由重新发明轮子?为什么不利用Oracle的内置审计?

Is there a reason you want to reinvent the wheel? Why not make use of Oracle's inbuilt auditing?

Oracle Base 提供了一些有关如何入门的基本信息关于审核:

Oracle Base provides some basic info as to how to get started on Auditing:

可以通过设置AUDIT_TRAIL静态参数来启用审核，该参数具有以下允许值.

Auditing can enabled by setting the AUDIT_TRAIL static parameter, which has the following allowed values.

AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }

以下列表提供了每种设置的说明:

The following list provides a description of each setting:

none or false - Auditing is disabled.
db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.
xml- Auditing is enabled, with all audit records stored as XML format OS files.
xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
os- Auditing is enabled, with all audit records directed to the operating system's audit trail.

要启用对数据库审核跟踪的审核，请启用对数据库的审核

To enable auditing to database audit trail enable auditing to db

SQL> ALTER SYSTEM SET audit_trail=db,extended SCOPE=SPFILE;

System altered.

关闭&重新启动数据库

Shutdown & restart the db

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

现在要由用户cube审核SELECTS，INSERTS，UPDATES，DELETES，请执行以下操作:

Now to audit SELECTS, INSERTS, UPDATES, DELETES by user cube do this:

CONNECT sys/password AS SYSDBA

AUDIT ALL BY cube BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY cube BY ACCESS;

可以通过查询DBA_AUDIT_TRAIL

进一步阅读:

• 审核
• 细粒度审核
• 配置和管理审核

• Auditing
• Fine grained auditing
• Configuring and administering auditing

这篇关于创建一个监听多个表的全局触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！