【PL/SQL】DBMS_FGAでファイングレイン監査を設定する方法｜ADD_POLICY・監査ログ確認・ハンドラ連携

-- 例: FGAを管理するユーザーに必要な権限を付与
GRANT AUDIT_ADMIN TO SEC_ADMIN;
GRANT EXECUTE ON DBMS_FGA TO SEC_ADMIN;

-- 監査対象表に対する権限も必要
GRANT SELECT, UPDATE, DELETE ON HR.EMPLOYEES TO SEC_ADMIN;

BEGIN
  DBMS_FGA.ADD_POLICY(
    object_schema      => 'HR',
    object_name        => 'EMPLOYEES',
    policy_name        => 'FGA_EMP_SALARY',
    audit_condition    => 'SALARY >= 10000',
    audit_column       => 'SALARY',
    handler_schema     => NULL,
    handler_module     => NULL,
    enable             => TRUE,
    statement_types    => 'SELECT',
    audit_column_opts  => DBMS_FGA.ANY_COLUMNS
  );
END;
/

BEGIN
  DBMS_FGA.ADD_POLICY(
    object_schema      => 'HR',
    object_name        => 'EMPLOYEES',
    policy_name        => 'FGA_EMP_SALARY_DML',
    audit_condition    => 'SALARY >= 10000',
    audit_column       => 'SALARY',
    statement_types    => 'SELECT,UPDATE,DELETE',
    audit_column_opts  => DBMS_FGA.ANY_COLUMNS,
    enable             => TRUE
  );
END;
/

-- どちらかの列を参照したら監査
audit_column      => 'SALARY,COMMISSION_PCT',
audit_column_opts => DBMS_FGA.ANY_COLUMNS

-- 両方の列を参照したら監査
audit_column      => 'SALARY,COMMISSION_PCT',
audit_column_opts => DBMS_FGA.ALL_COLUMNS

SELECT
    db_user,
    os_user,
    userhost,
    object_schema,
    object_name,
    policy_name,
    statement_type,
    timestamp,
    sql_text
FROM dba_fga_audit_trail
WHERE policy_name = 'FGA_EMP_SALARY'
ORDER BY timestamp DESC;

SELECT
    dbusername,
    os_username,
    userhost,
    object_schema,
    object_name,
    fga_policy_name,
    action_name,
    event_timestamp,
    sql_text
FROM unified_audit_trail
WHERE fga_policy_name = 'FGA_EMP_SALARY'
ORDER BY event_timestamp DESC;

CREATE TABLE sec_admin.fga_alert_log (
  id            NUMBER GENERATED BY DEFAULT AS IDENTITY,
  object_schema VARCHAR2(128),
  object_name   VARCHAR2(128),
  policy_name   VARCHAR2(128),
  db_user       VARCHAR2(128),
  host_name     VARCHAR2(256),
  log_time      TIMESTAMP DEFAULT SYSTIMESTAMP,
  CONSTRAINT pk_fga_alert_log PRIMARY KEY (id)
);

CREATE OR REPLACE PROCEDURE sec_admin.fga_alert_handler(
  object_schema IN VARCHAR2,
  object_name   IN VARCHAR2,
  policy_name   IN VARCHAR2
) AUTHID DEFINER
IS
BEGIN
  INSERT INTO sec_admin.fga_alert_log (
    object_schema,
    object_name,
    policy_name,
    db_user,
    host_name
  ) VALUES (
    object_schema,
    object_name,
    policy_name,
    SYS_CONTEXT('USERENV', 'SESSION_USER'),
    SYS_CONTEXT('USERENV', 'HOST')
  );
END;
/

BEGIN
  DBMS_FGA.ADD_POLICY(
    object_schema      => 'HR',
    object_name        => 'EMPLOYEES',
    policy_name        => 'FGA_EMP_SALARY_ALERT',
    audit_condition    => 'SALARY >= 10000',
    audit_column       => 'SALARY',
    handler_schema     => 'SEC_ADMIN',
    handler_module     => 'FGA_ALERT_HANDLER',
    statement_types    => 'SELECT,UPDATE',
    audit_column_opts  => DBMS_FGA.ANY_COLUMNS,
    enable             => TRUE
  );
END;
/

-- FGAポリシー一覧を確認
SELECT object_schema, object_name, policy_name, enabled, sel, ins, upd, del
FROM dba_audit_policies
WHERE object_schema = 'HR'
  AND object_name = 'EMPLOYEES'
ORDER BY policy_name;

-- 無効化
BEGIN
  DBMS_FGA.DISABLE_POLICY(
    object_schema => 'HR',
    object_name   => 'EMPLOYEES',
    policy_name   => 'FGA_EMP_SALARY'
  );
END;
/

-- 再有効化
BEGIN
  DBMS_FGA.ENABLE_POLICY(
    object_schema => 'HR',
    object_name   => 'EMPLOYEES',
    policy_name   => 'FGA_EMP_SALARY'
  );
END;
/

-- 削除
BEGIN
  DBMS_FGA.DROP_POLICY(
    object_schema => 'HR',
    object_name   => 'EMPLOYEES',
    policy_name   => 'FGA_EMP_SALARY'
  );
END;
/

-- SALARY列を参照し、条件に合う可能性があるため監査対象
SELECT employee_id, last_name, salary
FROM hr.employees
WHERE salary >= 10000;

-- SALARY列を参照しないため、audit_column指定によっては監査されない
SELECT employee_id, last_name
FROM hr.employees
WHERE department_id = 10;

-- UPDATEもstatement_typesに含めていれば監査対象
UPDATE hr.employees
SET salary = salary
WHERE salary >= 10000;