FGA稽核（fine-grained auditing）即細粒度稽核。標準的資料庫稽核可以捕獲對某個表的所有訪問，但是可能只有某些行包含了使用者關心的敏感資訊，為了查詢少量的重要資訊，我們可能需要篩選大量的稽核記錄。而細粒度稽核可以配置只在訪問特定行或特定行的特定列時生成稽核記錄，並可配置稽核條件滿足時執行一個PL/SQL程式碼，以完成更加複雜的監控操作。

一、FGA稽核的基本方法

程式包dbms_fga用來建立、使能、刪除FGA稽核策略

SQL> desc dbms_fga

Element Type

-------------- ---------

EXTENDED CONSTANT

DB CONSTANT

DB_EXTENDED CONSTANT

XML CONSTANT

ALL_COLUMNS CONSTANT

ANY_COLUMNS CONSTANT

ADD_POLICY PROCEDURE

DROP_POLICY PROCEDURE

ENABLE_POLICY PROCEDURE

DISABLE_POLICY PROCEDURE

程式包中的過程add_policy用來建立一個稽核策略

SQL> desc dbms_fga.add_policy

Parameter Type Mode Default?

----------------- -------------- ---- --------

OBJECT_SCHEMA VARCHAR2 IN Y 待稽核物件的使用者名稱，預設為建立FGA稽核策略的使用者

OBJECT_NAME VARCHAR2 IN 待稽核表的名稱

POLICY_NAME VARCHAR2 IN 每一個稽核策略都應有一個策略名

AUDIT_CONDITION VARCHAR2 IN Y 確定稽核行的條件表示式，為NULL時對任何行的訪問都納入稽核

AUDIT_COLUMN VARCHAR2 IN Y 待稽核列的列表，為NULL時對任何列的訪問都納入稽核

HANDLER_SCHEMA VARCHAR2 IN Y 達到稽核條件時所執行儲存過程的使用者名稱，預設為建立策略的使用者

HANDLER_MODULE VARCHAR2 IN Y 達到稽核條件時所執行的PL/SQL儲存過程

ENABLE BOOLEAN IN Y 預設為true，策略自動啟用，用disable_policy可禁用策略，若為false，則需使用enable_policy啟用策略

STATEMENT_TYPES VARCHAR2 IN Y 定義要稽核的語句型別，如select、insert、update或delete中的一個或多個，預設只稽核select

AUDIT_TRAIL BINARY_INTEGER IN Y 是否將執行的SQL語句及其繫結變數寫入FGA稽核跟蹤，預設為寫入

AUDIT_COLUMN_OPTS BINARY_INTEGER IN Y 當執行語句涉及到audit_column引數中指定的任何列或所有列時，是否進行稽核，

包括dbms_fga.any_columns（預設）或dbms_fga.all_columns

POLICY_OWNER VARCHAR2 IN Y 策略所有者

過程drop_policy用來刪除一個稽核策略

SQL> desc dbms_fga.drop_policy

Parameter Type Mode Default?

------------- -------- ---- --------

OBJECT_SCHEMA VARCHAR2 IN Y

OBJECT_NAME VARCHAR2 IN

POLICY_NAME VARCHAR2 IN

稽核結果透過檢視dba_fga_audit_trail可以檢視，稽核完畢後也可以直接將檢視記錄刪除。

這裡需要特別說明的是，FGA稽核是獨立於標準資料庫稽核的，也就是說，即便標準資料庫稽核未開啟，audit_trail引數設定為none，FGA稽核也是有效可行的。這給我們帶來了一個方便，不用重啟例項更改引數，就可以實施FGA稽核。

以下是對使用者的某個表新增查詢和DML操作的稽核，且暫時不啟用

begin

dbms_fga.add_policy(object_schema => 'RMES',

object_name => 'R_WIP_PRINT_T',

policy_name => 'POLICY_WIP_PRINT',

audit_condition => null,

audit_column => null,

enable => false,

statement_types => 'select,insert,update,delete');

end;

/

以下還可以在稽核條件滿足時執行一個儲存過程，以實現更復雜的監控

begin

dbms_fga.add_policy(object_schema => 'RMES',

object_name => 'R_WIP_PRINT_T',

policy_name => 'POLICY_WIP_PRINT',

audit_condition => null,

audit_column => null,

handler_schema => 'SYS',

handler_module => 'LLN_AUDIT_PROC',

enable => false,

statement_types => 'select,insert,update,delete');

end;

/

啟用稽核

begin

dbms_fga.enable_policy(object_schema => 'RMES',

object_name => 'R_WIP_PRINT_T',

policy_name => 'POLICY_WIP_PRINT',

enable => true);

end;

/

禁用稽核

begin

dbms_fga.enable_policy(object_schema => 'RMES',

object_name => 'R_WIP_PRINT_T',

policy_name => 'POLICY_WIP_PRINT',

enable => false);

end;

/

刪除稽核

begin

dbms_fga.drop_policy(object_schema => 'RMES',

object_name => 'R_WIP_PRINT_T',

policy_name => 'POLICY_WIP_PRINT');

end;

/

檢視稽核結果

col db_user for a10

col os_user for a20

col userhost for a20

col object_schema for a15

col object_name for a20

col sql_text for a60

col sql_bind for a60

select session_id, timestamp, db_user, os_user, userhost, object_schema, object_name, policy_name, sql_text, sql_bind, statement_type from dba_fga_audit_trail order by timestamp desc;

結果中的sesssion_id就是檢視v$session中的audsid，由此可以對應出具體的會話資訊。

清除FGA稽核的歷史記錄

delete from dba_fga_audit_trail;

commit;

二、FGA稽核實驗

1、沒有附加儲存過程的稽核實驗：

1）使用者hr有一張員工表employees，其中欄位salary記錄了每位員工的工資資訊

2）建立一個FGA稽核策略，捕獲包含對employees表salary列的所有DML語句，並加入條件只對行的所屬部門ID包含80部門的納入稽核

begin

dbms_fga.add_policy(object_schema => 'HR',

object_name => 'EMPLOYEES',

policy_name => 'MY_POLICY',

audit_condition => 'department_id = 80',

audit_column => 'SALARY',

statement_types => 'select,insert,update,delete');

end;

/

3）測試對錶的稽核操作

測試之前可以先清除FGA稽核的歷史記錄

delete from dba_fga_audit_trail;

commit;

非sys使用者登入，檢視員工表的所有資訊

select * from hr.employees;

檢視稽核結果

col db_user for a10

col os_user for a20

col userhost for a20

col object_schema for a15

col object_name for a20

col sql_text for a50

select session_id, timestamp, db_user, os_user, userhost, object_schema, object_name, policy_name, sql_text, statement_type from dba_fga_audit_trail;

SESSION_ID TIMESTAMP DB_USER OS_USER USERHOST OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT STATEME

---------- ------------------- ---------- -------------------- -------------------- --------------- -------------------- ------------------------------ -------------------------------------------------- -------

441434 2017-09-20 11:54:53 HR Administrator WORKGROUP\MYPC HR EMPLOYEES MY_POLICY select * from hr.employees SELECT

由於查詢包含了滿足稽核條件的記錄，因此該語句會被納入稽核。

檢視30部門的員工資訊

select * from hr.employees where department_id=30;

檢視稽核結果

col db_user for a10

col os_user for a20

col userhost for a20

col object_schema for a15

col object_name for a20

col sql_text for a50

select session_id, timestamp, db_user, os_user, userhost, object_schema, object_name, policy_name, sql_text, statement_type from dba_fga_audit_trail;

SESSION_ID TIMESTAMP DB_USER OS_USER USERHOST OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT STATEMENT_TYPE

---------- -------------------- ---------- ------------------------------ -------------------- --------------- --------------- --------------- ------------------------------ --------------

452718 2015/9/3 18:28:39 HR VM-2008-090\Administrator WORKGROUP\MYPC HR EMPLOYEES MY_POLICY select * from hr.employees SELECT

由於查詢非針對80部門，因此不會納入稽核。

檢視80部門員工的所有資訊

select * from hr.employees where department_id=80;

檢視稽核結果

col db_user for a10

col os_user for a20

col userhost for a20

col object_schema for a15

col object_name for a20

col sql_text for a50

select session_id, timestamp, db_user, os_user, userhost, object_schema, object_name, policy_name, sql_text, statement_type from dba_fga_audit_trail;

SESSION_ID TIMESTAMP DB_USER OS_USER USERHOST OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT STATEME

---------- ------------------- ---------- -------------------- -------------------- --------------- -------------------- ------------------------------ -------------------------------------------------- -------

441434 2017-09-20 11:54:53 HR Administrator WORKGROUP\MYPC HR EMPLOYEES MY_POLICY select * from hr.employees SELECT

441434 2017-09-20 12:00:51 HR Administrator WORKGROUP\MYPC HR EMPLOYEES MY_POLICY select * from hr.employees where department_id=80 SELECT

由於查詢包含了SALARY列和指定的80部門條件，故該語句加入稽核。

檢視80部門員工的姓名和入職日期等資訊

select employee_id, first_name, last_name, hire_date from hr.employees where department_id=80;

檢視稽核結果

col db_user for a10

col os_user for a20

col userhost for a20

col object_schema for a15

col object_name for a20

col sql_text for a50

select session_id, timestamp, db_user, os_user, userhost, object_schema, object_name, policy_name, sql_text, statement_type from dba_fga_audit_trail;

SESSION_ID TIMESTAMP DB_USER OS_USER USERHOST OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT STATEMENT_TYPE

---------- -------------------- ---------- ------------------------------ -------------------- --------------- --------------- --------------- -------------------------------------------------- --------------

452718 2015/9/3 18:28:39 HR VM-2008-090\Administrator WORKGROUP\MYPC HR EMPLOYEES MY_POLICY select * from hr.employees SELECT

452718 2015/9/3 20:48:20 HR VM-2008-090\Administrator WORKGROUP\MYPC HR EMPLOYEES MY_POLICY select * from hr.employees where department_id=80 SELECT

由於該查詢未包含SALARY列，故不會納入稽核。

更新操作

update hr.employees set salary=15000 where employee_id=145;

該更新滿足稽核條件，該ID員工屬於80部門，且針對的是SALARY列的更新，因此被納入稽核

col db_user for a10

col os_user for a20

col userhost for a20

col object_schema for a15

col object_name for a20

col sql_text for a60

select session_id, timestamp, db_user, os_user, userhost, object_schema, object_name, policy_name, sql_text, statement_type from dba_fga_audit_trail;

SESSION_ID TIMESTAMP DB_USER OS_USER USERHOST OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT STATEME

---------- ------------------- ---------- -------------------- -------------------- --------------- -------------------- ------------------------------ ------------------------------------------------------------ -------

441434 2017-09-20 11:54:53 HR Administrator WORKGROUP\MYPC HR EMPLOYEES MY_POLICY select * from hr.employees SELECT

441434 2017-09-20 12:00:51 HR Administrator WORKGROUP\MYPC HR EMPLOYEES MY_POLICY select * from hr.employees where department_id=80 SELECT

441434 2017-09-20 12:04:29 HR Administrator WORKGROUP\MYPC HR EMPLOYEES MY_POLICY update hr.employees set salary=15000 where employee_id=145 UPDATE

無論DML操作最終是否被提交，稽核記錄都將存在而不會被回滾掉。

更新另一位員工的SALARY

update hr.employees set salary=3000 where employee_id=198;

檢視稽核結果

col db_user for a10

col os_user for a20

col userhost for a20

col object_schema for a15

col object_name for a20

col sql_text for a60

select session_id, timestamp, db_user, os_user, userhost, object_schema, object_name, policy_name, sql_text, statement_type from dba_fga_audit_trail;

SESSION_ID TIMESTAMP DB_USER OS_USER USERHOST OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT STATEME

---------- ------------------- ---------- -------------------- -------------------- --------------- -------------------- ------------------------------ ------------------------------------------------------------ -------

441434 2017-09-20 11:54:53 HR Administrator WORKGROUP\MYPC HR EMPLOYEES MY_POLICY select * from hr.employees SELECT

441434 2017-09-20 12:00:51 HR Administrator WORKGROUP\MYPC HR EMPLOYEES MY_POLICY select * from hr.employees where department_id=80 SELECT

441434 2017-09-20 12:04:29 HR Administrator WORKGROUP\MYPC HR EMPLOYEES MY_POLICY update hr.employees set salary=15000 where employee_id=145 UPDATE

由於該員工不屬於80部門，因此不會納入稽核。

4）稽核策略的刪除

刪除稽核策略，需要指定稽核物件和策略名

begin

dbms_fga.drop_policy(object_schema => 'HR',

object_name => 'EMPLOYEES',

policy_name => 'MY_POLICY');

end;

/

清除FGA稽核的歷史記錄

delete from dba_fga_audit_trail;

commit;

2、附加儲存過程的稽核實驗：

1）建立自己的會話稽核跟蹤表

create table sys.lln_audit_trail(audsid number,

username varchar2(30),

machine varchar2(64),

ip varchar2(15),

osuser varchar2(30),

program varchar2(64),

logon_time date,

sid number,

serial# number,

pid number,

spid varchar2(12),

action varchar2(32),

sql_id varchar2(13),

object_schema varchar2(30),

object_name varchar2(30),

policy_name varchar2(30))

tablespace rmes;

2）建立儲存過程，配合FGA稽核執行會話跟蹤

create or replace procedure sys.lln_audit_proc(object_schema varchar2,

object_name varchar2,

policy_name varchar2) is

-- 配合FGA稽核執行會話跟蹤

my_audsid number;

my_username varchar2(30);

my_machine varchar2(64);

my_osuser varchar2(30);

my_program varchar2(64);

my_logon_time date;

my_sid number;

my_serial# number;

my_pid number;

my_spid varchar2(12);

my_action varchar2(32);

my_sql_id varchar2(13);

begin

-- 查詢會話資訊

select s.audsid,

s.username,

s.machine,

s.osuser,

s.program,

s.logon_time,

s.sid,

s.serial#,

p.pid,

p.spid,

s.action,

s.sql_id

into my_audsid,

my_username,

my_machine,

my_osuser,

my_program,

my_logon_time,

my_sid,

my_serial#,

my_pid,

my_spid,

my_action,

my_sql_id

from v$session s, v$process p

where s.paddr = p.addr

and s.audsid = sys_context('userenv', 'sessionid');

-- 記錄會話資訊，這裡是自動提交的，不可以加顯示提交命令，否則稽核的操作將報內部錯誤

insert into lln_audit_trail

(audsid,

username,

machine,

ip,

osuser,

program,

logon_time,

sid,

serial#,

pid,

spid,

action,

sql_id,

object_schema,

object_name,

policy_name)

values

(my_audsid,

my_username, --ora_login_user

my_machine,

sys_context('userenv', 'ip_address'),

my_osuser,

my_program,

my_logon_time, --sysdate

my_sid,

my_serial#,

my_pid,

my_spid,

my_action,

my_sql_id,

object_schema,

object_name,

policy_name);

end;

/

3）建立和使能FGA稽核策略

begin

dbms_fga.add_policy(object_schema => 'RMES',

object_name => 'R_WIP_PRINT_T',

policy_name => 'POLICY_WIP_PRINT',

audit_condition => null,

audit_column => null,

handler_schema => 'SYS',

handler_module => 'LLN_AUDIT_PROC',

enable => false,

statement_types => 'select,insert,update,delete');

end;

/

begin

dbms_fga.enable_policy(object_schema => 'RMES',

object_name => 'R_WIP_PRINT_T',

policy_name => 'POLICY_WIP_PRINT',

enable => true);

end;

/

4）開啟一個會話，對稽核的表執行查詢和DML測試操作

5）查詢稽核和跟蹤結果

稽核結果包含了在指定表上執行的SQL和繫結變數等資訊

col db_user for a10

col os_user for a20

col userhost for a20

col object_schema for a15

col object_name for a20

col sql_text for a60

col sql_bind for a60

select session_id, timestamp, db_user, os_user, userhost, object_schema, object_name, policy_name, sql_text, sql_bind, statement_type from dba_fga_audit_trail order by timestamp desc;

自建的會話稽核跟蹤表上記錄了對應的具體會話資訊，透過稽核ID來關聯

col username for a10

col machine for a20

col program for a20

col action for a10

col slq_id for a15

col object_schema for a15

col object_name for a20

col policy_name for a20

select * from lln_audit_trail order by logon_time desc;

6）稽核策略的禁用和刪除

禁用稽核

begin

dbms_fga.enable_policy(object_schema => 'RMES',

object_name => 'R_WIP_PRINT_T',

policy_name => 'POLICY_WIP_PRINT',

enable => false);

end;

/

刪除稽核

begin

dbms_fga.drop_policy(object_schema => 'RMES',

object_name => 'R_WIP_PRINT_T',

policy_name => 'POLICY_WIP_PRINT');

end;

/

清除稽核記錄

delete from dba_fga_audit_trail;

commit;

truncate table lln_audit_trail;

刪除自定義的儲存過程和稽核表

drop procedure sys.lln_audit_proc;

drop table lln_audit_trail purge;

Oracle FGA稽核

相關文章