Oracle 實戰經驗（四）：資料加密觸發器與自定義加密函數

1. 資料庫觸發器：攔截 INSERT/UPDATE 自動加密

DROP TRIGGER scjmmm;
CREATE OR REPLACE TRIGGER scjmmm
BEFORE INSERT OR UPDATE OF mm ON ghxxb
FOR EACH ROW
BEGIN
  :new.mm := encrypt(:new.mm, :new.gh, TO_CHAR(SYSDATE, 'SS'));
END;
/

2. 加密函數 ENCRYPT

CREATE OR REPLACE FUNCTION encrypt (
  inpass  IN VARCHAR2,
  in_gh   IN VARCHAR2,
  in_ss   IN VARCHAR2
) RETURN VARCHAR2 IS
  bcs   VARCHAR2(20);
  bcs1  NUMBER;
  cs    NUMBER;
  jg    NUMBER;
  m_gh  VARCHAR2(4);
  m_mm  VARCHAR2(20);
BEGIN
  m_gh := in_gh;
  m_mm := inpass;
  cs   := TO_NUMBER(in_ss);
  IF cs <= 1 THEN cs := 77; END IF;

  bcs := SUBSTR(TO_CHAR(ASCII(SUBSTR(m_gh,1,1))),1,2);
  IF bcs < '1' THEN bcs := '7'; END IF;
  m_gh := SUBSTR(m_gh,2);

  LOOP EXIT WHEN NVL(LENGTH(m_gh),0)=0;
    bcs := bcs || SUBSTR(TO_CHAR(ASCII(SUBSTR(m_gh,1,1))),-1,1);
    m_gh := SUBSTR(m_gh,2);
  END LOOP;

  LOOP EXIT WHEN NVL(LENGTH(m_mm),0)=0;
    bcs := bcs || SUBSTR(TO_CHAR(ASCII(SUBSTR(m_mm,1,1))),-1,1);
    m_mm := SUBSTR(m_mm,2);
  END LOOP;

  bcs1 := TO_NUMBER(bcs);
  jg   := cs * bcs1;
  LOOP EXIT WHEN LENGTH(TO_CHAR(jg)) > 13;
    jg := jg * cs;
  END LOOP;

  RETURN (in_ss || SUBSTR(TO_CHAR(jg),1,14));
END;
/
GRANT EXECUTE ON encrypt TO PUBLIC;

演算法說明

1. 取工號 gh 首字元 ASCII 碼前兩位作為種子；若 <1 則用 7。
2. 工號剩餘字元取 ASCII 碼最後一位拼接。
3. 密碼 mm 每字元取 ASCII 碼最後一位拼接。
4. 組成數字串 bcs，轉數值 bcs1。
5. 以秒數 ss（cs）為乘數，反覆相乘直到結果超過 13 位。
6. 回傳 ss + 前14位結果 作為密文。

安全性提醒

• 此為簡易混淆演算法，非加密學安全，僅防誤讀。
• 生產環境建議使用 DBMS_CRYPTO（AES/3DES）或透明資料加密 (TDE)。
• 金鑰管理、鹽值、初始化向量等機制此處未涉及。