潛水珽的異想世界

因為最近工作上要導入ISMS(Information Security Management System,資訊安全管理系統),oracle資料庫上也開始要針對使用者做稽核,但Oracle中所提供的audit所記錄的資訊並不完整,所以就上網找了一些資料,下面會提供logon trigger、logout trigger以及oracle中所提供的DBMS package來補強我們對資料庫物件的稽核工作。

ref:http://www.dba-oracle.com/art_builder_sec_audit.htm
ref:http://www.lslnet.com/linux/f/docs1/i33/big5253524.htm

先建立一個用來記錄登入登出帳號的狀態表:
CREATE TABLE USERSTATSLOG
(
  USER_ID          VARCHAR2(30 BYTE),
  SESSION_ID       NUMBER(8),
  HOST             VARCHAR2(30 BYTE),
  LAST_PROGRAM     VARCHAR2(48 BYTE),
  LAST_ACTION      VARCHAR2(32 BYTE),
  LAST_MODULE      VARCHAR2(32 BYTE),
  LOGON_DAY        DATE,
  LOGON_TIME       VARCHAR2(10 BYTE),
  LOGOFF_DAY       DATE,
  LOGOFF_TIME      VARCHAR2(10 BYTE),
  ELAPSED_MINUTES  NUMBER(8)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;

COMMENT ON TABLE USERSTATSLOG IS '資料庫管理員稽核記錄';

COMMENT ON COLUMN USERSTATSLOG.USER_ID IS '使用者帳號';

COMMENT ON COLUMN USERSTATSLOG.SESSION_ID IS '連線編號';

COMMENT ON COLUMN USERSTATSLOG.HOST IS '主機名稱';

COMMENT ON COLUMN USERSTATSLOG.LAST_PROGRAM IS '登出前所使用的程式';

COMMENT ON COLUMN USERSTATSLOG.LAST_ACTION IS '登出前最後的動作';

COMMENT ON COLUMN USERSTATSLOG.LOGON_DAY IS '登入日';

COMMENT ON COLUMN USERSTATSLOG.LOGON_TIME IS '登入時間';

COMMENT ON COLUMN USERSTATSLOG.LOGOFF_DAY IS '登出日';

COMMENT ON COLUMN USERSTATSLOG.LOGOFF_TIME IS '登出時間';

COMMENT ON COLUMN USERSTATSLOG.ELAPSED_MINUTES IS '總登入時間';

logon trigger:
CREATE OR REPLACE TRIGGER LOGON_AUDIT_TRIGGER
AFTER LOGON
ON DATABASE
BEGIN
         
            insert into USERSTATSLOG values(
               user,
               sys_context('USERENV','SESSIONID'),
               sys_context('USERENV','HOST'),
               null,
               null,
               null,
               sysdate,
               to_char(sysdate, 'hh24:mi:ss'),
               null,
               null,
               null
            );
         

END;
/

logout trigger:
CREATE OR REPLACE TRIGGER LOGOFF_AUDIT_TRIGGER
BEFORE LOGOFF
ON DATABASE
BEGIN


    -- ***************************************************
    -- Update the last action accessed
    -- ***************************************************
      update
      USERSTATSLOG
      set
      last_action = (select action from v$session where    
      sys_context('USERENV','SESSIONID') = audsid)
      where
      sys_context('USERENV','SESSIONID') = session_id;
    
      --***************************************************
      -- Update the last program accessed
      -- ***************************************************
      update
      USERSTATSLOG
      set
      last_program = (select program from v$session where    
      sys_context('USERENV','SESSIONID') = audsid)
      where
      sys_context('USERENV','SESSIONID') = session_id;
      -- ***************************************************
      -- Update the last module accessed
      -- ***************************************************
      update
          USERSTATSLOG
      set
      last_module = (select module from v$session where    
      sys_context('USERENV','SESSIONID') = audsid)
      where
      sys_context('USERENV','SESSIONID') = session_id;
      -- ***************************************************
      -- Update the logoff day
      -- ***************************************************
      update
         USERSTATSLOG
      set
         logoff_day = sysdate
      where
         sys_context('USERENV','SESSIONID') = session_id;
      -- ***************************************************
      -- Update the logoff time
      -- ***************************************************
      update
         USERSTATSLOG
      set
         logoff_time = to_char(sysdate, 'hh24:mi:ss')
      where
         sys_context('USERENV','SESSIONID') = session_id;
      -- ***************************************************
      -- Compute the elapsed minutes
      -- ***************************************************
      update
      USERSTATSLOG
      set
      elapsed_minutes =    
      round((logoff_day - logon_day)*1440)
      where
      sys_context('USERENV','SESSIONID') = session_id;


END;
/

DBMS_FGA_policy:
--增加 FGA 策略

-- 審計表
GRANT RESOURCE,CONNECT TO BANK IDENTIFIED BY BANK;

CREATE TABLE BANK.ACCOUNTS
(
  ACCT_NO NUMBER PRIMARY KEY,
  CUST_ID NUMBER NOT NULL ,
  BALANCE NUMBER(15,2) NULL
);
insert into bank.accounts values(1,1,10000);
insert into bank.accounts values(2,2,20000);
commit;

Begin
dbms_fga.drop_policy (
  object_schema=>;'BANK',
  object_name=>;'ACCOUNTS',
  policy_name=>;'ACCOUNTS_ACCESS');
dbms_fga.add_policy (
  object_schema=>;'BANK',
  object_name=>;'ACCOUNTS',
  policy_name=>;'ACCOUNTS_ACCESS');
end;
/

select * from bank.accounts;
select timestamp, db_user,os_user,object_schema,object_name,sql_text  from dba_fga_audit_trail;

-- 審計列和審計條件, 在add_policy中加入
--   audit_column => 'BALANCE'
--   audit_condition => 'BALANCE >;= 11000'
Begin
dbms_fga.drop_policy (
  object_schema=>'BANK',
  object_name=>'ACCOUNTS',
  policy_name=>'ACCOUNTS_ACCESS');

dbms_fga.add_policy (
  object_schema=>'BANK',
  object_name=>'ACCOUNTS',
  audit_column => 'BALANCE',
  audit_condition => 'BALANCE >;= 11000',
  policy_name=>;'ACCOUNTS_ACCESS');
end;
/

-- 這個不可用，Why?
select BALANCE from bank.accounts;
select timestamp, db_user,os_user,object_schema,object_name,sql_text  from dba_fga_audit_trail;


--管理 FGA 策略
--要刪除策略，您可以使用以下語句：
begin
dbms_fga.drop_policy (
object_schema => 'BANK',
object_name => 'ACCOUNTS',
policy_name => 'ACCOUNTS_ACCESS'
   );
end;
/

-- 對於更改策略而言，沒有隨取隨用的解決方案。要更改策略中的任何參數，必須刪除策略，再使用更改後的參數添加策略。
-- 需要臨時禁用審計收集 — 例如，如果您希望將線索表移動到不同的表空間或者要刪除線索表。您可以按如下方法禁用 FGA 策略：
begin
dbms_fga.enable_policy (
object_schema => 'BANK',
object_name => 'ACCOUNTS',
policy_name => 'ACCOUNTS_ACCESS',
enable => FALSE
   );
end;
/
-- 重新啟用很簡單 enable => TRUE

--演示何時審計操作以及何時不審計操作的各種情況 SQL 語句 審計狀態

select balance from accounts;   進行審計。用戶選擇了在添加策略時所指定的審計列 BALANCE。
select * from accounts;         進行審計。即使用戶沒有明確指定列 BALANCE，* 也隱含地選擇了它。
select cust_id from accounts where balance < 10000; 進行審計。即使用戶沒有明確指定列 BALANCE，where 子句也隱含地選擇了它。
select cust_id from accounts;   不進行審計。用戶沒有選擇列 BALANCE。
select count(*) from accounts;  不進行審計。用戶沒有明確或隱含地選擇列 BALANCE。


--處理器模塊
-- FGA 的功能不只是記錄審計線索中的事件；FGA 還可以任意執行過程。
-- 過程可以執行一項操作，比如當用戶從表中選擇特定行時向審計者發送電子郵件警告，或者可以寫到不同的審計線索中。
-- 這種存儲代碼段可以是獨立的過程或者是程序包中的過程，稱為策略的處理器模塊。
-- 實際上由於安全性原因，它不必與基表本身處於同一模式中，您可能希望特意將它放置在不同的模式中。
-- 由於只要 SELECT 出現時過程就會執行，非常類似於 DML 語句啟動的觸發器，您還可以將其看作 SELECT 語句觸發器。
-- 以下參數指定將一個處理器模塊指定給策略：
--  handler_schema 擁有數據過程的模式
--  handler_module 過程名稱
-- 處理器模塊還可以採用程序包的名稱來代替過程名稱。在這種情況下，參數 handler_module 在 package.procedure 的格式中指定。


--FGA 數據字典視圖
-- FGA 策略的定義位於數據字典視圖 DBA_AUDIT_POLICIES 中。表 2 包含該視圖中一些重要列的簡短描述。
-- 審計線索收集在 SYS 擁有的表 FGA_LOG$ 中。對於 SYS 擁有的任何原始表，此表上的某些視圖以對用戶友好的方式顯示信息。DBA_FGA_AUDIT_TRAIL 是該表上的一個視圖。
-- 一個重要的列是 SQL_BIND，它指定查詢中使用的綁定變量的值 — 這是顯著增強該工具功能的一項信息。
-- 另一個重要的列是 SCN，當發生特定的查詢時，它記錄系統更改號。
-- 此信息用於識別用戶在特定時間看到了什麼，而不是現在的值，它使用了閃回查詢，這種查詢能夠顯示在指定的 SCN 值時的數據。


--視圖和 FGA
-- 到目前為止我已經討論了在表上應用 FGA；現在讓我們來看如何在視圖上使用 FGA。假定在 ACCOUNTS 表上定義視圖 VW_ACCOUNTS 如下：

create view bank.vw_accounts as select * from bank.accounts;

select * from bank.vw_accounts;
select timestamp, db_user,os_user,object_schema,object_name,sql_text  from dba_fga_audit_trail;

-- 如果您只希望審計對視圖的查詢而不是對表的查詢，可以對視圖本身建立策略。
-- 通過將視圖名稱而不是表的名稱傳遞給打包的過程 dbms_fga.add_policy 中的參數 object_name，可以完成這項工作。
-- 隨後 DBA_FGA_AUDIT_TRAIL 中的 OBJECT_NAME 列將顯示視圖的名稱，並且不會出現有關表訪問的附加記錄。

--其它用途
-- 除了記錄對表的選擇訪問，FGA 還可用於某些其它情況：
--  您可以對數據倉庫使用 FGA，以捕獲特定的表、視圖或物化視圖上發生的所有語句，這有助於計劃索引。您不需要到 V$SQL 視圖去獲取這些信息。即使 SQL 語句已經超出了 V$SQL 的期限，在 FGA 審計線索中將會始終提供它。
--  由於 FGA 捕獲綁定變量，它可以幫助您瞭解綁定變量值的模式，這有助於設計直方圖集合等。
--  處理器模塊可以向審計者或 DBA 發送警告，這有助於跟蹤惡意應用程序。
--  由於 FGA 可以作為 SELECT 語句的觸發器，您可以在需要這種功能的任何時候使用它。

--結論
-- FGA 使您在 Oracle 數據庫中支持隱私和職能策略。因為審計發生在數據庫內部而不是應用程序中，所以無論用戶使用的訪問方法是什麼（通過諸如 SQL*Plus 等工具或者應用程序），都對操作進行審計，允許進行非常簡單的設置。
-- 下一次我將討論高級 FGA 技術以及 Oracle Database 10g 中的新特性，這些特性使 FGA 的功能極為強大，適用於所有類型的審計情況。




數據字典視圖 DBA_AUDIT_POLICIES 中重要的列
-----------------------------------------------------------------------
OBJECT_SCHEMA   對其定義了 FGA 策略的表或視圖的所有者
OBJECT_NAME     表或視圖的名稱
POLICY_NAME     策略的名稱 — 例如，ACCOUNTS_ACCESS
POLICY_TEXT     在添加策略時指定的審計條件 — 例如，BALANCE >;= 11000
POLICY_COLUMN   審計列 — 例如，BALANCE
ENABLED         如果啟用則為 YES，否則為 NO 
PF_SCHEMA       擁有策略處理器模塊的模式（如果存在）
PF_PACKAGE      處理器模塊的程序包名稱（如果存在）
PF_FUNCTION     處理器模塊的過程名稱（如果存在）



數據字典視圖 DBA_FGA_AUDIT_TRAIL 中重要的列
-----------------------------------------------------------------------
SESSION_ID      審計會話標識符；與 V$SESSION 視圖中的會話標識符不同
TIMESTAMP       審計記錄生成時的時間標記
DB_USER         發出查詢的數據庫用戶
OS_USER         操作系統用戶
USERHOST        用戶連接的機器的主機名
CLIENT_ID       客戶標識符（如果由對打包過程 dbms_session.set_identifier 的調用所設置）
EXT_NAME        外部認證的客戶名稱，如 LDAP 用戶
OBJECT_SCHEMA   對該表的訪問觸發了審計的表所有者
OBJECT_NAME     對該表的 SELECT 操作觸發了審計的表名稱
POLICY_NAME     觸發審計的策略名稱（如果對表定義了多個策略，則每個策略將插入一條記錄。在此情況下，該列顯示哪些行是由哪個策略插入的。)
SCN             記錄了審計的 Oracle 系統更改號
SQL_TEXT        由用戶提交的 SQL 語句 
SQL_BIND        由 SQL 語句使用的綁定變量（如果存在）