so I created a function for hashing and it worked but how do I return it to the original text
CREATE TABLE "USERS"
( "USER_ID" number,
"USER_NAME" varchar2(255) NOT NULL ENABLE,
"PASSWORD" varchar2(255) NOT NULL ENABLE,
PRIMARY KEY ("USER_ID") USING INDEX ENABLE,
CONSTRAINT "USERS_U1" UNIQUE ("USER_NAME") USING INDEX ENABLE
)
/
CREATE OR REPLACE FUNCTION HASH_PASSWORD
(p_user_name in varchar2,
p_password in varchar2)
return varchar2
is
l_password varchar2(255);
-- The following salt is an example.
-- Should probably be changed to another random string.
l_salt varchar2(255) := '2345USFGOJN2T3HW89EFGOBN23R5SDFGAKL';
begin
--
-- The following encryptes the password using a salt string and the
-- DBMS_OBFUSCATION_TOOLKIT.
-- This is a one-way encryption using MD5
--
l_password := utl_raw.cast_to_raw (
dbms_obfuscation_toolkit.md5(
input_string => p_password ||
substr(l_salt,4,14) ||
p_user_name ||
substr(l_salt,5,10)));
return l_password;
end hash_password;
/
CREATE OR REPLACE TRIGGER BIU_USERS
before insert or update on users
for each row
begin
if inserting then
-- Get a unique sequence value to use as the primary key
select users_seq.nextval into :new.user_id from dual;
end if;
-- Make sure to save the username in upper case
:new.user_name := upper(:new.user_name);
if :new.password is not null then
-- Make sure to hash the password so it is not stored in clear text
-- Hash the password so we are not saving clear text
:new.password := hash_password(upper(:new.user_name), :new.password);
-- If the password is empty
else
-- Keep the old hashed password. We don't want a blank password.
:new.password := :old.password;
end if;
end;
/
CREATE OR REPLACE FUNCTION AUTHENTICATE_USER
(p_username in varchar2,
p_password in varchar2)
return boolean
is
l_user_name users.user_name%type := upper(p_username);
l_password users.password%type;
l_hashed_password varchar2(1000);
l_count number;
begin
-- Returns from the AUTHENTICATE_USER function
-- 0 Normal, successful authentication
-- 1 Unknown User Name
-- 2 Account Locked
-- 3 Account Expired
-- 4 Incorrect Password
-- 5 Password First Use
-- 6 Maximum Login Attempts Exceeded
-- 7 Unknown Internal Error
--
-- First, check to see if the user exists
select count(*)
into l_count
from users
where user_name = l_user_name;
if l_count > 0 then
-- Hash the password provided
l_hashed_password := hash_password(l_user_name, p_password);
-- Get the stored password
select password
into l_password
from users
where user_name = l_user_name;
-- Compare the two, and if there is a match, return TRUE
if l_hashed_password = l_password then
-- Good result.
APEX_UTIL.SET_AUTHENTICATION_RESULT(0);
return true;
else
-- The Passwords didn't match
APEX_UTIL.SET_AUTHENTICATION_RESULT(4);
return false;
end if;
else
-- The username does not exist
APEX_UTIL.SET_AUTHENTICATION_RESULT(1);
return false;
end if;
-- If we get here then something weird happened.
APEX_UTIL.SET_AUTHENTICATION_RESULT(7);
return false;
exception
when others then
-- We don't know what happened so log an unknown internal error
APEX_UTIL.SET_AUTHENTICATION_RESULT(7);
-- And save the SQL Error Message to the Auth Status.
APEX_UTIL.SET_CUSTOM_AUTH_STATUS(sqlerrm);
return false;
end authenticate_user;
/