The DPU_USER table stores information about DPU user accounts. DPU_USER( DPU_ID
ID: 3839912 • Letter: T
Question
The DPU_USER table stores information about DPU user accounts.
DPU_USER(
DPU_ID,
PASSWORD,
LAST_SUCCESSFUL_LOGIN_TIME,
LAST_FAILED_LOGIN_TIME,
FAILED_PASSWORD_ATTEMPT_COUNT,
IS_LOCKED_OUT,
LAST_LOCKED_OUT_TIME,
LAST_PASSWORD_CHANGED_TIME);
DPU_ID: Primary Key
FAILED_PASSWORD_ATTEMPT_COUNT: the number of consecutive failed password attempts
IS_LOCKED_OUT: 'Y' – the account is locked out
'N' – the account is not locked out
Create and populate the DPU_USER table as described below.
CREATE TABLE DPU_USER
( DPU_ID NUMBER PRIMARY KEY,
PASSWORD VARCHAR2(30) NOT NULL,
LAST_SUCCESSFUL_LOGIN_TIME DATE,
LAST_FAILED_LOGIN_TIME DATE,
FAILED_PASSWORD_ATTEMPT_COUNT NUMBER,
IS_LOCKED_OUT CHAR NOT NULL
CONSTRAINT CK_DPU_USER CHECK (IS_LOCKED_OUT IN ('Y','N')),
LAST_LOCKED_OUT_TIME DATE,
LAST_PASSWORD_CHANGED_TIME DATE);
/
INSERT INTO DPU_USER
VALUES(82001, '99CpsBTKpN1',TO_DATE('02-APR-2015 14:05:08', 'DD-MON-YYYY HH24:MI:SS'),
NULL, 0, 'N', NULL, TO_DATE('02-DEC-2014 13:05:08', 'DD-MON-YYYY HH24:MI:SS'));
INSERT INTO DPU_USER
VALUES(82002, 'ZWNWnQJT901', TO_DATE('02-DEC-2014 16:15:01', 'DD-MON-YYYY HH24:MI:SS'),
NULL, 0, 'N', NULL, TO_DATE('02-NOV-2014 11:11:18', 'DD-MON-YYYY HH24:MI:SS'));
INSERT INTO DPU_USER
VALUES(82003, 'gc88Wmvpx81', TO_DATE('01-APR-2015 19:15:08', 'DD-MON-YYYY HH24:MI:SS'),
TO_DATE('05-MAY-2015 21:45:18', 'DD-MON-YYYY HH24:MI:SS'), 1, 'N', NULL, TO_DATE('30-JAN-2015 23:01:01', 'DD-MON-YYYY HH24:MI:SS'));
INSERT INTO DPU_USER
VALUES(82004, 'KcxweSYg5551', TO_DATE('03-JAN-2015 14:12:33', 'DD-MON-YYYY HH24:MI:SS'),
TO_DATE('06-MAY-2015 09:12:22', 'DD-MON-YYYY HH24:MI:SS'), 5, 'Y', TO_DATE('06-MAY-2015 09:12:22', 'DD-MON-YYYY HH24:MI:SS'), NULL);
INSERT INTO DPU_USER
VALUES(82005, 'CDYe44BBXd11', TO_DATE('22-MAR-2015 05:22:18', 'DD-MON-YYYY HH24:MI:SS'),
NULL, 0, 'N', NULL, NULL);
INSERT INTO DPU_USER
VALUES(82006, 'vhSDHMDg6661', TO_DATE('07-FEB-2015 04:00:08', 'DD-MON-YYYY HH24:MI:SS'),
NULL, 0, 'N', NULL, TO_DATE('01-FEB-2015 04:35:01', 'DD-MON-YYYY HH24:MI:SS'));
COMMIT;
1)
Based on the DPU_USER table created in (1), create a procedure for validating user login.
The procedure header is
CREATE OR REPLACE PROCEDURE check_dpu_user_login
(
in_dpu_id NUMBER,
in_password VARCHAR2,
out_code OUT VARCHAR2
)
(You cannot change the procedure header. You will get a zero point if a different procedure header is used. Submitting more than one procedure will receive 0 points. No DBMS_OUTPUT statement is needed in the procedure.)
An account will be locked after 5 consecutive failed login attempts.
A password is case-sensitive.
The logical steps are as follows;
The value of in_dpu_id is not in the DPU_ID column of the DPU_USER table.
You assign 'err1' to the out_code parameter.
The value of in_dpu_id is in the DPU_ID column of the DPU_USER table, but the corresponding account is locked out (IS_LOCKED_OUT = 'Y').
You assign 'err2' to the out_code parameter.
The value of in_dpu_id is in the DPU_ID column of the DPU_USER table and the value of in_password matches the corresponding password in the table. The corresponding row in the DPU_USER table will be updated:
LAST_SUCCESSFUL_LOGIN_TIME <== SYSDATE
FAILED_PASSWORD_ATTEMPT_COUNT <== 0
LAST_FAILED_LOGIN_TIME <== NULL
You assign 'ok' to the out_code parameter.
The value of in_dpu_id is in the DPU_ID column of the DPU_USER table, but the value of in_password does not match the corresponding password in the table. You update the corresponding row in the DPU_USER table and the out_code parameter:
FAILED_PASSWORD_ATTEMPT_COUNT <==
FAILED_PASSWORD_ATTEMPT_COUNT + 1
LAST_FAILED_LOGIN_TIME <== SYSDATE
Case 1: FAILED_PASSWORD_ATTEMPT_COUNT != 5
You assign 'err3' to the out_code parameter.
Case 2: FAILED_PASSWORD_ATTEMPT_COUNT = 5
IS_LOCKED_OUT <== 'Y'
LAST_LOCKED_OUT_TIME <== SYSDATE
You assign 'err4' to the out_code parameter.
Hints: UPDATE DPU_USER SET …… WHERE DPU_ID = in_dpu_id;
out_code := ……;
You need to test your procedure with different parameters in a PL/SQL block.
Explanation / Answer
CREATE OR REPLACE PROCEDURE check_dpu_user_login(in_dpu_id NUMBER, in_password VARCHAR2, out_code OUT VARCHAR2)
IS
is_present number(1);
is_locked char(1);
pass DPU_USER.PASSWORD%TYPE;
failed_count DPU_USER.FAILED_PASSWORD_ATTEMPT_COUNT%TYPE;
BEGIN
select count(*) into is_present from DPU_USER where DPU_ID = in_dpu_id;
if is_present = 0 then
out_code := 'err1';
return;
end if;
select IS_LOCKED_OUT,PASSWORD,FAILED_PASSWORD_ATTEMPT_COUNT into is_locked,pass,failed_count from DPU_USER where DPU_ID = in_dpu_id;
if is_locked = 'Y' or is_locked = 'y' then
out_code := 'err2';
return;
end if;
if pass != in_password then
failed_count := failed_count + 1;
if failed_count = 5 then
UPDATE DPU_USER
set FAILED_PASSWORD_ATTEMPT_COUNT = failed_count,
LAST_FAILED_LOGIN_TIME = sysdate,
IS_LOCKED_OUT = 'Y',
LAST_LOCKED_OUT_TIME = sysdate;
out_code := 'err4';
commit;
return;
else
UPDATE DPU_USER
set FAILED_PASSWORD_ATTEMPT_COUNT = failed_count,
LAST_FAILED_LOGIN_TIME = sysdate;
out_code := 'err3';
commit;
return;
end if;
else
UPDATE DPU_USER
set LAST_SUCCESSFUL_LOGIN_TIME = sysdate,
FAILED_PASSWORD_ATTEMPT_COUNT = 0,
LAST_FAILED_LOGIN_TIME = sysdate;
commit;
end if;
END;
/
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.