合 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
- 总结
什么是审计（Audit）？
审计（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$审计字典基表上。
1 2 3 4 | 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的值。
数据字典
有关数据库审计的一些数据字典视图如下所示：
1 2 3 4 5 6 7 8 9 10 | 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之前迁移方法如下所示：
1 2 3 4 5 6 7 | 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进行迁移：
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$表，如下：
1 2 | TRUNCATE TABLE SYS.AUD$; DELETE FROM SYS.AUD$ WHERE OBJ$NAME='EMP'; |
需要注意的是，如果AUD$表过大，那么直接TRUNCATE AUD$表，系统要立即释放大量的EXTENTS，会严重影响系统性能。可以通过如下2个步骤逐步释放EXTENTS：
① 清空数据并且保留原来的EXTENTS：
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：
1 2 3 4 5 6 7 | 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表空间：
1 2 3 4 5 6 7 8 | 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过程可以设置审计的清除间隔，还有很多其它的实用存储过程请参考官方文档。
授予审计权限
如下示例为授予审计的一些权限：
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触发器。
以下是一个审计触发器的典型示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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。
1 2 3 4 5 6 | 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中。
请问博主，如果有用户做了数据导出的这个行为，那么我如何进行审计呢？
审计expdp或exp就行