Folgendes Package habe ich mal im Netz gefunden und in einer meiner Anwendungen integriert. Es dient als "Authentication Schemes" und kann gleichzeitig neue User anlegen.

Als Scheme packst du es mit folgendem Aufruf zu "Authentication Function":

RETURN APP_SECURITY_PKG.valid_user;

Auf Seite 101 habe ich noch zu einer anderen Seite verlinkt, auf der sich die User dann mit den gewünschten Angaben neu anlegen können. Dazu bedarf es dann dem Page-Process: app_security_pkg.add_user.

Hier die Specs des Packages:

create or replace PACKAGE app_security_pkg
AS
PROCEDURE add_user
(
p_username IN VARCHAR2
,p_password IN VARCHAR2
,p_vorname IN VARCHAR2
,p_nachname IN VARCHAR2
,p_mail IN VARCHAR2
);

PROCEDURE login
(
p_uname IN VARCHAR2
,p_password IN VARCHAR2
,p_session_id IN VARCHAR2
,p_flow_page IN VARCHAR2
);

FUNCTION get_hash
(
p_username IN VARCHAR2
,p_password IN VARCHAR2
)
RETURN VARCHAR2;

PROCEDURE valid_user2
(
p_username IN VARCHAR2
,p_password IN VARCHAR2
);

FUNCTION valid_user
(
p_username IN VARCHAR2
,p_password IN VARCHAR2
)
RETURN BOOLEAN;

END app_security_pkg;

Und hier der Body:

create or replace PACKAGE BODY app_security_pkg
AS
PROCEDURE login
(
p_uname IN VARCHAR2
,p_password IN VARCHAR2
,p_session_id IN VARCHAR2
,p_flow_page IN VARCHAR2
)
IS
lv_goto_page NUMBER DEFAULT 1;
lv_message varchar2(4000);
BEGIN

-- This logic is a demonstration of how to redirect
-- to different pages depending on who successfully
-- authenticates. In my example, it simply demonstrates
-- the ADMIN user going to page 1 and all other users going
-- to page 100. Add you own logic here to detrmin which page
-- a user should be directed to post authentication.
IF UPPER(p_uname) = 'ADMIN'
THEN
lv_goto_page := 1;
ELSE
lv_goto_page := 1;
END IF;

wwv_flow_custom_auth_std.login
(
p_uname => p_uname,
p_password => p_password,
p_session_id => p_session_id,
p_flow_page => p_flow_page || ':' || lv_goto_page
);

EXCEPTION
WHEN OTHERS
THEN
lv_message := sqlerrm;

insert into sqlerrmessages(text) values(lv_message);
commit;
END login;

PROCEDURE add_user
(
p_username IN VARCHAR2
,p_password IN VARCHAR2
,p_vorname IN VARCHAR2
,p_nachname IN VARCHAR2
,p_mail IN VARCHAR2
)
AS
BEGIN
INSERT INTO app_users (username, PASSWORD, vorname, nachname, mail)
VALUES (UPPER (p_username),
get_hash(TRIM(p_username), p_password),
p_vorname,
p_nachname,
p_mail);

COMMIT;

EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
RAISE;
END add_user;

-- Function to Perform a oneway hash of the users
-- passwords. This cannot be reversed. This exmaple
-- is a very week hash and if been used on a production
-- system, you may want to use a stronger hash algorithm.
-- Read the Documentation for more info on DBMS_CRYPTO as
-- this is the supported package from Oracle and
-- DBMS_OBFUSCATION_TOOLKIT is now depricated.
FUNCTION get_hash (p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
RETURN DBMS_OBFUSCATION_TOOLKIT.md5 (
input_string => UPPER (p_username)
|| '/'
|| UPPER (p_password));
END get_hash;

PROCEDURE valid_user2 (p_username IN VARCHAR2, p_password IN VARCHAR2)
AS
v_dummy VARCHAR2 (1);
BEGIN
SELECT '1'
INTO v_dummy
FROM app_users
WHERE UPPER (username) = UPPER (p_username)
AND PASSWORD = get_hash(p_username, p_password);
EXCEPTION
WHEN NO_DATA_FOUND
THEN raise_application_error (-20000, 'Invalid username / password.');
END valid_user2;

FUNCTION valid_user (p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN BOOLEAN
AS
vl_message varchar2(4000);
BEGIN
valid_user2 (UPPER (p_username), p_password);
RETURN TRUE;
EXCEPTION
WHEN OTHERS
THEN
vl_message := sqlerrm;

insert into sqlerrmessages(text) values(vl_message);
commit;
RETURN FALSE;
END valid_user;

END app_security_pkg;

--
Best regards,
Carsten mailto:carsten.cerny@cc13.com
My blog [cc13.com]