ユーザIDやパスワードは、OracleのDBMS_OBFUSCATION_TOOLKITパッケージを使って暗号化して保管するPL/SQLパッケージ。DBMS_OBFUSCATION_TOOLKITを使っているので、Oracle9i以降が必要（たぶん）。
ユーザ情報管理機能の必要なアプリケーションで使うと便利かもしれない。
もう使ってはいけないとどこかで読んだような気もするmd5でハッシュをとっている部分は、簡単な変更で入れ替えることもできるはず。
このパッケージを使うことで、アプリケーションからは暗号化/復号化の処理、テーブルの列構成の詳細を隠蔽することができるはず。
小道具というにはちょっと大きいかも。
http://gist.github.com/233572
/**
* ユーザ認証管理パッケージ
* テーブルUSER_AUTHとパッケージAUTH_PKGで構成される。
* ユーザIDとパスワードはMD5ダイジェストとして格納される。
* Oracle9i以降専用!!
*
* ユーザIDの管理（あるユーザIDは誰か？など）は当パッケージを利用するシステムの責任
* ユーザIDを登録（REGIST_USER）、削除（DROP）、パスワード認証（AUTHENTICATE）などを管理する
*
* クラス：ユーザIDのクラス。ユーザIDはクラス内でユニークである必要がある。
* ユーザID：何文字でもいい。内部ではMD5のハッシュで管理している。クラス内でユニークとなること。
* パスワード：何文字でもいい。内部ではMD5のハッシュで管理している
* 複数のクラスを管理する場合は、SET_USERCLASSを呼び出してから他の操作をすること。
* USERCLASS()で現在どのクラスを使っているかを知ることができる。
* 最初にAUTH_PKG.SET_USERCLASS(ユーザクラス）を呼び出して使うクラスの設定を初期化する。
* この初期化をしないと、クラスとして「DEFAULT」が設定される。
* 初期化しないで使う場合は、全関数でユーザクラスを最初の引数に指定する。
*
* RETURN_CODE_TRUEとRETURN_CODE_FALSEはFUNCTIONが返す論理値に割り当てる文字列を指定する。
* RETURN_CODE_TRUEにはTRUEの時に返す文字列。デフォルトは'Y'。
* RETURN_CODE_FALSEにはFASEの時に返す文字列。デフォルトは'N'。
*
* テーブル名
* USER_AUTH
*
* 順序名
* SEQ_USER_AUTH_ID
*
*/
/******************************************************************
ユーザ認証管理パッケージ用の認証テーブル USER_AUTHの作成
*******************************************************************/
DROP SEQUENCE SEQ_USER_AUTH_ID;
CREATE SEQUENCE SEQ_USER_AUTH_ID;
DROP TABLE USER_AUTH;
CREATE TABLE USER_AUTH(
AuthId number(9,0) NOT NULL,
UserClass varchar2(10) NOT NULL,
UserId char(32) NOT NULL,
Password char(32) NOT NULL,
PasswordChanged date DEFAULT SYSDATE,
Updated date DEFAULT SYSDATE,
Created date NOT NULL
);
COMMENT ON TABLE USER_AUTH IS 'ユーザ認証システム：認証テーブル';
COMMENT ON COLUMN USER_AUTH.AuthID IS '認証ID AUTH_ID_SEQで発番された連番';
COMMENT ON COLUMN USER_AUTH.UserClass IS 'ユーザIDの属しているクラス';
COMMENT ON COLUMN USER_AUTH.UserID IS 'ユーザID：ユーザ入力値のMD5メッセージダイジェスト。';
COMMENT ON COLUMN USER_AUTH.Password IS 'パスワード：ユーザ入力値のMD5メッセージダイジェスト';
COMMENT ON COLUMN USER_AUTH.PasswordChanged IS '最後にパスワードを変更した日時';
COMMENT ON COLUMN USER_AUTH.Updated IS '更新日時';
COMMENT ON COLUMN USER_AUTH.Created IS '作成日時';
CREATE UNIQUE INDEX USER_AUTH_USERID ON USER_AUTH(UserClass,UserID);
ALTER TABLE USER_AUTH ADD CONSTRAINT USER_AUTH_PK PRIMARY KEY (AuthID);
/******************************************************************
AUTH_PKG本体の作成
*******************************************************************/
CREATE OR REPLACE PACKAGE AUTH_PKG AS
--グローバル変数定義
--TRUE/FALSEやOK/NGやYES/NOに変更してもいい
RETURN_CODE_TRUE VARCHAR2(10) := 'Y';
RETURN_CODE_FALSE VARCHAR2(10) := 'N';
--USERCLASSを設定しない場合のデフォルトユーザクラス
DEFAULT_USERCLASS CONSTANT VARCHAR2(10) := 'DEFAULT';
--初期登録でパスワードを指定しなかった場合にデフォルトで指定するパスワード
DEFAULT_PASSWORD CONSTANT VARCHAR2(32) := 'changeOnInstall';
-- パッケージのユーザクラスをセットする
PROCEDURE SET_USERCLASS(i_userclass IN VARCHAR2);
-- 設定されているユーザクラスを返す
FUNCTION USERCLASS RETURN VARCHAR2;
--パスワード変更
PROCEDURE CHANGE_PASSWORD(i_userclass IN VARCHAR2, i_userid IN VARCHAR2, i_password IN VARCHAR2);
PROCEDURE CHANGE_PASSWORD(i_userid IN VARCHAR2, i_password IN VARCHAR2);
--ユーザ完全削除
PROCEDURE DROP_USER(i_userclass IN VARCHAR2, i_userid IN VARCHAR2);
PROCEDURE DROP_USER(i_userid IN VARCHAR2);
--ユーザ登録
--パスワードを指定しないとデフォルト値が設定される
PROCEDURE REGIST_USER(i_userclass IN VARCHAR2, i_userid IN VARCHAR2, i_password IN VARCHAR2);
PROCEDURE REGIST_USER(i_userid IN VARCHAR2, i_password IN VARCHAR2);
--ユーザの存在チェック
FUNCTION USER_EXISTS(i_userclass IN VARCHAR2, i_userid IN VARCHAR2) RETURN CHAR;
FUNCTION USER_EXISTS(i_userid IN VARCHAR2) RETURN CHAR;
--認証（パスワードチェック）してOKならRETURN_CODE_TRUEを返す。失敗したらRETURN_CODE_FALSEを返す
FUNCTION AUTHENTICATE(i_userid IN VARCHAR2, i_password IN VARCHAR2) RETURN VARCHAR2;
FUNCTION AUTHENTICATE(i_userclass IN VARCHAR2, i_userid IN VARCHAR2, i_password IN VARCHAR2) RETURN VARCHAR2;
--最後にパスワードを変更した日時を返す
FUNCTION PASSWORD_CHANGED(i_userclass IN VARCHAR2, i_userid IN VARCHAR2) RETURN DATE;
FUNCTION PASSWORD_CHANGED(i_userid IN VARCHAR2) RETURN DATE;
--最後にパスワードを変更した日時が有効な日数(i_days)を過ぎていたらRETURN_CODE_TRUEを返す
FUNCTION PASSWORD_EXPIRED(i_userclass IN VARCHAR2, i_userid IN VARCHAR2, i_days IN NUMBER) RETURN CHAR;
FUNCTION PASSWORD_EXPIRED(i_userid IN VARCHAR2, i_days IN NUMBER) RETURN CHAR;
--指定した文字数のランダムな文字列を返す。初期パスワードの生成に。
FUNCTION RANDOM_STRING(i_string_length IN NUMBER DEFAULT 6) RETURN VARCHAR2;
--MD5 基本的には内部利用を想定しているけれど、別に外で使っても良い
FUNCTION MD5(input_string IN VARCHAR2) RETURN VARCHAR2;
--暗号化した文字列を返す 現バージョンはMD5を使った1方向だけに対応。
FUNCTION ENCRYPT(input_string IN VARCHAR2, ENCRYPT_METHOD IN VARCHAR2 DEFAULT 'MD5') RETURN VARCHAR2;
END AUTH_PKG;
/
CREATE OR REPLACE PACKAGE BODY AUTH_PKG AS
--パッケージローカルのセッション内変数
CURRENT_USERCLASS VARCHAR2(10) := DEFAULT_USERCLASS;
SEED_COUNTER number := 1;
----------------------------------------------------------
-- md5ダイジェストを得るためのストアードファンクション
-- USER_AUTHテーブルのpasswordフィールドにはmd5ダイジェストを記録する
----------------------------------------------------------
FUNCTION MD5(input_string IN VARCHAR2) RETURN VARCHAR2
IS
hex_digest varchar2(32);
digest varchar2(16);
BEGIN
digest := DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => input_string);
SELECT Lower(RAWTOHEX(digest)) INTO hex_digest FROM dual;
RETURN hex_digest;
END;
----------------------------------------------------------
-- 受け取った文字列を暗号化して返す
----------------------------------------------------------
FUNCTION ENCRYPT(input_string IN VARCHAR2, ENCRYPT_METHOD IN VARCHAR2 DEFAULT 'MD5') RETURN VARCHAR2
IS
BEGIN
IF ENCRYPT_METHOD = 'MD5' THEN
RETURN MD5(input_string);
ELSE
RAISE_APPLICATION_ERROR(-20003,'暗号化方法' || ENCRYPT_METHOD || 'が私には理解できません。');
END IF;
END;
----------------------------------------------------------
-- P 初期化する
----------------------------------------------------------
PROCEDURE SET_USERCLASS( i_userclass IN VARCHAR2)
IS
BEGIN
CURRENT_USERCLASS := i_userclass;
END;
----------------------------------------------------------
-- F CURRENT_USERIDを返す
----------------------------------------------------------
FUNCTION USERCLASS RETURN VARCHAR2
IS
BEGIN
RETURN CURRENT_USERCLASS;
END;
----------------------------------------------------------
-- P CHANGE_PASSWORD
----------------------------------------------------------
PROCEDURE CHANGE_PASSWORD
( i_userclass IN VARCHAR2,
i_userid IN VARCHAR2,
i_password IN VARCHAR2)
IS
ret_val CHAR(1) := RETURN_CODE_TRUE;
BEGIN
ret_val := USER_EXISTS(i_userclass, i_userid);
IF ret_val = RETURN_CODE_TRUE THEN
UPDATE USER_AUTH
SET
password = ENCRYPT(i_password),
PasswordChanged=SYSDATE,
Updated=SYSDATE
WHERE userclass = i_userclass
AND userid = ENCRYPT(i_userid);
END IF;
END;
PROCEDURE CHANGE_PASSWORD
( i_userid IN VARCHAR2,
i_password IN VARCHAR2)
IS
BEGIN
CHANGE_PASSWORD(CURRENT_USERCLASS, i_userid, i_password);
END;
----------------------------------------------------------
-- P DROP_USER 完全にレコードを削除する
----------------------------------------------------------
PROCEDURE DROP_USER (i_userclass IN VARCHAR2, i_userid IN VARCHAR2)
IS
BEGIN
DELETE USER_AUTH
WHERE userclass = i_userclass
AND userid = ENCRYPT(i_userid);
END;
PROCEDURE DROP_USER (i_userid IN VARCHAR2)
IS
BEGIN
DROP_USER(CURRENT_USERCLASS, i_userid);
END;
----------------------------------------------------------
-- P REGIST_USER
----------------------------------------------------------
PROCEDURE REGIST_USER(i_userclass IN VARCHAR2, i_userid IN VARCHAR2, i_password IN VARCHAR2)
IS
BEGIN
IF USER_EXISTS(i_userclass, i_userid)=RETURN_CODE_TRUE THEN
RAISE_APPLICATION_ERROR(-20002,'ご指定のユーザ' || i_userid || 'は既に存在しています。');
ELSE
INSERT INTO USER_AUTH(authid,userclass,userid,password,updated,created)
VALUES(SEQ_USER_AUTH_ID.NEXTVAL,i_userclass,ENCRYPT(i_userid),ENCRYPT(i_password),SYSDATE,SYSDATE);
END IF;
END;
PROCEDURE REGIST_USER( i_userid IN VARCHAR2, i_password IN VARCHAR2)
IS
BEGIN
REGIST_USER(CURRENT_USERCLASS, i_userid, i_password);
END;
----------------------------------------------------------
-- F USER_EXISTS ユーザが存在したらRETURN_CODE_TRUE、存在しなければRETURN_CODE_FALSEを返す
----------------------------------------------------------
FUNCTION USER_EXISTS(i_userclass IN VARCHAR2, i_userid IN VARCHAR2)
RETURN CHAR
IS
r number;
BEGIN
SELECT COUNT(*) INTO r
FROM USER_AUTH
WHERE userclass=i_userclass
AND userid = ENCRYPT(i_userid);
IF r > 0 THEN
RETURN RETURN_CODE_TRUE;
ELSE
RETURN RETURN_CODE_FALSE;
END IF;
END;
FUNCTION USER_EXISTS( i_userid IN VARCHAR2)
RETURN CHAR
IS
BEGIN
RETURN USER_EXISTS(CURRENT_USERCLASS, i_userid);
END;
----------------------------------------------------------
-- F AUTH
----------------------------------------------------------
--認証（パスワードチェック）してOKならRETURN_CODE_TRUEを返す。失敗したらRETURN_CODE_FALSEを返す
FUNCTION AUTHENTICATE(
i_userclass IN VARCHAR2,
i_userid IN VARCHAR2,
i_password IN VARCHAR2
) RETURN VARCHAR2
IS
r number;
BEGIN
SELECT COUNT(*) INTO r
FROM USER_AUTH
WHERE userclass=i_userclass
AND userid = ENCRYPT(i_userid)
AND password = ENCRYPT(i_password);
IF r > 0 THEN
RETURN RETURN_CODE_TRUE;
ELSE
RETURN RETURN_CODE_FALSE;
END IF;
END;
--認証（パスワードチェック）してOKならRETURN_CODE_TRUEを返す。失敗したらRETURN_CODE_FALSEを返す
FUNCTION AUTHENTICATE(
i_userid IN VARCHAR2,
i_password IN VARCHAR2
) RETURN VARCHAR2
IS
BEGIN
RETURN AUTHENTICATE(CURRENT_USERCLASS, i_userid, i_password);
END;
----------------------------------------------------------
-- F PASSWORD
----------------------------------------------------------
--パスワードの変更日時を返す。
FUNCTION PASSWORD_CHANGED(
i_userclass IN VARCHAR2,
i_userid IN VARCHAR2
) RETURN DATE
IS
r date;
BEGIN
SELECT PasswordChanged INTO r
FROM USER_AUTH
WHERE userclass=i_userclass
AND userid = ENCRYPT(i_userid);
RETURN r;
END;
--パスワードの変更日時を返す。
FUNCTION PASSWORD_CHANGED(
i_userid IN VARCHAR2
) RETURN DATE
IS
BEGIN
RETURN PASSWORD_CHANGED(CURRENT_USERCLASS, i_userid);
END;
----------------------------------------------------------
-- F PASSWORD_EXPIRED
----------------------------------------------------------
--パスワードの有効期限が過ぎているかどうかをチェックしてRETURN_CODEを返す。
FUNCTION PASSWORD_EXPIRED(
i_userclass IN VARCHAR2,
i_userid IN VARCHAR2,
i_days IN NUMBER
) RETURN CHAR
IS
r date;
BEGIN
SELECT PASSWORD_CHANGED(i_userclass,i_userid) + i_days INTO r FROM DUAL;
IF r < SYSDATE THEN
RETURN RETURN_CODE_TRUE;
ELSE
RETURN RETURN_CODE_FALSE;
END IF;
END;
FUNCTION PASSWORD_EXPIRED(
i_userid IN VARCHAR2,
i_days IN NUMBER
) RETURN CHAR
IS
r date;
BEGIN
RETURN PASSWORD_EXPIRED(CURRENT_USERCLASS, i_userid);
END;
----------------------------------------------------------
-- F ランダムな文字列を返す
----------------------------------------------------------
FUNCTION RANDOM_STRING(i_string_length IN NUMBER DEFAULT 6) RETURN VARCHAR2
IS
seed number;
r char(32);
BEGIN
seed := to_number(to_char(sysdate,'SSSSS')) + SEED_COUNTER;
SEED_COUNTER := SEED_COUNTER + 1;
dbms_random.initialize(seed);
dbms_random.seed(seed);
r := MD5(to_char(abs(dbms_random.random())));
RETURN substr(r,-i_string_length,i_string_length);
END;
END AUTH_PKG;
