【サンプルあり】Oracle DBMS_CRYPTOを使った暗号化・復号化

はじめに

OracleのDBMS_CRYPTO.ENCRYPT／DBMS_CRYPTO.DECRYPTを使うとデータを暗号化したり、復号化することができます。
試しに使ってみました。

Oracleの現場を効率化する100の技

posted with amazlet at 15.06.13

技術評論社 (2015-05-29)
売り上げランキング: 8,686

Amazon.co.jpで詳細を見る

テスト用のテーブル

raw型のencNameというカラムを用意して、暗号化された氏名を格納します。

create table table01
(
 id char(8),           --ID
 name varchar(50),     --名前
 furigana varchar(50), --ふりがな
 seibetsu varchar(3),  --性別
 birthday char(8),     --生年月日
 encName raw(200),     --暗号化氏名
 primary key( id )
);

事前準備

暗号化のためのDBMS_CRYPTO.ENCRYPTを使うには、DBMS_CRYPTOの実行権限が必要です。
sysユーザでログインして、権限を付与します。

GRANT EXECUTE ON DBMS_CRYPTO TO ユーザ名;

暗号化したデータをinsertする

INSERT INTO table01
  (
    id,
    name,
    furigana,
    seibetsu,
    birthday,
    encName
  )
  VALUES
  (
    '00000001',
    'テスト　太郎',
    'てすと　たろう',
    '男',
    '20150612',
    DBMS_CRYPTO.ENCRYPT( 
      src => UTL_I18N.STRING_TO_RAW ('テスト　太郎',  'AL32UTF8'),
      typ => 8 + 256 + 4096,
      KEY => UTL_I18N.STRING_TO_RAW(RPAD('mypassword', 32, CHR(0)),  'AL32UTF8')
    )
  )
;

8 + 256 + 4096の部分は、暗号化方式、暗号ブロック連鎖、データ補完を指定しています。

DBMS_CRYPTO 使用方法〜暗号化〜 - オラクル・Oracleをマスターするための基本と仕組み

実行結果

selectしたデータを復号化する

SELECT NAME,
  UTL_I18N.RAW_TO_CHAR(
    DBMS_CRYPTO.DECRYPT(
      src => ENCNAME, 
      typ => 8 + 256 + 4096, 
      key => UTL_I18N.STRING_TO_RAW(RPAD('mypassword', 32, CHR(0)), 'AL32UTF8')
    )
    , 'AL32UTF8')
  AS "復号データ"
FROM TABLE01;

実行結果
f:id:replication:20150613155756p:plain

SQLにパスワードを埋め込まない

DBMS_CRYPTO.ENCRYPT／DECRYPTはパラメタとして暗号化方式やパスワードを渡す必要があり、これをSQL内に埋め込むのはセキュリティ上好ましくない。

SQL から直接利用する場合、暗号化する対象によっては、強度のある暗号化を使用しても SQL 文の中に暗号化に使用するパスフレーズが埋め込まれて暗号化されていないネットワーク上をそのまま流れることで暗号化する効果は低くなるので要注意。
DBMS_CRYPTO 使用方法～暗号化～ - オラクル・Oracleをマスターするための基本と仕組み

このため、暗号化・復号化のストアドファンクションとを登録しておくことで、SQL内にパスワードを記載しなくても良いようにします。

ストアドファンクション

--暗号化ファンクション
CREATE OR REPLACE FUNCTION MY_ENCRYPT(
  P_TEXT      VARCHAR2
)
RETURN VARCHAR2
IS
  vEncrypted  RAW(2000);
BEGIN
  vEncrypted := DBMS_CRYPTO.ENCRYPT(
    src => UTL_I18N.STRING_TO_RAW (P_TEXT,  'AL32UTF8'),
    typ => DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC
         + DBMS_CRYPTO.PAD_PKCS5,
    key => UTL_I18N.STRING_TO_RAW(RPAD('mypassword', 32, CHR(0)), 'AL32UTF8'));
  RETURN vEncrypted;
END;
/

--復号化ファンクション
CREATE OR REPLACE FUNCTION MY_DECRYPT(
  P_ENCRYPTED  VARCHAR2
)
RETURN VARCHAR2
IS
  vRaw  RAW(2000);
BEGIN
  vRaw := DBMS_CRYPTO.DECRYPT(
    src => P_ENCRYPTED,
    typ => DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC
         + DBMS_CRYPTO.PAD_PKCS5,
    key => UTL_I18N.STRING_TO_RAW(RPAD('mypassword', 32, CHR(0)), 'AL32UTF8'));
  RETURN UTL_I18N.RAW_TO_CHAR (vRaw, 'AL32UTF8');
END;
/

暗号化ファンクションを使ったinsert

INSERT INTO TABLE01
  (
    ID,
    NAME,
    FURIGANA,
    SEIBETSU,
    BIRTHDAY,
    ENCNAME
  )
  VALUES
  (
    '00000002',
    'テスト　太郎',
    'てすと　たろう',
    '男',
    '20150612',
    MY_ENCRYPT('テスト　太郎')
  )
;

復号化ファンクションを使ったselect

SELECT NAME, MY_DECRYPT(ENCNAME) AS "復号データ" FROM TABLE01;

小さい頃はエラ呼吸

いつのまにやら肺で呼吸をしています。