一点一滴努力,满仓满谷收成

FGA-细颗粒度审计，Oracle 9i版本出现，只支持对select的审计，在10g及之上的版本，FGA支持insert,delete,update,merge的审计。审计结果dba_fga_audit_trail存放在SYSTEM表空间中，实际dba_fga_audit_trail是一个视图，其基表是fga_log$。当数据库有大量的审计信息时,fga_log$会膨胀得很大，SYSTEM占太多空间是每个DBA都不想看到的，因此把fga_log$表移动到一专属的表空间很有必要。
1 move到专属表空间时报ORA-00997错

SQL> alter table fga_log$ move tablespace USERS;
alter table fga_log$ move tablespace TBS_OPERLOG
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

2 相关信息
DBA_FGA_AUDIT_TRAIL的基表是fga_log$


SQL> SELECT OWNER, VIEW_NAME, TEXT
2 FROM DBA_VIEWS
3 WHERE VIEW_NAME = ‘DBA_FGA_AUDIT_TRAIL’
4 /

OWNER VIEW_NAME TEXT
———- ——————– ——————————————————————————–
SYS DBA_FGA_AUDIT_TRAIL select
sessionid,
CAST (
(FROM_TZ(ntimestamp#,’00:00′) AT LOCAL) AS DATE
),
dbuid, osuid, oshst, clientid, auditid, extid,
obj$schema, obj$name, policyname, scn, to_nchar(substr(lsqltext,1,2000)),
to_nchar(substr(lsqlbind,1,2000)), comment$text,
DECODE(stmt_type,
1, ‘SELECT’, 2, ‘INSERT’, 4, ‘UPDATE’, 8, ‘DELETE’, ‘INVALID’),
FROM_TZ(ntimestamp#,’00:00′) AT LOCAL,
proxy$sid, user$guid, instance#, process#,
xid, statement, entryid, dbid
from sys.fga_log$

SQL>

fga_log$默认存放在SYSTEM表空间中

SQL> SELECT owner,table_name,tablespace_name FROM dba_tables WHERE table_name=’FGA_LOG$’
2 /
OWNER TABLE_NAME TABLESPACE_NAME
———- ————— ——————–
SYS FGA_LOG$ SYSTEM

SQL>

3 处理过程
目标是把fga_log$移动到USERS表空间中,报ORA-00997错的原因是fga_log$表中PLHOL列是LONG型的，Oracle的文档已说明LONG及LONG RAW都是要废弃的类型，以LOB类型取代，10g一些基本之所以还有一些long类型的列出现是考虑到了与之前版本的兼容性。
为此，这里处理的思路是把PLHOL先修改成CLOB类型，然后做fga_log$表move操作，最后把相关的lob字段数据移动到新表空间中。
改类型：

SQL> alter table fga_log$ modify (PLHOL clob);

Table altered.

移表：

SQL> alter table fga_log$ move tablespace users;

Table altered.

移lob类型字段：

SQL> alter table fga_log$ move lob(LSQLTEXT) store as (tablespace users);

Table altered.

SQL> alter table fga_log$ move lob(PLHOL) store as (tablespace users);

Table altered.

SQL> alter table fga_log$ move lob(LSQLBIND) store as (tablespace users);

Table altered.

查看一下结果：

SQL> SELECT owner,table_name,tablespace_name FROM dba_tables WHERE table_name=’FGA_LOG$’
2 /
OWNER TABLE_NAME TABLESPACE_NAME
———- ————— ——————–
SYS FGA_LOG$ USERS

SQL>

SQL> SELECT OWNER, INDEX_NAME, INDEX_TYPE, TABLESPACE_NAME
2 FROM DBA_INDEXES
3 WHERE TABLE_NAME = ‘FGA_LOG$’
4 /
OWNER INDEX_NAME INDEX_TYPE TABLESPACE_NAME
———– —————————— ———— —————–
SYS SYS_IL0000000565C00013$$ LOB USERS
SYS SYS_IL0000000565C00016$$ LOB USERS
SYS SYS_IL0000000565C00028$$ LOB USERS

SQL>

可见都已成功迁移至USERS表空间中。
4 做一个FGA策略测试一下
对HR.T1表的增、删、改进行审计

SQL> BEGIN
2 DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => ‘HR’,
3 OBJECT_NAME => ‘T1’,
4 POLICY_NAME => ‘T_HR_T1’,
5 STATEMENT_TYPES => ‘INSERT,DELETE,UPDATE’);
6 END;
7 /

PL/SQL procedure successfully completed.

SQL>

DML几条记录后看一下结果：

SQL> SELECT TIMESTAMP, DB_USER, USERHOST, POLICY_NAME, SQL_TEXT, SQL_BIND
2 FROM DBA_FGA_AUDIT_TRAIL
3 /
TIMESTAMP DB_USER USERHOST POLICY_NAME SQL_TEXT SQL_BIND
———– ——— ————— ————- ———————————————— —————————————————–
2014/8/13 1 QIUYB UNICOM\QIUYB T_HR_T1 insert into hr.t1 values(3000,’test fga audit’)
2014/8/14 9 HR UNICOM\QIUYB T_HR_T1 update t1 #1(19):test2-select for up #2(18):AAADVjAAEAAAAAMAAY
set name = :v2
where rowid = :plsqldev_rowid
2014/8/14 9 HR UNICOM\QIUYB T_HR_T1 insert into t1 values(5555,’insert fga test’)

SQL>

5 其它
DBA_AUDIT_POLICIES:可以看到设定了哪些fga policy。
删除policy:

SQL> exec dbms_fga.drop_policy(object_schema => ‘HR’,object_name => ‘T1’,policy_name => ‘T_HR_T1’);
PL/SQL procedure successfully completed

SQL>

清理：
可以对fga_log$做truncate及delete操作。