创建一个监听多个表的全局触发器 [英] 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屋!