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指令碼時，結果執行成功，具體原因待查。