本文参考文章： 5
概述： 5
审计功能分类： 5
一、 审计相关实例参数 6
? audit_sys_operations 6
一..1 linux/unix 6
一..2 windows 7
二、 标准审计 10
(一) 权限审计（About Privilege Auditing） 10
1. 默认审计 10
11G ：如下特权调用时默认会进行审计。 10
2. 如何禁用默认审计项目和启用默认审计项目? 11
启用默认权限审计： 11
删除默认特权审计： 11
3. 查看目前权限审计内容 11
4. 执行标准审计的权限 12
5. 什么情况下产生审计记录 12
6. 在unix系统上通过syslog服务记录审计信息 12
7. 权限审计语法 12
(二) 语句审计 13
1. WHENEVER SUCCESSFUL/ WHENEVER NOT SUCCESSFU 13
2. Auditing Actions Performed by Specific Users 13
3. Configuring SQL Statement Auditing 14
(三) 审计模式对象 15
1. Configuring Auditing for a Schema Table 15
2. Auditing Successful Statements on a Schema Table 15
3. Configuring Auditing for Any New Objects Using the DEFAULT Clause 16
4. Auditing the Execution of a Procedure or Function 16
5. 关于视图访问时的审计举例： 17
6. 删除模式对象上的审计 17
7. Auditing Directory Objects 18
8. Auditing Functions, Procedures, Packages, and Triggers 18
9. Auditing All Functions, Procedures, Packages, and Triggers 19
10. Auditing a User's Execution of Functions, Procedures, Packages, and Triggers 19
11. Auditing the Execution of a Procedure or Function within a Schema 19
12. Removing the Auditing of Functions, Procedures, Packages, and Triggers 19
13. Auditing Network Activity 19
(四) 查看有哪些标准审计项目被创建： 20
三、 细粒度审计 20
三..1 创建细粒度审计策略 21
三..2 审计指定的列和行 26
三..3 启动和禁用FGA策略 26
三..4 删除FGA策略 27
三..5 其他功能 27
Tutorial: Adding an Email Alert to a Fine-Grained Audit Policy 27
Tutorial: Auditing Nondatabase Users 27
四、 管理审计记录信息 27
四..1.1 定期导出并自动清理审计信息方法： 28
四..1.2 移动日志表到非系统表空间方法： 33
四..1.3 Managing the Operating System Audit Trail 34
四..1.4 使用 DBMS_AUDIT_MGMT 手动或自动清理审计记录 35
四..1.5 设置自动清理审计记录的定时任务 35
四..1.6 手动调用清理过程进行审计记录的清理 40
四..1.7 删除部分审计记录中的信息 42
四..1.8 其他清除相关操作 43
1) Verifying That the Audit Trail Is Initialized for Cleanup 43
2) Setting the Default Audit Trail Purge Interval for Any Audit Trail Type 43
3) Cancelling the Initialization Cleanup Settings 44
4) Enabling or Disabling an Audit Trail Purge Job 44
5) Setting the Default Audit Trail Purge Job Interval for a Specified Purge Job 45
6) Deleting an Audit Trail Purge Job 46
7) Clearing the Archive Timestamp Setting 46
8) Clearing the Database Audit Trail Batch Size 47
五、 和审计记录相关的数据字典视图 48
本文参考文章：
http://docs.oracle.com/cd/E11882_01/network.112/e36292/auditing.htm#DBSEG60061
概述：
数据库审计功能是oracle自身提供的对数据库操作进行记录的功能。可以审计权限的调用记录、用户的dml操作记录、查询操作记录等等。本文便于大家理解审计功能，内容主要来自官方文档，日常维护操作主要涉及细粒度审计和管理审计记录信息、审计记录的查询功能等章节。
适用版本10g 、11g
11gR2增加了审计记录的管理功能 Audit Data Management
审计功能分类：
oracle审计分标准审计和细粒度审计（FGA）。
标准审计又分语句审计、权限审计、模式对象审计。
一、 审计相关实例参数
l audit_sys_operations
如果设置audit_sys_operations为true（默认false），则在生成的日志文件中会记录所有当前SYSDBA用户执行的sql语句。
一..1 linux/unix
默认每次SYSDBA or SYSOPER用户登录都会在audit_file_dest指定的位置生成有一个日志文件（格式如：orcl_ora_8927_20161021155140021145143795.aud，其中8927是连接会话的服务器进程号$ORACLE_SID_short_form_process_name_processid_sequence_number.aud
登录信息如：
oracle@localhost adump]$ tail -f orcl_ora_8927_20161021155140021145143795.aud
Fri Oct 21 15:51:40 2016 +08:00
LENGTH : '160'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[10] '1446178924'
一..2 windows
widnows环境下无论是否设置audit_sys_operations参数都不会在audit_file_dest中生成文件，而是默认记录在window系统的事件查看器中。
同样如果不设置audit_sys_operations=true则只记录登录信息和默认审计信息，如果设置为true则所有sysdba会话执行的sql都会记录下来。
l audit_trail
AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }
none 禁用数据库审计
os 启用数据库审计，并将数据库审计记录定向到操作系统审计记录
db 启用数据库审计，并将数据库所有审计记录定向到数据库的SYS.AUD$表
db,extended 启用数据库审计，并将数据库所有审计记录定向到数据库的SYS.AUD$表。另外，填充SYS.AUD$表的SQLBIND 列和SQLTEXT CLOB 列。
xml 启用数据库审计，并将所有记录写到XML格式的操作系统文件中。
xml,extended 启用数据库审计，输出审计记录的所有列，包括SqlText和SqlBind的值。
http://docs.oracle.com/cd/E11882_01/network.112/e36292/auditing.htm#DBSEG006
http://docs.oracle.com/cd/E11882_01/network.112/e36292/auditing.htm#DBSEG343
audit trail =DB ，aud$ 默认记录会话的登录、退出信息。
不记录sys用户的
|
| SESSIONID | ENTRYID | STATEMENT | TIMESTAMP# | USERID | USERHOST | TERMINAL | ACTION# | RETURNCODE | OBJ$CREATOR | OBJ$NAME | AUTH$PRIVILEGES | AUTH$GRANTEE | NEW$OWNER | NEW$NAME | SES$ACTIONS | SES$TID | LOGOFF$LREAD | LOGOFF$PREAD | LOGOFF$LWRITE | LOGOFF$DEAD | LOGOFF$TIME | COMMENT$TEXT | CLIENTID | SPARE1 | SPARE2 | OBJ$LABEL | SES$LABEL | PRIV$USED | SESSIONCPU | NTIMESTAMP# | PROXY$SID | USER$GUID | INSTANCE# | PROCESS# | XID | AUDITID | SCN | DBID | SQLBIND | SQLTEXT | OBJ$EDITION |
| ## | ## | 1 | 1 | SCOTT | localhost.localdomain | pts/0 | ## | 0 | Authenticated by: DATABASE | oracle | 5 | 22-10月-16 01.17.24.103645 下午 | 0 | 3484 | 0000000000000000 | ## | ||||||||||||||||||||||||||
l AUDIT_SYSLOG_LEVEL
audit_file_dest 中每连接一个会话就会生成一个aud文件，其中记录登录退出信息。如果同时设置了audit_syslog_level ，如设置 local1.warnning linux/unix则会发送到系统的syslog组件（如linux默认/var/log/message）。
| Property | Description |
| Parameter type | String |
| Syntax | AUDIT_SYSLOG_LEVEL = 'facility_clause.priority_clause' |
|
| facility_clause::= { USER | LOCAL[0 | 1 | 2 | 3 | 4 | 5 | 6 | 7] | SYSLOG | DAEMON | KERN | MAIL | AUTH | LPR | NEWS | UUCP | CRON } |
|
| priority_clause::= { NOTICE | INFO | DEBUG | WARNING | ERR | CRIT | ALERT | EMERG } |
| Default value | There is no default value. |
| Modifiable | No |
| Basic | No |
| Examples |
AUDIT_SYSLOG_LEVEL = 'KERN.EMERG'; AUDIT_SYSLOG_LEVEL = 'LOCAL1.WARNING'; |
如何自定义审计日志的存放位置？
设置了AUDIT_SYSLOG_LEVEL=local1.warning ，然后在/etc/syslog.conf（rhel5.x）或者/etc/rsyslog.conf（rhel 6.x）
添加:
local1.warning /var/log/ora_audit.log
/var/log/ora_audit.log自定义的位置。syslog 或rsyslog服务重启服务生效。
参考：
How To Set the AUDIT_SYSLOG_LEVEL Parameter? (文档 ID 553225.1)
关于syslogd服务
http://linux.vbird.org/linux_basic/0570syslog.php#syslogd 《鸟哥私房菜》
二、 标准审计
(一) 权限审计（About Privilege Auditing）
当系统权限被调用时才会进行审计工作，如scott用户被赋予了select any table 的权限，当scott访问自己名下的表示emp时是不会进行审计的，因为这并不调用select any table的权限，当scott查询HR用户下的EMP表示，调用了select any table的权限，则这个操作就会被审计记录。
AUDIT_TRAIL 设置非NONE就是表示打开了标准审计。
1. 默认审计
当以下权限被调用时，如果audit_tial 非none默认会记录到到审计AUD$表中。并且如果是是db,ext... 则会记录执行的具体语句到。
11G ：如下特权调用时默认会进行审计。| ALTER ANY PROCEDURE | CREATE ANY LIBRARY | DROP ANY TABLE |
| ALTER ANY TABLE | CREATE ANY PROCEDURE | DROP PROFILE |
| ALTER DATABASE | CREATE ANY TABLE | DROP USER |
| ALTER PROFILE | CREATE EXTERNAL JOB | EXEMPT ACCESS POLICY |
| ALTER SYSTEM | CREATE PUBLIC DATABASE LINK | GRANT ANY OBJECT PRIVILEGE |
| ALTER USER | CREATE SESSION | GRANT ANY PRIVILEGE |
| AUDIT SYSTEM | CREATE USER | GRANT ANY ROLE |
| CREATE ANY JOB | DROP ANY PROCEDURE |
Oracle Database audits the following SQL shortcuts by default:
| ROLE | SYSTEM AUDIT | PUBLIC SYNONYM |
| DATABASE LINK | PROFILE |
2. 如何禁用默认审计项目和启用默认审计项目 ? 启用默认权限审计：
$ORACLE_HOME/rdbms/admin/secconf.sql
运行secconf.sql其中有条修改默认密码安全策略的sql 要注意：
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
;
要注意该脚本对密码策略的修改。
删除默认特权审计：$ORACLE_HOME/rdbms/admin/undoaud.sql
3. 查看目前权限审计内容select * from dba_priv_audit_opts;
select * from dba_stmt_audit_opts;
注：手动建库默认不开启默认权限审计，DBCA建库默认开启权限审计
另外存在mandatory auditing（强制审计）.当audit_trail=db时 ，有些信息是强制进行审计的，包括数据库启动、关闭信息、sysdba and sysoper的登录信息。
4. 执行标准审计的权限
审计命令是audit ，撤销是noaudit，用户可以对自己名下的对象进行打开或者关闭审计，而不需要其他特殊的权限，如果audit_trail的值是none ，audit的命令也可以执行成功，但是不会记录，当以后将audit_trail的值设置成非none才开始记录审计信息。
5. 什么情况下产生审计记录审计信息的记录被定义为单独的事务，也就是说即使用户的事务被回滚，审计的信息依然会记录。
对于语句审计和权限审计，对当前会话不生效，对新建会话生效。
而模式对象审计则修改设置后对当前会话立即生效。
如果数据库处于read only的只读模式，则数据库启动时audit_trail参数会自动将值修改为OS。
FGA和audit_trail 以及AUDIT_SYS_OPERATIONS 实例参数无关。
DBA_COMMON_AUDIT_TRAIL视图包换所有的标准审计和FGA审计记录信息。
6. 在 unix系统上通过 syslog服务记录审计信息当AUDIT_TRAIL 设置为OS，或者os,extended ,则写到audmp中，
7. 权限审计语法AUDIT DELETE ANY TABLE BY ACCESS;
对于特权审计 audit + xxx +by [asscess|session]，其中xxx和附权时的语法 一样。
取消指定特权审计
NOAUDIT DELETE ANY TABLE;
取消全部特权审计
NOAUDIT ALL PRIVILEGES;
设置特权审计需要用户有AUDIT SYSTEM的权限.
(二) 语句审计
包含statement、privilege、object、network等方面的内容。
1. WHENEVER SUCCESSFUL/ WHENEVER NOT SUCCESSFU审计可以设计执行成功的（WHENEVER SUCCESSFUL）或者执行不成功的（WHENEVER NOT SUCCESSFU）或者无论成功不成功都进行审计。
审计的方式：
AUDIT SELECT TABLE BY ACCESS;默认，更加详细，oracle推荐
还有by session ，非默认
by access记录每次的操作事件。
审计指定数据库用户的行为操作
AUDIT SELECT TABLE, UPDATE TABLE BY scott, blake BY ACCESS;
回收全是使用noaudit 也可以指定用户by scott，但是不支持by access 参数。
Auditing all SQL statements for individual users
AUDIT ALL STATEMENTS BY jward, jsmith BY ACCESS WHENEVER SUCCESSFUL;
Auditing all the SQL statement shortcut activities performed by individual users
AUDIT ALL BY jward BY ACCESS;
Auditing all SQL statements for the current session, regardless of user.
AUDIT ALL STATEMENTS IN SESSION CURRENT BY ACCESS WHENEVER NOT SUCCESSFUL;
AUDIT ALL STATEMENTS IN SESSION CURRENT;
Auditing login and logoff connections and disconnections.
AUDIT SESSION BY ACCESS;
AUDIT SESSION BY jward, jsmith BY ACCESS;
如果打开了 AUDIT SESSION 审计，并且审计空间爆满，无法写入审计记录，那么普通用户将不能登录数据库！
Auditing statements that fail because an object does not exist.
AUDIT NOT EXISTS;
Example 9-9 Using NOAUDIT to Remove Session and SQL Statement Auditing
NOAUDIT session;
NOAUDIT session BY preston, sebastian;
NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;
Example 9-10 shows how to remove all statement auditing by using the NOAUDIT statement.
Example 9-10 Using NOAUDIT to Remove ALL STATEMENTS Auditing
NOAUDIT ALL STATEMENTS;
特权审计和对象审计同时发生在一个操作上时，只有对象审计会起到作用。
(三) 审计模式对象
1. Configuring Auditing for a Schema Table
AUDIT DELETE ON laurel.emp BY ACCESS;
2. Auditing Successful Statements on a Schema Table
AUDIT SELECT, INSERT, DELETE
ON jward.dept
BY ACCESS
WHENEVER SUCCESSFUL;
3. Configuring Auditing for Any New Objects Using the DEFAULT Clause
AUDIT SELECT
ON DEFAULT
BY ACCESS
WHENEVER NOT SUCCESSFUL;
后期创建新的表、视图、sequence都会自动对其对象上的select操作进行审计。
default 包含
| ALTER | EXECUTE | INSERT | SELECT |
| AUDIT | GRANT | LOCK | UPDATE |
| COMMENT | FLASHBACK | READ |
|
| DELETE | INDEX | RENAME |
ALL_DEF_AUDIT_OPTS 视图可以查询DEFAULT的审计statment。
如：
ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA
1 -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-
4. Auditing the Execution of a Procedure or Function
AUDIT EXECUTE ON sec_mgr.auth_orders BY ACCESS;
5. 关于视图访问时的审计举例：
AUDIT SELECT ON HR.EMPLOYEES BY ACCESS;
CREATE VIEW employees_departments AS
SELECT employee_id, last_name, department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;
AUDIT SELECT ON employees_departments BY ACCESS;
SELECT * FROM employees_departments;
如果查询employees_departments ，会记录对employees_departments 视图的访问的审计，还会记录对记录对基表EMPLOYEES的访问审计信息。
本例中如果去掉对 AUDIT SELECT ON HR.EMPLOYEES BY ACCESS; 操作，查询employees_departments 不会对EMPLOYEES 进行审计。
数据库用户可以对自己名下的所有的对象开启审计，但是如果要给其他用户下的对象打开审计就需要alter system权限，或者被赋予AUDIT ANY 权限。
6. 删除模式对象上的审计
NOAUDIT DELETE
ON emp;
NOAUDIT SELECT, INSERT, DELETE
ON jward.dept;
To remove all object audit options on the emp table, enter the following statement:
NOAUDIT ALL ON emp;
To remove all default object audit options, enter the following statement:
NOAUDIT ALL ON DEFAULT;
noaudit all on default ，只是将default上的默认审计全部取消掉，之后再新建的对象就默认不会去打开对象的审计功能，但是在取消审计之前建立的对象依然会被审计，除非针对对象再单独取消审计。
AUDIT ALTER, DELETE ON DEFAULT BY ACCESS;
命令之后创建的所有对象都会打开对alert 和delete的审计
7. Auditing Directory Objects
略
8. Auditing Functions, Procedures, Packages, and Triggers
你可以分别审计独立的函数，过程，程序包
如果审计程序包，则包中的函数和过程也会审计
不可以单独审计程序包中的函数和过程。
9. Auditing All Functions, Procedures, Packages, and Triggers
AUDIT EXECUTE PROCEDURE BY ACCESS;
10.Auditing a User's Execution of Functions, Procedures, Packages, and Triggers
AUDIT EXECUTE PROCEDURE BY psmith BY ACCESS;
11.Auditing the Execution of a Procedure or Function within a Schema
AUDIT EXECUTE ON sales_data.check_work BY ACCESS WHENEVER SUCCESSFUL;
12.Removing the Auditing of Functions, Procedures, Packages, and Triggers
NOAUDIT EXECUTE PROCEDURE;
NOAUDIT EXECUTE PROCEDURE BY psmith;
NOAUDIT EXECUTE ON sales_data.checkwork;
13.Auditing Network Activity
略
对于system用户的审计设置和普通用户如scott,hr没有什么区别。
标准审计中，用户自己可以打开对自己对象的审计，也可以直接关掉审计。
(四) 查看有哪些标准审计项目被创建：
dba_obj_audit_opts; （对象级审计）
dba_priv_audit_opts;（权限级审计）
dba_stmt_audit_opts;（语句级审计）
三、 细粒度审计
细粒度审计可以审计的内容：
l 对某个时间段内的操作进行审计；
l 某个IP发起的数据库访问；
l 对某列的查询更新进行审计；
l 对某列值得修改操作进行审计。
一个表或者视图最多可以有256个审计策略。
审计策略不能进行修改，只能删除后重新创建。
fga审计信息默认存放在SYS.FGA_LOG$
DBA_COMMON_AUDIT_TRAIL FGA和标准审计信息整合在一起
查询FGA审计策略
DBA_AUDIT_POLICIES
三..1 创建细粒度审计策略
开启FGA不需要修改任何数据库参数，只要有权限执行DBMS_FGA 包就可以。
除了sys用户以外所有的用户下的表或者视图都可以设置细粒度审计。
如果某列上有FGA审计策略，则该列无法进行加密解密，会报错ORA-28133: full table access is restricted by fine-grained security 如果加密解密，必须先禁用列上的FGA策略，加密解密后才开启列上的FGA策略。（实际操作中数据加密功能和FGA功能相同列上就无法共同使用)
DBMS_FGA.ADD_POLICY(
object_schema VARCHAR2,
object_name VARCHAR2,
policy_name VARCHAR2,
audit_condition VARCHAR2,
audit_column VARCHAR2,
handler_schema VARCHAR2,
handler_module VARCHAR2,
enable BOOLEAN,
statement_types VARCHAR2,
audit_trail BINARY_INTEGER IN DEFAULT,
audit_column_opts BINARY_INTEGER IN DEFAULT);
· object_schema: Specifies the schema of the object to be audited. (If NULL, the current log-on user schema is assumed.)
指定被审计对象的属主，如果不指定则使用当前登录用户。
· object_name: Specifies the name of the object to be audited.
指定被审计的对象。
· policy_name: Specifies the name of the policy to be created. Ensure that this name is unique.
设定本策略的名称，注意名称的唯一性。
· audit_condition:
审计的条件
Specifies a Boolean condition in a row. NULL is allowed and acts as TRUE. See "Auditing Specific Columns and Rows" for more information. If you specify NULL or no audit condition, then any action on a table with that policy creates an audit record, whether or not rows are returned.
o Do not include functions, which execute the auditable statement on the same base table, in the audit_condition setting. For example, suppose you create a function that executes an INSERT statement on the HR.EMPLOYEES table. The policy's audit_condition contains this function and it is for INSERT statements (as set by statement_types). When the policy is used, the function executes recursively until the system has run out of memory. This can raise the error ORA-1000: maximum open cursors exceeded or ORA-00036: maximum number of recursive SQL levels (50) exceeded.
o Do not issue the DBMS_FGA.ENABLE_POLICY or DBMS_FGA.DISABLE_POLICY statement from a function in a policy's condition.
· audit_column:
设置审计的列，如果设置成null 或者忽略，则默认审计所有的列。
Specifies one or more columns to audit, including hidden columns. If set to NULL or omitted, all columns are audited. These can include Oracle Label Security hidden columns or object type columns. The default, NULL, causes audit if any column is accessed or affected.
· handler_schema:
如果当有审计条件被触发时，进行告警，此处指定报警管理用户，默认是null使用当前用户。
If an alert is used to trigger a response when the policy is violated, specifies the name of the schema that contains the event handler. The default, NULL, uses the current schema. See also "Tutorial: Adding an Email Alert to a Fine-Grained Audit Policy".
· handler_module: Specifies the name of the event handler. Include the package the event handler is in. This function is invoked only after the first row that matches the audit condition in the query is processed.
Follow these guidelines:
o Do not create recursive fine-grained audit handlers. For example, suppose you create a handler that executes an INSERT statement on the HR.EMPLOYEES table. The policy that is associated with this handler is for INSERT statements (as set by the statement_typesparameter). When the policy is used, the handler executes recursively until the system has run out of memory. This can raise the errorORA-1000: maximum open cursors exceeded or ORA-00036: maximum number of recursive SQL levels (50) exceeded.
o Do not issue the DBMS_FGA.ENABLE_POLICY or DBMS_FGA.DISABLE_POLICY statement from a policy handler. Doing so can raise theORA-28144: Failed to execute fine-grained audit handler error.
· enable:
策略是否生效。
Enables or disables the policy using true or false. If omitted, the policy is enabled. The default is TRUE.
· statement_types:
指定被审计的sql类型， 包含insert,update,delete默认是select
Specifies the SQL statements to be audited: INSERT, UPDATE, DELETE, or SELECT only. The default is SELECT.
· audit_trail:
指定FGA审计信息存放位置，此处和数据库实例参数audit_trail的取值无任何关系。
Specifies the destination (DB or XML) of fine-grained audit records. Also specifies whether to populate LSQLTEXT and LSQLBINDin FGA_LOG$. However, be aware that sensitive data, such as credit card information, can be recorded in clear text. See "Auditing Sensitive Information" for how you can handle this scenario.
If you set the audit_trail parameter to XML, then the XML files are written to the directory specified by the AUDIT_FILE_DEST initialization parameter.
For read-only databases, Oracle Database writes the fine-grained audit trail to XML files, regardless of the audit_trail setting.
· audit_column_opts: If you specify more than one column in the audit_column parameter, then this parameter determines whether to audit all or specific columns. See "Auditing Specific Columns and Rows" for more information.
See Oracle Database PL/SQL Packages and Types Reference for additional details about the ADD_POLICY synt
举例
BEGIN
DBMS_FGA.ADD_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'chk_hr_employees',
enable => TRUE,
statement_types => 'INSERT, UPDATE, SELECT, DELETE',
audit_trail => DBMS_FGA.DB);
END;
/
三..2 审计指定的列和行
audit_condition => 'DEPARTMENT_ID = 50',
audit_column => 'SALARY,COMMISSION_PCT,'
audit_column_opts => DBMS_FGA.ANY_COLUMNS,
三..3 启动和禁用FGA策略
DBMS_FGA.DISABLE_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'chk_hr_employees');
/
DBMS_FGA.ENABLE_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'chk_hr_employees',
enable => TRUE);
/
三..4 删除FGA策略
当你删除策略所在的对象时，或者策略所有者被删除时，策略都会被自动删除。
手动删除的语法是：
DBMS_FGA.DROP_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'chk_hr_employees');
三..5 其他功能
Tutorial: Adding an Email Alert to a Fine-Grained Audit Policy
可以设置有偶见提醒，但是作为内网的数据库一般无法使用这个功能，除非内网有邮件服务器，实际生产中实用性不大，因此不做介绍。
Tutorial: Auditing Nondatabase Users
EXEC DBMS_SESSION.SET_IDENTIFIER('Robert');
这样v$session的client_info列会记录为Robert
可以审计符合Robert登录的所有会话的操作。
四、 管理审计记录信息
四..1.1 定期导出并自动清理审计信息方法：
该问题牵扯两张表 aud$ , fga_log$ ，其是用来存放审计信息。
针对该问题方案思路是：
首先将fga_log$和aud$的审计表移动到独立的表空间（后附移动步骤）
，避免因清理不及时导致数据库停机。
其次开始部署清理脚本：
1每日对aud$ 、fga_log$ 表 使用exp导出
2禁用fga审计策略
3 truncate这两张表
4 启用fga审计策略
5传递导出文件到ftp服务器
6 删除导出文件
7 ftp服务器每日清除60天前的审计文件。
编写脚本fga.sh如下，内容不再赘述，如其他环境使用只需修改开头变量部分即可：
每日2点在2号实例服务器上执行
#!/bin/bash
#The definition of the variable
#FGASHELL shell fot the fga
#FGADIR where to save the dmp
#CHARACTER the character of the database
#SYSPASWD the password of the SYS
#FTP_SERVER the IP of ftp server
#USER the user of ftp
#PWD the password of ftp
#FTP_PATH the path on ftp server
#LOCAL_PATH the path on database server
FGASHELL=/home/oracle/fga
FGADIR=/rmanback/fga_log
CHARACTER="AMERICAN_AMERICA.AL32UTF8"
SYSPASWD=oracle
#for ftp variable
FTP_SERVER=192.168.0.240
USER="fga"
PWD="fga"
FTP_PATH="/"
LOCAL_PATH="/rmanback/fga_log"
#############################################
#create sql file
echo "set pagesize 0 heading off echo off feedback off linesize 500 " > $FGASHELL/find_fga.sql
echo "spool find_fga.log " >> $FGASHELL/find_fga.sql
echo "select t.object_schema, t.object_name, t.policy_name from dba_audit_policies t where t.enabled='YES'; " >> $FGASHELL/find_fga.sql
echo "spool off " >> $FGASHELL/find_fga.sql
echo "exit " >> $FGASHELL/find_fga.sql
source ~/.bash_profile
#exp the fga_log$
DATE=`date +%Y-%m-%d`
##The value of NLS_LANG set by Manual , by the sql ' select userenv('language') from dual; '
export NLS_LANG=$CHARACTER
exp \"sys/$SYSPASWD as sysdba\" file=$FGADIR/fga_log_$DATE.dmp log=$FGADIR/fga_logexp$DATE.log tables=sys.fga_log$ buffer=99999999
exp \"sys/$SYSPASWD as sysdba\" file=$FGADIR/aud_log_$DATE.dmp log=$FGADIR/aud_logexp$DATE.log tables=sys.aud$ buffer=99999999
sqlplus / as sysdba @find_fga.sql
#get SCHEMA OBJECT_NAME OBJECT_NAME
cat $FGASHELL/find_fga.log | while read line
do
echo "${line}"
SCHEMA=$(echo "${line}"| awk -F " " '{print $1}')
OBJECT_NAME=$(echo "${line}"| awk -F " " '{print $2}')
POLICY_NAME=$(echo "${line}"| awk -F " " '{print $3}')
#disable the policy
sqlplus / as sysdba <<EOF
begin
dbms_fga.enable_policy (
object_schema => '$SCHEMA',
object_name =>'$OBJECT_NAME',
policy_name => '$POLICY_NAME',
enable => FALSE
);
end;
/
exit
EOF
done
#truncate fga_log$
sqlplus / as sysdba <<EOF
truncate table fga_log$;
truncate table aud$;
exit
EOF
####get SCHEMA OBJECT_NAME OBJECT_NAME
cat find_fga.log | while read line
do
echo "${line}"
SCHEMA=$(echo "${line}"| awk -F " " '{print $1}')
OBJECT_NAME=$(echo "${line}"| awk -F " " '{print $2}')
POLICY_NAME=$(echo "${line}"| awk -F " " '{print $3}')
#enable the policy
sqlplus / as sysdba <<EOF
begin
dbms_fga.enable_policy (
object_schema => '$SCHEMA',
object_name =>'$OBJECT_NAME',
policy_name => '$POLICY_NAME',
enable => TRUE
);
end;
/
exit
EOF
done
#FTP
/usr/bin/ftp -i -n $FTP_SERVER <<EOF
user $USER $PWD
passive
binary
cd /$FTP_PATH
lcd /$LOCAL_PATH
mput *
EOF
rm -rf $FGADIR/*
ftp服务器使用windows 2008
ftp上部署审计文件清除脚本del_fga.bat：
forfiles /p d:\ftp /s /d -60 /m *.dmp /c "cmd /c del /q /f @path"
每日凌晨2点执行。
四..1.2 移动日志表到非系统表空间方法：
1.查看AUD$和FGA_LOG$所在表空间
SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;
2.查看AUD$和FGA_LOG$数据量
select segment_name,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name in ('AUD$','FGA_LOG$');
3.创建audit_tbs表空间 根据数据增长量决定表空间上的上线
create tablespace audit_tbs datafile 'xxxxxxx1.dbf' size 100M autoextend on; --此处表空间的初始大小必须必现有的fga_log$ 所需空间要大，否则无法移动。
4.move AUD$和FGA_LOG$
SQL> BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,--this moves table AUD$
audit_trail_location_value => 'AUDIT_TBS');
END;
/
SQL> BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,--this moves table FGA_LOG$
audit_trail_location_value => 'AUDIT_TBS');
END;
/
5.查看move后的AUD$和FGA_LOG$所在表空间
SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;
四..1.3 Managing the Operating System Audit Trail
时间和大小，内容略。
四..1.4 使用 DBMS_AUDIT_MGMT 手动或自动清理审计记录
11gR2增加了审计记录的管理功能 Audit Data Management
Audit Trail Cleanup可以实现自动清理审计记录。
可以手动删除，也可以创建job自动删除
使用DBMS_AUDIT_MGMT 来进行自动删除
定时自动删除某个时间点之前
手动删除某个时间之前的
删除某个时间段内的
四..1.5 设置自动清理审计记录的定时任务
job的时间要安排在数据库不忙的时间运行。
创建多个清理job时要保证彼此之间不会产生影响，不会在同一时间清理相同内容。
· Step 1: If Necessary, Tune Online and Archive Redo Log Sizes
· Step 2: Plan a Timestamp and Archive Strategy
· Step 3: Initialize the Audit Trail Cleanup Operation
· Step 4: Optionally, Set an Archive Timestamp for Audit Records
· Step 5: Create and Schedule the Purge Job
· Step 6: Optionally, Configure the Audit Trail Records to be Deleted in Batches
Step 2: Plan a Timestamp and Archive Strategy
DBA_AUDIT_MGMT_LAST_ARCH_TS
归档要删除掉的系统aud和数据库审计表中的指定数据。
INSERT INTO table SELECT ... FROM SYS.AUD$ ...;
INSERT INTO table SELECT ... FROM SYS.FGA_LOG$ ...;
Step 3: Initialize the Audit Trail Cleanup Operation
初始化清理job
BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
DEFAULT_CLEANUP_INTERVAL => 12 );
END;
/
如果不进行初始化，下面的操作会报：ORA-46258
AUDIT_TRAIL_TYPE: Enter one of the following values:
· DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail table, AUD$.
· DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail table, FGA_LOG$.
· DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trail tables.
· DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS: Operating system audit trail files with the .aud extension. (This setting does not apply to Windows Event Log entries.)
· DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML: XML Operating system audit trail files.
· DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES: Both operating system and XML audit trail files.
· DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL: All audit trail records, that is, both database audit trail and operating system audit trail types.
DEFAULT_CLEANUP_INTERVAL job的运行间隔 单位是小时
Step 4: Optionally, Set an Archive Timestamp for Audit Records
设置一个时间，来告知清理job，以这个时间前的审计信息全部清除。
alter session set nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff';
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
LAST_ARCHIVE_TIME => '2009-05-28 06:30:00.00'
RAC_INSTANCE_NUMBER => 0 );
END;
/
查看
select * from DBA_AUDIT_MGMT_LAST_ARCH_TS;
· DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Specified the standard audit trail table, AUD$.
· DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Specifies the fine-grained audit trail table, FGA_LOG$.
· DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS: Operating system audit trail files with the .aud extension. (This setting does not apply to Windows Event Log entries.)
· DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML: Specifies XML audit trail files.
RAC_INSTANCE_NUMBER:默认是0 表示单个实例，如果是AUDIT_TRAIL_AUD_STD 和AUDIT_TRAIL_FGA_STD可以忽略这个参数。
Step 5: Create and Schedule the Purge Job
For example:
BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_PURGE_INTERVAL => 12,
AUDIT_TRAIL_PURGE_NAME => 'Standard_Audit_Trail_PJ',
USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/
此时查看select * from dba_scheduler_jobs where job_name='STANDARD_AUDIT_TRAIL_PJ' 可以看到新创建的JOB。
AUDIT_TRAIL_PURGE_INTERVAL 单位小时
USE_LAST_ARCH_TIMESTAMP 参数值TRUE表示删除DBA_AUDIT_MGMT_LAST_ARCH_TS 设置前的所有记录。
参数值取FALSE则直接删除全部记录。
Step 6: Optionally, Configure the Audit Trail Records to be Deleted in Batches
分批次删除，默认数据库内的审计信息是10000行一次，操作系统的审计文件是1000个一次。
可以使用 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY 来调整默认设置。
也可以通过DBA_AUDIT_MGMT_CONFIG_PARAMS 视图来查看当前设置。
For example:
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_PROPERTY => DBMS_AUDIT_MGMT.DB_DELETE_BATCH_SIZE,
AUDIT_TRAIL_PROPERTY_VALUE => 100000);
END;
/
如果是删除操作系统中的审计文件则把DB_DELETE_BATCH_SIZE改为FILE_DELETE_BATCH_SIZE
四..1.6 手动调用清理过程进行审计记录的清理
The pseudo code in Example 9-27 creates a database audit trail purge operation that the user calls by invoking the DBMS_ADUIT.CLEAN_AUDIT_TRAIL procedure. The purge operation deletes records that were created before the last archived timestamp by using a loop. The loop archives the audit records, calculates which audit records were archived and uses the SetCleanUpAuditTrail call to set the last archive timestamp, and then calls the CLEAN_AUDIT_TRAIL procedure. It deletes the database audit trail records in batches of 100,000 records each. In this example, major steps are in bold typeface.
Example 9-27 Directly Calling a Database Audit Trail Purge Operation
-- 1. Initialize the AUD$ table for cleanup:
PROCEDURE CleanUpAuditTrailMain()
BEGIN
-- Connect to the database using appropriate login.
CALL ConnectToDatabase();
-- The login used must have privileges to modify Audit settings.
-- Currently, the DBA will be the authorized user
DBMS_AUDIT_MGMT.INIT_CLEANUP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
DEFAULT_CLEANUP_INTERVAL => 12 );
END; /*PROCEDURE */
/
-- 2. Optionally, set the batch size:
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_PROPERTY => DBMS_AUDIT_MGMT.DB_DELETE_BATCH_SIZE,
AUDIT_TRAIL_PROPERTY_VALUE => 100000 /* delete batch size */);
END; /*PROCEDURE */
/
-- 3. Set the last archive timestamp:
PROCEDURE SetCleanUpAuditTrail()
BEGIN
CALL FindLastArchivedTimestamp(AUD$);
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
LAST_ARCHIVE_TIME => '20-AUG-2009 00:00:00');
END /* PROCEDURE */
/
-- 4. Run a customized archive procedure to purge the audit trail records:
BEGIN
CALL MakeAuditSettings();
LOOP (/* How long to loop*/)
-- Invoke function for audit record archival
CALL DoAuditRecordArchival(AUD$);
CALL SetCleanUpAuditTrail();
IF(/* Clean up is needed immediately */)
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
USE_LAST_ARCH_TIMESTAMP => TRUE);
END IF
END LOOP /*LOOP*/
END; /* PROCEDURE */
/
四..1.7 删除部分审计记录中的信息
举例：
DELETE FROM SYS.AUD$
WHERE NTIMESTAMP# > TO_TIMESTAMP ('28-FEB-09 09.07.59.907000 PM') AND
NTIMESTAMP# < TO_TIMESTAMP ('28-MAR-09 09.07.59.907000 PM')
全部删除
DELETE FROM SYS.AUD$;
TRUNCATE TABLE SYS.AUD$;
四..1.8 其他清除相关操作
1) Verifying That the Audit Trail Is Initialized for Cleanup通过如下过程检查是否已经初始化，如果有则运行结果是TRUE，如果没有则FALSE，另外这只是针对AUDIT_TRAIL_AUD_STD，如果针对FGA则是AUDIT_TRAIL_FGA_STD。
For example:
SET SERVEROUTPUT ON
BEGIN
IF
DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT. AUDIT_TRAIL_AUD_STD)
THEN
DBMS_OUTPUT.PUT_LINE('AUD$ is initialized for cleanup');
ELSE
DBMS_OUTPUT.PUT_LINE('AUD$ is not initialized for cleanup.');
END IF;
END;
/
2) Setting the Default Audit Trail Purge Interval for Any Audit Trail Type
You can set a default purge operation interval, in hours, that must pass before the next purge operation takes place for a specified audit trail type.
For example:
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_PROPERTY => DBMS_AUDIT_MGMT.CLEAN_UP_INTERVAL,
AUDIT_TRAIL_PROPERTY_VALUE => 24 );
END;
/
3) Cancelling the Initialization Cleanup Settings
您可以通过调用DBMS_AUDIT_MGMT.DEINIT_CLEANUP过程来取消DBMS_AUDIT_MGMT.INIT_CLEANUP设置，即默认清除间隔。
For example, to cancel all purge settings for the standard audit trail:
BEGIN
DBMS_AUDIT_MGMT.DEINIT_CLEANUP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD);
END;
/
4) Enabling or Disabling an Audit Trail Purge Job
通过DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS启动或禁用自动清理任务。
For example:
BEGIN
DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS(
AUDIT_TRAIL_PURGE_NAME => 'OS_Audit_Trail_PJ',
AUDIT_TRAIL_STATUS_VALUE => DBMS_AUDIT_MGMT.PURGE_JOB_ENABLE);
END;
/
In this example:
· AUDIT_TRAIL_PURGE_NAME: Specifies a purge job called OS_Audit_Trail_PJ. To find existing purge jobs, query the JOB_NAME and JOB_STATUS columns of theDBA_AUDIT_MGMT_CLEANUP_JOBS data dictionary view.
· AUDIT_TRAIL_STATUS_VALUE: Enter one of the following properties:
o DBMS_AUDIT_MGMT.PURGE_JOB_ENABLE: Enables the specified purge job.
o DBMS_AUDIT_MGMT.PURGE_JOB_DISABLE: Disables the specified purge job.
5) Setting the Default Audit Trail Purge Job Interval for a Specified Purge Job设置默认审计跟踪清除指定清除作业的作业间隔。
For example:
BEGIN
DBMS_AUDIT_MGMT.SET_PURGE_JOB_INTERVAL(
AUDIT_TRAIL_PURGE_NAME => 'OS_Audit_Trail_PJ',
AUDIT_TRAIL_INTERVAL_VALUE => 24 );
END;
/
6) Deleting an Audit Trail Purge Job
To delete an audit trail purge job, use the DBMS_AUDIT_MGMT.DROP_PURGE_JOB PL/SQL procedure. To find existing purge jobs, query the JOB_NAME and JOB_STATUScolumns of the DBA_AUDIT_MGMT_CLEANUP_JOBS data dictionary view.
For example:
BEGIN
DBMS_AUDIT_MGMT.DROP_PURGE_JOB(
AUDIT_TRAIL_PURGE_NAME => 'FGA_Audit_Trail_PJ');
END;
/
7) Clearing the Archive Timestamp Setting
To clear the archive timestamp setting, use the DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP PL/SQL procedure.
For example:
BEGIN
DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,
RAC_INSTANCE_NUMBER => 1 );
END;
/
In this example:
· RAC_INSTANCE_NUMBER: If the AUDIT_TRAIL_TYPE property is set to DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS or DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML, then you cannot setRAC_INSTANCE_NUMBER to 0. You can omit this setting or specify 1 to indicate an instance number.
You can omit the RAC_INSTANCE_NUMBER setting when AUDIT_TRAIL_TYPE is DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD or DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, or if the database is not an Oracle RAC database. Otherwise, specify the correct instance number. You can find the instance number by issuing the SHOW PARAMETER INSTANCE_NUMBER command in SQL*Plus.
8) Clearing the Database Audit Trail Batch SizeTo clear the batch size setting, use the DBMS_AUDIT_MGMT.CLEAR_AUDIT_TRAIL_PROPERTY procedure.
For example:
BEGIN
DBMS_AUDIT_MGMT.CLEAR_AUDIT_TRAIL_PROPERTY(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_PROPERTY => DBMS_AUDIT_MGMT.DB_DELETE_BATCH_SIZE,
USE_DEFAULT_VALUES => TRUE );
END;
/
In this example:
· AUDIT_TRAIL_TYPE: Specifies the audit trail type, which in this case is the database system audit trail. Enter one of the AUDIT_TRAIL_TYPE values listed in"Step 6: Optionally, Configure the Audit Trail Records to be Deleted in Batches".
· AUDIT_TRAIL_PROPERTY: Specifies the DB_DELETE_BATCH_SIZE property. Query the DBA_AUDIT_MGMT_CONFIG_PARAMS data dictionary view to find the current status of this property.
· USE_DEFAULT_VALUES: Is set to TRUE, which clears the current audit record batch size and uses the default value, 10000, instead.
五、 和审计记录相关的数据字典视图
Table 9-8 lists data dictionary views that provide auditing information. For detailed information about these views, see Oracle Database Reference.
Table 9-8 Data Dictionary Views That Display Information about the Database Audit Trail
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24052272/viewspace-2129071/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/24052272/viewspace-2129071/
扫一扫
1039
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
