I have an Oracle stored procedure written by someone else which authenticates
users into a system. I'm trying to use Cold Fusion's(vers 5) cfstoredproc to
grant user's access to this system via a form i.e.
<CFSTOREDPROC PROCEDURE="ame.ame_pass"
dataSource="mydsn"
username="myusername"
password="mypassword">
<CFPROCPARAM TYPE="IN"
CFSQLTYPE="CF_SQL_INTEGER"
dbvarname=n_key
VALUE="#Form.username#"
NULL="NO">
<CFPROCPARAM TYPE="IN"
CFSQLTYPE="CF_SQL_VARCHAR"
dbvarname=n_pass
VALUE="#Form.password#"
NULL="NO">
<CFPROCRESULT NAME = "TCID">
</CFSTOREDPROC>
My problem is I can't figure out what to enter in the cfstoredproc to get the
sp 'Y', 'W', 'A' or 'E' characters returned.
The sp is:-
CREATE OR REPLACE PACKAGE BODY ame AS
Procedure ame_test (n_key IN NUMBER,
io_cursor IN OUT t_cursor)
IS
v_cursor t_cursor;
BEGIN
IF n_key <> 0
THEN
OPEN v_cursor FOR
SELECT p_key,value
FROM ame_access
where p_key = n_key;
ELSE
OPEN v_cursor FOR
SELECT p_key,value
FROM ame_access;
END IF;
io_cursor := v_cursor;
END ame_test;
Procedure ame_pass (n_key IN NUMBER,
n_pass IN VARCHAR,
io_cursor IN OUT t_cursor)
IS
v_cursor t_cursor;
-- Password Cursor
cursor c1 is
SELECT p_key
FROM ame_admin
where p_key = to_char(n_key)
and property = 'T-C PASSWORD'
and pw_dec(value) = upper(n_pass);
c1rec c1%rowtype;
p_found varchar2(10);
-- Password Change Cursor
cursor c2 is
SELECT value
FROM ame_admin
where p_key = to_char(n_key)
and property = 'T-C DATE PASSWD';
c2rec c2%rowtype;
-- Attempts Cursor
cursor c3 is
SELECT value
FROM ame_admin
where p_key = to_char(n_key)
and property = 'T-C ATTEMPTS';
c3rec c3%rowtype;
p_attempts_exceeded char;
BEGIN
IF n_key <> 0
THEN
open c1;
fetch c1 into c1rec;
-- If c1 is found then pwd is valid
if c1%found then
open c3;
fetch c3 into c3rec;
if c3%found then
-- Check attempts < 4
if c3rec.value <4 or c3rec.value > 49 then
p_found := 'Y';
open c2;
fetch c2 into c2rec;
if c2%found then
-- Check if password has expired
if to_date(c2rec.value,'DD-MON-YYYY')+60 < sysdate then
p_found := 'E';
-- Check if password will expire soon
elsif to_date(c2rec.value,'DD-MON-YYYY')+30 < sysdate then
p_found := 'W';
end if;
end if;
else
-- if attempts > 3 then send ein and attempts exceeded flag
OPEN v_cursor FOR
SELECT p_key,'A'
FROM ame_admin
where p_key = to_char(n_key)
and property = 'T-C PASSWORD';
end if;
end if;
close c3;
else
-- Incorrect Password
p_found := 'N';
end if;
close c1;
if p_found in('Y','W') then
/*********************************************************************/
/* Successful Login */
/* There are two successful login flags */
/* Y = Normal Login */
/* W = Warning message password will expire in x days */
/*********************************************************************/
-- Return ein and Y flag
OPEN v_cursor FOR
SELECT p_key,p_found
FROM ame_admin
where p_key = to_char(n_key)
and property = 'T-C PASSWORD';
-- Update Login Date
UPDATE ame_admin
SET value = TO_CHAR(sysdate,'DD-Mon-YYYY HH24:MI "W"')
WHERE property = 'T-C DATE ACCESS'
and p_key = to_char(n_key);
-- Set attempts to 0
UPDATE ame_admin
SET value = 0
WHERE p_key = to_char(n_key)
AND property = 'T-C ATTEMPTS';
elsif p_found = 'N' then
/*********************************************************************/
/* Unsuccessful Login */
/* There are two unsuccessful login flags */
/* A= Attempts Exceeded */
/* E= Password Expired */
/*********************************************************************/
open c3;
fetch c3 into c3rec;
if c3%found then
-- increment unsuccessful attempts
UPDATE ame_admin
SET value = c3rec.value +1
WHERE p_key = to_char(n_key)
AND property = 'T-C ATTEMPTS';
commit;
-- Check login attempts and set attempts flag
if nvl(c3rec.value,0) > 3 then
p_attempts_exceeded := 'Y';
else
p_attempts_exceeded := 'N';
end if;
end if;
if p_attempts_exceeded = 'Y' then
-- Attempts exceeded return ein and attempts exceeded
OPEN v_cursor FOR
SELECT p_key,'A'
FROM ame_admin
where p_key = to_char(n_key)
and property = 'T-C PASSWORD';
elsif p_attempts_exceeded = 'N' then
-- Return ein and invalid password
OPEN v_cursor FOR
SELECT p_key,'N'
FROM ame_admin
where p_key = to_char(n_key)
and property = 'T-C PASSWORD';
else
OPEN v_cursor FOR
SELECT p_key,'N'
FROM ame_admin
where p_key = to_char(n_key)
and property = 'T-C PASSWORD';
end if;
elsif p_found = 'E' then
-- Return ein and password expired
OPEN v_cursor FOR
SELECT p_key,'E'
FROM ame_admin
where p_key = to_char(n_key)
and property = 'T-C PASSWORD';
end if;
io_cursor := v_cursor;
END IF;
END ame_pass;
END ame;
/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185424
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
