Created
November 13, 2009 03:58
-
-
Save eight/233572 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* ユーザ認証管理パッケージ | |
* テーブル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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment