Hi, I am using database 11.2.0.4 and SQL Developer 4.0.3.16 to build application in APEX 4.2.5.00.08. Until now I have always used APEX authentication, or AD function. I would like to create a new custom authentication scheme, with encrypted passwords, using a PL/SQL package. I copied and pasted most of this from discussion threads. I have always wanted to be a programmer, alas, my hopes are fading. I know you folks can do this in your sleep. I can create the user and encrypt the password, however, I can't authenticate against the encrypted password. I may have mucked this up while trying different threads. I think the problem is with the process on the page, or I may not be passing the correct parameters. P101_PASSWORD is an APEX password item type. When I comment out --AND student_password = v_hash, it works, but of course, it is ignoring the password. I appreciate any helpful tips.
Peter
DECLARE
result number := 0;
BEGIN
result := AUTH_PKG.authenticate_user(p_user_name_in => :P101_USERNAME, p_password_in => :P101_PASSWORD);
IF (result = 0)
THEN
wwv_flow_custom_auth_std.post_login
(P_UNAME => :P101_USERNAME,
P_PASSWORD => :P101_PASSWORD,
P_SESSION_ID => v('APP_SESSION'),
P_FLOW_PAGE => :APP_ID||':1');
ELSE
owa_util.redirect_url('f?p=&APP_ID.:101:&SESSION.');
END IF;
END;
The error is ......additional_info: ORA-20001: An XXX error occurred in authenticate user - ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
CREATE TABLE "STUDENT"
( "STUDENT_ID" NUMBER,
"EMAIL" VARCHAR2(100 BYTE),
"STUDENT_PASSWORD" VARCHAR2(100 BYTE),
CONSTRAINT "STUDENT_PK" PRIMARY KEY ("STUDENT_ID")
create or replace PACKAGE BODY "AUTH_PKG" AS
PROCEDURE adduser(p_user_name_in IN student.email%TYPE, p_password_in IN student.student_password%TYPE)
is
v_pwd_hash varchar2(2000);
begin
v_pwd_hash := get_enc_val(p_password_in, 'AL32UTF8');
insert into student (email, student_password) values(p_user_name_in, v_pwd_hash);
commit;
end adduser;
FUNCTION authenticate_user(p_user_name_in IN student.email%TYPE, p_password_in IN student.student_password%TYPE)
RETURN number
IS
l_username student.email%TYPE := p_user_name_in;
l_password student.student_password%TYPE := p_password_in;
l_count NUMBER := 0;
v_hash varchar2(2000);
BEGIN
v_hash := get_dec_val(p_password_in, 'AL32UTF8');
SELECT count(*)
INTO l_count
FROM student
WHERE UPPER(email) = UPPER(l_username)
AND student_password = v_hash
AND NVL(num_invalid_logins, 0) < l_max_failed_logins;
IF l_count = 1 THEN
apex_util.set_session_state('USERNAME', UPPER(l_username));
apex_util.set_session_state('LOGIN_MESSAGE', 'count=1');
reset_invalid_login_count(l_username);
RETURN 1;
ELSE
apex_util.set_session_state('LOGIN_MESSAGE', 'count<>1');
update_invalid_login_count(l_username);
l_failed_logins_count := GET_NUM_INVALID_LOGINS(l_username);
IF l_failed_logins_count > l_max_failed_logins THEN
lock_user_account(l_username);
apex_util.set_session_state('LOGIN_MESSAGE', 'Your account has been locked.');
END IF;
RETURN 0;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'An XXX error occurred in authenticate user - ' || SQLERRM);
END authenticate_user;
function get_enc_val(p_in in raw, p_key in raw) return raw is
l_enc_val varchar2(2000);
l_mod number := dbms_crypto.ENCRYPT_AES128+dbms_crypto.CHAIN_CBC+dbms_crypto.PAD_PKCS5;
begin
l_enc_val := dbms_crypto.encrypt(UTL_I18N.STRING_TO_RAW(p_in,'AL32UTF8'),l_mod,p_key);
return l_enc_val;
end;
function get_dec_val (p_in in raw,p_key in raw) return raw
is
l_ret raw (2000);
l_dec_val raw (2000);
l_mod number := dbms_crypto.ENCRYPT_AES128+ dbms_crypto.CHAIN_CBC+ dbms_crypto.PAD_PKCS5;
begin
l_dec_val := dbms_crypto.decrypt(p_in,l_mod,p_key);
l_ret:= UTL_I18N.RAW_TO_CHAR(l_dec_val, 'AL32UTF8');
return l_ret;
end;
END AUTH_PKG;
Message was edited by: 2775296 Hi, the original post had get_enc_val(p_in in varchar2, p_key in raw). I tried changing data types from raw to varchar2, and varchar2 to raw, but I am not sure which ones should be raw or varchar2. I got the same result. I also tried changing v_hash := get_dec_val(p_password_in, 'AL32UTF8'); to v_hash := get_enc_val(p_password_in, 'AL32UTF8'); I got the same result. I don't think get_dec_val is needed for this. Any explanation is helpful. Thanks.
Peter