FGA审计及audit_trail参数

通过这个案例我只想记录以下在我的测试过程中出现的错误和异常，今天想测试FGA特性，结果发生了如下两个错误：
1、执行报错误，是因为
audit_trail => DBMS_FGA.DB_EXTENDED, 语句前面没加sys用户名导致，即应该是如下：
audit_trail => sys.DBMS_FGA.DB_EXTENDED,

2、执行成功，dba_fga_audit_trail试图中无数据但在dba_common_audit_trail视图有数据；

另外，要启用FGA审计，需要配置audit_trail初始化参数，该参数是静态参数，需要重启实例。如下问题本人操作的具体过程：

[@more@]

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select timestamp,db_user,userhost,sql_text from dba_fga_audit_trail;

no rows selected

SQL> show parameter audit_trail

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string NONE
SQL> alter system set audit_trail=db_extended scope=spfile;

System altered.

SQL> show parameter autdit_trail
SQL> show parameter audit_trail

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string NONE
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 524288000 bytes
Fixed Size 1220336 bytes
Variable Size 159383824 bytes
Database Buffers 356515840 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL> show parameter audit_trail

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB_EXTENDED
SQL>

SQL> exec dbms_fga.drop_policy(object_schema=>'HR',object_name=>'EMPLOYEES',policy_name=>'my2');

PL/SQL procedure successfully completed.

SQL> begin
2 DBMS_FGA.ADD_POLICY (
3 object_schema => 'HR',
4 object_name => 'EMPLOYEES',
5 policy_name => 'my2',
6 audit_condition => 'SALARY is not null or COMMISSION_PCT is not null',
7 audit_column => 'SALARY,COMMISSION_PCT',
8 handler_schema => NULL,
9 handler_module => NULL,
10 enable => TRUE,
11 statement_types => 'SELECT',
12 audit_trail => SYS.DBMS_FGA.DB+SYS.DBMS_FGA.DB_EXTENDED,
13 audit_column_opts => DBMS_FGA.ANY_COLUMNS);
14 end;
15 /

PL/SQL procedure successfully completed.

SQL> conn hr/hr
Connected.
SQL> select salary from employees where rownum <10;

SALARY
----------
24000
17000
17000
9000
6000
4800
4800
4200
12000

9 rows selected.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select timestamp,db_user,userhost,sql_text from dba_fga_audit_trail;

no rows selected

SQL> select count(*) from dba_common_audit_trail;

COUNT(*)
----------
5
SQL>select lsqltext from fga_log$;
no rows selected

以上语句错误就在
audit_trail => SYS.DBMS_FGA.DB+SYS.DBMS_FGA.DB_EXTENDED, 语句中，将该语句改为
audit_trail => YS.DBMS_FGA.DB_EXTENDED, 后重新执行，如下：

SQL> exec dbms_fga.drop_policy(object_schema=>'HR',object_name=>'EMPLOYEES',policy_name=>'my2');

PL/SQL procedure successfully completed.

SQL> begin
2 DBMS_FGA.ADD_POLICY (
3 object_schema => 'HR',
4 object_name => 'EMPLOYEES',
5 policy_name => 'my2',
6 audit_condition => 'SALARY is not null or COMMISSION_PCT is not null',
7 audit_column => 'SALARY,COMMISSION_PCT',
8 handler_schema => NULL,
9 handler_module => NULL,
10 enable => TRUE,
11 statement_types => 'SELECT',
12 audit_trail => SYS.DBMS_FGA.DB_EXTENDED,
13 audit_column_opts => DBMS_FGA.ANY_COLUMNS);
14 end;
/

PL/SQL procedure successfully completed.

SQL> conn hr/hr
Connected.
SQL> select * from employees;
........
SQL> select salary from hr.employees;

SALARY
----------
24000
17000


SQL> col userhost for a10

SQL> col db_user for a12
SQL> set wrap off;
SQL> /

TIMESTAMP DB_USER USERHOST SQL_TEXT
--------- ------------ ---------- ----------------------------------------------
15-AUG-13 HR dbserv1 select * from employees
15-AUG-13 SYSTEM dbserv1 select salary from hr.employees

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select lsqltext from fga_log$;

LSQLTEXT
--------------------------------------------------------------------------------
select * from employees
select salary from hr.employees

当以上结果执行成功后，想重现第一步的错误号，再把audit_trail => sys.DBMS_FGA.DB_EXTENDED中的sys去掉执行fga脚本时，结果执行成功，具体原因待查。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18841027/viewspace-1060856/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18841027/viewspace-1060856/