审计（Audit）用于监视用户所执行的数据库操作，审计信息可存储于数据字典表，称为审计记录。审计记录存储在SYSTEM表空间中的SYS.AUD$表中，可通过视图DBA_AUDIT_TRAIL查看。审计记录也可以存储在操作系统文件中（默认位置为$ORACLE_BASE/admin/$ORACLE_SID/adump/）。若审计表不存在，则可以通过脚本$ORACLE_HOME/rdbms/admin/cataudit.sql来创建。

审计的内容主要包括对数据库连接、SQL语句执行以及数据库对象访问等操作的跟踪记录。Oracle系统对任何用户所做的登录、操作数据库对象进行自动记录，以便使DBA在事后可以进行监督和检查。

启用审计

Oracle 11g默认启用审计，AUDIT_TRAIL参数的缺省值为DB，这意味着审计数据将记录在数据库中的AUD$审计字典基表上。

SQL> SHOW PARAMETER AUDIT_TRAIL
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB

SQL> SHOW PARAMETER AUDIT_TRAIL

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

audit_trail string DB

初始化参数AUDIT_TRAIL用于控制数据库审计，取值说明如下所示：

NONE：禁用数据库审计。
OS：启用数据库审计，并将数据库审计记录写入操作系统文件中。
DB：启用数据库审计，并将数据库所有审计记录写入数据库的SYS.AUD$表。
db_extended：启用数据库审计，并将数据库所有审计记录写入数据库的SYS.AUD$表。另外，填充SYS.AUD$表的SQLBIND列和SQLTEXT列。
XML：启用数据库审计，并将所有记录写到XML格式的操作系统文件中。
XML,EXTENDED：启用数据库审计，填充审计记录的所有列，包括SQLTEXT和SQLBIND的值。

数据字典

有关数据库审计的一些数据字典视图如下所示：

SELECT * FROM DBA_PRIV_AUDIT_OPTS;
SELECT * FROM DBA_STMT_AUDIT_OPTS;
SELECT * FROM DBA_OBJ_AUDIT_OPTS;
SELECT * FROM DBA_FGA_AUDIT_TRAIL;
SELECT * FROM DBA_AUDIT_OBJECT;
SELECT * FROM DBA_AUDIT_SESSION;
SELECT * FROM SYS.AUD$;
SELECT * FROM DBA_AUDIT_TRAIL;
SELECT * FROM ALL_DEF_AUDIT_OPTS;
SELECT * FROM DBA_AUDIT_POLICIES;

SELECT * FROM DBA_PRIV_AUDIT_OPTS;

SELECT * FROM DBA_STMT_AUDIT_OPTS;

SELECT * FROM DBA_OBJ_AUDIT_OPTS;

SELECT * FROM DBA_FGA_AUDIT_TRAIL;

SELECT * FROM DBA_AUDIT_OBJECT;

SELECT * FROM DBA_AUDIT_SESSION;

SELECT * FROM SYS.AUD$;

SELECT * FROM DBA_AUDIT_TRAIL;

SELECT * FROM ALL_DEF_AUDIT_OPTS;

SELECT * FROM DBA_AUDIT_POLICIES;

有关审计的数据字典视图介绍：

名称	说明
SYS.AUD$	唯一保留审计结果的表，其它均为视图。
STMT_AUDIT_OPTION_MAP	包含有关审计选项类型代码的信息，由SQL.BSQ脚本在CREATE DATABASE时创建。
AUDIT_ACTIONS	包含审计跟踪动作类型代码的描述，例如INSERT、DROP VIEW、DELETE、LOGON和LOCK。
ALL_DEF_AUDIT_OPTS	包含默认对象审计选项。
USER_OBJ_AUDIT_OPTS	描述当前用户拥有的所有对象上的审计选项。
DBA_AUDIT_TRAIL	包含标准审计跟踪条目，USER_AUDIT_TRAIL只包含已连接用户的审计行。
USER_AUDIT_TRAIL	显示与当前用户有关的审计跟踪条目。
DBA_AUDIT_OBJECT	包含系统中所有对象的审计跟踪记录。
DBA_AUDIT_SESSION	列出涉及CONNECT和DISCONNECT的所有审计记录。
USER_AUDIT_SESSION	列出涉及当前用户的CONNECT和DISCONNECT的所有审计跟踪记录。
DBA_AUDIT_STATEMENT	列出涉及数据库全部的GRANT、REVOKE、AUDIT、NOAUDIT和ALTER SYSTEM语句的审计跟踪记录。
DBA_AUDIT_EXISTS	列出BY AUDIT NOT EXISTS产生的审计跟踪条目。
DBA_AUDIT_POLICIES	记录了数据库中的细粒度审计策略定义。
DBA_FGA_AUDIT_TRAIL	列出基于值的审计跟踪记录。
DBA_STMT_AUDIT_OPTS	对语句生效的审计选项。
DBA_PRIV_AUDIT_OPTS	对系统权限生效的审计选项。
DBA_OBJ_AUDIT_OPTS	对数据库生效的审计选项。

迁移SYS.AUD$表

在日常的数据库维护中，经常出现SYSTEM表空间被撑满，在绝大多数情况下是因为数据库登录审计的功能被启动了，此时一般建议把SYS.AUD$相关对象迁移到其它表空间，从而避免SYSTEM被用完的风险。

在Oracle 11g之前迁移方法如下所示：

ALTER TABLE SYS.AUDIT$ MOVE TABLESPACE USERS;
ALTER TABLE SYS.AUDIT_ACTIONS MOVE TABLESPACE USERS;
ALTER TABLE SYS.AUD$ MOVE TABLESPACE USERS;
ALTER TABLE SYS.AUD$ MOVE LOB(SQLBIND) STORE AS SYS_IL0000000384C00041$$ (TABLESPACE USERS);
ALTER TABLE SYS.AUD$ MOVE LOB(SQLTEXT) STORE AS SYS_IL0000000384C00041$$ (TABLESPACE USERS);
ALTER INDEX SYS.I_AUDIT REBUILD ONLINE TABLESPACE USERS;
ALTER INDEX SYS.I_AUDIT_ACTIONS REBUILD ONLINE TABLESPACE USERS;

ALTER TABLE SYS.AUDIT$ MOVE TABLESPACE USERS;

ALTER TABLE SYS.AUDIT_ACTIONS MOVE TABLESPACE USERS;

ALTER TABLE SYS.AUD$ MOVE TABLESPACE USERS;

ALTER TABLE SYS.AUD$ MOVE LOB(SQLBIND) STORE AS SYS_IL0000000384C00041$$ (TABLESPACE USERS);

ALTER TABLE SYS.AUD$ MOVE LOB(SQLTEXT) STORE AS SYS_IL0000000384C00041$$ (TABLESPACE USERS);

ALTER INDEX SYS.I_AUDIT REBUILD ONLINE TABLESPACE USERS;

ALTER INDEX SYS.I_AUDIT_ACTIONS REBUILD ONLINE TABLESPACE USERS;

从Oracle 11g开始可以使用DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION进行迁移：

EXEC DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,AUDIT_TRAIL_LOCATION_VALUE => 'USERS');

1	EXEC DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,AUDIT_TRAIL_LOCATION_VALUE => 'USERS');

在Oracle 11g之前通过手工清理的方式或自定义作业来定期清理SYS.AUD$表，如下：

TRUNCATE TABLE SYS.AUD$;
DELETE FROM SYS.AUD$ WHERE OBJ$NAME='EMP';

1 2	TRUNCATE TABLE SYS.AUD$; DELETE FROM SYS.AUD$ WHERE OBJ$NAME='EMP';

需要注意的是，如果AUD$表过大，那么直接TRUNCATE AUD$表，系统要立即释放大量的EXTENTS，会严重影响系统性能。可以通过如下2个步骤逐步释放EXTENTS：

①　清空数据并且保留原来的EXTENTS：

TRUNCATE TABLE SYS.AUD$ REUSE STORAGE;

1	TRUNCATE TABLE SYS.AUD$ REUSE STORAGE;

在这里，REUSE STORAGE是TRUNCATE的一个参数，表示保持原来的存储不变。一般情况下，SQL命令“TRUNCATE TABLE TABLE_NAME;”其实就是“TRUNCATE TABLE TABLE_NAME DROP STORAGE;”。DROP STORAGE是TRUNCATE TABLE的默认参数。

②　逐步回缩EXTENTS：

ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 5000M;

ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 2000M;

……

ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 10M;

ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 5000M;

ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 2000M;

……

ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 10M;

需要注意的是，在执行的时候，可以根据实际情况调整每次回缩空间的大小。

若审计在OS和XML选项下进行手动删除审计文件。在Oracle 11g中通过DBMS_AUDIT_MGMT包下的子过程进行手动或定期清理。下面的过程可以迁移审计记录到USERS表空间：

conn / as sysdba
BEGIN
  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
                                           AUDIT_TRAIL_LOCATION_VALUE => 'USERS');

END;
/

conn / as sysdba

BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,

AUDIT_TRAIL_LOCATION_VALUE => 'USERS');

END;

使用包DBMS_AUDIT_MGMT下的INIT_CLEANUP过程可以设置审计的清除间隔，还有很多其它的实用存储过程请参考官方文档。

授予审计权限

如下示例为授予审计的一些权限：

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

GRANT AUDIT ANY TO LHR_TEST;
GRANT AUDIT SYSTEM TO LHR_TEST;

1 2	GRANT AUDIT ANY TO LHR_TEST; GRANT AUDIT SYSTEM TO LHR_TEST;

审计的分类

Oracle中审计总体上可分为标准审计（Standard Auditing）、基于值的审计（Value-Based Auditing）和细粒度审计（Fine-Grained Auditing），细粒度审计也称为“基于政策的审计”，在Oracle 10g之后功能得到很大增强。其中，标准审计可分为用户级审计和系统级审计。用户级审计是任何Oracle用户都可设置的审计，主要是用户针对自己创建的数据库表或视图进行审计，记录所有用户对这些表或视图的一切成功和（或）不成功的访问以及各种类型的SQL操作。系统级审计只能由DBA设置，用以监测成功或失败的登录要求、监测GRANT和REVOKE操作以及其它数据库级权限下的操作。
在Oracle中分别支持以下三种标准审计类型，或者说，可以从3个角度去启用审计：
①　语句审计（Statement Auditing），对某种类型的SQL语句审计，不指定结构或对象。审计SQL语句的成功执行或不成功执行。这里从SQL语句的角度出发，进行指定。审计只关心执行的语句。例如，AUDIT CREATE TABLE语句，其中，AUDIT为使用审计的关键字。该语句表示对CREATE TABLE语句的执行进行记录，不管这条语句是否为针对某个对象的操作。
②　权限审计（Privilege Auditing），对执行相应动作的系统特权的使用审计，对涉及某些权限的操作进行审计，这里强调“系统权限”，例如，“AUDIT CREATE TABLE;”命令，可以表明对涉及“CREATE TABLE”权限的操作进行审计。所以，在这种命令的情况下，既产生一个语句审计，又产生了一个权限审计。有时候语句审计和权限审计是相互重复的。
③　对象审计（Object Auditing），对一特殊模式对象上的指定对象的审计。对一个特殊模式对象上的DML语句进行审计。记录作用在指定对象上的操作。例如，AUDIT SELECT ON SCOTT.DEPT语句，表示指定SCOTT用户的DEPT表，审计对其进行的SELECT语句。

基于值的审计

进行数据库审计时会记录审计对象中发生的插入、更新和删除操作，但是不会捕获更改的实际值。要扩展数据库审计，可使用基于值的审计，利用数据库触发器（事件驱动的PL/SQL构造）来捕获更改的值。

用户在连接了相应触发器的表中插入、更新或删除数据时，触发器在后台将审计信息复制到包含审计信息的表中。因为审计触发器代码在每次插入、更新或删除操作发生时都必须执行，所以与标准数据库审计相比，使用基于值的审计时，性能下降幅度比较大。性能下降幅度取决于触发器代码的效率。只在标准数据库审计捕获的信息不足的情况下，才使用基于值的审计。

基于值的审计由用户或第三方代码实施。Oracle DB提供了可用来构建基于值的审计系统的PL/SQL构造。基于值的审计的关键部分是审计触发器，这是一个单纯为了捕获审计信息而构造的PL/SQL触发器。

以下是一个审计触发器的典型示例：

CREATE OR REPLACE TRIGGER system.hrsalary_audit
AFTER UPDATE OF salary
ON hr.employees 
REFERENCING NEW AS NEW OLD AS OLD 
FOR EACH ROW  
BEGIN
IF :old.salary != :new.salary THEN 
INSERT INTO system.audit_employees  
VALUES (sys_context('userenv','os_user'), sysdate, sys_context('userenv','ip_address'),
:new.employee_id ||' salary changed from '||:old.salary|| ' to '||:new.salary);
END IF; 
END;
/

CREATE OR REPLACE TRIGGER system.hrsalary_audit

AFTER UPDATE OF salary

ON hr.employees

REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW

BEGIN

IF :old.salary != :new.salary THEN

INSERT INTO system.audit_employees

VALUES (sys_context('userenv','os_user'), sysdate, sys_context('userenv','ip_address'),

:new.employee_id ||' salary changed from '||:old.salary|| ' to '||:new.salary);

END IF;

END;

这个触发器将审计的重点设置为捕获hr.employees表薪水列的更改。更新某行后，触发器就会检查薪水列。如果新旧薪水不相等，则触发器会在audit_employees表（通过在SYSTEM方案中单独执行一项操作而创建）中插入一条审计记录。审计记录中包括了用户名、执行更改的IP地址、标识所更改记录的主键及更改的实际薪水值。

如果标准数据库审计收集的数据不足，还可使用数据库触发器来捕获关于用户连接的信息。通过使用登录触发器，管理员可以捕获用来标识连接到数据库的用户的数据。示例中包括下列各项：

登录人员的IP地址
用于连接到实例的程序名的前48个字符
用于连接到实例的终端名

在许多情况下，会使用细粒度审计(FGA)功能，而不会使用基于值的触发器。

审计中BY ACCESS和BY SESSION的区别是什么？

BY ACCESS表示每一个被审计的操作都会生成一条AUDIT TRAIL，而BY SESSION表示一个会话里面同类型的操作只会生成一条AUDIT TRAIL，默认为BY SESSION。

如何对SYSDBA和SYSOPER进行审计？

对SYSDBA和SYSOPER的审计具有如下的特点：

①　审计线索必须存储在数据库外部。

②　始终会对以SYSDBA或SYSOPER身份执行的连接进行审计。

③　可以使用AUDIT_SYS_OPERATIONS启用对SYSDBA或SYSOPER操作的附加审计。当AUDIT_SYS_OPERATIONS参数为FALSE时，系统只以OS文件记录SYSDBA身份的登录、开关数据库的操作。当AUDIT_SYS_OPERATIONS参数为TRUE时，系统以OS文件记录SYSDBA身份的登录、开关数据库的操作，以及其它辅助的操作。该参数的默认值为FALSE。

SYS@LHR11G> show parameter AUDIT_SYS_OPERATIONS

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
audit_sys_operations                 boolean                FALSE
SYS@LHR11G>

SYS@LHR11G> show parameter AUDIT_SYS_OPERATIONS

NAME TYPE VALUE

------------------------------------ ---------------------- ------------------------------

audit_sys_operations boolean FALSE

SYS@LHR11G>

④　可使用AUDIT_FILE_DEST控制审计线索。

无论是远程或本地SYSDBA、SYSOPER权限用户登录都会在AUDIT_FILE_DEST指定的目的地生成相应审计文件，记录登录信息。Windows平台SYSDBA权限用户的审计记录会被写到事件查看器中。

什么是细粒度审计？

从Oracle 9i开始，引入了细粒度的对象审计，使得审计变得更为关注某个方面，并且更为精确。细粒度的审计可以在访问某些行和列时审计对表的访问，从而极大地减少审计表的记录数量。使用标准的审计，可以发现访问了哪些对象，以及由谁访问，但是无法知道访问了哪些行或列。细粒度审计被称为FGA（Fine-Grained Audit），也叫精细化审计，由DBMS_FGA的PL/SQL程序来实现，细粒度审计除了审计功能外，还可用于绑定变量值的捕获。FGA策略的定义位于数据字典视图DBA_AUDIT_POLICIES中。

如果FGA策略定义错误，那么可以使用DBMS_FGA.DROP_POLICY存储过程来删除。DBA_FGA_AUDIT_TRAIL中的数据可以通过删除基表的方式来删除（基表为SYS.FGA_LOG$），也可以直接在视图上做删除操作：

DELETE FROM DBA_FGA_AUDIT_TRAIL; --直接在视图上做删除操作
DELETE FROM SYS.FGA_LOG$;  --删除基表

1 2	DELETE FROM DBA_FGA_AUDIT_TRAIL; --直接在视图上做删除操作 DELETE FROM SYS.FGA_LOG$; --删除基表

其基表的获取方式可以有以下2种方式：

SELECT TEXT FROM DBA_VIEWS D WHERE D.VIEW_NAME='DBA_FGA_AUDIT_TRAIL'; --查询TEXT列
SELECT D.REFERENCED_OWNER, D.REFERENCED_NAME, D.REFERENCED_TYPE
  FROM DBA_DEPENDENCIES D
 WHERE D.NAME = 'DBA_SQL_PLAN_BASELINES'; --查询引用列

SELECT TEXT FROM DBA_VIEWS D WHERE D.VIEW_NAME='DBA_FGA_AUDIT_TRAIL'; --查询TEXT列

SELECT D.REFERENCED_OWNER, D.REFERENCED_NAME, D.REFERENCED_TYPE

FROM DBA_DEPENDENCIES D

WHERE D.NAME = 'DBA_SQL_PLAN_BASELINES'; --查询引用列

下面给出一个使用FGA审计的示例：

LHR@lhrdb> CREATE TABLE ACCOUNT_LHR
  2  (AACT_NO NUMBER NOT NULL,
  3       CUST_ID NUMBER NOT NULL,
  4       BALANCE NUMBER(15,2)
  5   );
Table created.
LHR@lhrdb> BEGIN
  2      DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => 'LHR',
  3                         OBJECT_NAME   => 'ACCOUNT_LHR',
  4                         POLICY_NAME   => 'ACCOUNT_ACCESS_LHR');
  5  END;
  6  /
PL/SQL procedure successfully completed.
LHR@lhrdb> SELECT TIMESTAMP, 
  2  DB_USER,
  3  OS_USER,
  4  OBJECT_SCHEMA,
  5  OBJECT_NAME,
  6  SQL_TEXT
  7  FROM DBA_FGA_AUDIT_TRAIL;
no rows selected
LHR@lhrdb> SELECT * FROM LHR.ACCOUNT_LHR;
no rows selected
LHR@lhrdb> COL TIMESTAMP FOR A20
LHR@lhrdb> COL DB_USER FOR A8
LHR@lhrdb> COL OS_USER  FOR A8
LHR@lhrdb> COL OBJECT_SCHEMA FOR A5
LHR@lhrdb> COL OBJECT_NAME FOR A15
LHR@lhrdb> COL SQL_TEXT  FOR A30
LHR@lhrdb> SELECT TIMESTAMP, 
  2  DB_USER,
  3  OS_USER,
  4  OBJECT_SCHEMA,
  5  OBJECT_NAME,
  6  SQL_TEXT
  7  FROM DBA_FGA_AUDIT_TRAIL;
TIMESTAMP            DB_USER  OS_USER  OBJEC OBJECT_NAME     SQL_TEXT
-------------------- -------- -------- ----- --------------- -------------------------
2016-08-25 14:38:45  LHR      oracle   LHR   ACCOUNT_LHR     select * from lhr.account_lhr

LHR@lhrdb> CREATE TABLE ACCOUNT_LHR

2 (AACT_NO NUMBER NOT NULL,

3 CUST_ID NUMBER NOT NULL,

4 BALANCE NUMBER(15,2)

5 );

Table created.

LHR@lhrdb> BEGIN

2 DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => 'LHR',

3 OBJECT_NAME => 'ACCOUNT_LHR',

4 POLICY_NAME => 'ACCOUNT_ACCESS_LHR');

5 END;

6 /

PL/SQL procedure successfully completed.

LHR@lhrdb> SELECT TIMESTAMP,

2 DB_USER,

3 OS_USER,

4 OBJECT_SCHEMA,

5 OBJECT_NAME,

6 SQL_TEXT

7 FROM DBA_FGA_AUDIT_TRAIL;

no rows selected

LHR@lhrdb> SELECT * FROM LHR.ACCOUNT_LHR;

no rows selected

LHR@lhrdb> COL TIMESTAMP FOR A20

LHR@lhrdb> COL DB_USER FOR A8

LHR@lhrdb> COL OS_USER FOR A8

LHR@lhrdb> COL OBJECT_SCHEMA FOR A5

LHR@lhrdb> COL OBJECT_NAME FOR A15

LHR@lhrdb> COL SQL_TEXT FOR A30

LHR@lhrdb> SELECT TIMESTAMP,

2 DB_USER,

3 OS_USER,

4 OBJECT_SCHEMA,

5 OBJECT_NAME,

6 SQL_TEXT

7 FROM DBA_FGA_AUDIT_TRAIL;

TIMESTAMP DB_USER OS_USER OBJEC OBJECT_NAME SQL_TEXT

-------------------- -------- -------- ----- --------------- -------------------------

2016-08-25 14:38:45 LHR oracle LHR ACCOUNT_LHR select * from lhr.account_lhr

如何禁用或删除某个用户下的所有细粒度审计？

答案：可以写程序使用循环来完成，如下所示：

BEGIN
 FOR CUR IN (SELECT * FROM  DBA_AUDIT_POLICIES) LOOP
       DBMS_FGA.DISABLE_POLICY(CUR.OBJECT_SCHEMA,CUR.OBJECT_NAME,CUR.POLICY_NAME);--禁用
--DBMS_FGA.DROP_POLICY(CUR.OBJECT_SCHEMA,CUR.OBJECT_NAME,CUR.POLICY_NAME);--删除
 END LOOP;
END;

BEGIN

FOR CUR IN (SELECT * FROM DBA_AUDIT_POLICIES) LOOP

DBMS_FGA.DISABLE_POLICY(CUR.OBJECT_SCHEMA,CUR.OBJECT_NAME,CUR.POLICY_NAME);--禁用

--DBMS_FGA.DROP_POLICY(CUR.OBJECT_SCHEMA,CUR.OBJECT_NAME,CUR.POLICY_NAME);--删除

END LOOP;

END;

FGA审计和标准审计有什么区别？

FGA审计和标准审计的区别参考下表：

	标准审计	FGA审计
启用参数	标准审计必须用参数AUDIT_TRAIL在数据库级启用。这个参数是静态参数，必须重启数据库来使其生效。	FGA不需要修改任何参数。
取消审计	标准审计一旦被设置在一个对象上，要想解除它，必须用NOAUDIT命令删除审计选项。	FGA可以临时禁用和启用，不丢失任何元数据信息。
范围	标准审计可以处理其它许多语句和权限，甚至会话连接和断开。	FGA只能够处理四种类型的语句：SELECT、INSERT、UPDATE和DELETE。
空间占用	每次会话只创建一条记录（按会话）或每次访问对象创建一条记录（按访问），这种占用资源很少的方式对于控制审计表中的空间非常重要。	相比而言比较占用空间。
审计记录的位置	标准审计可以写数据库表或OS文件。	FGA日志仅写到数据库表FGA_LOG$中。可以在FGA中创建用户自定义的审计处理程序来写OS文件，但它们的完整性不能保证。
实际存在的对象	标准审计可以设置用于默认对象。当表是在运行期创建的时候，这个功能变得极为有用。默认的审计选项允许没有数据库管理员干预的审计。	用户必须在一个现有的表上创建FGA策略。
审计细节	相比而言没有FGA灵活。	在FGA中，审计更加灵活，仅当访问某些列，当某个特定的条件为真时等等。
赋值变量	在标准审计中，必须把初始化参数AUDIT_TRAIL设为“DB,EXTENDED”，以启用这一功能。	在FGA中，SQL赋值变量默认被捕获。
权限	标准审计需要审计系统或语句权限。	FGA只需要DBMS_FGA程序包上的运行权限。

示例1：对lhr.t1表做DELETE审计

标准审计

CREATE TABLE T1 AS SELECT * FROM DUAL;
SELECT * from t1 ;

INSERT INTO T1 SELECT * FROM DUAL;
commit;
DELETE from t1;
commit;

-- 开启审计
AUDIT DELETE ON LHR.T1 BY ACCESS;
SELECT * FROM DBA_OBJ_AUDIT_OPTS WHERE OBJECT_NAME = 'T1';

-- 若要SQL_TEXT不为空，则需要
alter system set audit_trail='db_extended' scope=spfile;
-- 重启数据库

-- 查询审计记录，transactionid='0000000000000000' 表示rollback或影响的行数为0的操作
SELECT * FROM DBA_AUDIT_TRAIL WHERE OBJ_NAME = 'T1' and transactionid!='0000000000000000';

CREATE TABLE T1 AS SELECT * FROM DUAL;

SELECT * from t1 ;

INSERT INTO T1 SELECT * FROM DUAL;

commit;

DELETE from t1;

commit;

-- 开启审计

AUDIT DELETE ON LHR.T1 BY ACCESS;

SELECT * FROM DBA_OBJ_AUDIT_OPTS WHERE OBJECT_NAME = 'T1';

-- 若要SQL_TEXT不为空，则需要

alter system set audit_trail='db_extended' scope=spfile;

-- 重启数据库

-- 查询审计记录，transactionid='0000000000000000' 表示rollback或影响的行数为0的操作

SELECT * FROM DBA_AUDIT_TRAIL WHERE OBJ_NAME = 'T1' and transactionid!='0000000000000000';

FGA细粒度审计(推荐)

BEGIN
  DBMS_FGA.ADD_POLICY(
    object_schema   => 'LHR',
    object_name     => 'T1',
    policy_name     => 'T1_DELETE_AUDIT_POLICY',
    statement_types => 'DELETE',
    enable          => true);
END;
/

SELECT * FROM  DBA_AUDIT_POLICIES;

DELETE from lhr.t1 where ROWNUM<=1;
commit;

-- 查询审计记录
SELECT * FROM DBA_FGA_AUDIT_TRAIL D WHERE OBJECT_NAME = 'T1';

BEGIN

DBMS_FGA.ADD_POLICY(

object_schema => 'LHR',

object_name => 'T1',

policy_name => 'T1_DELETE_AUDIT_POLICY',

statement_types => 'DELETE',

enable => true);

END;

SELECT * FROM DBA_AUDIT_POLICIES;

DELETE from lhr.t1 where ROWNUM<=1;

commit;

-- 查询审计记录

SELECT * FROM DBA_FGA_AUDIT_TRAIL D WHERE OBJECT_NAME = 'T1';

触发器(不推荐)

-- 创建审计表
CREATE SEQUENCE LHR.S_T1_AUDIT;
CREATE TABLE LHR.T1_AUDIT (
  audit_id   NUMBER,
  username   VARCHAR2(100),
  ip_address VARCHAR2(100),
  audit_time TIMESTAMP,
  sqltext   CLOB
);

-- 创建触发器
CREATE OR REPLACE TRIGGER LHR.T1_DELETE_AUDIT
AFTER DELETE ON LHR.T1
FOR EACH ROW
DECLARE
    SQL_TEXT ORA_NAME_LIST_T;
  V_SQL_FULLTEXT    VARCHAR2(32767) := NULL;

begin

/*
  -- 获取SQL语句
  FOR I IN 1..ORA_SQL_TXT(SQL_TEXT) LOOP
    V_SQL_FULLTEXT := V_SQL_FULLTEXT || SQL_TEXT(I);
  end loop;
*/

  INSERT INTO LHR.T1_AUDIT (audit_id, username, ip_address,audit_time, sqltext)
  VALUES (LHR.S_T1_AUDIT.NEXTVAL, ORA_LOGIN_USER, USERENV('SESSIONID'),SYSDATE, V_SQL_FULLTEXT);

END;
/

-- 创建审计表

CREATE SEQUENCE LHR.S_T1_AUDIT;

CREATE TABLE LHR.T1_AUDIT (

audit_id NUMBER,

username VARCHAR2(100),

ip_address VARCHAR2(100),

audit_time TIMESTAMP,

sqltext CLOB

);

-- 创建触发器

CREATE OR REPLACE TRIGGER LHR.T1_DELETE_AUDIT

AFTER DELETE ON LHR.T1

FOR EACH ROW

DECLARE

SQL_TEXT ORA_NAME_LIST_T;

V_SQL_FULLTEXT VARCHAR2(32767) := NULL;

begin

-- 获取SQL语句

FOR I IN 1..ORA_SQL_TXT(SQL_TEXT) LOOP

V_SQL_FULLTEXT := V_SQL_FULLTEXT || SQL_TEXT(I);

end loop;

INSERT INTO LHR.T1_AUDIT (audit_id, username, ip_address,audit_time, sqltext)

VALUES (LHR.S_T1_AUDIT.NEXTVAL, ORA_LOGIN_USER, USERENV('SESSIONID'),SYSDATE, V_SQL_FULLTEXT);

END;

不能获取sql语句，会报错ORA-06502: PL/SQL: numeric or value error

示例2：如何监控数据库的登陆登出、DDL语句等内容？

系统中一些常用的监控都可以使用DDL触发器和系统触发器来实现。可以先创建一张记录DDL语句的表XB_AUDIT_DDL_LHR（由于该表记录数会很大，所以，需创建成按月自动分区的分区表），并创建合适的索引，然后创建存储过程用于插入DDL信息到该日志表中。最后再创建系统触发器就可以将DDL语句或系统事件的信息插入日志表中。下面详细说明DDL触发器和系统触发器的使用。

首先创建一张记录DDL语句的表XB_AUDIT_DDL_LHR，由于该表记录数很大，所以，创建成按月自动分区的分区表，代码如下所示：

CREATE TABLE XB_AUDIT_DDL_LHR( 
  ID   NUMBER  PRIMARY KEY,
  INST_ID NUMBER,
  OPER_DATE     DATE,
  OPERATION    VARCHAR2(30), 
  OBJECT_OWNER VARCHAR2(255),
  OBJECT_TYPE  VARCHAR2(30),
  OBJECT_NAME  VARCHAR2(30), 
  SQL_TEXT     VARCHAR2(4000),
  SQL_FULLTEXT    CLOB, 
  OS_USER      VARCHAR2(255), 
  CLIENT_IP           VARCHAR2(20),
  CLIENT_HOSTNAME     VARCHAR2(30),
  DB_SCHEMA    VARCHAR2(30),
  SID          NUMBER,
  SERIAL#      NUMBER,
  SPID         NUMBER,
  SESSION_TYPE VARCHAR2(1000),
  DATABASE_NAME VARCHAR2(255),
  ERRORS_INFOR   VARCHAR2(4000),
  SQL_ID  VARCHAR2(13),
  PREV_SQL_ID VARCHAR2(13)
  ) NOLOGGING
PARTITION BY RANGE(OPER_DATE)  INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))  SUBPARTITION BY HASH(OPERATION)
SUBPARTITION TEMPLATE (
  SUBPARTITION SP1 ,
  SUBPARTITION SP2 ,
  SUBPARTITION SP3) 
   (PARTITION P201610  VALUES LESS THAN(TO_DATE('201610','YYYYMM')));

-- 创建主键列的用到的序列及其它常用列的索引
CREATE SEQUENCE S_XB_AUDIT_DDL_LHR START WITH 1 INCREMENT BY 1 CACHE 2000;
SELECT S_XB_AUDIT_DDL_LHR.NEXTVAL FROM DUAL;
CREATE INDEX IND_AUDIT_DDL_OBJECT_NAME ON   XB_AUDIT_DDL_LHR(OBJECT_NAME,OPERATION) LOCAL NOLOGGING ;
CREATE INDEX IND_AUDIT_DDL_OS_USER ON   XB_AUDIT_DDL_LHR(OS_USER) LOCAL NOLOGGING;
CREATE INDEX IND_AUDIT_DDL_SID ON   XB_AUDIT_DDL_LHR(SID,SERIAL#) LOCAL NOLOGGING;
CREATE INDEX IND_AUDIT_DDL_OBJECT_DATE ON   XB_AUDIT_DDL_LHR(OPER_DATE,OPERATION,OS_USER) LOCAL NOLOGGING ;
GRANT SELECT ON  XB_AUDIT_DDL_LHR TO PUBLIC;

CREATE TABLE XB_AUDIT_DDL_LHR(

ID NUMBER PRIMARY KEY,

INST_ID NUMBER,

OPER_DATE DATE,

OPERATION VARCHAR2(30),

OBJECT_OWNER VARCHAR2(255),

OBJECT_TYPE VARCHAR2(30),

OBJECT_NAME VARCHAR2(30),

SQL_TEXT VARCHAR2(4000),

SQL_FULLTEXT CLOB,

OS_USER VARCHAR2(255),

CLIENT_IP VARCHAR2(20),

CLIENT_HOSTNAME VARCHAR2(30),

DB_SCHEMA VARCHAR2(30),

SID NUMBER,

SERIAL# NUMBER,

SPID NUMBER,

SESSION_TYPE VARCHAR2(1000),

DATABASE_NAME VARCHAR2(255),

ERRORS_INFOR VARCHAR2(4000),

SQL_ID VARCHAR2(13),

PREV_SQL_ID VARCHAR2(13)

) NOLOGGING

PARTITION BY RANGE(OPER_DATE) INTERVAL(NUMTOYMINTERVAL(1,'MONTH')) SUBPARTITION BY HASH(OPERATION)

SUBPARTITION TEMPLATE (

SUBPARTITION SP1 ,

SUBPARTITION SP2 ,

SUBPARTITION SP3)

(PARTITION P201610 VALUES LESS THAN(TO_DATE('201610','YYYYMM')));

-- 创建主键列的用到的序列及其它常用列的索引

CREATE SEQUENCE S_XB_AUDIT_DDL_LHR START WITH 1 INCREMENT BY 1 CACHE 2000;

SELECT S_XB_AUDIT_DDL_LHR.NEXTVAL FROM DUAL;

CREATE INDEX IND_AUDIT_DDL_OBJECT_NAME ON XB_AUDIT_DDL_LHR(OBJECT_NAME,OPERATION) LOCAL NOLOGGING ;

CREATE INDEX IND_AUDIT_DDL_OS_USER ON XB_AUDIT_DDL_LHR(OS_USER) LOCAL NOLOGGING;

CREATE INDEX IND_AUDIT_DDL_SID ON XB_AUDIT_DDL_LHR(SID,SERIAL#) LOCAL NOLOGGING;

CREATE INDEX IND_AUDIT_DDL_OBJECT_DATE ON XB_AUDIT_DDL_LHR(OPER_DATE,OPERATION,OS_USER) LOCAL NOLOGGING ;

GRANT SELECT ON XB_AUDIT_DDL_LHR TO PUBLIC;

创建存储过程，用于插入DDL信息到日志表中，如下所示：

CREATE OR REPLACE PROCEDURE PRO_TRI_DDL_INSET_LHR(P_SQL_FULLTEXT VARCHAR2)
  AUTHID CURRENT_USER AS
  SP_XB_AUDIT_DDL_LHR XB_AUDIT_DDL_LHR%ROWTYPE;
  V_TMP               VARCHAR2(255);
BEGIN
  SELECT A.SID,
         A.SERIAL#,
         (SELECT B.SPID
            FROM GV$PROCESS B
           WHERE B.ADDR = A.PADDR
             AND B.INST_ID = USERENV('INSTANCE')) SPID,
         UPPER(A.OSUSER) OSUSER,
         A.MACHINE || '--' || A.PROGRAM || '--' || A.MODULE || '--' ||
         A.ACTION SESSION_TYPE,
         SUBSTR(P_SQL_FULLTEXT, 1, 3900),
         A.SQL_ID,
         A.PREV_SQL_ID,
         A.USERNAME,
         A.INST_ID
    INTO SP_XB_AUDIT_DDL_LHR.SID,
         SP_XB_AUDIT_DDL_LHR.SERIAL#,
         SP_XB_AUDIT_DDL_LHR.SPID,
         SP_XB_AUDIT_DDL_LHR.OS_USER,
         SP_XB_AUDIT_DDL_LHR.SESSION_TYPE,
         SP_XB_AUDIT_DDL_LHR.SQL_TEXT,
         SP_XB_AUDIT_DDL_LHR.SQL_ID,
         SP_XB_AUDIT_DDL_LHR.PREV_SQL_ID,
         SP_XB_AUDIT_DDL_LHR.DB_SCHEMA,
         SP_XB_AUDIT_DDL_LHR.INST_ID
    FROM GV$SESSION A
   WHERE A.AUDSID = USERENV('SESSIONID')
     AND A.INST_ID = USERENV('INSTANCE');
  INSERT INTO XB_AUDIT_DDL_LHR
    (ID,
     INST_ID,
     OPER_DATE,
     OPERATION,
     OBJECT_TYPE,
     OBJECT_NAME,
     SQL_TEXT,
     SQL_FULLTEXT,
     OS_USER,
     CLIENT_IP,
     CLIENT_HOSTNAME,
     DB_SCHEMA,
     SID,
     SERIAL#,
     SPID,
     SESSION_TYPE,
     DATABASE_NAME,
     OBJECT_OWNER,
     ERRORS_INFOR,
     SQL_ID,
     PREV_SQL_ID)
  VALUES
    (S_XB_AUDIT_DDL_LHR.NEXTVAL,
     USERENV('INSTANCE'), -- sp_xb_audit_ddl_lhr.INST_ID  ora_instance_num
     SYSDATE,
     ORA_SYSEVENT, --sys.sysevent
     ORA_DICT_OBJ_TYPE,
     ORA_DICT_OBJ_NAME,
     SP_XB_AUDIT_DDL_LHR.SQL_TEXT,
     P_SQL_FULLTEXT,
     UPPER(SYS_CONTEXT('USERENV', 'OS_USER')), -- sp_xb_audit_ddl_lhr.os_user
     SYS_CONTEXT('userenv', 'ip_address'), --ora_client_ip_address
     SYS_CONTEXT('userenv', 'terminal'), --sys_context('userenv', 'host')
     NVL2(ORA_LOGIN_USER,
          SYS_CONTEXT('USERENV', 'SESSION_USER'),
          SP_XB_AUDIT_DDL_LHR.DB_SCHEMA), -- SYS_CONTEXT('USERENV', 'SESSION_USER') sys.login_user
     SP_XB_AUDIT_DDL_LHR.SID, ---- SYS_CONTEXT('USERENV', 'SID'),
     SP_XB_AUDIT_DDL_LHR.SERIAL#,
     SP_XB_AUDIT_DDL_LHR.SPID,
     SP_XB_AUDIT_DDL_LHR.SESSION_TYPE || V_TMP,
     ORA_DATABASE_NAME, --sys_context('USERENV', 'DB_NAME')
     ORA_DICT_OBJ_OWNER,
     DBMS_UTILITY.FORMAT_ERROR_STACK,
     SP_XB_AUDIT_DDL_LHR.SQL_ID,
     SP_XB_AUDIT_DDL_LHR.PREV_SQL_ID);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
END PRO_TRI_DDL_INSET_LHR;

CREATE OR REPLACE PROCEDURE PRO_TRI_DDL_INSET_LHR(P_SQL_FULLTEXT VARCHAR2)

AUTHID CURRENT_USER AS

SP_XB_AUDIT_DDL_LHR XB_AUDIT_DDL_LHR%ROWTYPE;

V_TMP VARCHAR2(255);

BEGIN

SELECT A.SID,

A.SERIAL#,

(SELECT B.SPID

FROM GV$PROCESS B

WHERE B.ADDR = A.PADDR

AND B.INST_ID = USERENV('INSTANCE')) SPID,

UPPER(A.OSUSER) OSUSER,

A.MACHINE || '--' || A.PROGRAM || '--' || A.MODULE || '--' ||

A.ACTION SESSION_TYPE,

SUBSTR(P_SQL_FULLTEXT, 1, 3900),

A.SQL_ID,

A.PREV_SQL_ID,

A.USERNAME,

A.INST_ID

INTO SP_XB_AUDIT_DDL_LHR.SID,

SP_XB_AUDIT_DDL_LHR.SERIAL#,

SP_XB_AUDIT_DDL_LHR.SPID,

SP_XB_AUDIT_DDL_LHR.OS_USER,

SP_XB_AUDIT_DDL_LHR.SESSION_TYPE,

SP_XB_AUDIT_DDL_LHR.SQL_TEXT,

SP_XB_AUDIT_DDL_LHR.SQL_ID,

SP_XB_AUDIT_DDL_LHR.PREV_SQL_ID,

SP_XB_AUDIT_DDL_LHR.DB_SCHEMA,

SP_XB_AUDIT_DDL_LHR.INST_ID

FROM GV$SESSION A

WHERE A.AUDSID = USERENV('SESSIONID')

AND A.INST_ID = USERENV('INSTANCE');

INSERT INTO XB_AUDIT_DDL_LHR

(ID,

INST_ID,

OPER_DATE,

OPERATION,

OBJECT_TYPE,

OBJECT_NAME,

SQL_TEXT,

SQL_FULLTEXT,

OS_USER,

CLIENT_IP,

CLIENT_HOSTNAME,

DB_SCHEMA,

SID,

SERIAL#,

SPID,

SESSION_TYPE,

DATABASE_NAME,

OBJECT_OWNER,

ERRORS_INFOR,

SQL_ID,

PREV_SQL_ID)

VALUES

(S_XB_AUDIT_DDL_LHR.NEXTVAL,

USERENV('INSTANCE'), -- sp_xb_audit_ddl_lhr.INST_ID ora_instance_num

SYSDATE,

ORA_SYSEVENT, --sys.sysevent

ORA_DICT_OBJ_TYPE,

ORA_DICT_OBJ_NAME,

SP_XB_AUDIT_DDL_LHR.SQL_TEXT,

P_SQL_FULLTEXT,

UPPER(SYS_CONTEXT('USERENV', 'OS_USER')), -- sp_xb_audit_ddl_lhr.os_user

SYS_CONTEXT('userenv', 'ip_address'), --ora_client_ip_address

SYS_CONTEXT('userenv', 'terminal'), --sys_context('userenv', 'host')

NVL2(ORA_LOGIN_USER,

SYS_CONTEXT('USERENV', 'SESSION_USER'),

SP_XB_AUDIT_DDL_LHR.DB_SCHEMA), -- SYS_CONTEXT('USERENV', 'SESSION_USER') sys.login_user

SP_XB_AUDIT_DDL_LHR.SID, ---- SYS_CONTEXT('USERENV', 'SID'),

SP_XB_AUDIT_DDL_LHR.SERIAL#,

SP_XB_AUDIT_DDL_LHR.SPID,

SP_XB_AUDIT_DDL_LHR.SESSION_TYPE || V_TMP,

ORA_DATABASE_NAME, --sys_context('USERENV', 'DB_NAME')

ORA_DICT_OBJ_OWNER,

DBMS_UTILITY.FORMAT_ERROR_STACK,

SP_XB_AUDIT_DDL_LHR.SQL_ID,

SP_XB_AUDIT_DDL_LHR.PREV_SQL_ID);

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

END PRO_TRI_DDL_INSET_LHR;

下面的触发器将会话退出和数据库关闭的信息加入日志表XB_AUDIT_DDL_LHR中：

CREATE OR REPLACE TRIGGER TRI_AUDIT_LOGOFF_LHR
  BEFORE LOGOFF OR SHUTDOWN ON DATABASE --database 、 SCHEMA
  -- 退出、数据库关闭 
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  -- 调用存过
  PRO_TRI_DDL_INSET_LHR('');
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
END;

CREATE OR REPLACE TRIGGER TRI_AUDIT_LOGOFF_LHR

BEFORE LOGOFF OR SHUTDOWN ON DATABASE --database 、 SCHEMA

-- 退出、数据库关闭

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

-- 调用存过

PRO_TRI_DDL_INSET_LHR('');

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

END;

此时若有会话退出的话，查询日志表XB_AUDIT_DDL_LHR可以看到如下的信息：

-- 需要保证都是VALID状态
SELECT DO.OWNER,DO.OBJECT_NAME,STATUS FROM dba_objects do where do.OBJECT_NAME IN ('PRO_TRI_DDL_INSET_LHR','TRI_AUDIT_LOGOFF_LHR' ); 

-- 查询日志
select * from XB_AUDIT_DDL_LHR;

-- 需要保证都是VALID状态

SELECT DO.OWNER,DO.OBJECT_NAME,STATUS FROM dba_objects do where do.OBJECT_NAME IN ('PRO_TRI_DDL_INSET_LHR','TRI_AUDIT_LOGOFF_LHR' );

-- 查询日志

select * from XB_AUDIT_DDL_LHR;

下面再创建登录和DDL触发器，脚本如下所示：

CREATE OR REPLACE TRIGGER TRI_AUDIT_DDL_LHR
  -- AFTER DDL OR servererror OR logon OR STARTUP ON DATABASE --database 、 SCHEMA
  -- ddl语句、服务器报错、用户登陆、用户退出、数据库启动、数据库关闭
  AFTER DDL OR SERVERERROR OR LOGON OR STARTUP ON DATABASE
/* WHEN (ora_dict_obj_name NOT IN
  ('XB_AUDIT_DDL_LHR', 'PRO_TRI_DDL_INSET_LHR') OR
  ora_dict_obj_name IS NULL)*/
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  N              NUMBER;
  V_SQL_FULLTEXT VARCHAR2(32767) := NULL;
  SQL_TEXT       ORA_NAME_LIST_T;
BEGIN
  IF ORA_SYSEVENT = 'LOGON' THEN
    --  填充 v$session 的 CLIENT_INFO 和  CLIENT_IDENTIFIER   列
    DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT('userenv',
                                                      'ip_address'));
    DBMS_SESSION.SET_IDENTIFIER(SYS_CONTEXT('userenv', 'HOST'));
  ELSE
    -- 得到执行的DDL语句
    BEGIN
      N := ORA_SQL_TXT(SQL_TEXT);
      FOR I IN 1 .. N LOOP
        V_SQL_FULLTEXT := V_SQL_FULLTEXT || SQL_TEXT(I);
      END LOOP;
    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK;
    END;

  END IF;
  -- 插入日志
  PRO_TRI_DDL_INSET_LHR(V_SQL_FULLTEXT);
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
END TRI_AUDIT_DDL_LHR;

CREATE OR REPLACE TRIGGER TRI_AUDIT_DDL_LHR

-- AFTER DDL OR servererror OR logon OR STARTUP ON DATABASE --database 、 SCHEMA

-- ddl语句、服务器报错、用户登陆、用户退出、数据库启动、数据库关闭

AFTER DDL OR SERVERERROR OR LOGON OR STARTUP ON DATABASE

/* WHEN (ora_dict_obj_name NOT IN

('XB_AUDIT_DDL_LHR', 'PRO_TRI_DDL_INSET_LHR') OR

ora_dict_obj_name IS NULL)*/

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

N NUMBER;

V_SQL_FULLTEXT VARCHAR2(32767) := NULL;

SQL_TEXT ORA_NAME_LIST_T;

BEGIN

IF ORA_SYSEVENT = 'LOGON' THEN

-- 填充 v$session 的 CLIENT_INFO 和 CLIENT_IDENTIFIER 列

DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT('userenv',

'ip_address'));

DBMS_SESSION.SET_IDENTIFIER(SYS_CONTEXT('userenv', 'HOST'));

ELSE

-- 得到执行的DDL语句

BEGIN

N := ORA_SQL_TXT(SQL_TEXT);

FOR I IN 1 .. N LOOP

V_SQL_FULLTEXT := V_SQL_FULLTEXT || SQL_TEXT(I);

END LOOP;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

END;

END IF;

-- 插入日志

PRO_TRI_DDL_INSET_LHR(V_SQL_FULLTEXT);

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

END TRI_AUDIT_DDL_LHR;

测试DDL触发器，创建表、TRUNCATE表、删除表，如下所示：

CREATE TABLE TEST_DDL AS SELECT * FROM DUAL;
TRUNCATE TABLE TEST_DDL; 
DROP TABLE TEST_DDL;
SELECT * FROM XB_AUDIT_DDL_LHR T WHERE T.OBJECT_NAME='TEST_DDL';

CREATE TABLE TEST_DDL AS SELECT * FROM DUAL;

TRUNCATE TABLE TEST_DDL;

DROP TABLE TEST_DDL;

SELECT * FROM XB_AUDIT_DDL_LHR T WHERE T.OBJECT_NAME='TEST_DDL';

若有服务器的错误，也可以被记录下来，如下所示：

SELECT * FROM XB_AUDIT_DDL_LHR T WHERE T.OPERATION='SERVERERROR';

1	SELECT * FROM XB_AUDIT_DDL_LHR T WHERE T.OPERATION='SERVERERROR';

本小节的监控内容也解决了前面提出的一个问题，“如何监控会话的登录登出情况？”，可以通过查询日志表XB_AUDIT_DDL_LHR来解决，如下所示：

SELECT T.OS_USER, T.CLIENT_IP, COUNT(1)
  FROM XB_AUDIT_DDL_LHR T
 GROUP BY T.OS_USER, T.CLIENT_IP;

SELECT T.OS_USER, T.CLIENT_IP, COUNT(1)

FROM XB_AUDIT_DDL_LHR T

GROUP BY T.OS_USER, T.CLIENT_IP;

示例3：在Oracle中，怎么捕获整个数据库的DDL语句或者是说捕获对象结构变化与修改？

CREATE OR REPLACE TRIGGER TR_TRACE_DDL
AFTER DDL ON DATABASE 
DECLARE
  SQL_TEXT ORA_NAME_LIST_T;
  STATE_SQL DDL$TRACE.DDL_SQL%TYPE;
BEGIN
   FOR I IN 1..ORA_SQL_TXT(SQL_TEXT) LOOP
      STATE_SQL := STATE_SQL||SQL_TEXT(I);
   END LOOP;
   INSERT INTO DDL$TRACE(LOGIN_USER,DDL_TIME,IP_ADDRESS,AUDSID,
                    SCHEMA_USER,SCHEMA_OBJECT,DDL_SQL)
        VALUES(ORA_LOGIN_USER,SYSDATE,USERENV('SESSIONID'),
               SYS_CONTEXT('USERENV','IP_ADDRESS'),
               ORA_DICT_OBJ_OWNER,ORA_DICT_OBJ_NAME,STATE_SQL);
EXCEPTION 
   WHEN OTHERS THEN 
      SP_WRITE_LOG('CAPTURE DDL EXCPTION:'||SQLERRM);
END TR_TRACE_DDL;

CREATE OR REPLACE TRIGGER TR_TRACE_DDL

AFTER DDL ON DATABASE

DECLARE

SQL_TEXT ORA_NAME_LIST_T;

STATE_SQL DDL$TRACE.DDL_SQL%TYPE;

BEGIN

FOR I IN 1..ORA_SQL_TXT(SQL_TEXT) LOOP

STATE_SQL := STATE_SQL||SQL_TEXT(I);

END LOOP;

INSERT INTO DDL$TRACE(LOGIN_USER,DDL_TIME,IP_ADDRESS,AUDSID,

SCHEMA_USER,SCHEMA_OBJECT,DDL_SQL)

VALUES(ORA_LOGIN_USER,SYSDATE,USERENV('SESSIONID'),

SYS_CONTEXT('USERENV','IP_ADDRESS'),

ORA_DICT_OBJ_OWNER,ORA_DICT_OBJ_NAME,STATE_SQL);

EXCEPTION

WHEN OTHERS THEN

SP_WRITE_LOG('CAPTURE DDL EXCPTION:'||SQLERRM);

END TR_TRACE_DDL;

在创建以上触发器时要注意几点

1、必须创建一个DDL$TRACE的表，用来记录DDL的记录

2、SP_WRITE_LOG是一个记录日志的存储过程，可以根据自己的需要进行调整。

示例4：怎么捕获用户登录信息，如SID，IP地址等？

可以利用登录触发器，如：

CREATE OR REPLACE TRIGGER TR_LOGIN_RECORD
AFTER LOGON ON DATABASE
DECLARE
MIUSERSID NUMBER;
MTSESSION V$SESSION%ROWTYPE;
CURSOR CSESSION(IIUSERSID IN NUMBER) IS
   SELECT * FROM V$SESSION
      WHERE SID=IIUSERSID;
BEGIN
  SELECT SID INTO MIUSERSID FROM V$MYSTAT WHERE ROWNUM<=1;
  OPEN CSESSION(MIUSERSID);
  FETCH CSESSION INTO MTSESSION;
  -- IF USER EXISTS THEN INSERT DATA
  IF CSESSION%FOUND THEN
     INSERT INTO LOG$INFORMATION(LOGIN_USER,LOGIN_TIME,IP_ADRESS,AUSID,TERMINAL, 
                     OSUSER,MACHINE,PROGRAM,SID,SERIAL#)
              VALUES(ORA_LOGIN_USER,SYSDATE,SYS_CONTEXT ('USERENV','IP_ADDRESS'), 
                      USERENV('SESSIONID'),
                      MTSESSION.TERMINAL,MTSESSION.OSUSER,
                      MTSESSION.MACHINE,MTSESSION.PROGRAM,
                      MTSESSION.SID,MTSESSION.SERIAL#);
  ELSE
  -- IF USER DON'T EXISTS THEN RETURN ERROR
      SP_WRITE_LOG('SESSION INFORMATION ERROR:'||SQLERRM);
      CLOSE CSESSION;
      RAISE_APPLICATION_ERROR(-20099,'LOGIN EXCEPTION',FALSE);
  END IF;
  CLOSE CSESSION;
EXCEPTION
  WHEN OTHERS THEN
    SP_WRITE_LOG('LOGIN TRIGGER ERROR:'||SQLERRM);
END TR_LOGIN_RECORD;

CREATE OR REPLACE TRIGGER TR_LOGIN_RECORD

AFTER LOGON ON DATABASE

DECLARE

MIUSERSID NUMBER;

MTSESSION V$SESSION%ROWTYPE;

CURSOR CSESSION(IIUSERSID IN NUMBER) IS

SELECT * FROM V$SESSION

WHERE SID=IIUSERSID;

BEGIN

SELECT SID INTO MIUSERSID FROM V$MYSTAT WHERE ROWNUM<=1;

OPEN CSESSION(MIUSERSID);

FETCH CSESSION INTO MTSESSION;

-- IF USER EXISTS THEN INSERT DATA

IF CSESSION%FOUND THEN

INSERT INTO LOG$INFORMATION(LOGIN_USER,LOGIN_TIME,IP_ADRESS,AUSID,TERMINAL,

OSUSER,MACHINE,PROGRAM,SID,SERIAL#)

VALUES(ORA_LOGIN_USER,SYSDATE,SYS_CONTEXT ('USERENV','IP_ADDRESS'),

USERENV('SESSIONID'),

MTSESSION.TERMINAL,MTSESSION.OSUSER,

MTSESSION.MACHINE,MTSESSION.PROGRAM,

MTSESSION.SID,MTSESSION.SERIAL#);

ELSE

-- IF USER DON'T EXISTS THEN RETURN ERROR

SP_WRITE_LOG('SESSION INFORMATION ERROR:'||SQLERRM);

CLOSE CSESSION;

RAISE_APPLICATION_ERROR(-20099,'LOGIN EXCEPTION',FALSE);

END IF;

CLOSE CSESSION;

EXCEPTION

WHEN OTHERS THEN

SP_WRITE_LOG('LOGIN TRIGGER ERROR:'||SQLERRM);

END TR_LOGIN_RECORD;

在以上触发器中需要注意以下几点：
1、该用户有V_$SESSION与V_$MYSTAT的对象查询权限，可以在SYS下对该拥护显式授权。
2、SP_WRITE_LOG是一个记录日志的存储过程，可以根据自己的需要进行调整。
3、必须在创建该触发器之前创建一个LOG$INFORMATION的表记录登录信息。

示例5：怎么捕获表上的DML语句？

可以采用DML触发器，如：

CREATE OR REPLACE TRIGGER TR_CAPT_SQL
BEFORE DELETE OR INSERT OR UPDATE 
   ON MANAGER.TEST
DECLARE
SQL_TEXT ORA_NAME_LIST_T;
STATE_SQL CAPT$SQL.SQL_TEXT%TYPE;
BEGIN
FOR I IN 1..ORA_SQL_TXT(SQL_TEXT) LOOP
    STATE_SQL := STATE_SQL || SQL_TEXT(I);
END LOOP;
    INSERT INTO CAPT$SQL(LOGIN_USER,CAPT_TIME,IP_ADDRESS,AUDSID,OWNER,TABLE_NAME,SQL_TEXT)
        VALUES(ORA_LOGIN_USER,SYSDATE,SYS_CONTEXT('USERENV','IP_ADDRESS'),
               USERENV('SESSIONID'),'MANAGER','TEST',STATE_SQL);
EXCEPTION 
     WHEN OTHERS THEN
       SP_WRITE_LOG('CAPTURE DML EXCEPTION:'||SQLERRM);
END TR_CAPT_SQL;

CREATE OR REPLACE TRIGGER TR_CAPT_SQL

BEFORE DELETE OR INSERT OR UPDATE

ON MANAGER.TEST

DECLARE

SQL_TEXT ORA_NAME_LIST_T;

STATE_SQL CAPT$SQL.SQL_TEXT%TYPE;

BEGIN

FOR I IN 1..ORA_SQL_TXT(SQL_TEXT) LOOP

STATE_SQL := STATE_SQL || SQL_TEXT(I);

END LOOP;

INSERT INTO CAPT$SQL(LOGIN_USER,CAPT_TIME,IP_ADDRESS,AUDSID,OWNER,TABLE_NAME,SQL_TEXT)

VALUES(ORA_LOGIN_USER,SYSDATE,SYS_CONTEXT('USERENV','IP_ADDRESS'),

USERENV('SESSIONID'),'MANAGER','TEST',STATE_SQL);

EXCEPTION

WHEN OTHERS THEN

SP_WRITE_LOG('CAPTURE DML EXCEPTION:'||SQLERRM);

END TR_CAPT_SQL;

在创建以上触发器时要注意以下几点：

1、必须创建一个CAPT$SQL的表，用来记录DDL的记录

2、SP_WRITE_LOG是一个记录日志的存储过程，可以根据自己的需要进行调整。

报错：ORA_SQL_TXT函数报错ORA-06502: PL/SQL: numeric or value error

在11.2.0.4环境中执行，报错ORA-06502: PL/SQL: numeric or value error，未找到原因，只能定义为bug。

总结

1、SYS用户的操作不会被审计记录

2、FGA细粒度审计不需要配置audit_trail参数为db_extended即可记录sqltext

3、隐含参数“_SYSTEM_TRIG_ENABLED”的默认值是TRUE，即允许DDL和系统触发器。当设置隐含参数“_SYSTEM_TRIG_ENABLED”为FALSE的时候，将禁用DDL和系统触发器。

打赏赞(1)

标签： DDL Oracle 审计触发器

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

2023年 5月
一	二	三	四	五	六	日
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31

AI (2)
ClickHouse (5)
DM达梦 (9)
Docker (34)
GBase (37)
Greenplum (88)
HANA (2)
K8S (14)
Kafka (3)
Memcached (2)
MongoDB (3)
MySQL (180)
NoSQL (3)
OceanBase (14)
OGG (43)
openGauss (9)
Oracle (593)
OS (330)
PostgreSQL (230)
Python (26)
Redis (18)
SQL Server (106)
SQLite (1)
StarRocks (9)
TDSQL (5)
TiDB (18)
YMatrix (5)
中间件 (9)
云 (10)
人大金仓KingBase (7)
其它 (78)
培训考证 (43)
大数据 (4)
存储 (4)
巡检监控 (44)
工具 (67)
数据迁移 (16)
架构篇 (9)
生活 (8)
电脑 (15)
网络 (81)
育儿 (5)
视频 (17)
面试笔试 (38)

嘿，我是小麦，需要帮助随时找我哦

18509239930
个人微信
麦老师QQ聊天
个人邮箱
点击加入QQ群
个人微店
回到顶部

小麦苗

Oracle中的审计以及登陆登出、DDL触发器记录表审计等

什么是审计（Audit）？

启用审计

数据字典

迁移SYS.AUD$表

授予审计权限

审计的分类

基于值的审计

审计中BY ACCESS和BY SESSION的区别是什么？

如何对SYSDBA和SYSOPER进行审计？

什么是细粒度审计？

如何禁用或删除某个用户下的所有细粒度审计？

FGA审计和标准审计有什么区别？

示例1：对lhr.t1表做DELETE审计

标准审计

FGA细粒度审计(推荐)

触发器(不推荐)

示例2：如何监控数据库的登陆登出、DDL语句等内容？

示例3：在Oracle中，怎么捕获整个数据库的DDL语句或者是说捕获对象结构变化与修改？

示例4：怎么捕获用户登录信息，如SID，IP地址等？

示例5：怎么捕获表上的DML语句？

报错：ORA_SQL_TXT函数报错ORA-06502: PL/SQL: numeric or value error

总结

相关文章

您可能还喜欢...

发表回复取消回复

博客寄语

博客概况

本站其它工具

搜标题

搜索本网站

标签云☁

登陆本博客

博客日历

博客归档

博客分类

Oracle中的审计以及登陆登出、DDL触发器记录表审计等

什么是审计（Audit）？

启用审计

数据字典

迁移SYS.AUD$表

授予审计权限

审计的分类

基于值的审计

审计中BY ACCESS和BY SESSION的区别是什么？

如何对SYSDBA和SYSOPER进行审计？

什么是细粒度审计？

如何禁用或删除某个用户下的所有细粒度审计？

FGA审计和标准审计有什么区别？

示例1：对lhr.t1表做DELETE审计

标准审计

FGA细粒度审计(推荐)

触发器(不推荐)

示例2：如何监控数据库的登陆登出、DDL语句等内容？

示例3：在Oracle中，怎么捕获整个数据库的DDL语句或者是说捕获对象结构变化与修改？

示例4：怎么捕获用户登录信息，如SID，IP地址等？

示例5：怎么捕获表上的DML语句？

报错：ORA_SQL_TXT函数报错ORA-06502: PL/SQL: numeric or value error

总结

相关文章

您可能还喜欢...

新书出版 |《Oracle程序员面试笔试宝典》

Oracle rac 21c配置EMDE

Oracle将Standard标准集群修改为Flex集群

发表回复 取消回复

博客寄语

博客概况

本站其它工具

搜标题

搜索本网站

标签云☁

登陆本博客

博客日历

博客归档

博客分类

发表回复取消回复